From derby-user-return-10995-apmail-db-derby-user-archive=db.apache.org@db.apache.org Tue May 19 14:25:28 2009 Return-Path: Delivered-To: apmail-db-derby-user-archive@www.apache.org Received: (qmail 86204 invoked from network); 19 May 2009 14:25:26 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.3) by minotaur.apache.org with SMTP; 19 May 2009 14:25:26 -0000 Received: (qmail 83686 invoked by uid 500); 19 May 2009 14:25:26 -0000 Delivered-To: apmail-db-derby-user-archive@db.apache.org Received: (qmail 83639 invoked by uid 500); 19 May 2009 14:25:26 -0000 Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: List-Id: Reply-To: "Derby Discussion" Delivered-To: mailing list derby-user@db.apache.org Received: (qmail 83631 invoked by uid 99); 19 May 2009 14:25:26 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 19 May 2009 14:25:26 +0000 X-ASF-Spam-Status: No, hits=-0.0 required=10.0 tests=SPF_HELO_PASS,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (athena.apache.org: domain of lists@nabble.com designates 216.139.236.158 as permitted sender) Received: from [216.139.236.158] (HELO kuber.nabble.com) (216.139.236.158) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 19 May 2009 14:25:17 +0000 Received: from isper.nabble.com ([192.168.236.156]) by kuber.nabble.com with esmtp (Exim 4.63) (envelope-from ) id 1M6QFU-0006hg-AO for derby-user@db.apache.org; Tue, 19 May 2009 07:24:56 -0700 Message-ID: <23617469.post@talk.nabble.com> Date: Tue, 19 May 2009 07:24:56 -0700 (PDT) From: dacia To: derby-user@db.apache.org Subject: Re: Problems create SQL Function In-Reply-To: <4A129CC2.8010708@Sun.COM> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Nabble-From: axel.tischer@gmx.de References: <23595863.post@talk.nabble.com> <4A1156B1.3000501@Sun.COM> <23614219.post@talk.nabble.com> <4A129CC2.8010708@Sun.COM> X-Virus-Checked: Checked by ClamAV on apache.org 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.