Brillant response from you all - thanx a lot! B-)
> -----Original Message----- > From: Rajesh Kartha [mailto:[EMAIL PROTECTED] > Sent: Friday, December 17, 2004 4:06 PM > To: Derby Discussion > Subject: Re: CREATE FUNCTION with SQL examples ? > > 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-) > > > > > > > > > > > > > >
