The following is our query:
Product: SQL Server 2000 Driver for JDBC Service Pack 3

    We want to know whether this driver supports cursors as output parameters
in a stored procedure. If it does not support, is there any suggested
work-around.

    If it is supported, how to use them.

    The standard JDBC way to register the database specific output parameter
fails with an un-supported exception.

Our code:
cstmt.registerOutParameter(7,java.sql.Types.OTHER);

Exception:
Stored Procedure = {call sp_GetSummary(?,?,?,?,?,?,?)}
java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]The
specified
SQL type is not supported by this driver.
        at com/microsoft/jdbc/base/BaseExceptions.createException
        at com/microsoft/jdbc/base/BaseExceptions.getException
        at com/microsoft/jdbc/base/BasePreparedStatement.validateSqlType
        at com/microsoft/jdbc/base/BaseCallableStatement.registerOutParameter
        at sqlServer.main (sqlServer.java)

Thanks in advance.

Subbu wrote:

No.

Stick to standard JDBC. Cursors as output parameters are an Oracle-only
thing, supported by non-standard calls in the oracle driver. It is apparent
that you are very early into your experience with MS SQLServer, because you
should already know that MS SQLServer stored procedures have no way of
returning cursors as output parameters. SQLServer returns query results
in-line. A stored procedure is just like the fresh SQL you'd send for a
query. Here is an example of a complex stored procedure and the canonical
code form for processing all the update counts and result sets it might
return:

     String big_proc =
       " create procedure sp_myname @aname varchar(30) output as "
       + " begin                                                   "
       + "    declare @string varchar(30)                          "
       + "    select @aname = 'no name'                            "
       + "    declare @tmp_return_code INT                         "
       + "    declare str_cursor cursor for                        "
       + "       select name from master.dbo.sysdatabases          "
       + "       for read only                                     "
       + "    IF @@ERROR != 0                                      "
       + "    begin                                                "
       + "        RETURN @@ERROR                                   "
       + "    end                                                  "
       + "    open str_cursor                                      "
       + "    IF @@ERROR != 0                                      "
       + "    begin                                                "
       + "      RETURN @@ERROR                                     "
       + "    end                                                  "
       + "                                                         "
       + "    fetch str_cursor into @string                        "
       + "                                                         "
       + "    SELECT @tmp_return_code = @@ERROR                    "
       + "    IF @tmp_return_code != 0                             "
       + "    begin                                                "
       + "        GOTO WAY_OUT                                     "
       + "    end                                                  "
       + "                                                         "
       + "    WHILE (@@fetch_status = 0)                           "
       + "      BEGIN                                              "
       + "         FETCH str_cursor INTO @string                   "
       + "                                                         "
       + "          IF @@fetch_status = -1                         "
       + "          begin                                          "
       + "            select @tmp_return_code = 88                 "
       + "            GOTO WAY_OUT                                 "
       + "          end                                            "
       + "                                                         "
       + "          IF @@fetch_status = -2                         "
       + "          begin                                          "
       + "            select @tmp_return_code = 77                 "
       + "            GOTO WAY_OUT                                 "
       + "          end                                            "
       + "                                                         "
       + "          select @aname = @string                        "
       + "          SELECT @tmp_return_code = @@ERROR              "
       + "          IF @tmp_return_code != 0                       "
       + "          begin                                          "
       + "              RETURN @tmp_return_code                    "
       + "          end                                            "
       + "                                                         "
       + "       END                                               "
       + "                                                         "
       + " WAY_OUT:                                                "
       + "   CLOSE str_cursor                                      "
       + "   DEALLOCATE  str_cursor                                "
       + "   select @aname                                         "
       + "   return @tmp_return_code                               "
       + "                                                         "
       + " end                                                     ";

     Statement stmt = conn.createStatement();
     stmt.executeUpdate ( big_proc );
     CallableStatement cstmt1 = conn.prepareCall("{ ? = call sp_myname(?)}");

     cstmt1.registerOutParameter(1, java.sql.Types.INTEGER);
     cstmt1.registerOutParameter(2, java.sql.Types.VARCHAR);

     boolean hasResultSet = cstmt1.execute();
     int updateCount = -1;

     while (true)
       {
         if (getResultSet) {
           ResultSet r = cstmt1.getResultSet();
           while (r.next()) {
             // process result set
           }
           r.close();
         } else {
           updateCount = cstmt1.getUpdateCount();
           if (updateCount  != -1) {
             ;// process update count
           }
         }
         if ((!getResultSet) && (updateCount == -1)) break; // done with loop
         getResultSet = cstmt.getMoreResults();
       }

     // Best to retrieve status after all result sets and update counts
     // have been retrieved.
     System.out.println( "Output status: " + cstmt1.getInt(1));
     System.out.println( "Output param:  " + cstmt1.getString(2));


Joe Weinstein at BEA



Show quoteHide quote

Hi. I think a much simpler example may also be useful...

For a simpler procedure which just does one or a few queries, like:

create procedure myQueries @keval int as
begin
         select * from myTable
         select * from myOtherTable where myCol = @keyval
end

You will expect two result sets. One after the other.
The JDBC would look like:

      int myIntKeyVal = 123;
      PreparedStatement p = c.prepareStatement("{ call myQueries(?) }");
      p.setInt(1, myIntKeyVal );
      boolean hasResultSet = cstmt1.execute();
      int updateCount = -1;

      // Perfect loop for processing any arbitrarily complex procedure's inline returns
      while (true)
        {
          if (getResultSet) {
            ResultSet r = cstmt1.getResultSet();
            while (r.next()) {
              // process result set
            }
            r.close();
          } else {
            // leave this clause in! Even a select may
            // cause 'update counts' from triggers
            updateCount = cstmt1.getUpdateCount();
            if (updateCount  != -1) {
              ;// process update count if needed.
            }
          }
          if ((!getResultSet) && (updateCount == -1)) break; // done with loop
          getResultSet = cstmt.getMoreResults();
        }
        // Process any output parameters now, for CallableStatements

  Joe Weinstein at BEA

