db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Jean T. Anderson" <...@bristowhill.com>
Subject Re: cant' get user defined function to work
Date Mon, 26 Sep 2005 16:49:20 GMT
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:

    [jta@gertie 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:

    [jta@gertie 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:

    [jta@gertie 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


Mime
View raw message