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 19:31:53 GMT
Hmm - seams like I need to make SQL calls which modify data in the Derby
databse. Looks like FUNCTION is not an option than, and I need a
Procedure. Still I would like the behaviour of a function which returns
a number. This number is created like

    public static int nextval() throws SQLException
    {
        Connection conn =
DriverManager.getConnection("jdbc:default:connection");
        Statement s = conn.createStatement();
        s.executeUpdate("INSERT INTO dual (dummy) VALUES ('a')");
        s.executeUpdate("DELETE FROM dual WHERE dummy='a'");     
        conn.commit();
        s.close();
        
        String retVal = stringQuery(conn,"VALUES IDENTITY_VAL_LOCAL()");
        conn.close();
        return Integer.parseInt(retVal);
    }



... so you see what I want to do is to mimic Oracles mySequence.nextval
function so I in Derby can write 

SELECT nextval(), xyz FROM XYZ ...

For this to work nextval() has to be declared as a fuction.



QUESTION:  Is it possible to call a sql modifying procedure within a
Function ? I.e. wrap a procedure in a fcuntion ?

B-)





> -----Original Message-----
> From: Ali Demir [mailto:demir4@yahoo.com] 
> Sent: Monday, December 20, 2004 11:53 AM
> To: Derby Discussion
> Subject: RE: CREATE FUNCTION with SQL examples ?
> 
> 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