Getting error while saving image as blob in oracle

[Deepa More]

  
        con.setAutoCommit(false);
            stmt = con.createStatement();
            
            File file=new File(inputBinaryFileName);
            inputFileInputStream = new FileInputStream(file);
        
            sqlText =
                "INSERT INTO myform (id, data) " +
                "   VALUES('1', EMPTY_BLOB())";
            stmt.executeUpdate(sqlText);
            con.commit();
            stmt.close();
            Statement stmt2 = con.createStatement();
            sqlText = 
                "SELECT data " +
                "FROM   myform " +
                "WHERE  id = '1' " +
                "for update";
            rset = stmt2.executeQuery(sqlText);
            rset.next();
            image = ((OracleResultSet) rset).getBLOB("data");
            
            chunkSize = image.getChunkSize();
            binaryBuffer = new byte[chunkSize];
            
            position = 1;
            while ((bytesRead = inputFileInputStream.read(binaryBuffer)) != -1) {
                bytesWritten = image.putBytes(position, binaryBuffer, bytesRead);
                position        += bytesRead;
                totbytesRead    += bytesRead;
                totbytesWritten += bytesWritten;
            }
            
            inputFileInputStream.close();
 
            con.commit();
            rset.close();
            stmt2.close();

Got following error at:

  rset = stmt2.executeQuery(sqlText);  

java.sql.SQLException: General error
at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6987)
at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7115)
at sun.jdbc.odbc.JdbcOdbc.SQLExecDirect(JdbcOdbc.java:3111)
at sun.jdbc.odbc.JdbcOdbcStatement.execute(JdbcOdbcStatement.java:338)
at sun.jdbc.odbc.JdbcOdbcStatement.executeQuery(JdbcOdbcStatement.java:253)
at InsertFile.writeBLOBPut(InsertFile.java:87)
at InsertFile.main(InsertFile.java:35)

[Carol Enderlin]

What kind of driver are you using?

[Deepa More]

I am using Jdbc-Odbc driver:

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
con=DriverManager.getConnection("jdbc:odbc:TestDSN2","test","test");

[Deepa More]

It works when I changed the driver to thin driver by

oracle.jdbc.driver.OracleDriver

Still I am confused as the method I used in example was BOLOB.putBytes(,,). And when I tried to use setBinaryStream of PreparedStatement it doesn't set the blob value in db. Code is:

  
con.setAutoCommit(false);
Statement st=con.createStatement();
String initstr="insert into myform values ('1', EMPTY_BLOB())";
st.execute(initstr);
st.close();
con.commit();
PreparedStatement pstmt=con.prepareStatement("update myform set data = (?) where id ='1'");
File file=new File("C:\test.jpg");
InputStream is=new FileInputStream(args[1]);
//pstmt.setAsciiStream(1,is,is.available());//not working
pstmt.setBinaryStream(1,is,is.available());//not working
/* //gave java.sql.SQLException: Data size bigger than max size for this type: 115137 (at setBytes)
byte[] binaryBuffer = new byte[is.available()];
int bytesRead = is.read(binaryBuffer);
while(bytesRead > 0) {
     bytesRead = is.read(binaryBuffer);
}
pstmt.setBytes(1,binaryBuffer);*/
int upd = pstmt.executeUpdate();
is.close();
pstmt.close();
System.out.println("Length of the file "+(int)file.length()+" update count:"+upd);
System.out.println("Inserted the record successfully.");
con.commit();

There are so many ways of inserting blob data type, but what's the difference between them and when to use which method?


Thanks for support so far.
Deepa
[ January 30, 2007: Message edited by: Deepa More ]

[Carol Enderlin]

You can google on: Blob setBinaryStream

and find example code like I paste here. After inserting empty_blob() then you select the blob for update (rather than using an update statement).

Google hit with blob code:

