I CAN insert into Oracle BLOB in one step but...
I have a table TEST_BLOB with 3 columns (front_image, back_image, extra_image). After all said and done, I found my front image inserted into extra_image column, and my extra image inserted into front_image column. I made sure I use the right column index in my prepare statement. Any expert can tell me why?
I am using:
-Oracle 10g Release 10.2.0.1.0
-Oralce
JDBC Driver 10.1.0.2.0
-JDK 1.4.2
Here is the script to create TEST_BLOB table:
CREATE TABLE TEST_BLOG (
FRONT_IMAGE BLOB,
BACK_IMAGE BLOB,
EXTRA_IMAGE BLOB)
Here is the
JSP code to insert the images. You will need to modify the 3 image file locations and JDBC datasource:
<%@page contentType="text/html"%>
<%@pageimport="java.sql.*"%>
<%@pageimport="javax.sql.*"%>
<%@pageimport="java.io.*"%>
<%@pageimport="javax.naming.*"%>
<html>
<head><title>JSP Page</title></head>
<body>
String imageCheckFront = "/home/tomcat/front.tif";
String imageCheckBack = "/home/tomcat/back.tif";
String imageCheckExtra = "/home/tomcat/extra.tif";
InitialContext initCtx = null;
Connection conn = null;
DataSource ds = null;
PreparedStatement ps = null;
File imageFront = null;
File imageBack = null;
File imageExtra = null;
FileInputStream fisFront = null;
FileInputStream fisBack = null;
FileInputStream fisExtra = null;
byte[] frontByte;
byte[] backByte;
byte[] extraByte;
try {
initCtx = new InitialContext();
ds = (DataSource) initCtx.lookup("java:comp/env/jdbc/testdb");
conn = ds.getConnection();
DatabaseMetaData dbmd = conn.getMetaData();
//Output your DB and JDBC version
System.out.println("dbmd.getDatabaseProductName(): " + dbmd.getDatabaseProductName());
System.out.println("dbmd.getDatabaseProductVersion(): " + dbmd.getDatabaseProductVersion());
System.out.println("dbmd.getDriverName(): "+dbmd.getDriverName());
System.out.println("dbmd.getDriverVersion(): "+dbmd.getDriverVersion());
ps = conn.prepareStatement("INSERT INTO test_blob (front_image, back_image, extra_image) VALUES(?, ?, ?)" );
//INSERT FRONT IMAGE
imageFront = new File( imageCheckFront );
fisFront = new FileInputStream( imageFront );
frontByte = new byte[fisFront.available()];
fisFront.read(frontByte);
System.out.println( "frontByte.length: " + frontByte.length );
//INSERT BACK IMAGE
imageBack = new File( imageCheckBack );
fisBack = new FileInputStream( imageBack );
backByte = new byte[fisBack.available()];
fisBack.read(backByte);
System.out.println( "backByte.length: " + backByte.length );
//INSERT EXTRA IMAGE
imageExtra = new File( imageCheckExtra );
fisExtra = new FileInputStream( imageExtra );
extraByte = new byte[fisExtra.available()];
fisExtra.read(extraByte);
System.out.println( "extraByte.length: " + extraByte.length );
//setBinaryStream will also work if you so choose
//ps.setBinaryStream(1, fisFront, ( int )imageFront.length() );
//ps.setBinaryStream(2, fisBack, ( int )imageBack.length() );
//ps.setBinaryStream(3, fisExtra, ( int )imageExtra.length() );
ps.setBytes(1,frontByte);
ps.setBytes(2,backByte);
ps.setBytes(3,extraByte);
// Execute the INSERT
int count = ps.executeUpdate();
System.out.println( "Rows inserted: " + count );
}
finally {
fisFront.close();
fisBack.close();
fisExtra.close();
ps.close();
conn.close();
ds = null;
initCtx.close();
}
%>
</body>
</html>
Here is the SQL I used to validate data inserted into the table:
select lengthb(front_image), lengthb(back_image), lengthb(extra_image) from test_blob
Thanks in advance.
[ May 01, 2006: Message edited by: Master Wong ]