db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Ali Demir <dem...@yahoo.com>
Subject RE: CREATE FUNCTION with SQL examples ?
Date Mon, 20 Dec 2004 17:52:46 GMT
In this case, your code is invoked in a stored procedure. It is running on 
the server side, or if embedded, it is running in the engine itself. This 
is not the client code that is getting connections using a normal url. 
Imagine this sql:

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: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