[
https://issues.apache.org/jira/browse/DERBY-5505?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13152388#comment-13152388
]
Kim Haase commented on DERBY-5505:
----------------------------------
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
x'ffd8ffe000.....'
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.
> BLOB and CLOB Reference Manual topics could use some fixes
> ----------------------------------------------------------
>
> Key: DERBY-5505
> URL: https://issues.apache.org/jira/browse/DERBY-5505
> Project: Derby
> Issue Type: Improvement
> Components: Documentation
> Affects Versions: 10.8.2.2
> Reporter: Kim Haase
> Assignee: Kim Haase
> Priority: Minor
>
> The Reference Manual documentation on BLOBs and CLOBs has some gaps that I
> would like to fill as a result of my efforts to insert a BLOB into a table
> column. This should affect the following topics:
> CAST function (rrefsqlj33562.dita)
> CLOB data type (rrefclob.dita)
> BLOB data type (rrefblob.dita)
> A comment will provide further details.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators:
https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
