I'm working with BLOBs using Microsoft SQL Server 2000 JDBC drivers and attempting to insert and query a BLOB that represents a file. I can get a BLOB (assuming it works once I have one loaded -- it currently shows as null) but I need to create one from a file (not the database). I searched the JavaRanch forum history and I have found examples to get BLOBs, and have found examples specific to Oracle on how to create a BLOB. The problem is that won't work in this instance. I tried using the 'setBinaryStream' but that didn't work. I got an error from the driver that the types didn't match. I've read that BLOBs need to be declared as NTEXT (it's a Microsoft thing) and that the NTEXT field contains a B-Tree reference to the BLOB. Here's my code snippet attempt to create a BLOB:
Does anybody know how to create a brand new BLOB from a file so that I can update a database with that BLOB? [ August 20, 2002: Message edited by: Greg Ostravich ]
Typically, the creation of a blob is done by inserting an empty blob into the database... insert into my_table (key, blob_value) values (12345, EMPTY_BLOB()); Then, you retrieve the blob back out of the database and update it. I don't know how portable the EMPTY_BLOB() syntax is, as I've only used it in Oracle, but maybe you could just put a default value of EMPTY_BLOB (or whatever your db supports) onto your column definition. Now, before JDBC 3.0 (JDK 1.4), there was no real vendor-neutral way to update a Blob object (you had to cast to a vendor-specific class), so just make sure your JDBC driver supports the new 3.0 specific methods (like setBytes, setBinaryStream, and truncate).
James Carman, President<br />Carman Consulting, Inc.
Joined: Jul 11, 2002
Thanks for your assistance James. In my case that won't work because MS SQL Server doesn't seem to have the same function that Oracle has to create the empty BLOB. If it does, I haven't found it yet. I will keep your suggestion in mind though for a potential solution when I get back to the problem. The problem may also be caused by something else databse related so I may try it with an Open Source database and see if I can make my BLOBs work there, then go back to MS' product. Thanks again.
subject: BLOB Creation in Java (MS SQL Server or generic)