Sybase, JDBC and output values

Posted on 2005-03-09
Medium Priority
Last Modified: 2012-06-21
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);

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
Question by:Peewee
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Author Comment

ID: 13498230
one thing i forgot to mention was that the sql itself is just inserting data and returns no resultset itself.  If i ask for a resultset i get the following error:

java.sql.SQLException: JZ0R2: No result set for this query.

These are not my procs so i dont have the option of modifying them.


Expert Comment

ID: 13504469
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.
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.

LVL 14

Accepted Solution

Jan Franek earned 200 total points
ID: 13509188
Look at CallableStatement - it should be used for calling stored procedures and you can define output parameters there.

Expert Comment

ID: 13515803
For calling stored procedures writeen in any sql try to use Callablestatement and you will not get this kind of error

LVL 14

Expert Comment

by:Jan Franek
ID: 13524170

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is a fine trick which I've found useful many times, when you just don't want to accidentally run a batch script or the commands needs administrator rights.
The top devops trends for 2017 are focused on improved deployment frequency, decreased lead time for change and decreased MTTR.
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question