Win a copy of The Business Blockchain this week in the Cloud forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

CallableStatement connection problem

 
mahesh narayan
Ranch Hand
Posts: 41
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi all

I have a requirement where my stored procedure return an errorcode of 1 if the select query throws exception else returns 0. The errorcode is set as an output parameter.



when I try executing this piece of code an exception stating that the closed is thrown at line A.

java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Object has been closed.
at com.microsoft.jdbc.base.BaseExceptions.createException(Unknown Source)
at com.microsoft.jdbc.base.BaseExceptions.getException(Unknown Source)
at com.microsoft.jdbc.base.BaseResultSet.validateClosedState(Unknown Source)
at com.microsoft.jdbc.base.BaseResultSet.commonFetchInitialize(Unknown Source)
at com.microsoft.jdbc.base.BaseResultSet.next(Unknown Source)


It would be helpful if I someone can let me knowwhy this is happening and what is the alternative for this. Any help is highly appreciable.Thanks in advance.

rgds
Mahesh
 
somkiat puisungnoen
Ranch Hand
Posts: 1312
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
please, make sure your result of store procedure have data ( CURSOR is closed before return to your code or not???)


and post your store procedure for more information .
 
mahesh narayan
Ranch Hand
Posts: 41
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
This is the stored procedure am using

create PROCEDURE readsp
@ID int,
@ErrorCode int OUTPUT
AS

SELECT name,roll
FROM test where roll = @ID

set @ErrorCode = @@ERROR
IF @ErrorCode <> 0
BEGIN
RETURN -1
END
 
sumit chawla
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Chech ur java code u might closing the connection or statement object before executing close it after ur fetching is over
 
mahesh narayan
Ranch Hand
Posts: 41
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I got the following explanation from sun.com

"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.

After all values have been retrieved from ResultSet objects (using ResultSet.getter methods), and after it has been determined that there are no more update counts, values from OUT parameters can be retrieved using CallableStatement.getter methods. Similar to the ResultSet getter methods, the CallableStatement getter methods have two forms, one that uses the column index to indicate the column from which to retrieve a value, and one that uses the column name to indicate the column. Note that the getter methods using the column name were added in the JDBC 3.0 API, so check that your driver implements this feature before using it."


Can anyone suggest any alternative to achieve this requirement
 
mahesh narayan
Ranch Hand
Posts: 41
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks somkiat puisungnoen and Sumit chawla for your help. I understand that JDBC automatically takes care of this requirement.

regards
Mahesh
 
Ernest Friedman-Hill
author and iconoclast
Marshal
Pie
Posts: 24212
35
Chrome Eclipse IDE Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Moving to the JDBC forum.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic