How to insert/Retrieve object as Blob to/from database table?

[Tom Barns]

Hi I'm trying to insert and retrieve a B.lob into a table,I'm getting null as result for Blob object and i'm getting the right result for a string column.
here is my code:
//====================
import java.sql.*;
import java.net.*;
import COM.ibm.db2.app.*;
import COM.ibm.db2.jdbc.app.*;
class Test
{
public int x=10;
public void show()
{
System.out.println("Hello World today ");
}
}
public class InsertData
{
public static void main(String arg[])
{
Test test = new Test();
try
{
Class.forName("COM.ibm.db2.jdbc.app.DB2Driver");
String url = "jdbc:db2:d1a1";
Connection connection =
DriverManager.getConnectionurl, "fmc", "password");
String sql = "INSERT INTO CON.FNCONNECTIONTABLE
( USERID, FNCONNECTOBJ ) VALUES ( ?, ? )";
PreparedStatement ps = connection.prepareStatement(sql);
ps.setString(1,"Sys");
ps.setBlob(1,(java.sql.Blob)test );
ps.executeUpdate();

}
catch(Exception e)
{
System.out.println(e.getMessage());
}
}
}
//=====================================
import java.sql.*;
import java.net.*;
import COM.ibm.db2.app.*;
import COM.ibm.db2.jdbc.app.*;
public class GetData
{

public static void main(String arg[])

{

try
{
Class.forName("COM.ibm.db2.jdbc.app.DB2Driver");
//String url = "jdbc dbc:mydatabase";
String url = "jdbc:db2:d1a1";
Connection connection =
DriverManager.getConnection(url, "fmc", "password");
Statement st=connection.createStatement();
String sql="SELECT * FROM CON.FNCONNECTIONTABLE";

ResultSet rs=st.executeQuery(sql);
while(rs.next())
{
System.out.println(rs.getString(1) +"*********"+(java.sql.Blob) rs.getBlob(2));

}

}
catch(Exception e)
{
System.out.println(e.getMessage());
}


}

[Suresh Selvaraj]

Hi,
In your example, the string and the object is inserted into the same column.
Here it is;
ps.setString(1,"Sys");
ps.setBlob(1,(java.sql.Blob)test );
It should be ps.setBlob(2,test);
-Suresh Selvaraj

[Suresh Selvaraj]

Hi,
To add to my previous post...
Also you need to materialize the blob object that you retrieve either as an array of bytes or as a stream and only then you can print it.
You may use the methods getBinaryStream() or getBytes() of the BLOB Interface to achieve this.
Example:
Blob blob = rs.getBlob(2); (as in your example)
Long length = blob.length();
byte[] b = blob.getBytes(1, length);
Now you can loop thru. the byte array and print all the bytes or you can use an input stream as shown below.
ByteArrayInputStream baip = new ByteArrayInputStream(b);
ObjectInputStream ois = new ObjectInputStream(baip);
Test t = (Test)ois.readObject();
Now you can print using System.out.println(t.x);
-Suresh Selvaraj
Jtips.net