Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (asf.osuosl.org: local policy)
Subject: RE: cant' get user defined function to work
Date: Thu, 29 Sep 2005 11:30:06 +0200
MIME-Version: 1.0
To: derby-user@db.apache.org
Cc: 
From: Erik.Bengtson@intl.westernunion.com
Message-ID: <OF4A4A2CAA.5EF74297-ON4225708B.0033DBC8@hqintl1.com>
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit

Hi,

I was able to fix the problem, and of course it was on my side. Due to
several tries to make arguments match to datypes and the java method
arguments, I forgot to modify the code that was calling JPOX_ASCII('a'),
and instead it was generating JPOX_ASCII('a',10)

Sorry for taking your time and thanks for the help.

Regards,

Erik Bengtson
+32 2 639-7260


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

Erik.Bengtson@intl.westernunion.com wrote:

> Deepa,
> 
> 
>>If you define a function using 'CREATE FUNCTION ...' and call it using
>>CALL {function} statement, you get the same error as above. To call a
>>function in Derby, either VALUES or SELECT has to be used.
>>e.g: VALUES JPOX_ASCII('a');
> 
> 
> 
> I use 
> SELECT 1 FROM TABLE WHERE JPOX_ASCII('a') = 55

ij> 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;
0 rows inserted/updated/deleted
ij> create table t (i int);
0 rows inserted/updated/deleted
ij> insert into t values(1);
1 row inserted/updated/deleted
ij> SELECT 1 FROM T WHERE JPOX_ASCII('a') = 55;
ERROR 42X51: The class 'org.jpox.store.rdbms.adapter.Function' does not
exist or
 is inaccessible. This can happen if the class is not public.
ERROR XJ001: Java exception: 'org.jpox.store.rdbms.adapter.Function:
java.lang.C
lassNotFoundException'.


So that works for me, I didn't put your java code in my classpath but
the error you are reporting (42Y03) is because the function defintion
cannot be found, it hasn't even started looking for the Java class at
that point.

Now if I change schema, I get the error you are reporting:

ij> create schema xxx;
0 rows inserted/updated/deleted
ij> set schema xxx;
0 rows inserted/updated/deleted
ij> SELECT 1 FROM app.T WHERE JPOX_ASCII('a') = 55;
ERROR 42Y03: 'JPOX_ASCII' is not recognized as a function or procedure.

That's why your problem looks like a schema issue.
Dan.