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:[EMAIL PROTECTED]
> 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:[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-)
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
>
>
