db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bryan Pendleton (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-6923) Passing a ClientClob or ClientBlob from another connection to an INSERT statement results in 'null' being written.
Date Sat, 25 Feb 2017 16:57:44 GMT

    [ https://issues.apache.org/jira/browse/DERBY-6923?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15884306#comment-15884306

Bryan Pendleton commented on DERBY-6923:

Hi! Thanks for the clean and simple test programs.

Looking at your programs in detail, it seems like the crucial difference you're exploring
is between:
    insertStatement.setClob(2, new InputStreamReader(select.getClob(2).getAsciiStream()));
    insertStatement.setBlob(3, select.getBlob(3).getBinaryStream());
    insertStatement.setClob(2, select.getClob(2));
    insertStatement.setBlob(3, select.getBlob(3));

The Derby BLOB and CLOB implementation is a bit murky to me, I'm afraid, but I believe that
you are running into the behavior that the Derby documentation describes here: http://db.apache.org/derby/docs/10.13/ref/rrefjdbc96386.html
The Derby implementation of the java.sql.Blob and java.sql.Clob interfaces is LOCATOR-based,
meaning that the implementation provides a logical pointer to a LOB rather than a complete
copy of the object. Also, Derby does not materialize a LOB when you use the BLOB or CLOB data
type. You can, however, call methods on a java.sql.Blob and java.sql.Clob object to materialize
it (that is, to retrieve the entire object or parts of it).


Use the getBlob and getClob methods of the java.sql.ResultSet interface to retrieve a LOB
using its locator. You can then materialize all or part of the LOB by calling Blob and Clob
methods. Alternatively, you can call the ResultSet.getBytes method to materialize a BLOB,
and you can call the ResultSet.getString method to materialize a CLOB.

In other words, I suspect that in your second block of code, you are executing an INSERT statement
in which the blob and clob values that you have provided as parameters are "non-materialized".

But in the first block of code, you are executing an INSERT statement in which the blob and
clob values are "materialized", which occurred when you called getBinaryStream and getAsciiStream

The behavior of locator-based large objects in Derby has historically been quite a bit more
complicated in client-server configurations than in embedded configurations, which is I suspect
why you are seeing the behavior only in the client-server configuration.

In the client-server configuration, the blob/clob values have to be fully copied from the
server to the client in order to be "materialized", and then have to be fully copied **back**
from the client to the server when being inserted into the other table.

So, I suspect what's going wrong is that the Derby JDBC client software is supposed to detect
when you have performed a setBlob() or setClob() with a non-materialized blob or clob as the
source object, and then should materialize that blob when you execute the insert statement.

However, it seems like the Derby JDBC client is not doing that properly, with the result that,
in your case, things work fine if your program is careful to materialize the blob itself,
rather than depending on the Derby JDBC client to do that.

That is, I feel like the behavior you are demonstrating is a bug, but I'm (a) not sure how
to fix it, and (b) trying to suggest that, in the short term, you may need to continue to
use the explicit materialization workaround you've found in order to get the desired behavior
in your program.

> Passing a ClientClob or ClientBlob from another connection to an INSERT statement results
in 'null' being written.
> ------------------------------------------------------------------------------------------------------------------
>                 Key: DERBY-6923
>                 URL: https://issues.apache.org/jira/browse/DERBY-6923
>             Project: Derby
>          Issue Type: Bug
>          Components: Network Client, Network Server
>    Affects Versions:
>         Environment: Java 8
> MacOS
> Windows
>            Reporter: Gordon Innes
>            Priority: Critical
>         Attachments: DerbyEmbeddedOnly.java, DerbyNetworkOnly.java, DerbyNetworkRead.java,
> When assigning a Blob or Clob from a ResultSet to a PreparedStatement parameter on a
second connection, null is written to the column instead.
> Attached the same example using Embedded->Embedded, Embedded->Network, Network->Embedded
and Network->Network.
> Only the Network->Network example exhibits the fault.
> Originally discovered in HA-JDBC's sync code with 2 derby instances on Windows. Example
were tested using MacOS. Both were running on Oracle Java 8 VMs.

This message was sent by Atlassian JIRA

View raw message