• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Liutauras Vilda
  • Bear Bibeault
  • Junilu Lacar
  • Martin Vashko
Sheriffs:
  • Jeanne Boyarsky
  • Tim Cooke
  • Knute Snortum
Saloon Keepers:
  • Ron McLeod
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
Bartenders:
  • Scott Selikoff
  • salvin francis
  • Piet Souris

CallableStatement connection problem

 
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
 
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
 
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
 
author and iconoclast
Posts: 24203
43
Mac OS X Eclipse IDE Chrome
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Moving to the JDBC forum.
 
Barry's not gonna like this. Barry's not gonna like this one bit. What is Barry's deal with tiny ads?
Java file APIs (DOC, XLS, PDF, and many more)
https://products.aspose.com/total/java
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!