db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Erik.Bengt...@intl.westernunion.com
Subject RE: cant' get user defined function to work
Date Mon, 26 Sep 2005 14:58:24 GMT
Dan,

That doesn't help. I already tried variations of the same function, using
the same code.

While using any "java.lang.Math" or other java.lang.Class.operation, derby
was able to retrieve and run the function. Where should I send the code to
reproduce it? 

Erik Bengtson
+32 2 639-7260


-----Original Message-----
From: djd@debrunners.com [mailto:djd@debrunners.com] 
Sent: Monday, September 26, 2005 3:37 PM
To: derby-user@db.apache.org
Subject: Re: cant' get user defined function to work

Erik.Bengtson@intl.westernunion.com 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.


RE:

Mime
View raw message