I have a dump file from an Informix table with blob data represented as hex within the delimited file. I wish to load this into a similarly defined DB2 table. The DB2 LOAD process loads the table Ok but the blob data got interpreted as text - I now have the hex equivalent of what I want in the blob column.

I thought to write a small Java program to do the load myself but ran into problems with the blob. The Blob datatype does not have a public construtor. I presume this is because it represents a reference to a server-side data block and thus needs to be created in the context of the connection. After playing around with prepared statements and examining the samples I ended up creating a one-row table with a blob column that I can query. This gives me a Blob in my client program, but when I try to load it with setBytes I get an AbstractMethodError.

Can anybody help with this error? Also, is there a more direct way to construct a blob in the client to load into a table? I don't have the blob data in a separate file which most of the samples seem to expect. I have it as part of a delimited file and it needs further processing before loading.

Thanks.

See if this helps:

http://www.dbforums.com/t993796.html

Have you tried this:

I didn't quite understand the outcome of the referenced post. I will give setBinaryStream a try, although it does seem like a long way around. I have to extract the data from a delimited file, convert the hex to binary, then wrap a StringBufferInputStream around that. I was also hoping to use SQLj rather than JDBC for this since I have a lot of code in place already that uses SQLj.

It would appear that Oracle's support for blobs is better in this case. I didn't really have a problem with blobs on that database.

I noticed that I forgot to mention the platform: UDB 8.1.2 FP3 on RedHat ES 2.1. Client is 8.1.2 GA on Windows XP Pro, JDK 1.4.1_02.

In the samples/java/sqlj directory, see the sample BlobClobDemo.sqlj - it has what I suspect you're trying to do.

AbstractMethodError might indicate that you're using a version of JDBC driver that does not support JDBC 2.0. The referenced post shows how to activate newer driver.

The way you chose originally looks longer to me...

I had a look at the Blob object that is returned by the getBlob() method (through reflection) and it looks like the only useful contrete method it implements is getBinaryStream(). Its superclass implements the rest of the methods in java.sql.Blob as abstract which is why I was getting the AbstractMethodError. I guess that IBM hasn't got around to coding the rest of the interface.

The object returned from getPreparedStatement does have a concrete implementation of setBinaryStream so it looks like I will have to do it this way. I will also check the latest fixpacks to see if the support for the other methods has been added.

A consequence of this omission is that manipulating existing blobs is going to be awkward - it will require the use of input and output streams as the blob returned from the result set can not be directly modified. It also means that you can't use SQLj with blobs for anything more complicated than copying them from one table to another.

I suggest you do some more research before coming to the final conclusion...

http://publib.boulder.ibm.com/infoce...bDemo-sqlj.htm

Thank you. That's what I needed. I saw a reference to this example in an earlier post but it was not installed with my client.

I played around with the second part of the example dealing with input and found that the getBytes method does not work for me (it returns an empty array). The getBinaryStream method does work so I was able to extract the data into an array.