Using JDBC, is there a way to call a stored procedure with multiple
return values? Thanks. 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
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. 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
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 ?
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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,...
|
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...
|
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...
|
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...
|
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...
|
by: Dino L. |
last post by:
How can I run stored procedure (MSSQL) ?
|
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...
|
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
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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...
|
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)...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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...
|
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...
| |