Thanks a lot for your response joe.

I understand your point about multiple result sets from a stored procedure.

My problem is that we already have many stored procedures which has defined
CURSOR as an output parameter. In order to use the stored procedures from
JDBC, we think that we need to write wrapper stored procedures which do NOT
have any CURSOR as OUT parameters.

We were looking to see if somebody else has tried any different solution.

Subbu wrote:

Ok, I may be obsolete! If you are saying that you have MS SQLServer stored
procedures that return cursors as output parameters, then I am way behind the
times. I stopped advancing my MS SQLServer knowledge at 6.5... If SQLServer
now provides cursors as output parameters, then there may well be a way
to get result sets from output parameters. Try a getObject(int paramIndex)
and print out what sort of object it is. Maybe it will be a ResultSet, and
you can then cast the object as such to use it.
Joe

Joe Weinstein wrote:
Joe,

SQL Server does indeed support cursor output parameters, but only
internally. I have no idea why that is, since cursor parameters are
basically numeric handles to cursors but I have tried to "convince" jTDS
to accept cursor output parameters but SQL Server was complaining that
it cannot convert a cursor handle to INT (or any other type I tried).

Alin,
The jTDS Project.

Alin Sinpalean wrote:

Show quoteHide quoteHi Alin, thanks. I don't know if TDS has anything ready to process the output from
a cursor (I don't think the DBMS uses TDS internally), but in the spirit of pure
hacking, how about us doing a brute-force loop of sending every integer value in
the Type argument for the registerOutParameter() call until we happen onto the one
the DBMS calls 'cursor'? I'd go 0 to 100,000, and then -1 to -100,000... ;)
Joe

Hi
Im a java developer using sqlserver as backend
Im using Microsoft JDBC Driver.
I have to same issue. Can you please help me ...its very urgent for me.
I have a stored procedure which returns cursor.
How should I retrieve this cursor from SP using jdbc
Please help me...
Thanks in advance

jaya nair wrote:

You can't. Cursor returns from MS SQLServer procedures are
only for using inside the DBMS, such as when one procedure
calls another procedure.
    You will not find *any* MS SQLServer client API for
getting procedure output parameter values from cursors.

Joe Weinstein at BEA Systems

Hi Joe,
Thanks for the quick response.

Is there any other solution for this??

Because in Oracle , we have OracleTypes.CURSOR
for ex: call.registerOutParameter(1,OracleTypes.CURSOR)
Is there any  equivalent for SQLServer ?
We already have stored procedures which return cursors as OUT Parameters so
can you please suggest me the alternate way for this problem
Thanks in Advance


Show quoteHide quote

jaya nair wrote:

Right. I expected you were not MS SQLServer developers, but were really
Oracle-based. Oracle stored procedures have CURSOR output parameters
to return query results because they can't do that with a simple query.
Oracle must add non-standard JDBC methods in their drivers to
accomodate this too. In SQLServer, procedures can act precisely like
the fresh SQL would act. If you want query results, a SQLServer
procedure would simply be like:

create procedure myQueries
as
begin
     select * from myTable where myCol = ...
     select myCol1, myCol2 from myOther table...
end

The query results (the two separate queries) would come back inline.

This means that when you convert Oracle procedures to MS SQLServer,
it is not as simple as you first thought, and you will have to do
more interpretive work with the procedures and the client code that
processes the returns. You cannot return MS SQLServer cursors as
output parameters. You would process the procedure like this in
standard JDBC:

     PreparedStatement ps = c.prepareStatement("{ exec myQueries() }");
     boolean getResultSet = ps.execute();
     int updateCount = -1;

     while (true) { // handle all in-line results from any procedure
       if (getResultSet) {
         ResultSet r = ps.getResultSet();
         while (r.next()) {
           // process result set now
         }
         r.close();
       } else {
         updateCount = ps.getUpdateCount();
         if (updateCount  != -1) {
           ;// process update count
         }
       }
       if ((!getResultSet) && (updateCount == -1)) break; // done with loop
       getResultSet = ps.getMoreResults();
     }

Sorry to be the bearer of bad news, but at least now you
know the work to do...
Joe Weinstein at BEA Systems

Show quoteHide quote

Hi Joe
Thanks for very clear explanation regarding my problem.
But let me know any other latest MSSQL Drivers supports CURSOR as OUT
parameter.As im using SQL Server 2000 Driver for JDBC Service Pack 3.
Are there any other latest drivers which can solve my problem?
Thanks A Lot

Show quoteHide quote

jaya nair wrote:

It's not a question of which driver. It is the DBMS which does not
return cursors from output parameters to *any* client. That is any
JDBC client, any driver, any C++ client, osql, *any* client.
    You have to rewrite any MS SQLServer procedure to remove any
cursor output parameter if you want any client to call the
procedure.
Joe

Show quoteHide quote

Hi Joe,

Thanks for your response.
This really helped me lot.
Now we are trying to do as you said ( multiple resultsets)
THANKS A LOT

Show quoteHide quote

Just Venting
Recyle an identity column number when max is reached
Nested sets in SQL - inventor?
Performance question: Separate database or additional table?
DYNAMIC USE