db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bernd Ruehlicke" <BRuehli...@lgc.com>
Subject RE: CREATE FUNCTION with SQL examples ?
Date Mon, 20 Dec 2004 14:44:20 GMT
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:demir4@yahoo.com] 
> 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 <kartha@Source-Zone.Org> 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-)
> > >
> > >
> > >
> > >
> > >  
> > >
> > 
> > 
> 
> 

Mime
View raw message