tomcat-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "jon wingfield" <jon.wingfi...@mkodo.com>
Subject RE: OFF-TOPIC: Pointers to CallableStatement docs?
Date Tue, 20 Aug 2002 11:52:38 GMT
John,

I've dug out some old code and i think you may have to set initial values
for the OUT parameters as well.
We used functions with oracle 8i with no problems in this way:
One of the CallableStatements we prepared with "{?=call
pk_housekeeping.f_purge_inactive_consumers(?)}" and used as below:

<snippet>
private void initializeParameters(CallableStatement cs) throws SQLException
{
    int total = 0;
    if (cs!=null) {
        cs.setInt(1, total);
        cs.registerOutParameter(1, Types.INTEGER);
        if (purgeDate!=null) {
            cs.setDate(2, purgeDate);
        } else {
            cs.setNull(2, Types.DATE);
        }
    } else {
        throw new IllegalStateException ("CallableStatement has not been
initialized.");
    }
}
</snippet>

The statement was executed and the out parameters accessed via the getXXX
methods.

I do seem to recall (two years ago on a different project, i'm afraid) that
without the cs.setInt(1, total); line it did throw a SQLException. This may
be a driver dependent 'feature' coz the java.sql.CallableStatement javadocs
don't mention this as a requirement.
Hope this helps/works,

Jon

-----Original Message-----
From: Turner, John [mailto:JTurner@AAS.com]
Sent: 19 August 2002 19:27
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>


Mime
View raw message