• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 569
  • Last Modified:

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

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
muratkazanova
Asked:
muratkazanova
  • 7
  • 4
  • 3
1 Solution
 
for_yanCommented:
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
 
for_yanCommented:
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
 
CEHJCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
for_yanCommented:
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
 
muratkazanovaAuthor Commented:
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
 
CEHJCommented:
This is what i was referring to earlier

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

Return that as an OUT parameter
0
 
for_yanCommented:

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
 
for_yanCommented:
And returtning paraemeter cfrom stored procedure is another option which Imentioned from the very beginning  in ID:35446990
0
 
muratkazanovaAuthor Commented:
Modifying the existing stpred parameter is not an option for me at this moment.
0
 
for_yanCommented:


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
 
CEHJCommented:
You still haven't said why you need to know the row count muratkazanova ...
0
 
muratkazanovaAuthor Commented:
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
 
for_yanCommented:
Great, no rush, give it a try.
0
 
CEHJCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

  • 7
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now