Thanks for the question, justin.
Asked: September 02, 2000 - 9:02 pm UTC
Last updated: March 23, 2001 - 9:58 pm UTC
Version: 8.0.6
Viewed 1000+ times
You Asked
I wrote a java servlet which should populate a Blob value into the database, but I am getting the following error:
java.io.IOExeception Error while doing write LobByteValue
java.sql.SQLException row containing the LOB value is not locked
I am using the follwing code for my insert
Connection dbcon = null;
ResultSet rs = null;
Statement stmt = null;
Blob myBlob = null;
InputStream inStream = null;
try{
Properties props = new Properties();
props.put("user","etscdvl");
props.put("password","dvlp");
props.put("server","dccts1001.cscoe.ac.com");
props.put("database","enowotds");
Class.forName("weblogic.jdbc20.oci.Driver");
dbcon = DriverManager.getConnection ("jdbc20:weblogic:oracle", props);
String qry = "insert into mailbox values(9,9,9,empty_blob(),'t',1,'2000-01-01','s')";
stmt = dbcon.createStatement();
stmt.executeUpdate(qry);
String qry2 = "select PDF from mailbox where id_internal=9";
stmt = dbcon.createStatement();
rs = stmt.executeQuery(qry2);
while(rs.next()){
myBlob = rs.getBlob("PDF");
}
File binaryfile = new File("/opt/WebLogicCommerce/server/public_html/keith/mmexs.pdf");
System.out.println("length of test file is " + binaryfile.length());
inStream = new FileInputStream(binaryfile);
OutputStream os = null;
long size = myBlob.length();
byte[] buffer = new byte[65534];
int numBytes = inStream.read(buffer);
while (numBytes > 0) {
os.write(buffer, 0, numBytes);
numBytes = inStream.read(buffer)
}
os.flush();
os.close();
PreparedStatement pstmt = null;
String qry3 = "Update mailbox set PDF = ? where id_internal=9";
pstmt = dbcon.prepareStatement(qry3);
pstmt.setBlob(1,myBlob);
pstmt.executeUpdate(qry3);
}
catch(ClassNotFoundException cnfe){
}
catch(SQLException sqle){
}
My question is Do I need to lock the row previous to writing the blob into the record? If so, how do I do that? I think the two errors may be related to one another. thanks
and Tom said...
JDBC, what whatever lame reason, has "auto commit" turned on (which sort of defeats the whole point of a transactional database). Almost every Java/JDBC program should have:
conn.setAutoCommit (false);
immediately after a connect. That'll fix your issue above (with regards to the row locking issue).
Here is a quick and dirty example for your reference:
import java.io.*;
import java.lang.*;
import java.sql.*;
import java.util.*;
import oracle.jdbc.driver.*;
public class pdf
{
public static void main(String[] args) throws Exception
{
int length;
DriverManager.registerDriver (new OracleDriver());
Connection conn = DriverManager.getConnection
// ("jdbc:oracle:oci8:@ora8i.us.oracle.com",
("jdbc:oracle:thin:@aria:1521:ora8i",
"scott","tiger");
conn.setAutoCommit (false);
System.out.println( "connected..." );
PreparedStatement insertStmt = conn.prepareStatement
("insert into t ( id, pdf ) " +
"values ( 9, empty_blob() )");
System.out.println
( "prepared statement for v8.x BLOB insert..." );
PreparedStatement getStmt = conn.prepareStatement
("select pdf from t where id = 9 for update");
System.out.println
( "prepared statement for v8.x BLOB select..." );
insertStmt.executeUpdate();
OracleResultSet rset81 =
(OracleResultSet) getStmt.executeQuery();
if ( rset81.next() )
{
OutputStream os =
rset81.getBLOB(1).getBinaryOutputStream();
System.out.println( "Got v8.x BLOB stream..." );
File binaryfile = new File( "/tmp/zzzz" );
System.out.println("length of test file is " +
binaryfile.length());
InputStream is = new FileInputStream(binaryfile);
System.out.println( "Moving data from IS to OS..." );
byte buffer[] = new byte[32765];
while ((length = is.read (buffer,0,32765)) != -1)
{
os.write( buffer, 0, length );
System.out.println( "Wrote 32k..." );
}
is.close();
os.close();
rset81.close();
}
insertStmt.close();
getStmt.close();
conn.commit();
conn.close();
}
}
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment