ResultSet Data Manipulation

[Karel Young]

I am creating a Java program that accesses a Database and so Far I have had no problems, but I have just realised that I need a "Multi-connect" architecture in my database rather than a perminant connection to the DataBase.
I did some research and found that once I closed the Database Link my ResultSet (I am using this to hold the data rather than an Array/Vector) closed as well, after some hunting I found that I needed to change my PreparedStatement line to read
---
PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM " + tbl, ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE, ResultSet.HOLD_CURSORS_OVER_COMMIT);
---
But when I try to run this command I get this Error
---
Exception in thread "main" java.lang.UnsupportedOperationException
at sun.jdbc.odbc.JdbcOdbcConnection.setHoldability(JdbcOdbcConnection.java:1698)
---

Apart from this my program is fully functional.
If anyone can give me an idea on why I am getting this result/How to fix it. then I would appreciate it.
My System
Linux - Debian 3.0 - Kernel 2.4.25
Windows XP
mySQl
MS Access
Java 1.4.2_02

[Ben Wood]

Hi Karel.
The way I have approached this in the past (and I'm not sure whether it's a good way or not to be honest perhaps others will comment ) is to create my own class to wrap the ResultSet up and hence keep it after the DB connection has been closed.
I have something along the lines of:

 
public class MyDBResults
{
private Vector data;   // Holds the raw data from the database
private Vector metadata;   // Holds a list of DB field names as metadata
// CONSTRUCTOR - accepts a vector of query data and a vector of metadata
public MyDBResults(Vector d, Vector m)
{
data = d;
metadata = m;
}
// Return row data
public Vector getData()
{
return data;
}
// Return metadata (column headers)
public Vector getMetaData()
{
return metadata;
}
}
 

The two Vector arguments to the constructor are taken from the genuine ResultSet object before it is closed.
Hope this helps a little

[Ben Wood]

...and, I forgot this, which is how I populate those two Vectors that are passed to MyDBResults

stmt = conn.createStatement();
rs = stmt.executeQuery(sql_query); // Run SQL query on DB connection
rsmd = rs.getMetaData();
Vector data_v = new Vector();   // Holds raw DB query results (each element contains a vector of actual values)
// Populate query data into vector(s)
while(rs.next())
{
Vector row_v = new Vector();// Vector to hold a row of raw data from the database query
for(int i = 1; i <= rsmd.getColumnCount(); i++)
{
row_v.addElement(rs.getString(i));
}
data_v.addElement(row_v);
}
Vector metadata_v = new Vector();// Holds database result metadata (a list of field names)
// Populate metadata
for(int i = 1; i <= rsmd.getColumnCount(); i++)
{
metadata_v.addElement(rsmd.getColumnName(i));
}
// Parcel data and metadata up into one handy object to return
MyDBResults dbr = new MyDBResults(data_v, metadata_v);
 

[Karel Young]

I have thought about using Vectors but If possible I want to avoid using them for the DB implementation
I prefer the way the the ResultSet interface works and it also shortens some of the code by some quite large amounts

[Ben Wood]

Fair point. I'd be interested to see what else comes out of this thread myself as I could also use a more elegant solution to this ResultSet being closed problem.

[David Peterson]

Take a look at iBATIS SQL Maps.

[Karel Young]

Looking around on the Sun WebSite (Which is hard as 50% odd is down) it looks like they are creating a CachedRowSet Class which extends RowSet which in turn extends Resultset and gives the result I am looking for.
But although there are indications that you get get a copy of it off the web site (with Registration) there is actually no links to the Class itself.
I am doing a lot of hunting around at the moment to see what I can find.

[Karel Young]

I have found that I can add a new Record to the Record set by using the InserRow() method but I am running into an interensting problem in that the Database has an AutoIncrement field as the ID and as a result I cannot write to that field without the program crashing.
But I also cannot not write to the field because it throws an error.
An unexpected exception has been detected in native code outside the VM.
Unexpected Signal : 11 occurred at PC=0x4CD89AE3
Function=(null)+0x4CD89AE3
Library=/usr/lib/odbc/libmyodbc.so
NOTE: We are unable to locate the function name symbol for the error
just occurred. Please refer to release documentation for possible
reason and solutions.
There is about 5 times that amount of error but the rest is a library trace.
Any ideas?