select MyFunc(col1) from MyTable;
When you implement this MyFunc(), if you need a connection that somewhat represents the current connection (same transaction) which is executing this sql statement, you use "jdbc:default:connection" url.
Regards, Ali
At 06:44 AM 12/20/2004, you wrote:
Hmm - this "jdbc:default:connection" is a little scary to me. Suppose my Der by verison runs embedded in an application server which has multiple connections open at any time to differnt databases - how dose the DriverManager.getConnection("jdbc:default:connection"); know to give me a connection to the Derby Database ?
Or do I in such cases have to be explicit and use the explicit url to the given Derby instance ?
B-)
> -----Original Message----- > From: Suavi Ali Demir [mailto:[EMAIL PROTECTED] > Sent: Friday, December 17, 2004 4:57 PM > To: Derby Discussion > Subject: Re: CREATE FUNCTION with SQL examples ? > > Rajesh, do you not need to close your statement and result > set objects in a try-finally block? rs.close and s1.close() > would be needed, no? But you should NOT close the connection, right? > > Regards, > Ali > > 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 Kartha <[EMAIL PROTECTED]> wrote: > > 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-) > > > > > > > > > > > > > > > > > > > > > > > >
