tomcat-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Turner, John" <JTur...@AAS.com>
Subject RE: OFF-TOPIC: Pointers to CallableStatement docs?
Date Tue, 20 Aug 2002 13:04:12 GMT

We're using a thrid-party driver, trial version.  It's our third one (the
first two, from two other vendors, had even more problems).  The cost is
over $1000, and there's no support during the trial version.  I can't get
approval for the money unless I demonstrate that the driver will work.

John Turner


-----Original Message-----
From: Jim Urban [mailto:jurban@parkcitysolutions.com]
Sent: Tuesday, August 20, 2002 8:47 AM
To: 'Tomcat Users List'
Subject: RE: OFF-TOPIC: Pointers to CallableStatement docs?


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 - jurban@parkcitysolutions.com
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:JTurner@AAS.com]
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:jurban@parkcitysolutions.com]
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 - jurban@parkcitysolutions.com
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:JTurner@AAS.com]
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: rsequeira@transentric.com [mailto:rsequeira@transentric.com]
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"

                      <JTurner@AAS.com>        To:       'Tomcat Users List'


<tomcat-user@jakarta.apache.org>
                      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:MWagoner@wildflavors.com]
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:JTurner@AAS.com]
Sent: Monday, August 19, 2002 12:18 PM
To: 'tomcat-user@jakarta.apache.org'
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
jturner@aas.com | 248-488-3466
Advertising Audit Service
http://www.aas.com


--
To unsubscribe, e-mail:
<mailto:tomcat-user-unsubscribe@jakarta.apache.org>
For additional commands, e-mail:
<mailto:tomcat-user-help@jakarta.apache.org>

--
To unsubscribe, e-mail:
<mailto:tomcat-user-unsubscribe@jakarta.apache.org>
For additional commands, e-mail:
<mailto:tomcat-user-help@jakarta.apache.org>

--
To unsubscribe, e-mail:   <
mailto:tomcat-user-unsubscribe@jakarta.apache.org>
For additional commands, e-mail: <
mailto:tomcat-user-help@jakarta.apache.org>







--
To unsubscribe, e-mail:
<mailto:tomcat-user-unsubscribe@jakarta.apache.org>
For additional commands, e-mail:
<mailto:tomcat-user-help@jakarta.apache.org>

--
To unsubscribe, e-mail:
<mailto:tomcat-user-unsubscribe@jakarta.apache.org>
For additional commands, e-mail:
<mailto:tomcat-user-help@jakarta.apache.org>


--
To unsubscribe, e-mail:
<mailto:tomcat-user-unsubscribe@jakarta.apache.org>
For additional commands, e-mail:
<mailto:tomcat-user-help@jakarta.apache.org>

--
To unsubscribe, e-mail:
<mailto:tomcat-user-unsubscribe@jakarta.apache.org>
For additional commands, e-mail:
<mailto:tomcat-user-help@jakarta.apache.org>


--
To unsubscribe, e-mail:
<mailto:tomcat-user-unsubscribe@jakarta.apache.org>
For additional commands, e-mail:
<mailto:tomcat-user-help@jakarta.apache.org>

--
To unsubscribe, e-mail:   <mailto:tomcat-user-unsubscribe@jakarta.apache.org>
For additional commands, e-mail: <mailto:tomcat-user-help@jakarta.apache.org>


Mime
View raw message