From derby-user-return-376-apmail-db-derby-user-archive=db.apache.org@db.apache.org Fri Dec 17 22:04:16 2004 Return-Path: Delivered-To: apmail-db-derby-user-archive@www.apache.org Received: (qmail 6142 invoked from network); 17 Dec 2004 22:04:16 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (209.237.227.199) by minotaur-2.apache.org with SMTP; 17 Dec 2004 22:04:16 -0000 Received: (qmail 23308 invoked by uid 500); 17 Dec 2004 22:04:15 -0000 Delivered-To: apmail-db-derby-user-archive@db.apache.org Received: (qmail 23281 invoked by uid 500); 17 Dec 2004 22:04:15 -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 23238 invoked by uid 99); 17 Dec 2004 22:04:14 -0000 X-ASF-Spam-Status: No, hits=0.0 required=10.0 tests= X-Spam-Check-By: apache.org Received-SPF: pass (hermes.apache.org: local policy) Received: from i.meepzor.com (HELO Boron.MeepZor.Com) (204.146.167.214) by apache.org (qpsmtpd/0.28) with ESMTP; Fri, 17 Dec 2004 14:03:11 -0800 Received: from [192.168.0.2] (c-24-6-128-94.client.comcast.net [24.6.128.94]) by Boron.MeepZor.Com (8.12.8/8.12.8) with ESMTP id iBHM2swE016586 for ; Fri, 17 Dec 2004 17:02:55 -0500 Message-ID: <41C3584A.3080302@Source-Zone.Org> Date: Fri, 17 Dec 2004 14:06:02 -0800 From: Rajesh Kartha User-Agent: Mozilla Thunderbird 0.7.3 (Windows/20040803) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Derby Discussion Subject: Re: CREATE FUNCTION with SQL examples ? References: <1B37BEFB3456F1428E01932AFA147822E1A0B4@HOUEXCH902.landmark.lgc.com> In-Reply-To: <1B37BEFB3456F1428E01932AFA147822E1A0B4@HOUEXCH902.landmark.lgc.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Checked: Checked X-Spam-Rating: minotaur-2.apache.org 1.6.2 0/1000/N Hi , Here is an example, hope that helps : (Note: I am using a class Functions.java. The compiled class needs to be in the CLASSPATH) ij> create table table_tmp(col1 int,col2 char(2)); 0 rows inserted/updated/deleted ij> insert into table_tmp values(10,'ca'); 1 row inserted/updated/deleted ij> select max(col1) from TABLE_TMP; 1 ----------- 10 1 row selected ij> CREATE FUNCTION MYFUNC() returns BIGINT PARAMETER STYLE JAVA reads sql data language JAVA EXTERNAL NAME 'Functions.return BigInt' ; 0 rows inserted/updated/deleted ij> drop table abc; 0 rows inserted/updated/deleted ij> create table abc(id bigint); 0 rows inserted/updated/deleted ij> insert into abc values(myfunc()); 1 row inserted/updated/deleted ij> insert into abc values(myfunc()); 1 row inserted/updated/deleted ij> select * from abc; ID -------------------- 10 10 ------------------------------------------------------------------------------- The Functions.java looks like: public class Functions{ public static long returnBigInt() throws Exception { Connection conn = DriverManager.getConnection("jdbc:default:connection"); Statement s1 = conn.createStatement(); ResultSet rs=s1.executeQuery("select max(col1) from TABLE_TMP "); rs.next(); return rs.getInt(1); } } ------------------------------------------------------------------------------- -Rajesh **************************************************************************** Bernd Ruehlicke wrote: >Hi there, > >still Derby newby which is digging through the documentatins but cannot >find wht he needs ... > >trying to make a dum funtion MYFUNC without any parameters returning a >number it gets via soem sql lookups. Any examples out there of how a >function is to be declared without parameters and how the java program >can do SQL against Derby ? So that I can use this function like > >ij> values myfunc; > > >For the declaration I tried: >CREATE FUNCTION MYFUNC RETURNS BIGINT PARAMETER STYLE JAVA CONTAINS SQL >LANGUAGE JAVA EXTERNAL NAME 'com.xyz.DerbyFunctions.myfunc' > >and got >ij> CREATE FUNCTION TEST1 RETURNS BIGINT PARAMETER STYLE JAVA CONTAINS >SQL LANGUAGE JAVA EXTERNAL NAME 'com.xyz.DerbyFunctions.myfunc'; ERROR >42X01: Syntax error: Encountered "RETURNS" at line 1, column 23. > > >So QUESTION: > >1) How to make this CREATE FUNC call correctly ? >2) Is it possible to make a function so I do not need to add () for no >parameters >3) How to I do SQL against Derby in my Java static method ?!?! > >Thanx >B-) > > > > > >