Store Vector in MySql database

[Nikhilesh Reddy]

How to store and retrive vectors in mysql database.
I try to store vector using setObject() method in mysql BLOB column and I try to retrive the vector using
Vector myVect = (Vector) rs.getObject("dbVect");
but there is ClassCastException.Please some one help me to solve this very urgent.
Thanks

[Mohamed Yousuff]

I had the same problem some time back. I solved it but I do not have the code with me now. So I give you a skeleton or a rough sketch of the code below. There may be a few syntax errors. Hope you can follow.

Vector v = new Vector() ;
v.addElement("Test") ;
ByteArrayOutputStream baos = new ByteArrayOutputStream() ;
ObjectOutputStream oos = new ObjectOutputStream( baos ) ;
oos.writeObject( v ) ;
oos.flush() ;
oos.close() ;
"Insert into test (obj) values( '" + baos.toString() + "' ) "
"select obj from test"
execute query
String str = resultset.getString("obj") ;
ByteArrayInputStream bais = new ButeArrayInputStream( str.getBytes() )
ObjectInputStream ois = new ObjectInputStream( bais ) ;
Vector v = (Vector)ois.readObject() ;

Now you get the original vector

------------------

[Benny Stelter]

This does not work on my project.
I store some objects into my vector and store it to my mysql db. That works sofar.
If I try to read the vector from the db this error appears:
java.io.InvalidClassException: java.util.Vector; local class incompatible: stream classdesc serialVersionUID = -2804478835259887871, local class serialVersionUID = -2767605614048989439
Would be great if somebody could help.
Thanx!

[Mike Alfree]

The previous post was on the right track with ByteArrayOutputStream. But you do not want to use toString() on the baos variable. You should use toByteArray() instead. toString() will distort the information within the stream which is causing your invalid class exception.

[Benny Stelter]

Thanx for help, but now this message appears:
java.io.StreamCorruptedException: invalid stream header
That streaming stuff is confusing me a little bit

[Peter den Haan]

If you're going to serialize, store the result as binary data. Never, ever, store the stuff as a string (or char(N) or varchar(N) or nchar(N) or nvarchar(N) or varchar2(N) or text or any other character-based data type). Character data can be subjected to character set conversions and what have you, and you can never be sure that the random binary goo you get back is the same as the random binary goo you put in.
If you have to use some character type, then encode the serialized data, e.g. using Base64.
- Peter

[Benny Stelter]

Well, I figured out that when I use baos.toString() I get the same as if I write it to a file and open it with the text editor.
Saving/loading the vector to/from a file works. So baos.toString() can't be that wrong.
In the mysql db I use the BLOB type (what else should I use?) for the vector.
I also found out, that if I remove that line
Vector v = (Vector) ois.readObject();
I'm getting NO error message.
I read the vector from db like Mohamed Yousuff described it.
[ June 24, 2003: Message edited by: Benny Stelter ]

[Benny Stelter]

Well I found a way now and it is that easy!
Storing into db:

Vector v = new Vector();
// ... add some objects ...
try {
    PreparedStatement ps = dbConn.prepareStatement("INSERT INTO tbl_table (BLOBcolumn) VALUES (?)");
    ps.setObject(1, v, Types.OTHER);
    // there is also a type called Types.BLOB, but it didn't work with my mysql...
    ps.executeUpdate();
    ps.close();
} catch (Exception e) {
    System.out.println("ERROR storing vector into db: " + e.toString());
}

Reading from db:

Vector v = new Vector();
try {
    Statement stmt = dbConn.createStatement();
    ResultSet rs = stmt.executeQuery("SELECT BLOBcolumn FROM tbl_table");
    
    if (rs.next())
        v = (Vector) rs.getObject(1);
    
    rs.close();
    stmt.close();
} catch (Exception e) {
    System.out.println("ERROR reading vector from db: " + e.toString());
}

Well I found an example like this on another forum and no one other than MARK MATTHEWS (MYSQL AB) posted it, so I think it should be the right way
The methods setObject() and getObject() do the serializing stuff for you, so you don't have to mess around with streams, nice eh?
[ July 03, 2003: Message edited by: Benny Stelter ]