Set BLOB through JDBC

General Discussions
#1

Hi

I want to set a blob field through JDBC and Java 1.5. I cannot currently update to Java 1.6.

The blob can be very large. Let’s say, I have a 1 GB file. I cannot assume, that I have 1 GB free heap space. I want to directly stream the bytes into the blob. Hence I used setBinaryStream( 1, in, Integer.MAX_VALUE ) on the PreparedStatement. Unfortunately I have to pass the stream length to the method, because I am using Java 1.5. But this is stupid. I simply don’t know the stream length. And I cannot buffer the whole stream content in memory just to get the size, since it could be too large. Passing Integer.MAX_VALUE works, but blows up the memory consumption. A have also tried -1, but this has the same effect as Integer.MAX_VALUE.

So my question is: How can I set a blob field value from an InputStream without buffering more than necessary in memory?

Thanks in advance,
Marvin

#2

Where does the blob data come from?

#3

In my case it is a file posted with a web page. But we could also say, it is an arbitrary InputStream, of which we of course don’t know the size ;).

Marvin

#4

You are SOL I’m afraid. If it’s a file at least you can do file.length().

Also you will probably rather horribly find that many drivers buffer the whole stream into memory before sending it to the database. Why they do this fuckwittedness is beyond imagining, but suffice to say, I’ve come across this exact same problem. So you’re probably shafted even if you use Java 6 too. Unless your JDBC drivers are canny.

Cas :slight_smile:

#5

Just split your data over multiple records and bind the results together using an non-unique index with some identifier and a counter (auto increment primary key), for your ORDER BY.

I bet that’s reliable and fast across all database implementations.

#6

Thanks a lot guys. Now I at least know for sure, that I am not blind :).

Marvin