First, thanks to everyone who replied with help and suggestions. Quick
summary of the problem: stored procedure with 5 input parameters, 5 output
parameters, and a return status. Normally, you would think that the correct
CallableStatement would be:
cstmt = sConn.prepareCall("{? = call sp_validate_pwd(?, ?, ?, ?, ?, ?, ?, ?,
? ,?)}");
However, this did not work, no matter what arrangement was used for
declaring and registering the inputs and outputs. What did finally work is
ignoring the return status. I don't know why, but the return status was
never explicity declared in the stored procedure, though it did show up in
the MS Query Analyzer as a parameter for that stored procedure. Apparently,
since it was never explicitly declared, it's not required, and including a
spot for it in the call threw all of the other parameter positions off by
one.
So, the code snippet that is currently working is:
// prepare the stored procedure statement
try {
cstmt = sConn.prepareCall("{call sp_validate_pwd(?, ?, ?, ?, ?, ?,
?, ?, ? ,?)}");
} catch (SQLException sqle) {
sqle.printStackTrace();
}
// set the input parameters
try {
cstmt.setString(1, strUserID);
cstmt.setString(2, strPassword);
cstmt.setString(3, strRemoteAddress);
cstmt.setString(4, strReferURL);
cstmt.setString(5, strHTTPUserAgent);
} catch (Exception e) {
e.printStackTrace();
}
// register the output parameters for the stored procedure
try {
cstmt.registerOutParameter(6, Types.BIT);
cstmt.registerOutParameter(7, Types.BIT);
cstmt.registerOutParameter(8, Types.BIT);
cstmt.registerOutParameter(9, Types.CHAR);
cstmt.registerOutParameter(10, Types.VARCHAR);
} catch (Exception e) {
e.printStackTrace();
}
The moral of the story being that unless a parameter is explicitly declared
within the procedure itself, it's not available and shouldn't be accounted
for when preparing the statement, regardless of what sort of tool is used to
manage the stored procedures, like MS Query Analyzer. Maybe this is a
bug/feature of MS Query Analyzer, where a return status is included in the
list just for housekeeping purposes, or maybe it's true regardless of
database platform. Either way: unless a parameter is explicitly declared,
don't count on it in your CallableStatement.
Thanks again to everyone who helped, even though it was off-topic.
John Turner
[EMAIL PROTECTED]
--
To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]>
For additional commands, e-mail: <mailto:[EMAIL PROTECTED]>
