Derby
  1. Derby
  2. DERBY-378

support for import/export of tables with clob/blob and the other binary data types will be good addition to derby,

    Details

    • Type: Improvement Improvement
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 10.1.1.0
    • Fix Version/s: 10.3.1.4
    • Component/s: Tools
    • Labels:
      None

      Description

      Currently if I have a table that contains clob/blob column, import/export operations on that table
      throghs unsupported feature exception.

      set schema iep;
      set schema iep;
      create table ntype(a int , ct CLOB(1024));
      create table ntype1(bt BLOB(1024) , a int);

      call SYSCS_UTIL.SYSCS_EXPORT_TABLE ('iep', 'ntype' , 'extinout/ntype.dat' ,
      null, null, null) ;
      ERROR XIE0B: Column 'CT' in the table is of type CLOB, it is not supported by th
      e import/export feature.

      1. derby378_1.diff
        132 kB
        Suresh Thalamati
      2. derby378_1.stat
        2 kB
        Suresh Thalamati
      3. derby378_2.diff
        23 kB
        Suresh Thalamati
      4. derby378_2.stat
        0.4 kB
        Suresh Thalamati
      5. derby378_3.diff
        6 kB
        Suresh Thalamati
      6. derby378_3.stat
        0.3 kB
        Suresh Thalamati
      7. derby378_4.diff
        48 kB
        Suresh Thalamati
      8. derby378_4.stat
        0.8 kB
        Suresh Thalamati
      9. derby378_5.diff
        21 kB
        Suresh Thalamati
      10. derby378_6.diff
        5 kB
        Suresh Thalamati
      11. iexlobs_v1.txt
        11 kB
        Suresh Thalamati
      12. iexlobs.txt
        6 kB
        Suresh Thalamati

        Issue Links

          Activity

          Rick Hillegas made changes -
          Link This issue is related to DERBY-2859 [ DERBY-2859 ]
          Gavin made changes -
          Workflow jira [ 12321521 ] Default workflow, editable Closed status [ 12799814 ]
          Kathey Marsden made changes -
          Status Resolved [ 5 ] Closed [ 6 ]
          Rick Hillegas made changes -
          Link This issue is blocked by DERBY-2864 [ DERBY-2864 ]
          Rick Hillegas made changes -
          Link This issue is related to DERBY-2860 [ DERBY-2860 ]
          Suresh Thalamati made changes -
          Resolution Fixed [ 1 ]
          Status Open [ 1 ] Resolved [ 5 ]
          Suresh Thalamati made changes -
          Fix Version/s 10.3.0.0 [ 12310800 ]
          Suresh Thalamati made changes -
          Link This issue relates to DERBY-2527 [ DERBY-2527 ]
          Suresh Thalamati made changes -
          Attachment iexlobs_v1.txt [ 12355030 ]
          Hide
          Suresh Thalamati added a comment -

          updated the spec and added some notes for dcoumnentation.

          Show
          Suresh Thalamati added a comment - updated the spec and added some notes for dcoumnentation.
          Suresh Thalamati made changes -
          Attachment derby378_6.diff [ 12354136 ]
          Hide
          Suresh Thalamati added a comment -

          DERBY-378 (partial)
          This patch adds code required to enable new system procedures added to
          support import/export of lob data on hard upgrade from versions before
          10.3. Added a new test case to the 10.3 upgrade test cases.

          Show
          Suresh Thalamati added a comment - DERBY-378 (partial) This patch adds code required to enable new system procedures added to support import/export of lob data on hard upgrade from versions before 10.3. Added a new test case to the 10.3 upgrade test cases.
          Hide
          Suresh Thalamati added a comment -

          Thanks for your interest to document this issue Laura. Intially proposed spec is attached to this jira (iexlobs.txt) , but it is out of date. I will update the spec and post it in a day or two.

          Show
          Suresh Thalamati added a comment - Thanks for your interest to document this issue Laura. Intially proposed spec is attached to this jira (iexlobs.txt) , but it is out of date. I will update the spec and post it in a day or two.
          Hide
          Laura Stewart added a comment -

          Suresh is there a spec for me to look at for this issue?
          I'd like to understand the documentation hits.

          Show
          Laura Stewart added a comment - Suresh is there a spec for me to look at for this issue? I'd like to understand the documentation hits.
          Hide
          Suresh Thalamati added a comment -

          Thanks for the feedback Laura. Incase of export "EXTFILE" is used
          to indicate lob data is not stored in the main export file along with
          other table data, but in a different file specified by the user. And incase of
          import it indicates lobs data is in a different file, and the reference to
          it is stored in the import file specified by the user.

          Show
          Suresh Thalamati added a comment - Thanks for the feedback Laura. Incase of export "EXTFILE" is used to indicate lob data is not stored in the main export file along with other table data, but in a different file specified by the user. And incase of import it indicates lobs data is in a different file, and the reference to it is stored in the import file specified by the user.
          Hide
          Laura Stewart added a comment -

          These procedure names are fine with me. The names are clear (Export To and Import From),
          and they stay under 50 characters including underscores and the period (just barely
          More than 50 can cause real problems in the PDF output. The name can get truncated.

          I'm assuming the EXTFILE refers to External File, as opposed to a separate file?
          I think EXTFILE is better too.

          Show
          Laura Stewart added a comment - These procedure names are fine with me. The names are clear (Export To and Import From), and they stay under 50 characters including underscores and the period (just barely More than 50 can cause real problems in the PDF output. The name can get truncated. I'm assuming the EXTFILE refers to External File, as opposed to a separate file? I think EXTFILE is better too.
          Hide
          Suresh Thalamati added a comment -

          Thanks for the feedback Army & Mike. I agree with both of you ,
          LOBS_FROM_EXTFILE/LOBS_TO_EXTFILE sounds better than
          "LOBS_IN_EXTFILE". Unless someone else has a better
          suggestions. New procedure names will be :

          SYSCS_UTIL.SYSCS_EXPORT_TABLE_LOBS_TO_EXTFILE(..)
          SYSCS_UTIL.SYSCS_EXPORT_QUERY_LOBS_TO_EXTFILE(..)

          2)This patch implements following two new procedure that allow
          import of large object data stored in a external file.
          (for example exported previously using the above export procedures).

          SYSCS_UTIL.SYSCS_IMPORT_DATA_LOBS_FROM_EXTFILE(...)
          SYSCS_UTIL.SYSCS_IMPORT_TABLE_LOBS_FROM_EXTFILE(..)

          One another other thing I was debating myself was whether
          to use "EXTFILE" or "SEPFILE". Sticking with "EXTFILE",
          "SEPFILE" does not seem any better.

          Show
          Suresh Thalamati added a comment - Thanks for the feedback Army & Mike. I agree with both of you , LOBS_FROM_EXTFILE/LOBS_TO_EXTFILE sounds better than "LOBS_IN_EXTFILE". Unless someone else has a better suggestions. New procedure names will be : SYSCS_UTIL.SYSCS_EXPORT_TABLE_LOBS_TO_EXTFILE(..) SYSCS_UTIL.SYSCS_EXPORT_QUERY_LOBS_TO_EXTFILE(..) 2)This patch implements following two new procedure that allow import of large object data stored in a external file. (for example exported previously using the above export procedures). SYSCS_UTIL.SYSCS_IMPORT_DATA_LOBS_FROM_EXTFILE(...) SYSCS_UTIL.SYSCS_IMPORT_TABLE_LOBS_FROM_EXTFILE(..) – One another other thing I was debating myself was whether to use "EXTFILE" or "SEPFILE". Sticking with "EXTFILE", "SEPFILE" does not seem any better.
          Hide
          Mike Matrigali added a comment -

          A comment on Army's comment on names. I don't have strong feelings, I also thought using TO for export and FROM for import was more natural. I do think you should check with laura and see if these names are going to cause documentation issues because they are too long, I don't remember her ever saying what the problem length was.

          Show
          Mike Matrigali added a comment - A comment on Army's comment on names. I don't have strong feelings, I also thought using TO for export and FROM for import was more natural. I do think you should check with laura and see if these names are going to cause documentation issues because they are too long, I don't remember her ever saying what the problem length was.
          Suresh Thalamati made changes -
          Attachment derby378_5.diff [ 12353671 ]
          Hide
          Suresh Thalamati added a comment -

          DERBY-378 (partial)
          This patch adds some code required to support import/exoprt of lob data.
          1) Addded code to read clob data using getCharacterStream()
          instead of getString() while importing clob data from an extern file.
          (Note: Clobs are read using getString() until DERBY-2465 is fixed).
          2) Made some code changes to make each lob column has it it's own file handle to
          the lob file to read the data, otherwise streams can get corrupted when
          there are more than one clob/blob type column in the table.

          Show
          Suresh Thalamati added a comment - DERBY-378 (partial) This patch adds some code required to support import/exoprt of lob data. 1) Addded code to read clob data using getCharacterStream() instead of getString() while importing clob data from an extern file. (Note: Clobs are read using getString() until DERBY-2465 is fixed). 2) Made some code changes to make each lob column has it it's own file handle to the lob file to read the data, otherwise streams can get corrupted when there are more than one clob/blob type column in the table.
          Suresh Thalamati made changes -
          Attachment derby378_4.diff [ 12353331 ]
          Attachment derby378_4.stat [ 12353332 ]
          Hide
          Suresh Thalamati added a comment -

          DERBY-378 (partial)
          This patch adds code to handles NULL (SQL NULL) data while
          performing import/export of table with column types blob, clob.
          Checks for invalid hex strings in the import file while performing
          import into a table with Blob column. Import will throw an exception
          if it detects any invalid hex strings in the import file for blob column.

          Tests:

          1) Added a new junit test ImportExportLobTest.java to
          tests import/export of clobs and blob data.

          2) Wrapped BufferInputStream/BufferedReader around the streams
          used in BaseJDBCTestCase.java:assertEquals() methods to compare
          clobs/blobs. Without buffering these assert method were really slow.

          Show
          Suresh Thalamati added a comment - DERBY-378 (partial) This patch adds code to handles NULL (SQL NULL) data while performing import/export of table with column types blob, clob. Checks for invalid hex strings in the import file while performing import into a table with Blob column. Import will throw an exception if it detects any invalid hex strings in the import file for blob column. Tests: 1) Added a new junit test ImportExportLobTest.java to tests import/export of clobs and blob data. 2) Wrapped BufferInputStream/BufferedReader around the streams used in BaseJDBCTestCase.java:assertEquals() methods to compare clobs/blobs. Without buffering these assert method were really slow.
          Suresh Thalamati made changes -
          Attachment derby378_3.diff [ 12352911 ]
          Attachment derby378_3.stat [ 12352912 ]
          Hide
          Suresh Thalamati added a comment -

          DERBY -378 (partial)
          This patch checks for invalid hex strings in the import file
          while performing import into a table with CHAR FOR BIT DATA,
          VARCHAR FOR BIT DATA, LONG VARCHAR FOR BIT DATA data types.
          Import will throw an exception if it detects any invalid hex
          strings during import.

          Tests:
          Added a new junit test case to ImportExportBinaryDataTest.java
          to test for the invalid hex strings in the import file.

          Show
          Suresh Thalamati added a comment - DERBY -378 (partial) This patch checks for invalid hex strings in the import file while performing import into a table with CHAR FOR BIT DATA, VARCHAR FOR BIT DATA, LONG VARCHAR FOR BIT DATA data types. Import will throw an exception if it detects any invalid hex strings during import. Tests: Added a new junit test case to ImportExportBinaryDataTest.java to test for the invalid hex strings in the import file.
          Hide
          Suresh Thalamati added a comment -

          Committed derby378_2.diff to trunk on revision 515708..

          Show
          Suresh Thalamati added a comment - Committed derby378_2.diff to trunk on revision 515708..
          Hide
          Suresh Thalamati added a comment -

          Thanks for taking time to review , Dan.

          > 1) I see use of StringUtil.fromHexString() in the patch, but no use of StringUtil.toHexString() in the patch. How does the exporting work?

          Export calls Resultset.getString() method for these types also. getString() method
          return the data in hex format, by calling StringUtil.toHexString().

          > 2) If the input to StringUtil.fromHexString() is malformed by its length not being a multiple of two then null will be silently inserted. Should import throw an exception here?

          Good Catch. inserting nulls on malformed hex strings is bad. I will add a
          a check for the return value from StringUtil.fromHexString() , and throw an
          exception if it is null.

          Show
          Suresh Thalamati added a comment - Thanks for taking time to review , Dan. > 1) I see use of StringUtil.fromHexString() in the patch, but no use of StringUtil.toHexString() in the patch. How does the exporting work? Export calls Resultset.getString() method for these types also. getString() method return the data in hex format, by calling StringUtil.toHexString(). > 2) If the input to StringUtil.fromHexString() is malformed by its length not being a multiple of two then null will be silently inserted. Should import throw an exception here? Good Catch. inserting nulls on malformed hex strings is bad. I will add a a check for the return value from StringUtil.fromHexString() , and throw an exception if it is null.
          Hide
          Daniel John Debrunner added a comment -

          Looks good, couple of minor questions:

          1) I see use of StringUtil.fromHexString() in the patch, but no use of StringUtil.toHexString() in the patch. How does the exporting work?

          2) If the input to StringUtil.fromHexString() is malformed by its length not being a multiple of two then null will be silently inserted. Should import throw an exception here?

          These could be addressed after the patch is committed.

          Show
          Daniel John Debrunner added a comment - Looks good, couple of minor questions: 1) I see use of StringUtil.fromHexString() in the patch, but no use of StringUtil.toHexString() in the patch. How does the exporting work? 2) If the input to StringUtil.fromHexString() is malformed by its length not being a multiple of two then null will be silently inserted. Should import throw an exception here? These could be addressed after the patch is committed.
          Suresh Thalamati made changes -
          Attachment derby378_2.diff [ 12352804 ]
          Attachment derby378_2.stat [ 12352805 ]
          Hide
          Suresh Thalamati added a comment -

          DERBY -378 (partial)
          This patch adds code required to support import/export of a table with
          CHAR FOR BIT DATA, VARCHAR FOR BIT DATA, LONG VARCHAR FOR BIT DATA
          data types. Data of this type of columns is exported to the main export
          file as hex strings. On import data is also expected to be in hex strings
          in the main export file for these type of columns. This patch also
          disallows use of hex decimal characters (0-9 , a-f , A-F) as
          delimiters for import/export procedures.

          Maximum data length of these types is only 32700 ( 254 bytes for CHAR FOR
          BIT DATA , 32,672 for VARCHAR FOR BIT DATA and 32700 LONG VARCHAR FOR BIT DATA). Because max length allowed is less than 32k, I think providing import/Export
          using an external file for these types may not add much value. No external
          file support will be provided for these types. It can be added later,
          if some one thinks it is required.

          Tests:
          Added a new junit test to test the import/export of these binary types.

          It would be great if someone can review this patch.

          Show
          Suresh Thalamati added a comment - DERBY -378 (partial) This patch adds code required to support import/export of a table with CHAR FOR BIT DATA, VARCHAR FOR BIT DATA, LONG VARCHAR FOR BIT DATA data types. Data of this type of columns is exported to the main export file as hex strings. On import data is also expected to be in hex strings in the main export file for these type of columns. This patch also disallows use of hex decimal characters (0-9 , a-f , A-F) as delimiters for import/export procedures. Maximum data length of these types is only 32700 ( 254 bytes for CHAR FOR BIT DATA , 32,672 for VARCHAR FOR BIT DATA and 32700 LONG VARCHAR FOR BIT DATA). Because max length allowed is less than 32k, I think providing import/Export using an external file for these types may not add much value. No external file support will be provided for these types. It can be added later, if some one thinks it is required. Tests: Added a new junit test to test the import/export of these binary types. It would be great if someone can review this patch.
          Hide
          Suresh Thalamati added a comment -

          Committed derby_378.diff to trunk on revision 512109. If there are any review comments related to this patch , I will address them in the future patches for this issue.

          Show
          Suresh Thalamati added a comment - Committed derby_378.diff to trunk on revision 512109. If there are any review comments related to this patch , I will address them in the future patches for this issue.
          Hide
          A B added a comment -

          I haven't actually reviewed the code at all, but I was just wondering about the names for the new procedures. When I first looked at them use of the "IN_EXTFILE" sounded a tad awkward to me (esp. for export). I instinctively thought "...TO_EXTFILE" would be better for export and "FROM_EXTFILE" would be better for import. But then I realized that such names could potentially cause confusion (ex. If I'm importing lobs from EXTFILE does that mean there is other, non-lob data in EXTFILE that I'm not importing?) So those probably aren't much better.

          The only other thing that comes to mind is "USING_EXTFILE", but again, I don't know if that's really any better. Perhaps "IN_EXTFILE" is in fact best because it's generic and it's short, in which case please feel free to leave the procedure names as they are. I just thought I'd mention that on first reading the names seemed slightly odd, in case you or anyone else can come up with other suggestions.

          NOTE: I do not think this little comment should block commit of the patch. This is a pretty minor thing and could easily be changed as part of a follow-up patch when (and if) a different set of names is chosen.

          Show
          A B added a comment - I haven't actually reviewed the code at all, but I was just wondering about the names for the new procedures. When I first looked at them use of the "IN_EXTFILE" sounded a tad awkward to me (esp. for export). I instinctively thought "...TO_EXTFILE" would be better for export and "FROM_EXTFILE" would be better for import. But then I realized that such names could potentially cause confusion (ex. If I'm importing lobs from EXTFILE does that mean there is other, non-lob data in EXTFILE that I'm not importing?) So those probably aren't much better. The only other thing that comes to mind is "USING_EXTFILE", but again, I don't know if that's really any better. Perhaps "IN_EXTFILE" is in fact best because it's generic and it's short, in which case please feel free to leave the procedure names as they are. I just thought I'd mention that on first reading the names seemed slightly odd, in case you or anyone else can come up with other suggestions. NOTE: I do not think this little comment should block commit of the patch. This is a pretty minor thing and could easily be changed as part of a follow-up patch when (and if) a different set of names is chosen.
          Hide
          Suresh Thalamati added a comment -

          > Each blob/clob goes into its own separate external file? Or all the blobs/clobs go into a single external file?
          > [ Show » ] Bryan Pendleton [24/Feb/07 05:15 PM]

          All the blobs/clobs go into a single external file.

          Show
          Suresh Thalamati added a comment - > Each blob/clob goes into its own separate external file? Or all the blobs/clobs go into a single external file? > [ Show » ] Bryan Pendleton [24/Feb/07 05:15 PM] All the blobs/clobs go into a single external file.
          Hide
          Bryan Pendleton added a comment -

          Each blob/clob goes into its own separate external file? Or all the blobs/clobs go into a single external file?

          Show
          Bryan Pendleton added a comment - Each blob/clob goes into its own separate external file? Or all the blobs/clobs go into a single external file?
          Suresh Thalamati made changes -
          Attachment derby378_1.stat [ 12351943 ]
          Attachment derby378_1.diff [ 12351942 ]
          Hide
          Suresh Thalamati added a comment -

          DERBY -378 (partial)
          This patch adds some code required to support import/export of table with
          clob, blob(large objects) data types. Clob/Blobs data can be exported to
          an external file that different from the main export file. Location of the
          lob data in the external file will be written to the main export file.
          When writing the lob data to an external file, no conversion is done for the
          binary data , clob data will be written using the user specified code set.

          1)This patch implements following two new procedure to support
          exporting LOBS to an external file name:

          SYSCS_UTIL.SYSCS_EXPORT_TABLE_LOBS_IN_EXTFILE(..)
          SYSCS_UTIL.SYSCS_EXPORT_QUERY_LOBS_IN_EXTFILE(..)

          2)This patch implements following two new procedure that allow
          import of large object data stored in a external file.
          (for example exported previously using the above export procedures).

          SYSCS_UTIL.SYSCS_IMPORT_DATA_LOBS_IN_EXTFILE(...)
          SYSCS_UTIL.SYSCS_IMPORT_TABLE_LOBS_IN_EXTFILE(..)

          3) import/export of table with clob,blob types will also work
          with single input/output file, using the exiting import/export
          procedures. In this can binary data is converted into hex format before
          exporting and the data hex is converted to binary on import.
          Clob data is exported similar to other char types.

          Tests: derbyall/junitall test suites passed on Windows XP/JDK142, except
          for the known failures.

          Show
          Suresh Thalamati added a comment - DERBY -378 (partial) This patch adds some code required to support import/export of table with clob, blob(large objects) data types. Clob/Blobs data can be exported to an external file that different from the main export file. Location of the lob data in the external file will be written to the main export file. When writing the lob data to an external file, no conversion is done for the binary data , clob data will be written using the user specified code set. 1)This patch implements following two new procedure to support exporting LOBS to an external file name: SYSCS_UTIL.SYSCS_EXPORT_TABLE_LOBS_IN_EXTFILE(..) SYSCS_UTIL.SYSCS_EXPORT_QUERY_LOBS_IN_EXTFILE(..) 2)This patch implements following two new procedure that allow import of large object data stored in a external file. (for example exported previously using the above export procedures). SYSCS_UTIL.SYSCS_IMPORT_DATA_LOBS_IN_EXTFILE(...) SYSCS_UTIL.SYSCS_IMPORT_TABLE_LOBS_IN_EXTFILE(..) 3) import/export of table with clob,blob types will also work with single input/output file, using the exiting import/export procedures. In this can binary data is converted into hex format before exporting and the data hex is converted to binary on import. Clob data is exported similar to other char types. Tests: derbyall/junitall test suites passed on Windows XP/JDK142, except for the known failures.
          Hide
          Daniel John Debrunner added a comment -

          DERBY-438 might lead you to the tests that show BLOBs in triggers work.

          Show
          Daniel John Debrunner added a comment - DERBY-438 might lead you to the tests that show BLOBs in triggers work.
          Hide
          Suresh Thalamati added a comment -

          Thanks for the input Dan. When I specify the column type for import VTI as Types.BLOB , I got the following error :

          "The external virtual table interface does not support BLOB or CLOB columns. ''

          {0}

          '' column ''

          {1}

          ''. "

          This error is coming from impl/sql/compile/FromVti.java
          if( columnType == Types.BLOB || columnType == Types.CLOB)
          throw StandardException.newException(SQLState.LANG_VTI_BLOB_CLOB_UNSUPPORTED,
          getVTIName(), rsmd.getColumnName( i));

          I will check out triggers with BLOBS.

          Show
          Suresh Thalamati added a comment - Thanks for the input Dan. When I specify the column type for import VTI as Types.BLOB , I got the following error : "The external virtual table interface does not support BLOB or CLOB columns. '' {0} '' column '' {1} ''. " This error is coming from impl/sql/compile/FromVti.java if( columnType == Types.BLOB || columnType == Types.CLOB) throw StandardException.newException(SQLState.LANG_VTI_BLOB_CLOB_UNSUPPORTED, getVTIName(), rsmd.getColumnName( i)); I will check out triggers with BLOBS.
          Hide
          Daniel John Debrunner added a comment -

          For reading from a VTI I'm not sure why CLOB and BLOB are not supported. SQLBlob seems to contain the correct code in its setValueFromResultSet(). It might be a hangover from the time the codebase supported JDK 1.1 and java.sql.Blobs were not available. It might be simply the work was never done. What error do you get?

          For setting BLOB/CLOB into a VTI it might be tricker, since it requires the SQL layer to be able to manufacture a java.sql.Blob object, and since they are JDBC objects the language doesn't handle them. Though it's a simiar problem to BLOBs in triggers/functions and I think at least for triggers it works, so I think there is a mechanism for creating a java.sql.Blob from a BLOB column.

          and having written that, I thought BLOBs were used in triggers and triggers used VTIs ...

          Show
          Daniel John Debrunner added a comment - For reading from a VTI I'm not sure why CLOB and BLOB are not supported. SQLBlob seems to contain the correct code in its setValueFromResultSet(). It might be a hangover from the time the codebase supported JDK 1.1 and java.sql.Blobs were not available. It might be simply the work was never done. What error do you get? For setting BLOB/CLOB into a VTI it might be tricker, since it requires the SQL layer to be able to manufacture a java.sql.Blob object, and since they are JDBC objects the language doesn't handle them. Though it's a simiar problem to BLOBs in triggers/functions and I think at least for triggers it works, so I think there is a mechanism for creating a java.sql.Blob from a BLOB column. and having written that, I thought BLOBs were used in triggers and triggers used VTIs ...
          Hide
          Suresh Thalamati added a comment -

          Import uses VTI to import data into a table from a file. Just found out
          that derby does not support CLOB/BLOB data types with VTI. Any one remember
          why these types are not supported ?

          Currently all the columns in the import file are treated as VARCHAR type and
          cast them to the appropriate column type of the table, when the insert
          statement is generated. For example to import into a table T4(
          create table t4( a int , b char(100)) );

          INSERT INTO "T4"("A", "B") --DERBY-PROPERTIES insertMode=bulkInsert
          SELECT cast(COLUMN1 AS INTEGER) , COLUMN2 from new org.apache.derby.impl.load
          d.Import('c:/suresht/databases/emp.dat',null,null,null, 2 ) AS importvti ;

          Clob types column can casted from VARCHAR type, performance may be bad but it will work.
          Problem with blob data type is, it can be casted from any other type.

          I am kind of stuck on how to extract the binary data from an import file and insert
          into the table using the VTI, without VTI support for CLOB/BLOB types.
          types.

          Any ideas/suggestions ?

          Thanks
          -suresh

          Show
          Suresh Thalamati added a comment - Import uses VTI to import data into a table from a file. Just found out that derby does not support CLOB/BLOB data types with VTI. Any one remember why these types are not supported ? Currently all the columns in the import file are treated as VARCHAR type and cast them to the appropriate column type of the table, when the insert statement is generated. For example to import into a table T4( create table t4( a int , b char(100)) ); INSERT INTO "T4"("A", "B") --DERBY-PROPERTIES insertMode=bulkInsert SELECT cast(COLUMN1 AS INTEGER) , COLUMN2 from new org.apache.derby.impl.load d.Import('c:/suresht/databases/emp.dat',null,null,null, 2 ) AS importvti ; Clob types column can casted from VARCHAR type, performance may be bad but it will work. Problem with blob data type is, it can be casted from any other type. I am kind of stuck on how to extract the binary data from an import file and insert into the table using the VTI, without VTI support for CLOB/BLOB types. types. Any ideas/suggestions ? Thanks -suresh
          Show
          Suresh Thalamati added a comment - related discussion on the derby-dev list: http://www.nabble.com/%28DERBY-378%29-implementing--import-export-of-large-objects...-tf2515951.html#a7017509
          Suresh Thalamati made changes -
          Assignee Suresh Thalamati [ tsuresh ]
          Suresh Thalamati made changes -
          Field Original Value New Value
          Attachment iexlobs.txt [ 12343725 ]
          Hide
          Suresh Thalamati added a comment -

          Some thoughts on implementing import/export for large objects.

          Show
          Suresh Thalamati added a comment - Some thoughts on implementing import/export for large objects.
          Hide
          Brian Bonner added a comment -

          I agree with Suresh. There needs to be a way to import and export data from clobs/blobs.

          Show
          Brian Bonner added a comment - I agree with Suresh. There needs to be a way to import and export data from clobs/blobs.
          Suresh Thalamati created issue -

            People

            • Assignee:
              Suresh Thalamati
              Reporter:
              Suresh Thalamati
            • Votes:
              2 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development