db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Kim Haase (Commented) (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-5505) BLOB and CLOB Reference Manual topics could use some fixes
Date Thu, 17 Nov 2011 22:07:52 GMT

    [ 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


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

> 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:
>            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


View raw message