An INSERT statement that inserts a bit string (the contents of an image file) into a column defined as BLOB drew a stacktrace when the data was
It appeared to work correctly when the data was
cast(x'ffd8ffe000...' as blob)
But the image was truncated in the database. Word of mouth informs me that SQL cannot handle bit strings longer than 32K, so maybe that's the reason (though the SQL script being used is only 18543 bytes in size and the image files themselves are between 2.5K and 5K). The topic on the CAST function has a table that indicates that CHAR FOR BIT DATA and so on can be cast to BLOB; but under "Conversions from and to bit strings" it says, "In most cases the BLOB type cannot be cast to and from other types." This is a bit vague. I cannot find the 32K limitation stated anywhere, so maybe it should be added? Are there other specific limitations we could put here?
The topic on the CLOB data type has an example Java program, but it does not work correctly as shown. It incurs the following runtime error:
Error! java.sql.SQLException: Stream or LOB value cannot be retrieved more than once
This happens because of the following two statements:
java.sql.Clob aclob = rs.getClob(1);
java.io.InputStream ip = rs.getAsciiStream(1);
I can get rid of the error either by commenting out the first statement or by rewriting the second one as follows:
java.io.InputStream ip = aclob.getAsciiStream();
Also, should there be an "ip.close()" statement at the end of the outer while loop? Not to mention a "conn.close()" and database close at the end of the program? Also, is there any point in calling "conn.commit()" when the default auto-commit mode is on?
The topic on the BLOB data type has examples of CREATE and SELECT statements, but no example INSERT statement. It might be useful to show a sample CAST statement, mentioning the 32K limitation. It would possibly also be helpful to provide a Java program similar to the one for the CLOB data type.
I am attaching a rewritten version of the CLOB program and a similar BLOB one that writes the blob to a new version of the image file that was stored. Suggestions for improvement are welcome.