?
Solved

How can i retrieve the result set's row count of a stored procedure in java

Posted on 2011-04-22
14
Medium Priority
?
485 Views
Last Modified: 2012-08-14
Hello folks;
I'm new to jdbc and trying to figure out how to get the row count of a stored procedure result set. (SQL Server Stored Procedure)

Something similiar to C#'s datatable Rows.Count  kind of result before i do any further work.

My stored procedure returns one table result with multiple rows.
Please direct me to the right solution.
0
Comment
Question by:muratkazanova
  • 7
  • 4
  • 3
14 Comments
 
LVL 47

Expert Comment

by:for_yan
ID: 35446985
I am not sure I undersdtand what you mean about stored procedure but this is how you can determine number of row in result set:

5.1.4 Determining the Number of Rows in a Result Set

With the new cursor movement methods, it is easy to see how many rows a scrollable ResultSet object contains. All that is necessary is to go to the last row of the result set and get the number of that row. In the following example, rs will have one row for each employee.

ResultSet rs = stmt.executeQuery(
"SELECT LAST_NAME, FIRST_NAME FROM EMPLOYEES");
rs.last();
int numberOfRows = rs.getRow();
System.out.println("XYZ, Inc. has " + numberOfRows + " employees");
rs.beforeFirst();
while (next()) {
. . . // retrieve first and last names of each employee
}


0
 
LVL 47

Expert Comment

by:for_yan
ID: 35446990
If you are talking about java stored procedure then you determine the number of rows in the same way and then make sure your procedure returns this number
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 35447011
Why would you want to know?

if you need to know, it's better to count them as you're iterating the ResultSet, which you presumably need to do anyway. Otherwise, you're actually engaging in quite an expensive operation.

Some dbs have a rowcount property which you could return in the SP
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 47

Expert Comment

by:for_yan
ID: 35447885
I wanted to second the remark of CEHJ. Thourgh the snippet I pointed to should certainly work, you should use it with caution and have a good reason to do it. In my many years dealing with Jdbc I never used this .last method and usually iteration through rrsultset is what was necessary. I also think that with really huge resultsets it does not return you the whole result set (because I simetines iterated through really huge tables which would never fit into memory) so I guess this .last metjhod woul sometimes probably be as long as iteration through the whole. If you really nEed to know the row count upfront for a big resultset it is probably safer to run select count(*) from ... Wite the same clause conditions and then retrieve the number as rs.getInt(1) in your code. In this way you at least may be sure that database does it in the most efficient way.
$with all that if you know your resultset is reasonable size .last() method should work fuine
0
 

Author Comment

by:muratkazanova
ID: 35449078
No stored rocedure is in SQL Server and it hasn't been created in JAVA.
for_yan the way you provided works with command strings as you used with select .....
but unfortunately it didn't work with stored procedure result sets cause that's what i tried before i asked my question here.

I'm a net programmer and ado.net gives us this count pretty much effortless, i do a lot of object mapping in my code before i start working on them i want the count.

if i run the following code snippet

rs = proc.getResultSet();
            			rs.last();
            			int count = rs.getRow();
            			rs.beforeFirst();
            		    System.out.println(
            		    		String.format("We have /d result(s), here they are:",count));

Open in new window


before i start parsing, then i recieve The requested operation is not supported on forward only result sets. error message.

Hope i explained my issue a little bit in more detail now.
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 35449111
This is what i was referring to earlier

http://technet.microsoft.com/en-us/library/ms187316.aspx

Return that as an OUT parameter
0
 
LVL 47

Expert Comment

by:for_yan
ID: 35449113

Maybe you could just write a new table in your stored procedure
and call this stored procedure from the same java code.
after it writes  the table you can have just simple select call
and this should work - of course if I understand what you are doing
0
 
LVL 47

Expert Comment

by:for_yan
ID: 35449130
And returtning paraemeter cfrom stored procedure is another option which Imentioned from the very beginning  in ID:35446990
0
 

Author Comment

by:muratkazanova
ID: 35454210
Modifying the existing stpred parameter is not an option for me at this moment.
0
 
LVL 47

Accepted Solution

by:
for_yan earned 2000 total points
ID: 35454229


Perhpas, you can use CashedRowSet, like is shown here:
http://onjava.com/pub/a/onjava/2004/06/23/cachedrowset.html

and CashedRowSet has a method size() which should return
number of rows
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 35455941
You still haven't said why you need to know the row count muratkazanova ...
0
 

Author Comment

by:muratkazanova
ID: 35491329
Sorry for_yan, i didn't have a chance to apply CachedRowSet approach yet, i hoping to work on it this weekend. I'll let you how it turns out.
0
 
LVL 47

Expert Comment

by:for_yan
ID: 35491489
Great, no rush, give it a try.
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 35491526
A CachedRowSet won't really make any difference. The only reason that it can give you the row count is that rows have *already* been retrieved, which of course you could do yourself
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This tutorial will introduce the viewer to VisualVM for the Java platform application. This video explains an example program and covers the Overview, Monitor, and Heap Dump tabs.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses
Course of the Month14 days, 8 hours left to enroll

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question