It's been sometime since I used JDBC and SQL Server. But here are a few
points to note:
1) Your first "?" is the return status. It should be registered as an OUT
parameter.
2) Remember to use the right JDBC type when registering the OUT parameters.
This is of utmost importance.
3) All ouput parameters should be registered before the call to the stored
proc is made.
4) cstmt.setXXX(<questionmark_placeholder_position>, <value>)
5) cstmt.getXXX(<questionmark_placeholder_position>)
6) If your stored procedure is returning any result set, retrieve all the
results before retrieving the OUT parameters. To make sure all the results
have been retrieved, use the getMoreResults method.
An example:
------------------
TestAdd proc adds two numbers.
It returns a status of 1 is the any one of the input parameters is less
than 0.
It returns a status of 0 if the add worked and it also returns a result of
the addition.
Stored proc:
-----------------
create procedure TestAdd @param1 int, @param2 int, @param3 int output as
begin
if @param1 < 0 and @param2 <0
begin
return 1
end
else begin
select @param3 = @param1 + @param2
return 0
end
end
Java code:
----------------
CallableStatement cstmttmt = con.prepareCall("{? = call TestAdd(?, ?, ?)}"
);
cstmt.registerOutParameter(1, Types.INTEGER);
cstmt.setInt(2, 10);
cstmt.setInt(3, 20);
cstmt.registerOutParameter(4, Types.INTEGER);
cstmt.execute();
System.out.println("status : " + cstmt.getInt(1));
System.out.println("value after adding 10 and 20 : " + cstmt.getInt(4));
Hope this helps.
RS
"Turner, John"
<[EMAIL PROTECTED]> To: 'Tomcat Users List'
<[EMAIL PROTECTED]>
08/19/02 11:53 AM cc:
Please respond to Subject: RE: OFF-TOPIC: Pointers to
CallableStatement
"Tomcat Users docs?
List"
Right. My setup looks like this:
cstmt = sConn.prepareCall("{? = call
sp_validate_pwd(?,?,?,?,?,?,?,?,?,?)}");
Basically, there are 5 inputs (username, password, IP address, browser
type,
and referer) and I'm supposed to get a return status back (bad or good) and
5 outputs: 3 booleans and 2 strings (isValid, isExceeded, isEnabled, name,
and title).
I've tried everything I can think of...only having 6 question marks, having
all 11, only using 5, etc. to no avail. I enabled debug logging on the
driver, and I get messages that say "parameter my_parameter not registers
as
output" or "not registered as input", even when they are, and regardless of
how I use the set*() and registerOutParameter() methods. Very confusing.
I'd love to find a complete stored procedures How-To somewhere that
addresses complex stored procedures instead of the basic tutorials that do
simple math or just insert a row.
Thanks for the reply.
John
-----Original Message-----
From: Wagoner, Mark [mailto:[EMAIL PROTECTED]]
Sent: Monday, August 19, 2002 12:31 PM
To: 'Tomcat Users List'
Subject: RE: OFF-TOPIC: Pointers to CallableStatement docs?
When you say it returns a status, do you mean it is a function (I work
primarily with Oracle, so if this does not apply to MS I apologize)?
If so, you need to make the call something like:
CallableStatement stmt = conn.prepareCall("{call ? = proc(?,?, ... )}");
-----Original Message-----
From: Turner, John [mailto:[EMAIL PROTECTED]]
Sent: Monday, August 19, 2002 12:18 PM
To: '[EMAIL PROTECTED]'
Subject: OFF-TOPIC: Pointers to CallableStatement docs?
Hello -
I think there is a java-user list, or even a jdbc-interest list, but I'd
rather not subscribe when all I need is one quick pointer, so I am hoping
someone on this list can get me started.
I'm having quite a bit of difficulty working with stored procedures in my
classes and servlets. The database is MS SQL Server 2000. I've read every
single doc I can find, both at Sun, through Google, and even through the
driver vendor's documentation. I even scammed some code from a JDBC 3.0
book (the only one I could find) at Border's, with still no luck.
Can anyone point me to a resource that explains how to setup stored
procedures in a CallableStatement correctly? I understand about
registering
the output parameters and setting the input types, and I understand that
the
parameters in a CallableStatement are numbered from left to right starting
at 1. I've seen the examples at Sun, etc. but they're not much help.
The problem is that all of the examples I can find deal with very simple,
very rudimentary stored procedures, like finding the average of two
numbers,
or whatever. Our stored procedures are more involved than that.
Example: a stored procedure used to validate logins. It has 5 input
parameters, and 5 output parameters. It returns a status. According to
the
docs I have read so far, that means I should have a CallableStatement with
11 question marks ("?") in it (5 + 5 + 1 = 11). but that doesn't work, and
I have tried every combination of inputs, outputs, input/outputs, etc. that
I can think of, to no avail.
Any help or pointers to resources that explain stored procedures and
CallableStatements in more in-depth fashion would be greatly appreciated.
- John
============================================
John Turner
[EMAIL PROTECTED] | 248-488-3466
Advertising Audit Service
http://www.aas.com
--
To unsubscribe, e-mail:
<mailto:[EMAIL PROTECTED]>
For additional commands, e-mail:
<mailto:[EMAIL PROTECTED]>
--
To unsubscribe, e-mail:
<mailto:[EMAIL PROTECTED]>
For additional commands, e-mail:
<mailto:[EMAIL PROTECTED]>
--
To unsubscribe, e-mail: <
mailto:[EMAIL PROTECTED]>
For additional commands, e-mail: <
mailto:[EMAIL PROTECTED]>
--
To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]>
For additional commands, e-mail: <mailto:[EMAIL PROTECTED]>
