You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by "Hammonds, Nicholas" <Ni...@thus.net> on 2008/04/18 17:38:35 UTC
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
Re: Problem with getting the return value out of my stored procedure
Posted by Knut Anders Hatlen <Kn...@Sun.COM>.
"Hammonds, Nicholas" <Ni...@thus.net> writes:
> 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.
Hi Nicholas,
To get the return value out, you'll need to assign a value to b[0], like
this:
b[0] = 42;
--
Knut Anders
RE: Problem with getting the return value out of my stored procedure
Posted by "Hammonds, Nicholas" <Ni...@thus.net>.
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
Re: limitation in stored procedure names
Posted by Rick Hillegas <Ri...@Sun.COM>.
Hi Nic,
The "." is used to separate identifiers in valid SQL names. For a schema
object like a procedure, the name can have only one dot in it. The piece
before the dot identifies a unique schema in the database and the piece
after the dot identifies a unique procedure name inside that schema. The
compiler doesn't know what to do with the second dot. The compiler
doesn't know if you are trying to create a procedure named
"CUSTOM.IPAPOST_MESSAGE.IPA_POST_AXIOSS_RESPONSE" in the current schema
or a procedure named "IPA_POST_AXIOSS_RESPONSE" in the
"CUSTOM.IPA.POST_MESSAGE" schema or a procedure named
"IPA_POST_MESSAGE.IPA_POST_AXIOSS_RES" in the "CUSTOM" schema. If you
double quote the whole name, then the compiler will not be confused:
CREATE PROCEDURE
"custom.ipa_post_message.ipa_post_axioss_response" ...
If you do this, then whenever you invoke the procedure, you must invoke
it using the double quoted name. E.g.,:
CALL "custom.ipa_post_message.ipa_post_axioss_response" (...)
Hope this helps,
-Rick
Hammonds, Nicholas wrote:
> I am using apache derby in my unit testing. Consequently I need to
> create some stored procedures that will basically do the minimum and
> behave as expected. Therefore I am restricted to using exactly the same
> parameters and name for my stored procedures.
>
> One such procedure I am trying to create is as follows
>
> stat = con.createStatement();
> stat.execute("CREATE PROCEDURE
> custom.ipa_post_message.ipa_post_axioss_response(IN pin_activity_id INT,
> IN pis_message_id INT, IN pis_response VARCHAR(50), IN pis_status
> VARCHAR(50), OUT pis_return VARCHAR(50)) parameter style java language
> java external name
> 'net.thus.unittest.DBHelper.storedProc_ipa_post_axioss_response'");
>
> It complains about a syntax error at the position of the second '.' .
> If I replace the second '.' with a '_' everything works fine. As I say
> I have no influence of the procedure names is this DB is run and
> developed by a third party. Anybody know of a fix/workaround for this?
>
> Many Thanks
> Nic
>
>
>
RE: limitation in stored procedure names
Posted by "Hammonds, Nicholas" <Ni...@thus.net>.
Brilliant that has solved my problems, and thanks for the good
explanation.
Nic ;)
-----Original Message-----
From: Hammonds, Nicholas
Sent: Monday, April 21, 2008 5:04 PM
To: 'derby-user@db.apache.org'
Subject: limitation in stored procedure names
I am using apache derby in my unit testing. Consequently I need to
create some stored procedures that will basically do the minimum and
behave as expected. Therefore I am restricted to using exactly the same
parameters and name for my stored procedures.
One such procedure I am trying to create is as follows
stat = con.createStatement();
stat.execute("CREATE PROCEDURE
custom.ipa_post_message.ipa_post_axioss_response(IN pin_activity_id INT,
IN pis_message_id INT, IN pis_response VARCHAR(50), IN pis_status
VARCHAR(50), OUT pis_return VARCHAR(50)) parameter style java language
java external name
'net.thus.unittest.DBHelper.storedProc_ipa_post_axioss_response'");
It complains about a syntax error at the position of the second '.' .
If I replace the second '.' with a '_' everything works fine. As I say
I have no influence of the procedure names is this DB is run and
developed by a third party. Anybody know of a fix/workaround for this?
Many Thanks
Nic
limitation in stored procedure names
Posted by "Hammonds, Nicholas" <Ni...@thus.net>.
I am using apache derby in my unit testing. Consequently I need to
create some stored procedures that will basically do the minimum and
behave as expected. Therefore I am restricted to using exactly the same
parameters and name for my stored procedures.
One such procedure I am trying to create is as follows
stat = con.createStatement();
stat.execute("CREATE PROCEDURE
custom.ipa_post_message.ipa_post_axioss_response(IN pin_activity_id INT,
IN pis_message_id INT, IN pis_response VARCHAR(50), IN pis_status
VARCHAR(50), OUT pis_return VARCHAR(50)) parameter style java language
java external name
'net.thus.unittest.DBHelper.storedProc_ipa_post_axioss_response'");
It complains about a syntax error at the position of the second '.' .
If I replace the second '.' with a '_' everything works fine. As I say
I have no influence of the procedure names is this DB is run and
developed by a third party. Anybody know of a fix/workaround for this?
Many Thanks
Nic