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,
[EMAIL PROTECTED]
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:[EMAIL PROTECTED]]
> 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: [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]>
