Jean T. Anderson wrote:
...
Erik's function worked for me when I changed this:
public final class Function
to this:
public class Function
sorry, I realized my reply could have used the complete example. I think
the problem may have been a CLASSPATH issue combined with the "final".
so here's the code I ran:
[EMAIL PROTECTED] test]$ cat org/jpox/store/rdbms/adapter/Function.java
package org.jpox.store.rdbms.adapter;
public class Function
{
/**
* ASCII conversion code
* @param code
* @return
*/
public static int ascii(String code)
{
return (int)code.charAt(0);
}
}
My current working directory is in my CLASSPATH:
[EMAIL PROTECTED] test]$ echo $CLASSPATH
/opt/Apache/db-derby-10.1.1.0-bin/lib/derby.jar:/opt/Apache/db-derby-10.1.1.0-bin/lib/derbytools.jar:.
Now I create a database, create the function, and invoke it:
[EMAIL PROTECTED] test]$ java org.apache.derby.tools.ij
ij version 10.1
ij> connect 'jdbc:derby:test;create=true';
ij> CREATE FUNCTION JPOX_ASCII(C CHAR(1)) RETURNS INTEGER
EXTERNAL NAME 'org.jpox.store.rdbms.adapter.Function.ascii'
RETURNS NULL ON NULL INPUT
LANGUAGE JAVA PARAMETER STYLE JAVA;
0 rows inserted/updated/deleted
ij> values jpox_ascii('c');
1
-----------
99
1 row selected
The "CALLED ON NULL INPUT" works, but as Dan pointed out earlier, that
could cause problems later:
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.
-jean
