I don't know if it'll help, but here's one that I use:
if ( db.connect() ) {
CallableStatement cstmt =
db.getConnection().prepareCall( "begin delete_record( ?, ?, ?,
? ); end;" );
cstmt.setInt( 1, ( ( Integer ) ht.get(
teq.ARCHIVE_NBR ) ).intValue() );
cstmt.setString( 2, session.getValue( "USERNAME" ).toString() );
cstmt.setString( 3, "" + request.getRemoteAddr() );
cstmt.registerOutParameter( 4, java.sql.Types.VARCHAR );
cstmt.executeUpdate();
result = cstmt.getString( 4 );
deleted = true;
cstmt.close();
db.commit();
}
The "db" object is a wrapper that I have around the database connection.
Otherwise it
should be self-explainitory. Hope this helps some...
--mikej
-=-----
mike jackson
[EMAIL PROTECTED]
> -----Original Message-----
> From: Turner, John [mailto:[EMAIL PROTECTED]]
> Sent: Tuesday, August 20, 2002 4: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]>
