retrieving output parameter from stored procedure in SQL2000 jdbc driver

retrieving output parameter from stored procedure in SQL2000 jdbc driver

Post by kevi » Wed, 19 Feb 2003 22:19:59



hi

iv'e got a problem reading a parameter from a stored procedure in
SQLServer 2000 from java. I've followed all the example code to make
this call, and it's stored proc, and it quite happily executes.
However, the second time i ran the procedure, (when it should have
been returning a non zero sequence column from the table, it returned
zero. I t still does, and i have no idea why

Can anybody see a daft newbie error?  

java code :-

    // updatesQuery.query2 is a string column name

    cstmt = copydb.prepareCall("{ ? = call getsequence (?, ?) }");
    cstmt.registerOutParameter(1, java.sql.Types.INTEGER);
    cstmt.setString(2, updatesQuery.query2);
    cstmt.registerOutParameter(3, java.sql.Types.INTEGER);
    .  
    .
    .
    iRetCode = cstmt.getInt(1);

    // this returns zero regardless of the column value  
    iSeqno = cstmt.getInt(3);

calling the stored proc :-

CREATE PROCEDURE [getsequence]


AS



-- use columnname in sp_executesql to dynamically make update and
select clauses
-- sequences is the table of sequence values. 1 per main table





[sequences]';


return
GO

 
 
 

retrieving output parameter from stored procedure in SQL2000 jdbc driver

Post by Joseph Weinstei » Thu, 20 Feb 2003 01:14:20



> hi

> iv'e got a problem reading a parameter from a stored procedure in
> SQLServer 2000 from java. I've followed all the example code to make
> this call, and it's stored proc, and it quite happily executes.
> However, the second time i ran the procedure, (when it should have
> been returning a non zero sequence column from the table, it returned
> zero. I t still does, and i have no idea why

> Can anybody see a daft newbie error?


Joe Weinstein at BEA

> java code :-

>     // updatesQuery.query2 is a string column name

>     cstmt = copydb.prepareCall("{ ? = call getsequence (?, ?) }");
>     cstmt.registerOutParameter(1, java.sql.Types.INTEGER);
>     cstmt.setString(2, updatesQuery.query2);
>     cstmt.registerOutParameter(3, java.sql.Types.INTEGER);
>     .
>     .
>     .
>     iRetCode = cstmt.getInt(1);

>     // this returns zero regardless of the column value
>     iSeqno = cstmt.getInt(3);

> calling the stored proc :-

> CREATE PROCEDURE [getsequence]


> AS



> -- use columnname in sp_executesql to dynamically make update and
> select clauses
> -- sequences is the table of sequence values. 1 per main table





> [sequences]';


> return
> GO


 
 
 

retrieving output parameter from stored procedure in SQL2000 jdbc driver

Post by kevi » Thu, 20 Feb 2003 19:05:15




> > hi

> > iv'e got a problem reading a parameter from a stored procedure in
> > SQLServer 2000 from java. I've followed all the example code to make
> > this call, and it's stored proc, and it quite happily executes.
> > However, the second time i ran the procedure, (when it should have
> > been returning a non zero sequence column from the table, it returned
> > zero. I t still does, and i have no idea why

> > Can anybody see a daft newbie error?


> Joe Weinstein at BEA

> > java code :-

> >     // updatesQuery.query2 is a string column name

> >     cstmt = copydb.prepareCall("{ ? = call getsequence (?, ?) }");
> >     cstmt.registerOutParameter(1, java.sql.Types.INTEGER);
> >     cstmt.setString(2, updatesQuery.query2);
> >     cstmt.registerOutParameter(3, java.sql.Types.INTEGER);
> >     .
> >     .
> >     .
> >     iRetCode = cstmt.getInt(1);

> >     // this returns zero regardless of the column value
> >     iSeqno = cstmt.getInt(3);

> > calling the stored proc :-

> > CREATE PROCEDURE [getsequence]


> > AS



> > -- use columnname in sp_executesql to dynamically make update and
> > select clauses
> > -- sequences is the table of sequence values. 1 per main table





> > [sequences]';


> > return
> > GO


did not notice until i'd posted, that i was executing the SQLUPDATE
twice. However that was not the real problem. the SQLSELECT variable
needs the dynamic parameters (from the SQLSERVER help on
sp_executesql, if you're reading this for advice)
but the parameters need to be defined in the call as * output *

the SQLUPDATE call should look like :-


sequences';


this code works fine.
thanks for posting.

 
 
 

1. Error at obtaining output parameters of the stored procedure, calling other stored procedure

From ASP we call the procedure usp_one MS SQL 7.0, which through linked
server calls(causes) the procedure usp_two MS SQL 6.5, placed on other
computer. Both procedures have both input, and output parameters.
The problem consists that in output parameters of the first procedure the
values output parameters second (are received by way of their following in
the second procedure), and the values output of parameters of the first
procedure are lost.
Certainly, the successful procedure call usp_one (is present in view of an
only return code) happens, only if the types output of parameters under the
order of their following coincide or can be converted by default.

2. why there is no arrays in stored procedures?

3. output parameters stored procedures / extended stored procedures

4. Printing screens in FP 2.6a

5. Howto?: Execute a stored procedure and retrieve an output parameter using SQLDMO

6. Some plonker typed rm temp.dbf AAARRGGHHH!

7. Retrieving datas with stored procedure and OUTPUT parameters

8. printing...

9. Retrieving OUTPUT parameters after a call to a stored procedure in C module with ESQL

10. retrieve value of output parameter from Stored Procedure

11. Retrieving output params from stored procedures ODBC-JDBC bridge Java SQL Server

12. Output parameter frm Stored Procedure via jdbc-odbc

13. JDBC: return values and output parameters from stored procedures