Derby
  1. Derby
  2. DERBY-1341

LOB set method(s) are currently no supported, but part of the Java 1.4 JDBC interface

    Details

    • Type: Improvement Improvement
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 10.0.2.0, 10.0.2.1, 10.0.2.2, 10.1.1.0, 10.1.2.1, 10.1.3.1, 10.2.1.6, 10.3.1.4
    • Fix Version/s: 10.3.1.4
    • Component/s: JDBC
    • Labels:
      None
    • Environment:
      all
    • Urgency:
      Normal
    • Issue & fix info:
      Release Note Needed

      Description

      JDBC LOB . getBtypes methods are not implemented in any Derby version to date: there is a "place-holder" method that throws a SQLException reporting that the methods are not implemented.

      It would be excellent to have any efficient Derby implementation of the getBytes LOB methods that provide "random-access" to the binary // character content of database large objects. The specific context is implementing a Lucene Directory interface that stores indexing data (index files) and other binary data in a local encrypted Derby instance.

      A work around is to write an encrypted RandomAccessFile implementation as a file-sdystem buffer, perhaps writing to the database on closure. An efficient Derby implementation of LOB . getBytes would avoid this an make for a clean design. I can think of several reasons why random-access to LOBs would be valuable in a "hostile" client environment.

      1. releaseNote.html
        5 kB
        Anurag Shekhar
      2. releaseNote.html
        5 kB
        Rick Hillegas
      3. releaseNote.html
        5 kB
        Anurag Shekhar
      4. releaseNotes-v2.html
        5 kB
        Anurag Shekhar
      5. releaseNotes.html
        5 kB
        Anurag Shekhar
      6. LobStreamTest.java
        16 kB
        Fernanda Pizzorno
      7. derby-1341-blob-forreview.diff
        39 kB
        Anurag Shekhar
      8. derby-1341.diff
        38 kB
        Anurag Shekhar

        Issue Links

          Activity

          Hide
          Kristian Waagan added a comment -

          Closing issue.

          Show
          Kristian Waagan added a comment - Closing issue.
          Hide
          Anurag Shekhar added a comment -

          changing J2SE 6 to Java SE 6. Sorry for the late correction.

          Show
          Anurag Shekhar added a comment - changing J2SE 6 to Java SE 6. Sorry for the late correction.
          Hide
          Rick Hillegas added a comment -

          Scrubbing release note so that the SAX parser in the release note generator can digest it.

          Show
          Rick Hillegas added a comment - Scrubbing release note so that the SAX parser in the release note generator can digest it.
          Hide
          Anurag Shekhar added a comment -

          fixing the file name.

          Show
          Anurag Shekhar added a comment - fixing the file name.
          Hide
          Anurag Shekhar added a comment -

          thanks Øystein for the review.

          I made the changes in releaseNotes-v2.html of the release notes

          Show
          Anurag Shekhar added a comment - thanks Øystein for the review. I made the changes in releaseNotes-v2.html of the release notes
          Hide
          Øystein Grøvlen added a comment -

          Release note looks good, I have a few comments to the form:

          • According to the comments in the template, the summary is supposed
            to be a one-liner. I suggest you move the rest to the next
            section.
          • You should delete the headings for the optional sections that you
            have not used.
          • I think you should refer to Java SE 6, not jdk 1.6.
          Show
          Øystein Grøvlen added a comment - Release note looks good, I have a few comments to the form: According to the comments in the template, the summary is supposed to be a one-liner. I suggest you move the rest to the next section. You should delete the headings for the optional sections that you have not used. I think you should refer to Java SE 6, not jdk 1.6.
          Hide
          Anurag Shekhar added a comment -

          release notes for newly implemented methods in Blob and Clob (embedded driver).

          Show
          Anurag Shekhar added a comment - release notes for newly implemented methods in Blob and Clob (embedded driver).
          Hide
          Anurag Shekhar added a comment -

          updating subject to reflect the more generalized work done as part of this issue.

          Show
          Anurag Shekhar added a comment - updating subject to reflect the more generalized work done as part of this issue.
          Hide
          Rick Hillegas added a comment -

          Unknown release vehicle.

          Show
          Rick Hillegas added a comment - Unknown release vehicle.
          Hide
          Rick Hillegas added a comment -

          Moving to 10.2.2.0.

          Show
          Rick Hillegas added a comment - Moving to 10.2.2.0.
          Hide
          Anurag Shekhar added a comment -

          Thanks Fernanda for the test.
          I will prefer to keep LOBStreamControl's scope at the package. Let me try to modify the test so that it can still test the Streams. I am thinking of creating new blob using connection.createBlob and getting the stream from this blob to use in the test class you have written.

          Show
          Anurag Shekhar added a comment - Thanks Fernanda for the test. I will prefer to keep LOBStreamControl's scope at the package. Let me try to modify the test so that it can still test the Streams. I am thinking of creating new blob using connection.createBlob and getting the stream from this blob to use in the test class you have written.
          Hide
          Fernanda Pizzorno added a comment -

          I am planning to use similar stream to those implemented in the patch (derby-1341-blob-forreview.diff) for Derby-1560. I have written the attached test (LobStreamTest.java) to verify the implementation of input and output streams. You might be interested in using this test while working on the implementation of these streams. To be able to run the test, the class LOBStreamControl, its constructor and the getInputStream() and getOutputStream() methods must be public.

          Show
          Fernanda Pizzorno added a comment - I am planning to use similar stream to those implemented in the patch (derby-1341-blob-forreview.diff) for Derby-1560. I have written the attached test (LobStreamTest.java) to verify the implementation of input and output streams. You might be interested in using this test while working on the implementation of these streams. To be able to run the test, the class LOBStreamControl, its constructor and the getInputStream() and getOutputStream() methods must be public.
          Hide
          Anurag Shekhar added a comment -

          This patch is only for review.

          After the review I will be uploading the complete patch (blob and clob) and the test case. I plan to use same approach for clob implementation too.

          In this patch I have modified the LOBStreamContrl to use storage factory for temporary file. Rest of the code is same as the privious patch.

          My final patch will have following
          1. Implementation for Blob and Clob.
          2. Test cases.
          3. I will be adding one more method in StorageFactory to generate unique temporary file (similar to the method in java.io.File)

          Show
          Anurag Shekhar added a comment - This patch is only for review. After the review I will be uploading the complete patch (blob and clob) and the test case. I plan to use same approach for clob implementation too. In this patch I have modified the LOBStreamContrl to use storage factory for temporary file. Rest of the code is same as the privious patch. My final patch will have following 1. Implementation for Blob and Clob. 2. Test cases. 3. I will be adding one more method in StorageFactory to generate unique temporary file (similar to the method in java.io.File)
          Hide
          Anurag Shekhar added a comment -

          This diff is only for review of aprocah I am taking to resolve this issue.

          I have introduced 3 new class in this patch. LOBStreamControl, LOBOutputStream and LOBInputStream.

          LOBStreamControl acts as wrpper on top of bytes stored in memory and the temporary file on the file system. The read and write methods exposed by LOBStreamControl are used by EmbeddedBlob and LOBXXXXStream.

          when the total data size is less than 4k its stored in array once either the setBytes on blob or write on Stream incheses the size more than 4k the data is written into a temporrary file and subsequent operations are performed on the file.

          If call to truncate reduces the size of the file below 4k the data is again moved back to memory.

          Chanes made in EmbeddedBlob

          Changed EmbeddedBlob to pass all the calls which were priviously acessing the bytes array to LOBStreamControl.

          Show
          Anurag Shekhar added a comment - This diff is only for review of aprocah I am taking to resolve this issue. I have introduced 3 new class in this patch. LOBStreamControl, LOBOutputStream and LOBInputStream. LOBStreamControl acts as wrpper on top of bytes stored in memory and the temporary file on the file system. The read and write methods exposed by LOBStreamControl are used by EmbeddedBlob and LOBXXXXStream. when the total data size is less than 4k its stored in array once either the setBytes on blob or write on Stream incheses the size more than 4k the data is written into a temporrary file and subsequent operations are performed on the file. If call to truncate reduces the size of the file below 4k the data is again moved back to memory. Chanes made in EmbeddedBlob Changed EmbeddedBlob to pass all the calls which were priviously acessing the bytes array to LOBStreamControl.
          Hide
          Rick Hillegas added a comment -

          Lance Andersen adds:

          >
          >
          > [ http://issues.apache.org/jira/browse/DERBY-1341?page=comments#action_12414483 ]
          >
          >Anurag Shekhar commented on DERBY-1341:
          >---------------------------------------
          >
          >I was wrong about life time of lob. It is supposed to restricted only for the transaction (jdbc 3.0 section 16.3.1)
          >
          >
          For locator based that would be true. However if it is a copy , it could well live past the transaction. This has been clarified in the jdbc 4 spec

          Show
          Rick Hillegas added a comment - Lance Andersen adds: > > > [ http://issues.apache.org/jira/browse/DERBY-1341?page=comments#action_12414483 ] > >Anurag Shekhar commented on DERBY-1341 : >--------------------------------------- > >I was wrong about life time of lob. It is supposed to restricted only for the transaction (jdbc 3.0 section 16.3.1) > > For locator based that would be true. However if it is a copy , it could well live past the transaction. This has been clarified in the jdbc 4 spec
          Hide
          Anurag Shekhar added a comment -

          Initially memory may be sufficient to hold the array user sets in. But user may call setBytes multiple times resulting in a huge array which may be stored in memory.

          I mean may not be sotred in memory sorry about the typo

          Show
          Anurag Shekhar added a comment - Initially memory may be sufficient to hold the array user sets in. But user may call setBytes multiple times resulting in a huge array which may be stored in memory. I mean may not be sotred in memory sorry about the typo
          Hide
          Anurag Shekhar added a comment -

          I was wrong about life time of lob. It is supposed to restricted only for the transaction (jdbc 3.0 section 16.3.1)

          Yes its the model where DatabaseMetaData.locatorsUpdateCopy() will return true (updates made on a copy)

          I am following the thread and plan to be consistant with client driver's behaviour unless its concluded other wise in that thread.

          Initially memory may be sufficient to hold the array user sets in. But user may call setBytes multiple times resulting in a huge array which may be stored in memory. Same is true when user is writing in the output stream.

          Show
          Anurag Shekhar added a comment - I was wrong about life time of lob. It is supposed to restricted only for the transaction (jdbc 3.0 section 16.3.1) Yes its the model where DatabaseMetaData.locatorsUpdateCopy() will return true (updates made on a copy) I am following the thread and plan to be consistant with client driver's behaviour unless its concluded other wise in that thread. Initially memory may be sufficient to hold the array user sets in. But user may call setBytes multiple times resulting in a huge array which may be stored in memory. Same is true when user is writing in the output stream.
          Hide
          Daniel John Debrunner added a comment -

          So I assume you are implementing the model where DatabaseMetaData.locatorsUpdateCopy() method returns true, see section 16.3.3 of JDBC 3.0. Good to state this up front.

          I aslo assume you have been following the discussion on the dev list (and in another jira entry ?) about the defined semantics for the set methods (overwrite or not etc.)

          "Side effect of this impelementation is that the life time of lob is restrcited till the transaction in which the objects are fetched. "
          I thought that was the defined behaviour for Blob and Clob objects, not just an implementation artifact?

          "setBytes/String methods can use array/string field initially once the size crosses initial threshold the data will be written into the file"
          Why switch to a file if the object is already in-memory? Doesn't that indicate there is enough memory to store it?

          The StoreFactory was never intended to be used on the client, maybe it's suitable, maybe it's not.

          Show
          Daniel John Debrunner added a comment - So I assume you are implementing the model where DatabaseMetaData.locatorsUpdateCopy() method returns true, see section 16.3.3 of JDBC 3.0. Good to state this up front. I aslo assume you have been following the discussion on the dev list (and in another jira entry ?) about the defined semantics for the set methods (overwrite or not etc.) "Side effect of this impelementation is that the life time of lob is restrcited till the transaction in which the objects are fetched. " I thought that was the defined behaviour for Blob and Clob objects, not just an implementation artifact? "setBytes/String methods can use array/string field initially once the size crosses initial threshold the data will be written into the file" Why switch to a file if the object is already in-memory? Doesn't that indicate there is enough memory to store it? The StoreFactory was never intended to be used on the client, maybe it's suitable, maybe it's not.
          Hide
          Anurag Shekhar added a comment -

          Currently the blob and clob can be constructed only by resultset. jdbc 4.0 connection object does have methods to construct these methods but the absence of set methods in embedded driver makes these objects (created by connection) useless.

          To implement these method I will be making some mdofication in the way behaviour of lob objects.

          Currently the lob objects stores data either in an array (or string in case of clob) or a stream is constructed directly on top of linked field in store if the size of the lob stored is found to be more than one page. Side effect of this impelementation is that the life time of lob is restrcited till the transaction in which the objects are fetched.

          Client implementation assumes all of it can be kept in main memory but that doesn't looks like a reasonable assumption. I can think of only one solution to handle this, by having a temporary file.

          I am thinking of using the temp file from the StoreFactory. StoreFactory has methods to create temporary file.

          This is what I was thinking to implement (with assumption that I can use temp files)

          setBytes/String methods can use array/string field initially once the size crosses initial threshold the data will be written into the file
          and further set methods will operate on file. In case the blob/clob size is smaller than
          threshold the data is copied in memory other wise a stream is attached with the object. I haven't checked how this stream is created I am not sure if the stream is always operates on store api as the blob and clob objects remain valid even after closing result set.

          setStream methods can return a custom stream which will initially store the data in memory and once the threshold is reached it will use temp file.

          While setting the blob/clob into prepared statement internally it will call setString/setBytes in case the data is in memory and setStream in case the temp file is in use.

          Show
          Anurag Shekhar added a comment - Currently the blob and clob can be constructed only by resultset. jdbc 4.0 connection object does have methods to construct these methods but the absence of set methods in embedded driver makes these objects (created by connection) useless. To implement these method I will be making some mdofication in the way behaviour of lob objects. Currently the lob objects stores data either in an array (or string in case of clob) or a stream is constructed directly on top of linked field in store if the size of the lob stored is found to be more than one page. Side effect of this impelementation is that the life time of lob is restrcited till the transaction in which the objects are fetched. Client implementation assumes all of it can be kept in main memory but that doesn't looks like a reasonable assumption. I can think of only one solution to handle this, by having a temporary file. I am thinking of using the temp file from the StoreFactory. StoreFactory has methods to create temporary file. This is what I was thinking to implement (with assumption that I can use temp files) setBytes/String methods can use array/string field initially once the size crosses initial threshold the data will be written into the file and further set methods will operate on file. In case the blob/clob size is smaller than threshold the data is copied in memory other wise a stream is attached with the object. I haven't checked how this stream is created I am not sure if the stream is always operates on store api as the blob and clob objects remain valid even after closing result set. setStream methods can return a custom stream which will initially store the data in memory and once the threshold is reached it will use temp file. While setting the blob/clob into prepared statement internally it will call setString/setBytes in case the data is in memory and setStream in case the temp file is in use.
          Hide
          Kristian Waagan added a comment -

          (I assume the 'getBytes' in the description can be replaced with 'setBytes')

          Derby currently only supports the two setBytes methods for BLOB and CLOB in the client driver.
          In the embedded driver, these methods are not yet implemented. I think this is an important feature (for people using LOBs), and Derby should close this functionality gap.

          For a list of JDBC methods not supported by Derby, consult http://wiki.apache.org/db-derby/JDBCSupport
          The list is pretty new, so please correct errors if you spot any!

          Show
          Kristian Waagan added a comment - (I assume the 'getBytes' in the description can be replaced with 'setBytes') Derby currently only supports the two setBytes methods for BLOB and CLOB in the client driver. In the embedded driver, these methods are not yet implemented. I think this is an important feature (for people using LOBs), and Derby should close this functionality gap. For a list of JDBC methods not supported by Derby, consult http://wiki.apache.org/db-derby/JDBCSupport The list is pretty new, so please correct errors if you spot any!

            People

            • Assignee:
              Anurag Shekhar
              Reporter:
              Keith McFarlane
            • Votes:
              1 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development