Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: neutral (athena.apache.org: local policy)
Content-class: urn:content-classes:message
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
Subject: Problem with getting the return value out of my stored procedure
Date: Fri, 18 Apr 2008 16:38:35 +0100
Message-ID: <F82EB340404E9B4C854C64FADBDDF524211212EC@bla-mail-2.ad.thus.net>
Thread-Topic: Problem with getting the return value out of my stored procedure
Thread-Index: AcihaornnLRtX4hsSIuVDLb0/F6++A==
From: "Hammonds, Nicholas" <Nicholas.Hammonds@thus.net>
To: <derby-user@db.apache.org>

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

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

=3D=3D=3D=3D=3D=3D=3D=3D

The matching java method=20

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

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 =3D (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 =3D 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