tomcat-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Reynir Hübner <rey...@hugsmidjan.is>
Subject RE: OFF-TOPIC: Pointers to CallableStatement docs?
Date Mon, 19 Aug 2002 21:25:52 GMT
Hi, 
I would first like to say, your code looks ok. I dont think it matters in what order you set
the parameters.
Are you calling a procedure or a function, I've had some problems with functions, in my experince
a function must have a OutParameter in the no. 1 param. 
which errors do you get ?

The stored-procedure-call should return an exception with errorcode / messages from the database-server
or the jdbc-driver. 
So, if you want us to be able to help, I think an error message could be very helpfull.

below is a sample of a working code, that calls stored procedure in oracle, I guess it's a
bit different, as the oracle driver has it's "own" datatypes. 

regards, 
reynir@reynir.net

 


String outParamTypeName = "PACKAGENAME.NAMESLIST";
String inParamTypeName = "PACKAGENAME.NAMES";

try
{
  String call = "{call package.name.Procedure(?,?,?,?)}";
  ArrayDescriptor arrayDesc = new ArrayDescriptor (inParamTypeName, m_connection);
  oracle.sql.ARRAY array = new oracle.sql.ARRAY(arrayDesc, m_connection, arrayValues);
  oracle.jdbc.driver.OracleCallableStatement cs =
          (oracle.jdbc.driver.OracleCallableStatement) m_connection.prepareCall(call);

  Calendar c = Calendar.getInstance();
  java.util.Date d = c.getTime();
  java.sql.Date dags= new java.sql.Date( d.getTime() );
  oracle.sql.NUMBER update = new oracle.sql.NUMBER (parm_update);
  cs.setARRAY(1,array);
  cs.setDate(2,dags);
  cs.registerOutParameter(3,OracleTypes.ARRAY,outParamTypeName);
  cs.setNUMBER(4,update);
  cs.execute();
  resultArray = cs.getARRAY(3);
  Object[] names_list = (Object []) resultArray.getArray();
  for (int y=0;y!=names_list.length;y++)
  {
    BigDecimal LastValue=null;
    BigDecimal firstValue=null;
    Struct l_objstruct = (Struct)names_list[y];
    // get the attributes in the STRUCT l_objstruct
    Object l_objval[] = l_objstruct.getAttributes();
    // Retrieve individual attributes
    if (l_objval[2]!=null) {LastValue = ((BigDecimal) l_objval[2]);}
    if (l_objval[3]!=null) {firstValue= ((BigDecimal) l_objval[3]);}
    // output the outcome
    out.write( number.format(LastValue.setScale(2, BigDecimal.ROUND_HALF_UP)));
    out.write( number.format(firstValue.setScale(2, BigDecimal.ROUND_HALF_UP)));
  }
  // close database connection
  m_connection.close();
}
catch(Exception e)
{
  out.write("<font color=red><pre>"+ e.toString() + "</pre></font>");
}








> -----Original Message-----
> From: Turner, John [mailto:JTurner@AAS.com]
> Sent: 19. ágúst 2002 18: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