Inserting BLOB data through JDBC

[prashantSum]

Hi all,
I am trying to insert data into oracle BLOB through JDBC. I am able to insert the data using oracle.sql.BLOB which have methods like putBytes() and setBytes().

I have two concerns:
First one: I am inserting the data using the following code

ps = conn.prepareStatement(sql);
oracle.sql.BLOB newBlob = oracle.sql.BLOB.createTemporary(conn,false, oracle.sql.BLOB.DURATION_SESSION);

newBlob.putBytes(1,docout.toString().getBytes());
ps.setBlob(1,newBlob);
ps.execute();

sql statement contains one ? (i.e. bind variable), and docout is a stringBuffer which contains desired length of user (ex: 40,000).

Question: Is this the right way to insert blob data through JDBC or is there is any other better way? actually I want to insert Word documents into BLOB.
Second One: I have experimented this code with Oracle 9i JDBC and Oracle 10g JDBC.
Oracle 9i: In Oracle 9i jdbc driver putBytes() method is working, setBytes() is not working (error: unsupported feature).
Oracle 10g: In Oracle 10g jdbc driver putBytes() is working but it says putBytes is a deprecated method, setBytes() is working properly.

We still did not tested with Oracle 8i.

My concern is I have to write a program which can be used with any version of Oracle JDBC like 8i, 9i or 10g where I can use a single method

[Amit Ray]

Another way to would be:

Inserting a BLOB

// CREATE TABLE t1 (c1 INT PRIMARY KEY NOT NULL, c2 BLOB(5M));

PreparedStatement pstmt = conn.prepareStatement ("INSERT INTO t1 VALUES (?,?)");
pstmt.setInt (1, 100);
File fBlob = new File ( "image1.gif" );
FileInputStream is = new FileInputStream ( fBlob );
pstmt.setBinaryStream (2, is, (int) fBlob.length() );
pstmt.execute ();
...

Retrieving a BLOB

// CREATE TABLE t1 (c1 INT PRIMARY KEY NOT NULL, c2 BLOB(5M));

Statement stmt = conn.createStatement ();
ResultSet rs= stmt.executeQuery("SELECT * FROM t1");
while(rs.next()) {
int val1 = rs.getInt(1);
InputStream val2 = rs.getBinaryStream(2);
...
} rs.close();
...

The good thing about this approach is - The function InputStreams encapsulates the logic to chunk the data in pieces.

Also check out the MSDN Link.

All I can tell you is, there are a lot of ways.. and the one you are using is certainly not the best.

For the protability part, are you using the JDBC Implementation (classes12.jar for example) corresponding to the Oracle version you are using ? Java just provides the interface in JDBC .. it is completely upto Oracle how they have implemented that in the different versions. But generally speaking, if a method is not depricated, it should work for all versions.

Cheers,
Amit Ray.

[prashantSum]

hi amit,
thanks for the reply and good guidance.

Ya, I was using JDBC Implementation (classes12.jar) of different versions of Oracle, but this is solved by implementing your suggestion.

But I have some more doubts.

Actually our issue is, we are unable to insert records where column width is more than 4000 chars when using Statement, i.e. no bind variables (?) in the statement, we are directly taking a string and concatinting it to sql query.

Is this really a restriction with Oracle or is it a type 4 driver problem, can we try inserting more than 4000 chars in Statement using type 2 driver

[prashantSum]

If any one knows the answer for the above question please reply me early, I am waiting for it.

[Amit Ray]

Nothing to do with the driver. Oracle has a 4000 character limitation on VARCHAR2 fields.

The Oracle SQL parser can only handle string literals up to 4000 characters in length. SQL*Plus is even more restricted. It can handle strings up to 2500 characters in length. From most Oracle clients, there will in fact be no way to insert data longer than 4000 characters long into a table. A statement/system that works perfectly with a 4000-character string will fail completely with a 4001-character string. You have to completely redesign the way you're doing things to work with strings that might be long.

The Oracle 10g JDBC driver claims to have a work around. Check out this link.

Also you can take a look at the following links:

Loading Large XMLs.

Limits in Oracle.

Hope that helps.

Amit Ray.

[prashantSum]

Thanks for reply Amit.