RE: Problem with getting the return value out of my stored procedure

[Hammonds, Nicholas]

Marvelous,
That worked, many thanks. 

Nic


-----Original Message-----
From: Hammonds, Nicholas 
Sent: Friday, April 18, 2008 4:39 PM
To: 'derby-user@db.apache.org'
Subject: Problem with getting the return value out of my stored
procedure

Hi,
I'm creating a stored derby with the following code

====== 
String sql =
            "CREATE PROCEDURE testproc(" +
                    "IN S_YEAR INTEGER, OUT RETTOT INTEGER) " + 
                    "PARAMETER STYLE JAVA CONTAINS SQL LANGUAGE JAVA
EXTERNAL NAME " +
                    "'net.thus.unittest.StoredProcStuff.javaTestProc'";
        
        
          Statement  stat = con.createStatement();
            stat.execute(sql);

========

The matching java method 

=========
public static void javaTestProc(int a, int[] b ) {
        System.out.println("paramOne is " + a);
    }
==========

I can call the procedure fine and see that I am able to pass the
parameter in, although I don't have a clue how to get a return value
out.


This is how I'm invoking the store procedure
CallableStatement addMessage;
            addMessage = (CallableStatement)con.prepareCall(
                "{call testproc(?,?)}");
            addMessage.setInt(1, 666);
            addMessage.registerOutParameter(2, Types.NUMERIC);
            addMessage.execute();

all works fine

then I try to get the result out of it with

int retVal = addMessage.getInt(2);

That returns 0, obviously I haven't set any return value.  So my
question is what do I need to in my javaTestProc routine so I can make
it return any int value I want it to.


Thanks
Nic