Passing a recordset to a class

[James Ajewole]

Hi,
Pls i need help. I want store frequently accessed data in a vector so i dont need to access the database over again and the solution i am using is as follows:
1.select all the data from the database
2.pass each resultset to a class (Record) which contains variables corresponding to each column of the database.
3.store the record object in a vector.
I tried this but i keep getting an error from the Record class that "no data found" it seems the recordset is not passed to the Record class. Any ideas. the code snippet is listed below:
thanks
// this is main class
while (rs.next()) {
// do some things here
Record rec = new Record(rs)
// add rec to a vector

}
// main class ends
public class Record {
private String name;
private String serialNo;
.
.
public Record(ResultSet rset) {
name = rset.getObject("employee_name").toString();
serialNo = rset.getObject("Serial").toString();
// others go here
}
}

[Joel McNary]

Are you using MS SQL Server by any chance? If so, are you connecting via the JDBC-ODBC bridge, or the JDBC driver? I'm not sure about the JDBC driver, but I do know that there is a problem with the MS SQL ODBC driver where you cannot retrieve the results from a record in the data set in a different order than which you selected them. That is:
SELECT NAME, AGE, WEIGHT FROM EMPLOYEE;
You must parse the record set:

rs.getString("NAME");
rs.getInteger("AGE");
rs.getInteger("WEIGHT");

If you try:

10: rs.getInteger("AGE");
11: rs.getInteger("WEIGHT");
12: rs.getString("NAME");

you will get an error at line 12 stating "no data found"; it can't go backwords to the "NAME" column.
----------------------------
----------------------------
If that's not the problem, please post teh SQL query that you are using and the method that calls this class. I'm afraid that I can't tell much from the method listed here.
Also, please post code using the [ CODE] and [ /CODE] tags; it makes them easier to read. (Push the "CODE" button on the message edit screen and place your code between the tags; or, you can just enter the tags by hand...)

[James Ajewole]

Actually i'm using msaccess. what i want to achieve is to pass the resultset as an argument to the constructor of another class. Is it possible to pass a resultset as an argument to a class or a method?
Also I tried passing a string array to the class but got some errors. i.e
String rsArray[] = new String[2];
//store resultset objects in string array
while (rs.next()) {
for (int i = 0; i<=2; i++) {
rsArray[i] = rs.getObject(i).toString();
}
}
//pass the array to a new class called EmployeeRecord
EmployeeRecord erec = new EmployeeRecord(rsArray)

is the above code correct? thanks.

[muthu muruges]

Some times the rs.getString("xxxx") (where xxxx refers to column name) will give problem. so try with the index instead of column name.
And index should start from 1 and not from 0.

[Stan James]

You can pass the result set to another class, but be wary of making too many classes aware of the database structure this way. If you can hide all knowledge that you're even using a relational database from other classes, you're better off.
Way back in the faded portions of my memory, I recall a result set that became invalid as soon as I did another operation on the database connection, so I had to get all the data out of the result set and into another structure before doing the next operation. In other words, caching result sets was right out. Any chance this kind of thing is hurting you?

[Jim Yingst]

Way back in the faded portions of my memory, I recall a result set that became invalid as soon as I did another operation on the database connection, so I had to get all the data out of the result set and into another structure before doing the next operation.
This may depend on the database, but in many cases you can have multiple concurrent ResultsSets for a Connection, no problem - as long as you have a separate Statement for each ResultSet. Generally each ResultSet is associated with the Statement that generated it, and if you re-use a Statement to generate a new ResultSet, the old ResultSet becomes invalid. Which could be the problem here. Probably you want to unload all data from the ResultSet before executing any other queries.

[Ron Newman]

From the 1.4.1 javadoc for ResultSet:
A ResultSet object is automatically closed when the Statement object that generated it is closed, re-executed, or used to retrieve the next result from a sequence of multiple results.

[Thomas Paul]

Sounds like you need a RowSet. Sun has some sample implemenations but they just took them down recently to re-work them. They should be back up before the end of the month.

[Jim Yingst]

Now, if only someone were to write a nice article explaining how to use RowSets...

[James Ajewole]

I think I'll try to use another resultset. I have another question, is it possible to pass a Vector to a method? Coz while trying possible solutions on how to pass the resultset to another class, i thought that if i get the data from the resultset into a vector, i could pass the vector as an arguement to another class but it seemed not to work.

[Joel McNary]

Yes, it is possible to pass Vectors and ResultSets as arguments--I've done both (although you really should be using ArrayLists or likewise instead of Vectors). It seems as though the problem is elsewhere. Could you please post your SQL query? That might help us understand your problem.