473,327 Members | 1,920 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

JDBC: calling a stored procedure with multiple return values.

Using JDBC, is there a way to call a stored procedure with multiple
return values? Thanks.

Jul 23 '05 #1
4 10600


ra********@gmail.com wrote:
Using JDBC, is there a way to call a stored procedure with multiple
return values? Thanks.


Absolutely. What do you mean by 'multiple return values'? Multiple output
parameters? Multiple result sets and/or update counts? Multiple mixes of
result sets and update counts?
If you will show the procedure signature and maybe even the text? Tell
us what the body of the procedure returns.

Joe Weinstein at BEA

Jul 23 '05 #2
Thanks for the reply. I meant "multiple output parameters".

Here is how I execute the stored procedure:

declare @ErrorID int
declare @ErrorStr varchar(255)
exec procName
@customerId = '1234567890',
@customerName = 'some name',
@error_code = @ErrorID,
@error_state = @ErrorStr
Here is the procedure:

create procedure uxt1.procName
@customerId char(15) output,
@customerName char(64) output,
@error_code int output,
@error_state varchar(255) output
.... ...
/* all the business logic */
.... ...
return (@error_state)
GO
Here's what SQL server gives me if I do a "Script object as Execute":
DECLARE @RC int
DECLARE @customerId char(15)
DECLARE @customerName char(64)
DECLARE @error_code int
DECLARE @error_state varchar(255)
EXEC @RC = [uxt1].[procName] @customerId, @customerName, @error_code
OUTPUT , @error_state OUTPUT
The following is what I've tried in a Java program:
....
CallableStatement cs = conn.prepareCall(" {? = call
uxt1.procName(?,?,?,?)}" );
cs.registerOutParameter(1,java.sql.Types.INTEGER);
cs.setString(2,"some ID");
cs.setString(3,"some Name");
cs.registerOutParameter(4,java.sql.Types.INTEGER);
cs.registerOutParameter(5,java.sql.Types.VARCHAR);
ResultSet rs = cs.executeQuery();
....

My code doesn't throw any exception; but the procedure was not executed
correctly (i.e. it's not doing what it's supposed to do, which is to
simply insert some values into a table).
Any help is appreciated. Thanks in advance.

Jul 23 '05 #3


ra********@gmail.com wrote:
Thanks for the reply. I meant "multiple output parameters".

Here is how I execute the stored procedure:

declare @ErrorID int
declare @ErrorStr varchar(255)
exec procName
@customerId = '1234567890',
@customerName = 'some name',
@error_code = @ErrorID,
@error_state = @ErrorStr
Here is the procedure:

create procedure uxt1.procName
@customerId char(15) output,
@customerName char(64) output,
@error_code int output,
@error_state varchar(255) output
... ...
/* all the business logic */
... ...
return (@error_state)
GO
Here's what SQL server gives me if I do a "Script object as Execute":
DECLARE @RC int
DECLARE @customerId char(15)
DECLARE @customerName char(64)
DECLARE @error_code int
DECLARE @error_state varchar(255)
EXEC @RC = [uxt1].[procName] @customerId, @customerName, @error_code
OUTPUT , @error_state OUTPUT
The following is what I've tried in a Java program:
...
CallableStatement cs = conn.prepareCall(" {? = call
uxt1.procName(?,?,?,?)}" );
cs.registerOutParameter(1,java.sql.Types.INTEGER);
cs.setString(2,"some ID");
cs.setString(3,"some Name");
cs.registerOutParameter(4,java.sql.Types.INTEGER);
cs.registerOutParameter(5,java.sql.Types.VARCHAR);
ResultSet rs = cs.executeQuery();
...

My code doesn't throw any exception; but the procedure was not executed
correctly (i.e. it's not doing what it's supposed to do, which is to
simply insert some values into a table).
Any help is appreciated. Thanks in advance.


Is the procedure executing at all? You shouldn't be calling executeQuery()
unless the first thing the procedure does is a select. Use execute() and
then loop:

cs.execute();
while (true)
{
int update_count = ps.getUpdateCount();
ResultSet rs = ps.getResultSet();
if ((rs == null && (update_count == -1)) break; // done

if (rs != null) process rs;
ps.getMoreResults();
}
// after processing inline results, call ps.getXXX() to get output parameters.

Whatever jdbc driver you're suing is pretty flakey if it
returns a result set from executeQuery() and the
procedure didn't do a select for data to go to the caller...

Joe Weinstein at BEA
Jul 23 '05 #4
I have closely the same problem, excepting that my stored procs returns a "TABLE".

In fact, I have a SQL function that must return a TABLE and get 2 parameters. Its signature is as follow:

FUNCTION myFunction (@codett varchar(40), @codet varchar(40) )
RETURNS @Tab TABLE([cp] [varchar] (40), [pourcentage] [int])

I'd like to call it from my DAO (using JDBC). having that 'cs' is a CallableStatement, I tried:

cs = jdbcConnection.prepareCall("{? = call myFunction(?, ?)}");
cs.registerOutParameter( 1, Types.OTHER );
cs.setString(2, codett);//codett contains a String
cs.setString(3, codet);//codet contains a String
ResultSet rset = cs.executeQuery();

However, when a get to the "executeQuery()" method, I get an SQLException saying that "myFunction" is a function Object.

have you met such a problem ?
May 15 '06 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Paul | last post by:
Hi, In SQL Books Online in the section on @@Error it gives the following example: -- Execute the INSERT statement. INSERT INTO authors (au_id, au_lname, au_fname, phone, address, city,...
18
by: Jarrod Morrison | last post by:
Hi All I was wondering if there is a way to call a stored procedure from inside another stored procedure. So for example my first procedure will call a second stored procedure which when...
8
by: Thomasb | last post by:
With a background in MS SQL Server programming I'm used to temporary tables. Have just started to work with DB2 ver 7 on z/OS and stumbled into the concept of GLOBAL TEMPORARY TABLE. I have...
2
by: Kent Lewandowski | last post by:
hi all, Recently I wrote some stored procedures using java jdbc code (admittedly my first stab) and then tried to implement the same within java packages (for code reuse). I encountered...
1
by: eugene | last post by:
Happy Christmas to all (who celebrate)! It's still not clear to me... when in a java stored procedure it says: conn = DriverManager.getConnection("jdbc:default:connection"); what driver DB2...
2
by: Dino L. | last post by:
How can I run stored procedure (MSSQL) ?
9
by: Kenevel | last post by:
Hi everyone, Has anyone come across a problem where on Linux using DB2 9.1 Express- C with the packaged jcc-JDBC driver that it fails correctly to parse a returned date value? I'm simply calling...
1
by: %NAME% | last post by:
Is it possible to invoke a stored procedure from a jdbc statement interface? are there any examples on that topic? thanks
7
by: Otto Carl Marte | last post by:
Hi, When using a CallableStatement with the IBM DB2 Universal JDBC Driver the executeUpdate and getUpdateCount() methods on CallableStatement always return -1. According to the JDBC...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.