blob insertion in oracle in single step (without LOB locator)
Can anyone suggest what is the recommended method of inserting blob in oracle. In oracle docs its mentioned that its two step process (ie to create an "empty_lob" first, to get the locator and then to insert the blob column using "select for update")
I tried inserting using the following code and it worked in single step (ie without creating the LOB locator) using oracle 10g driver
TABLE_NAME contains and long id and a blob column
public void insertBlob()
{
try{
byte[] blobDataWritten = null;
byte[] blobDataWritten = null;
PreparedStatement stmt = con.prepareStatement("insert into " + TABLE_NAME + " values (?, ?)");
StringBuffer myStrBuf = new StringBuffer();
for (int i = 0; i < 1024; i++) {
myStrBuf.append("\0");
}
String myString = myStrBuf.toString();
blobDataWritten = myString.getBytes();
System.out.println("Length of Blob to be inserted: " + blobDataWritten.length);
stmt.setLong(1, blobDataWritten.length);
ByteArrayInputStream bais = new ByteArrayInputStream(blobDataWritten);
stmt.setBinaryStream(2, bais, blobDataWritten.length);
int insertstatus= pinsert.executeUpdate();
System.out.println("\nBlob successfully inserted. Num Rows Inserted: " + ipinsert + " \n");
} catch (Throwable t) {
t.printStackTrace();
}
}
Thanks in advance
mkr
[1423 byte] By [
rajesh_mka] at [2007-10-2 11:00:00]
I know that you asked about BLOB, but since you were using a String, I thought I would mention that in Oracle 10g I believe they recommend setting the driver values as follows:
props.put("SetBigStringTryClob", "true");
and then using
setString(...) // for both Strings and CLOBS
I've used this successfully and it saves manual conversion. I don't know if this is any standard form for JDBC though, I only know that it works with Oracle 10g, and I thought I remembered the it was the preferred method and provided best case performance.
The way to go is to use the setBinaryStreammethod for PreparedStatement. This seems like the easiest way to work with Blobs. For example, I wanted to save excel files to oracle and did it like this:
InputStream is = new FileInputStream(excelFile);
String sql = "INSERT into redd.t_clpr_attachment(attachmentname,attachmentsize,mimetype,attachmentdata) VALUES(?, ?, ?, ?)";
ps = conn.prepareStatement(sql);
ps.setString(1, attachmentName);
ps.setLong(2, excelFile.length());
ps.setString(3, "application/vnd.ms-excel");
ps.setBinaryStream(4, is, (int) excelFile.length());
ps.executeUpdate();
You have to provide how much to write in the third parameter and after doing an update your done. I looked at the sun example and I don't know why they use EMPTYBLOGB() in the insert statement and then get a stream to it. After all, the getBinaryOutputStream() method of BLOB is deprecated.
HI,
I tried your example. I am able to insert well. I am trying to store TIF image as a Blob. I am not able to retrieve image.
Here is my Code
String query = "select IMAGE from detail ";
ResultSet rset = stmtBA.executeQuery(query);
InputStream tif_data = null;
while (rset.next()) {
// Get the Gif data as a Stream from Oracle to the client
tif_data= rset.getBinaryStream("IMAGE");
}
// Open a file to store the gif data
FileOutputStream file = new FileOutputStream ("c://Temp//db.tif");
// Loop, reading from the tif stream and writing to the file
if(tif_data != null){
int c;
while ((c = tif_data.read ()) != -1){
file.write (c);
// Close the file
}
}else{
System.out.println("It is empty");
}
file.close ();
I appreciate your Help.
Message was edited by:
vin_ant_99
