BTW: What JDBC driver are you using? We had tried the MS driver and had problems using it with stored procs. We ended up using a 3rd party driver which we had to pay for.
Jim Urban - [EMAIL PROTECTED] Park City Solutions Inc. Clinical Connectivity Suite Product Manager Suite 295 500 Park Blvd. Itasca, IL 60143 Voice: (630) 250-3045 x106 Fax: (630) 250-3046 CONFIDENTIALITY NOTICE This message and any included attachments are from Park City Solutions Inc. and are intended only for the entity to which it is addressed. The contained information is confidential and privileged material. If you are not the intended recipient, you are hereby notified that any use, dissemination, or copying of this communication is strictly prohibited and may be unlawful. If you have received this communication in error please notify the sender of the delivery error by e-mail or call Park City Solutions Inc. corporate offices at (435) 654-0621 -----Original Message----- From: Turner, John [mailto:[EMAIL PROTECTED]] Sent: Tuesday, August 20, 2002 6:49 AM To: 'Tomcat Users List' Subject: RE: OFF-TOPIC: Pointers to CallableStatement docs? Thanks, but I'm not getting any Java-related error messages. The Java code just blows right on through, as everything keys off of the return status, which is always "false" or "negative". This is because the stored procedure never executes, and according to the debug log printed by the driver, it's parameter related...that is, whatever I am doing with set*() and registerOutParameter() doesn't match up with what the procedure is expecting. That's my question...can anyone point me to a resource that shows how to call real-world stored procedures correctly with CallableStatement? By "real-world" I don't mean rudimentary "take two numbers and add them together" or "get a row from a table" procedures, I can already do that. I'm looking for a more advanced tutorial, something that shows how to have inputs, outputs, in/outputs, and a return status, all at once. It must be possible, I just can't figure it out, and can't find any resources that explain how to do it. We have VB DLLs calling these procedures in production, so I know the procedures work, at least in conjunction with VB. John Turner -----Original Message----- From: Jim Urban [mailto:[EMAIL PROTECTED]] Sent: Monday, August 19, 2002 5:05 PM To: 'Tomcat Users List' Subject: RE: OFF-TOPIC: Pointers to CallableStatement docs? What error are you receiving? Can you post a stack trace and a code clip with line numbers so we can see what is failing? Jim Urban - [EMAIL PROTECTED] Park City Solutions Inc. Clinical Connectivity Suite Product Manager Suite 295 500 Park Blvd. Itasca, IL 60143 Voice: (630) 250-3045 x106 Fax: (630) 250-3046 CONFIDENTIALITY NOTICE This message and any included attachments are from Park City Solutions Inc. and are intended only for the entity to which it is addressed. The contained information is confidential and privileged material. If you are not the intended recipient, you are hereby notified that any use, dissemination, or copying of this communication is strictly prohibited and may be unlawful. If you have received this communication in error please notify the sender of the delivery error by e-mail or call Park City Solutions Inc. corporate offices at (435) 654-0621 -----Original Message----- From: Turner, John [mailto:[EMAIL PROTECTED]] Sent: Monday, August 19, 2002 1:27 PM To: 'Tomcat Users List' Subject: RE: OFF-TOPIC: Pointers to CallableStatement docs? Thanks! I've set up my code as you've described, but no luck. For example, here is the relevant portion of the stored procedure declaring the parameters (forgive me, I don't work with stored procedures that often, so this may not be the right portion of the procedure to focus on): @userid_in varchar(8), @password_in varchar(8), @ip_addr varchar(15), @http_referer varchar(80), @http_user_agent varchar(80), @pwdvalid bit OUTPUT, @userenabled bit OUTPUT, @graceexceeded bit OUTPUT, @adminuser char(1) OUTPUT, @title varchar(4) OUTPUT My code looks like this: // 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(2, strUserID); cstmt.setString(3, strPassword); cstmt.setString(4, strRemoteAddress); cstmt.setString(5, strReferURL); cstmt.setString(6, strHTTPUserAgent); } catch (Exception e) { e.printStackTrace(); } // register the output parameters for the stored procedure try { cstmt.registerOutParameter(1, Types.INTEGER); cstmt.registerOutParameter(7, Types.BIT); cstmt.registerOutParameter(8, Types.BIT); cstmt.registerOutParameter(9, Types.BIT); cstmt.registerOutParameter(10, Types.CHAR); cstmt.registerOutParameter(11, Types.VARCHAR); } catch (Exception e) { e.printStackTrace(); } // execute the stored procedure try { cstmt.execute(); } catch (SQLException sqle) { sqle.printStackTrace(); } // grab the results from the stored procedure call try { spReturnStatus = cstmt.getInt(1); isValid = cstmt.getBoolean(7); isEnabled = cstmt.getBoolean(8); isExceeded = cstmt.getBoolean(9); strAdmin = cstmt.getString(10); strTitle = cstmt.getString(11); } catch (SQLException sqle) { sqle.printStackTrace(); } cstmt = null; I've tried different calls, putting the output parameters first (2-6) and the inputs last (7-11), I've tried no return value, putting that return value at the end (#11), etc. with no luck. All of the variables in the setString() methods are set before calling setString(). Am I at least on the right track with the code shown above? Or have I completely missed it? Thanks again for replying! John Turner -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Monday, August 19, 2002 2:14 PM To: Tomcat Users List Subject: RE: OFF-TOPIC: Pointers to CallableStatement docs? 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]> -- 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]>
