Peewee
asked on
Sybase, JDBC and output values
Hi There,
I have a stored procedure which returns a few parameters using the output keyword, ie
declare @id1 numeric(9,0), @id2 numeric(9,0)
execute my_proc 'Mr' ,'Peewee' ,'Gibbons' ,'N' ,@id1 output ,@id2 output
The proc itself works fine as expected, ie over an isql session it runs and returns the two parameters. My question is that how do i execute this via jdbc and get access to the two output values?
Here my java code thus far:
Connection conn = "connect string";
String insert = "declare @id1 numeric(9,0), @id2 numeric(9,0) execute my_proc 'Mr' ,'Peewee' ,'Gibbons' ,'N' ,@id1 output ,@id2 output";
Statement stmt = conn.createStatement();
boolean rc = stmt.execute(insert);
conn.close();
can someone ammend my java code to point me in the right direction so that i know how to access the output values.
thanks in advance
pg
I have a stored procedure which returns a few parameters using the output keyword, ie
declare @id1 numeric(9,0), @id2 numeric(9,0)
execute my_proc 'Mr' ,'Peewee' ,'Gibbons' ,'N' ,@id1 output ,@id2 output
The proc itself works fine as expected, ie over an isql session it runs and returns the two parameters. My question is that how do i execute this via jdbc and get access to the two output values?
Here my java code thus far:
Connection conn = "connect string";
String insert = "declare @id1 numeric(9,0), @id2 numeric(9,0) execute my_proc 'Mr' ,'Peewee' ,'Gibbons' ,'N' ,@id1 output ,@id2 output";
Statement stmt = conn.createStatement();
boolean rc = stmt.execute(insert);
conn.close();
can someone ammend my java code to point me in the right direction so that i know how to access the output values.
thanks in advance
pg
Well I am no java expert, but this is a problem that happens in other languagues too
amend you SQL to select your variables after the exec.
eg
String insert = "declare @id1 numeric(9,0), @id2 numeric(9,0) execute my_proc 'Mr' ,'Peewee' ,'Gibbons' ,'N' ,@id1 output ,@id2 output SELECT id1 = @id1, id2 = @id2";
then you will have a result set with the values, so you can ft\etch them as normal.
ChrisKing
amend you SQL to select your variables after the exec.
eg
String insert = "declare @id1 numeric(9,0), @id2 numeric(9,0) execute my_proc 'Mr' ,'Peewee' ,'Gibbons' ,'N' ,@id1 output ,@id2 output SELECT id1 = @id1, id2 = @id2";
then you will have a result set with the values, so you can ft\etch them as normal.
ChrisKing
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
For calling stored procedures writeen in any sql try to use Callablestatement and you will not get this kind of error
ASKER
java.sql.SQLException: JZ0R2: No result set for this query.
These are not my procs so i dont have the option of modifying them.
pg