db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Satheesh Bandaram <sathe...@Sourcery.Org>
Subject CREATE FUNCTION with SQL examples ?
Date Tue, 21 Dec 2004 21:33:17 GMT
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 <BRuehlicke@lgc.com>

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



Mime
View raw message