wood burning stoves*
The moose likes JDBC and the fly likes I CAN insert into Oracle BLOB in one step but... Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "I CAN insert into Oracle BLOB in one step but..." Watch "I CAN insert into Oracle BLOB in one step but..." New topic
Author

I CAN insert into Oracle BLOB in one step but...

Aaron Wong
Greenhorn

Joined: Jun 23, 2005
Posts: 6
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 ]
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: I CAN insert into Oracle BLOB in one step but...
 
Similar Threads
ORA-01460: unimplemented or unreasonable conversion requested
BLOB and CLOB
Connection pooling issue in Apache 6.0
Oracle Datasource Connection
ORA-01461: can bind a LONG value only for insert into a LONG column