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

Recordset counter

    Class.forName("java.jdbc.odbc.JdbcOdbcDriver");
     String url = "jdbc:odbc:NortonDB";
       
     conDB = DriverManager.getConnection(url, "admin","");
     Statement stmt = conDB.createStatement();
     ResultSet rs = stmt.executeQuery(QueryLine);
       
     
//recordsetCount=(rs.getMetaData()).getColumnDisplaySize(1);

Hello,
The above line of code (that is commented out) is not doing what I want it to.  It returns some weird number that is definitely not the number of entries in a particular queried recordset.  I need to know how many entries are in a recordset I am querying.
  I am using an MS Access database and Odbc bridge.  The code is in an application written in java. I have no trouble with getting the info, but it would be nice to know  how many items are in the recordset.  So what is the call I need to make after I execute my Query?  'getRecordsetSize()'  would be nice.
Please give me a full answer. Thank you.
Georg Haugland
HyperGraphics
georita@iglobal.net
0
georita
Asked:
georita
  • 3
  • 2
1 Solution
 
jpk041897Commented:
GetRecordsetSize is not supported by all ODBC drivers, as an alternative, add a Count statement to uour SQL call.

The Count collumn will contain the number of records retruieved (generated by the DB server not the ODBC driver) and is guaranteed to work across all ptatforms and drivers.

For instance

SELECT FIELD1, FIELD2, COUNT(*) FROM table WHERE FIELD1 =17;
0
 
georitaAuthor Commented:
How do you retrieve the count after the call is made?  Is there a call to a particular part of the ResultSet object, or what?   I am still not understanding the source of the count that  I could then use in a interger variable.
0
 
georitaAuthor Commented:
My query is only one field at a time anmd cannot use the count in the SQL call.  
Here is an example call:
"select QuestionID from Question"
This was your suggestion, and it gives me an error.

"select QuestionID, count(*) from Question"

I am putting the results into an array and I must know the size of the ResultSet before I can do it correctly.  Please advise.
Georg Haugland

0
 
jpk041897Commented:
If you need the number of records before you fetch, you will have to run the querry twice. Assuming,of course, that your driver does not support getRecordsetCount.

Make the first call

"select count (QuestionID) from Question"

The result set should contain a single number value that contains the number of QuestionID rows in the table.

then run your querry to retrieve the record set.

Alternativley, Use the Querry Design View in Access and create your querry, including a count , then press the View Button and click SQL view.  That will produce the SQL statement that you require.

As a final alternative, you could save the previous querry and use it as a stored procedure.
0
 
georitaAuthor Commented:
Excellent answer!!!
I thank you for your time
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.

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