• 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
  • Bear Bibeault
  • Devaka Cooray
  • Liutauras Vilda
  • Jeanne Boyarsky
Sheriffs:
  • Knute Snortum
  • Junilu Lacar
  • paul wheaton
Saloon Keepers:
  • Ganesh Patekar
  • Frits Walraven
  • Tim Moores
  • Ron McLeod
  • Carey Brown
Bartenders:
  • Stephan van Hulst
  • salvin francis
  • Tim Holloway

Retrieving data from cursors of stored procedures in MS SQL server  RSS feed

 
Ranch Hand
Posts: 75
Chrome Java Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello all, Can you please suggest me how to write the code which contains cursors in stored procedures in Ms SQL Server  so that I can retrieve using Result set in java program.

my code upto now:


when i use EXEC dbo.SampleProcedure;  I'm getting the output "Command(s) completed successfully." , instead i want all the firstnames and last names. Where i'm doing wrong?
 
Rancher
Posts: 3743
40
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What do you want your procedure to do?

At the moment it does the SELECT and then iterates over the results.

If you want the cursor returned then just do the SELECT (no CURSOR FOR).
 
Rajeev Srikhar
Ranch Hand
Posts: 75
Chrome Java Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks for the reply.
My StoredProcedure with cursor should return a Resultset, so that the same ResultSet I can retrieve in java program. Please tell me the steps in doing that
 
Dave Tolls
Rancher
Posts: 3743
40
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Then get rid of the whole cursor thing.

T-SQL will automatically return the last SELECT cursor:



That's pretty much it, which is why I asked what you wanted the procedure to do, as you may as well just do a SELECT.
 
Rajeev Srikhar
Ranch Hand
Posts: 75
Chrome Java Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks for replying, actually I got the ResultSet by using Stored Procedure like you mentioned. But I'm curious to use cursors in stored procedures and retrieve Result set data in java program.
 
Dave Tolls
Rancher
Posts: 3743
40
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Once you open the cursor in the procedure it is not then available for returning to the calling JDBC code.
That's the issue you had.
Because you started processing the cursor in the code then SQL Server didn't return the cursor.

ETA:  Actually, it might be more related to you closing the cursor at the end.
Not sure.
You might want to test that.
 
Ranch Hand
Posts: 531
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Rajeev Srikhar wrote:Thanks for replying, actually I got the ResultSet by using Stored Procedure like you mentioned. But I'm curious to use cursors in stored procedures and retrieve Result set data in java program.



Hi, Rajeev,

Should only use cursors when line by line data-processing on your result set is unavoidable for some reason. Have pity on your db server.

Read up a good recent book on MS SQL server, Amazon has plenty.

With best regards,

Anton.
 
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!