[EMAIL PROTECTED] wrote: > Hi, > > > > I’ve created the following function: > > DROP FUNCTION JPOX_ASCII; > > CREATE FUNCTION JPOX_ASCII(C CHAR(1)) RETURNS INTEGER > > EXTERNAL NAME 'org.jpox.store.rdbms.adapter.Function.ascii' > > CALLED ON NULL INPUT > > LANGUAGE JAVA PARAMETER STYLE JAVA; > [snip] > > When I try to use the function, I get the following: > > > > 'JPOX_ASCII' is not recognized as a function or procedure. > > ERROR 42Y03: 'JPOX_ASCII' is not recognized as a function or procedure. > > > > It works when the EXTERNAL NAME refers to a java.lang.* operation. I’ve > tried several other combinations on external name, and the function > parameters, and none of them was good. > > > > 1 - Is there another way where I can get the ASCII value for a CHAR(1) > column? > > 2 – How can I make the above function to work?
SQL functions and procedures are defined in a schema, so it's possible that the schema you are trying to execute the function from is not the schema the function was created in. You can define and call the function using an explicit schema, e.g. CREATE FUNCTION MYUTIL.JPOX_ASCII(C CHAR(1)) ... SELECT MYUTIL.JPOX_ASCII(col) FROM ... If that doesn't help, can you provide a reproducible case, because the external name being java.lang.* should not affect the visibility of the function. Also a potential problem with your function is that you define it as 'CALLED ON NULL INPUT', which means if a SQL NULL is passed to it a Java null will be passed in for the 'String code' value, and your function will get a NullPointerException. This can be avoided by defining the function as RETURNS NULL ON NULL INPUT. In this case if a SQL NULL is passed in the Java function will not be called and a SQL NULL will be returned. Dan.