>> // execute this query to create a BLOB.
>> preparedStatement = connection.prepareStatement("INSERT INTO A (a1,
>> a2) VALUES ('a1', EMPTY_BLOB())");
>> preparedStatement .execute();
>>
>> // update it in place
>> preparedStatement = connection.prepareStatement("SELECT a2 FROM A
>> WHERE a1 = 'a1' FOR UPDATE");
>> resultSet = preparedStatement .executeQuery();
>> rs.next();
>> Blob blob = rs.getBlob(1);
>> OutputStream out = blob.setBinaryStream(1);

That is what I've used in the past and I was aiming for a solution that didn't require any Oracle-specific class casts.

[Deepa More]

Thanks Carol for the reply.
I tried your solution, but

  OutputStream out = blob.setBinaryStream(1);  

is giving me the exception saying that

java.sql.SQLException: Unsupported feature

Does this mean, this feature is supported in later versions of oracle.
Currently I am using Oracle9i.

Thanks again,
Deepa

[Carol Enderlin]

What java version are you using?

java.sql.Blob's setBinaryStream documenation says it was added with JDK 1.4.

The oracle 9i driver shouldn't be a problem.

[Deepa More]

Hi Carol,
I am using jdk142_08 and included classes12.jar from my Oracle9 home.
But still getting same SQLException of Unsupported feature.

java.sql.SQLException: Unsupported feature
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:269)
at oracle.jdbc.dbaccess.DBError.throwUnsupportedFeatureSqlException(DBError.java:689)
at oracle.sql.BLOB.setBinaryStream(BLOB.java:1007)
at InsertFile.writeBlob(InsertFile.java:53)
at InsertFile.main(InsertFile.java:23)

Thanks
deepa
[ February 05, 2007: Message edited by: Deepa More ]

[Carol Enderlin]

Originally posted by Deepa More:
Hi Carol,
I am using jdk142_08 and included classes12.jar from my Oracle9 home.
But still getting same SQLException of Unsupported feature.

[ February 05, 2007: Message edited by: Deepa More ]

Like I said, java.sql.Blob's setBinaryStream method was added with JDK 1.4. The 12 in classes12.zip is for jdk 1.2, so it's unlikely that jar supports that feature. A better bet would to use ojdbc14.jar, since it supports jdk 1.4.

[Pankaj Mangal]

Deepa what you did for resolving this problem, i am facing this problem now.
Please help to resolve or any other way to store image in DB

Deepa More wrote:

  
        con.setAutoCommit(false);
            stmt = con.createStatement();
            
            File file=new File(inputBinaryFileName);
            inputFileInputStream = new FileInputStream(file);
        
            sqlText =
                "INSERT INTO myform (id, data) " +
                "   VALUES('1', EMPTY_BLOB())";
            stmt.executeUpdate(sqlText);
            con.commit();
            stmt.close();
            Statement stmt2 = con.createStatement();
            sqlText = 
                "SELECT data " +
                "FROM   myform " +
                "WHERE  id = '1' " +
                "for update";
            rset = stmt2.executeQuery(sqlText);
            rset.next();
            image = ((OracleResultSet) rset).getBLOB("data");
            
            chunkSize = image.getChunkSize();
            binaryBuffer = new byte[chunkSize];
            
            position = 1;
            while ((bytesRead = inputFileInputStream.read(binaryBuffer)) != -1) {
                bytesWritten = image.putBytes(position, binaryBuffer, bytesRead);
                position        += bytesRead;
                totbytesRead    += bytesRead;
                totbytesWritten += bytesWritten;
            }
            
            inputFileInputStream.close();
 
            con.commit();
            rset.close();
            stmt2.close();

Got following error at:

  rset = stmt2.executeQuery(sqlText);  

java.sql.SQLException: General error
at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6987)
at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7115)
at sun.jdbc.odbc.JdbcOdbc.SQLExecDirect(JdbcOdbc.java:3111)
at sun.jdbc.odbc.JdbcOdbcStatement.execute(JdbcOdbcStatement.java:338)
at sun.jdbc.odbc.JdbcOdbcStatement.executeQuery(JdbcOdbcStatement.java:253)
at InsertFile.writeBLOBPut(InsertFile.java:87)
at InsertFile.main(InsertFile.java:35)