You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by dacia <ax...@gmx.de> on 2009/05/18 14:22:36 UTC
Problems create SQL Function
Hi there
I try to create some SQL function, but I stuck:
[code]
create function getDate(timeNumber bigint) returns date
> language java external name
> 'java.util.Date(time)'
> parameter style java no sql;
0 rows inserted/updated/deleted
[/code]
now:
[code]
ij> values getDate(1234566712345667789);
ERROR 42X51: The class 'java.util' does not exist or is inaccessible. This
can happen if the class is not public.
ERROR XJ001: Java exception: 'java.util: java.lang.ClassNotFoundException'.
[/code]
What is wrong ?
Thanx
--
View this message in context: http://www.nabble.com/Problems-create-SQL-Function-tp23595863p23595863.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: Problems create SQL Function
Posted by dacia <ax...@gmx.de>.
Thank you Kristian
Kristian Waagan-4 wrote:
>
> dacia wrote:
>> Hi Kristian
>>
>> I tried this:
>>
>> java code:
>> public class DateFunctions {
>>
>> public static java.util.Date getDate(long instant) {
>> return new java.util.Date(instant);
>> }
>> }
>>
>> in ij:
>> ij>call sqlj.install_jar('DateFunctions.jar','DateFunctions',0);
>> Statement executed.
>> ij> create function getDate(timeNumber bigint) returns date
>>> language java external name
>>> 'DateFunctions.getDate'
>>> parameter style java no sql;
>> 0 rows inserted/updated/deleted
>>
>> ij>values getDate(123456789123456789);
>> ERROR 42X50: No method was found that matched the method call
>> DateFunctions.getDate(long), tried all combinations of object and
>> primitive
>> types and any possible type conversion for any parameters the method
>> call
>> may have. The method might exist but it is not public and/or static, or
>> the
>> parameter types are not method invocation convertible.
>
> Would be good if also the expected return type was included in the error
> message here...
>
>>
>> ij>values getDate(Cast(123456789123456789 as bigint));
>> ERROR 42X50:......
>>
>> ij>select s.schemaname, f.filename
>>> from sys.sysschemas s, sys.sysfiles f
>>> where s.schemaid=f.schemaid;
>> SCHEMANAME | FILENAME
>> APP |DATEFUNCTIONS
>> 1 row selected
>>
>> Is there any way to verify, if the function is loaded and which
>> parameters
>> are expected ?
>
> Not that I'm aware of, except for looking in the system tables. Anyone?
>
>> Or what did I wrong ?
>
> Sorry, I didn't think about the fact that you have to return a data type
> that Derby can handle (SQL). These are described in the Reference Manual
> under "Data types".
>
> In this case, java.util.Date was specified, but one of java.sql.Date,
> java.sql.Time or java.sql.Timestamp should be used.
>
> I had no problems invoking the function when doing that. I didn't create
> a Jar, just put the class on the classpath:
>
> ij version 10.6
> ij> connect 'jdbc:derby:memory:mydb;create=true';
> ij> create function getDate(timeNumber bigint) returns date
> > language java external name
> > 'DateFunctions.getDate'
> > parameter style java no sql;
> 0 rows inserted/updated/deleted
> ij> values getDate(23423423432);
> 1
> ----------
> 1970-09-29
>
> 1 row selected
> ij>
>
>
> Cheers,
> --
> Kristian
>
>>
>>
>>
>>
>> Kristian Waagan-4 wrote:
>>> dacia wrote:
>>>> Hi there
>>>>
>>>> I try to create some SQL function, but I stuck:
>>>>
>>>> [code]
>>>> create function getDate(timeNumber bigint) returns date
>>>>
>>>>> language java external name
>>>>> 'java.util.Date(time)'
>>>>> parameter style java no sql;
>>>>>
>>>> 0 rows inserted/updated/deleted
>>>> [/code]
>>>>
>>>> now:
>>>>
>>>> [code]
>>>> ij> values getDate(1234566712345667789);
>>>> ERROR 42X51: The class 'java.util' does not exist or is inaccessible.
>>>> This
>>>> can happen if the class is not public.
>>>> ERROR XJ001: Java exception: 'java.util:
>>>> java.lang.ClassNotFoundException'.
>>>> [/code]
>>>>
>>>>
>>>> What is wrong ?
>>>>
>>> Hi,
>>>
>>> You have to specify a public static method as the external name.
>>> In the code you posted, Derby tries to find the class java.util and the
>>> static method Date. Since there is no such class nor method, it fails.
>>>
>>> Unless you find a public static method in the Java API that returns a
>>> Date, I think you need to write your own wrapper method, for instance:
>>> public class MyClass {
>>> public static java.util.Date getDate(long instant) {
>>> return new java.util.Date(instant);
>>> }
>>> }
>>>
>>> The external name here would be 'MyClass.getDate', or
>>> 'MyClass.getDate(long)' (I think).
>>> Note that the method doesn't need to have the same name as the function
>>> you declare. The class you write has to be on the classpath for Derby to
>>> access it (unless you install a Jar file in the database).
>>>
>>>
>>> Hope this helps,
>>> --
>>> Kristian
>>>
>>>> Thanx
>>>>
>>>
>>>
>>
>
>
>
--
View this message in context: http://www.nabble.com/Problems-create-SQL-Function-tp23595863p23617469.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: Problems create SQL Function
Posted by Kristian Waagan <Kr...@Sun.COM>.
dacia wrote:
> Hi Kristian
>
> I tried this:
>
> java code:
> public class DateFunctions {
>
> public static java.util.Date getDate(long instant) {
> return new java.util.Date(instant);
> }
> }
>
> in ij:
> ij>call sqlj.install_jar('DateFunctions.jar','DateFunctions',0);
> Statement executed.
> ij> create function getDate(timeNumber bigint) returns date
>> language java external name
>> 'DateFunctions.getDate'
>> parameter style java no sql;
> 0 rows inserted/updated/deleted
>
> ij>values getDate(123456789123456789);
> ERROR 42X50: No method was found that matched the method call
> DateFunctions.getDate(long), tried all combinations of object and primitive
> types and any possible type conversion for any parameters the method call
> may have. The method might exist but it is not public and/or static, or the
> parameter types are not method invocation convertible.
Would be good if also the expected return type was included in the error
message here...
>
> ij>values getDate(Cast(123456789123456789 as bigint));
> ERROR 42X50:......
>
> ij>select s.schemaname, f.filename
>> from sys.sysschemas s, sys.sysfiles f
>> where s.schemaid=f.schemaid;
> SCHEMANAME | FILENAME
> APP |DATEFUNCTIONS
> 1 row selected
>
> Is there any way to verify, if the function is loaded and which parameters
> are expected ?
Not that I'm aware of, except for looking in the system tables. Anyone?
> Or what did I wrong ?
Sorry, I didn't think about the fact that you have to return a data type
that Derby can handle (SQL). These are described in the Reference Manual
under "Data types".
In this case, java.util.Date was specified, but one of java.sql.Date,
java.sql.Time or java.sql.Timestamp should be used.
I had no problems invoking the function when doing that. I didn't create
a Jar, just put the class on the classpath:
ij version 10.6
ij> connect 'jdbc:derby:memory:mydb;create=true';
ij> create function getDate(timeNumber bigint) returns date
> language java external name
> 'DateFunctions.getDate'
> parameter style java no sql;
0 rows inserted/updated/deleted
ij> values getDate(23423423432);
1
----------
1970-09-29
1 row selected
ij>
Cheers,
--
Kristian
>
>
>
>
> Kristian Waagan-4 wrote:
>> dacia wrote:
>>> Hi there
>>>
>>> I try to create some SQL function, but I stuck:
>>>
>>> [code]
>>> create function getDate(timeNumber bigint) returns date
>>>
>>>> language java external name
>>>> 'java.util.Date(time)'
>>>> parameter style java no sql;
>>>>
>>> 0 rows inserted/updated/deleted
>>> [/code]
>>>
>>> now:
>>>
>>> [code]
>>> ij> values getDate(1234566712345667789);
>>> ERROR 42X51: The class 'java.util' does not exist or is inaccessible.
>>> This
>>> can happen if the class is not public.
>>> ERROR XJ001: Java exception: 'java.util:
>>> java.lang.ClassNotFoundException'.
>>> [/code]
>>>
>>>
>>> What is wrong ?
>>>
>> Hi,
>>
>> You have to specify a public static method as the external name.
>> In the code you posted, Derby tries to find the class java.util and the
>> static method Date. Since there is no such class nor method, it fails.
>>
>> Unless you find a public static method in the Java API that returns a
>> Date, I think you need to write your own wrapper method, for instance:
>> public class MyClass {
>> public static java.util.Date getDate(long instant) {
>> return new java.util.Date(instant);
>> }
>> }
>>
>> The external name here would be 'MyClass.getDate', or
>> 'MyClass.getDate(long)' (I think).
>> Note that the method doesn't need to have the same name as the function
>> you declare. The class you write has to be on the classpath for Derby to
>> access it (unless you install a Jar file in the database).
>>
>>
>> Hope this helps,
>> --
>> Kristian
>>
>>> Thanx
>>>
>>
>>
>
Re: Problems create SQL Function
Posted by dacia <ax...@gmx.de>.
Hi Kristian
I tried this:
java code:
public class DateFunctions {
public static java.util.Date getDate(long instant) {
return new java.util.Date(instant);
}
}
in ij:
ij>call sqlj.install_jar('DateFunctions.jar','DateFunctions',0);
Statement executed.
ij> create function getDate(timeNumber bigint) returns date
> language java external name
> 'DateFunctions.getDate'
> parameter style java no sql;
0 rows inserted/updated/deleted
ij>values getDate(123456789123456789);
ERROR 42X50: No method was found that matched the method call
DateFunctions.getDate(long), tried all combinations of object and primitive
types and any possible type conversion for any parameters the method call
may have. The method might exist but it is not public and/or static, or the
parameter types are not method invocation convertible.
ij>values getDate(Cast(123456789123456789 as bigint));
ERROR 42X50:......
ij>select s.schemaname, f.filename
> from sys.sysschemas s, sys.sysfiles f
> where s.schemaid=f.schemaid;
SCHEMANAME | FILENAME
APP |DATEFUNCTIONS
1 row selected
Is there any way to verify, if the function is loaded and which parameters
are expected ?
Or what did I wrong ?
Kristian Waagan-4 wrote:
>
> dacia wrote:
>> Hi there
>>
>> I try to create some SQL function, but I stuck:
>>
>> [code]
>> create function getDate(timeNumber bigint) returns date
>>
>>> language java external name
>>> 'java.util.Date(time)'
>>> parameter style java no sql;
>>>
>> 0 rows inserted/updated/deleted
>> [/code]
>>
>> now:
>>
>> [code]
>> ij> values getDate(1234566712345667789);
>> ERROR 42X51: The class 'java.util' does not exist or is inaccessible.
>> This
>> can happen if the class is not public.
>> ERROR XJ001: Java exception: 'java.util:
>> java.lang.ClassNotFoundException'.
>> [/code]
>>
>>
>> What is wrong ?
>>
>
> Hi,
>
> You have to specify a public static method as the external name.
> In the code you posted, Derby tries to find the class java.util and the
> static method Date. Since there is no such class nor method, it fails.
>
> Unless you find a public static method in the Java API that returns a
> Date, I think you need to write your own wrapper method, for instance:
> public class MyClass {
> public static java.util.Date getDate(long instant) {
> return new java.util.Date(instant);
> }
> }
>
> The external name here would be 'MyClass.getDate', or
> 'MyClass.getDate(long)' (I think).
> Note that the method doesn't need to have the same name as the function
> you declare. The class you write has to be on the classpath for Derby to
> access it (unless you install a Jar file in the database).
>
>
> Hope this helps,
> --
> Kristian
>
>> Thanx
>>
>
>
>
--
View this message in context: http://www.nabble.com/Problems-create-SQL-Function-tp23595863p23614219.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: Problems create SQL Function
Posted by Kristian Waagan <Kr...@Sun.COM>.
dacia wrote:
> Hi there
>
> I try to create some SQL function, but I stuck:
>
> [code]
> create function getDate(timeNumber bigint) returns date
>
>> language java external name
>> 'java.util.Date(time)'
>> parameter style java no sql;
>>
> 0 rows inserted/updated/deleted
> [/code]
>
> now:
>
> [code]
> ij> values getDate(1234566712345667789);
> ERROR 42X51: The class 'java.util' does not exist or is inaccessible. This
> can happen if the class is not public.
> ERROR XJ001: Java exception: 'java.util: java.lang.ClassNotFoundException'.
> [/code]
>
>
> What is wrong ?
>
Hi,
You have to specify a public static method as the external name.
In the code you posted, Derby tries to find the class java.util and the
static method Date. Since there is no such class nor method, it fails.
Unless you find a public static method in the Java API that returns a
Date, I think you need to write your own wrapper method, for instance:
public class MyClass {
public static java.util.Date getDate(long instant) {
return new java.util.Date(instant);
}
}
The external name here would be 'MyClass.getDate', or
'MyClass.getDate(long)' (I think).
Note that the method doesn't need to have the same name as the function
you declare. The class you write has to be on the classpath for Derby to
access it (unless you install a Jar file in the database).
Hope this helps,
--
Kristian
> Thanx
>
Re: Problems create SQL Function
Posted by Francois Orsini <fr...@gmail.com>.
Dacia,
You don't need to include the source .java file(s) as part of the JAR
archive, only the .class ones.
Just FYI
--francois
On Tue, May 19, 2009 at 6:09 AM, dacia <ax...@gmx.de> wrote:
>
> Got it ! :-p
>
> Create your java class in your favorit text editor.
> Compile it (I do not know if this is really needed, I did !)
> create a jar file containing both your .class and your .java file !!! <-
> The
> .java was missing !!
>
> in ij do:
> ij>call sqlj.install_jar('<path to jar file>','<alias name as you
> like>',0);
> Statement executed.
> ( a copy of the jar is now availiable in derby.system.home / <databasename>
> / jar )
> ij>call syscs_util.syscs_set_database_property
> ('derby.database.classpath','<alias name of above>');
> Statement executed.
> ij> create function <beCreative>(<param-name> <Derby Data Type>) returns
> <Derby Data Type>
> > language java external name
> > '<java class name>.<method name>'
> > parameter style java no sql;
> 0 rows inserted/updated/deleted
>
> Run your function:
> ij>values <yourFunction>(<param\>);
>
> this should work.
>
> CAVEAT: Be sure, the return type of your java method is compatible to the
> return type of the defined function. e.g.
> If the expected return type of your function is date the method must have
> java.sql.date as return type.
>
> public static java.sql.Date getDate(final long instant) {
> java.util.Date d= new java.util.Date(instant);
> return new java.sql.Date(d.getTime());
> }
>
> To find the imported files in the database:
> select s.schemaname, f.filename
> from sys.sysschemas s, sys.sysfiles f
> where s.schemaid=f.schemaid;
> --
> View this message in context:
> http://www.nabble.com/Problems-create-SQL-Function-tp23595863p23616098.html
> Sent from the Apache Derby Users mailing list archive at Nabble.com.
>
>
Re: Problems create SQL Function
Posted by dacia <ax...@gmx.de>.
Got it ! :-p
Create your java class in your favorit text editor.
Compile it (I do not know if this is really needed, I did !)
create a jar file containing both your .class and your .java file !!! <- The
.java was missing !!
in ij do:
ij>call sqlj.install_jar('<path to jar file>','<alias name as you like>',0);
Statement executed.
( a copy of the jar is now availiable in derby.system.home / <databasename>
/ jar )
ij>call syscs_util.syscs_set_database_property
('derby.database.classpath','<alias name of above>');
Statement executed.
ij> create function <beCreative>(<param-name> <Derby Data Type>) returns
<Derby Data Type>
> language java external name
> '<java class name>.<method name>'
> parameter style java no sql;
0 rows inserted/updated/deleted
Run your function:
ij>values <yourFunction>(<param\>);
this should work.
CAVEAT: Be sure, the return type of your java method is compatible to the
return type of the defined function. e.g.
If the expected return type of your function is date the method must have
java.sql.date as return type.
public static java.sql.Date getDate(final long instant) {
java.util.Date d= new java.util.Date(instant);
return new java.sql.Date(d.getTime());
}
To find the imported files in the database:
select s.schemaname, f.filename
from sys.sysschemas s, sys.sysfiles f
where s.schemaid=f.schemaid;
--
View this message in context: http://www.nabble.com/Problems-create-SQL-Function-tp23595863p23616098.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: Problems create SQL Function
Posted by dacia <ax...@gmx.de>.
I also did
ij> call syscs_util.syscs_set_database_property
('derby.database.classpath','APP.DateFunctions');
before executing the values command.
--
View this message in context: http://www.nabble.com/Problems-create-SQL-Function-tp23595863p23614434.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.