• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

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

 
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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 ]
 
Consider Paul's rocket mass heater.
reply
    Bookmark Topic Watch Topic
  • New Topic