473,688 Members | 3,241 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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 10671


ra********@gmai l.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:
....
CallableStateme nt cs = conn.prepareCal l(" {? = call
uxt1.procName(? ,?,?,?)}" );
cs.registerOutP arameter(1,java .sql.Types.INTE GER);
cs.setString(2, "some ID");
cs.setString(3, "some Name");
cs.registerOutP arameter(4,java .sql.Types.INTE GER);
cs.registerOutP arameter(5,java .sql.Types.VARC HAR);
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********@gmai l.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:
...
CallableStateme nt cs = conn.prepareCal l(" {? = call
uxt1.procName(? ,?,?,?)}" );
cs.registerOutP arameter(1,java .sql.Types.INTE GER);
cs.setString(2, "some ID");
cs.setString(3, "some Name");
cs.registerOutP arameter(4,java .sql.Types.INTE GER);
cs.registerOutP arameter(5,java .sql.Types.VARC HAR);
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.getUpdateCou nt();
ResultSet rs = ps.getResultSet ();
if ((rs == null && (update_count == -1)) break; // done

if (rs != null) process rs;
ps.getMoreResul ts();
}
// 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
jogri13
1 New Member
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 CallableStateme nt, I tried:

cs = jdbcConnection. prepareCall("{? = call myFunction(?, ?)}");
cs.registerOutP arameter( 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
23493
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, state, zip, contract) values (@au_id,@au_lname,@au_fname,@phone,@address,
18
19476
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 executed will return one record and i want to use this data in the calling stored procedure. Is this possible ? Thanks in advance
8
7937
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 created a temporary database with a tables space. Verified that DECLARE GLOBAL TEMPORARY TABLE TEMP (A INTEGER); INSERT INTO SESSION.TEMP VALUES(10); SELECT A FROM SESSION.TEMP; works from a query tool.
2
9236
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 problems doing this. I wanted to implemented a generic "Helper" class like this: /** * Helper
1
1792
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 database manager loads? Is it configurable on the database server side so I could switch between different JDBC driver types, i.e. 2 and 4? I am on DB2 8.1.4 AIX and Linux. Thanks,
2
5451
by: Dino L. | last post by:
How can I run stored procedure (MSSQL) ?
9
5457
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 resultSet.getDate(paramIndex) and it's giving me a date that's way off.
1
2875
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
4189
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 specification it should return the number of rows affected by the stored procedure and 0 if no rows are affected (and it does this for all other database's jdbc drivers). I opened a PMR with IBM and received the following explanation. IBM claims that...
0
8590
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
1
8782
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8786
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7621
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6453
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5807
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4321
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4547
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2964
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

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.