Wednesday, September 12, 2007

JDBC v1.2 Application connectivity issue

Debugging an application connectivity issue - No data returned.

Steps taken -
  1. sql traces performed (stored procedure was being executed against db)
  2. sp returns data (proved this by running the traced code manually in a query window)
  3. became puzzled...

Solution - SQLServerException: The statement did not return a result set when sp uses cursors

The Java code was using executeQuery rather than execute . This meant that executeQuery was effectively seeing 'inside' the stored procedure and was looking at a recordset in use by a cursor, rather than the final recordset returned when the query was run manually.

"The method executeQuery is designed for statements that produce a single result set, such as SELECT statements"

"The method execute is used to execute statements that return more than one result set, more than one update count, or a combination of the two."

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1710309&SiteID=1

No comments: