Nice try, but I think the CALL statement that is needed to invoke a
procedure requires
MODIFIES SQL DATA option. This option is only allowed for procedures,
not functions, I think.
Satheesh
--------------------------------------------------------------------------------------------
Content-Type: text/plain;
charset="us-ascii"
Subject: CREATE FUNCTION with SQL examples ?
Date: Mon, 20 Dec 2004 13:31:53 -0600
From: Bernd Ruehlicke <[EMAIL PROTECTED]>
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-)
