db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From dacia <axel.tisc...@gmx.de>
Subject Re: Problems create SQL Function
Date Tue, 19 May 2009 14:24:56 GMT

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.


Mime
View raw message