CODEWITHSUNDEEP
jdbcresultsetexecutequery
icoder
icoder

Reputation: 11

JDBC executeQuery() returning both ResultSet and output params

I am calling one sp with few out params and as per my requirement,I need to use ResultSet on some condition and out params on other conditions. But using executeQuery(), I am getting, JZ0R0: ResultSet has already been closed error.(I am using Sybase 15)

Here is the example:

Connection conn = ~~;        
CallableStatement cstmt = conn.prepareCall("{call my_sp(?)");
cstmt.registerOutParameter(1,java.sql.Types.INTEGER);
ResultSet rs = cstmt.executeQuery();

If i try to do, below code now,

int res = cstmt.getInt(1);

Then try to iterate the ResultSet object, as below, then I get above ResultSet close error.

ResultSetMetaData rsmd = rs.getMetaData();

Is there any way I can get the output param value and then iterate on ResultSet object or vice-versa?

Upvotes: 1

Views: 5524

Answers (2)

texelcode
texelcode

Reputation: 61

I am not familiar with syBase,
but may be you can output script like this StoredProcedure in MSSQL : 

DECLARE @RC int
DECLARE @pUserID nvarchar(50)
DECLARE @pDepartmentID int

-- TODO: Set parameter values here.

EXECUTE @RC = [AccessMgrDB].[dbo].[SP_GenerateAbsentPresentReport] 
   @pUserID
  ,@pDepartmentID

GO

and go with java code :

set.con.setAutoCommit(false); 
        Statement st2 = set.con.createStatement(); 

        String queryAbsent = "DECLARE @RC int\n" +
            "DECLARE @pUserID nvarchar(50)\n" +
            "DECLARE @pDepartmentID int\n" +
            "EXECUTE ["+dbSrcNames+"].[dbo].[SP_GenerateAbsentPresentReport] \n" +
            ""+USER +
            ","+DPTID+"";

        ResultSet rs2 = st2.getGeneratedKeys();

        while (rs2.next()){
              String userID = rs2.getString("UserID");
              Date logDate = rs2.getDate ("logDate");
              String logDay = rs2.getString("logDay");
              String status = rs2.getString ("status");
              int pivot = rs2.getInt ("pivotvalue");
              int pivot2 = rs2.getInt ("pivotvalue2");
              if(status.equals("Absent"))

                  absent.add(logDate.toString());

              if(status.equals("Present"))

                  present.add(logDate.toString());

        st2.getMoreResults(java.sql.Statement.CLOSE_CURRENT_RESULT);

        set.con.commit();

I was once have the same problem in Calling StoredProcedure before, and it was just days ago ;)

Upvotes: 0

Vincent
Vincent

Reputation: 21

According to the JDBC spec you must retrieve the result set before output parameters

Here is the original spec

7.1.7 Retrieve OUT Parameters after Results

Because of limitations imposed by some DBMSs, it is recommended that for maximum portability, all of the results in a ResultSet object generated by the execution of a CallableStatement object should be retrieved before OUT parameters are retrieved. When all values have been retrieved from a result set, the method ResultSet.next will return false.

If a CallableStatement object returns multiple ResultSet objects (which is possible only if it is executed with a call to the method execute), all of the results should be retrieved before OUT parameters are retrieved. In this case, to be sure that all results have been accessed, the Statement methods getResultSet, getUpdateCount, and getMoreResults need to be called until there are no more results. When all results have been exhausted, the method getMoreResults returns false, and the method getUpdateCount returns -1.

Upvotes: 2

Related Questions