Re: how to import xml format string or xml file into clob using SYSCS_UTIL.SYSCS_IMPORT_DATA(_LOBS_FROM_EXTFILE)

[Rick Hillegas]

Hi Jingli,

If I were trying to reverse-engineer the right numbers, I would try 
inserting the xml string into your CLOB column directly and then use the 
export procedures. The exported files should be formatted so that the 
corresponding import procedures will work.

Hope this helps,
-Rick

Jingli Zhang wrote:
I am wondering if I can I import xml format string or xml file into
database using SYSCS_UTIL.SYSCS_IMPORT_DATA or
SYSCS_UTIL.SYSCS_IMPORT_DATA_LOBS_FROM_EXTFILE

Here is what I tried:
ps = conn.prepareStatement("call syscs_util.syscs_import_data('SCHEMA', '"
+tableName.toUpperCase()+"', 'templateXml', '2', "
                                                + "'"+file+"', null, '%', null, 
0)");

Here is the sample file content

1,%<ProductSection ...>
  <Info>global settings</Info>
  <Product>Credentails</Product>
  <Version>1.0</Version>
  <Property ...>
    <Description>Specifies username</Description>
  </Property>
  <Property ...>
    <Description>Specifies password</Description>
  </Property>
</ProductSection>%
2,%<ProductSection ...>
  <Info>Network interface definition for eth0</Info>
  <Product>eth0</Product>
  <Version>11</Version>
  <Property ...>
    <Description>Network configuration method: "true" or
"false"</Description>
  </Property>
  <Property ...>
    <Description>The hostname for the system</Description>
  </Property>
  <Property ...>
    <Description>The dns domain for the system</Description>
  </Property>%

It seems the line return cause the problem. I got following error
SQLState:   XIE0R
Severity: -1
Message:  Import error on line 2 of file C:/input: Invalid character string
format for type INTEGER.
java.sql.SQLException: Import error on line 2 of file C:/input: Invalid
character string format for type INTEGER.
        at org.apache.derby.client.am.SQLExceptionFactory40.getSQLException
(Unknown Source)
        at org.apache.derby.client.am.SqlException.getSQLException(Unknown
Source)
        at org.apache.derby.client.am.PreparedStatement.execute(Unknown
Source)

If I put the xml sections in this file into separated files and use
SYSCS_UTIL.SYSCS_IMPORT_DATA_LOBS_FROM_EXTFILE command.How do I set the
references point in the main file.
does the input file looks like

1,"c:/xml1.0.length"
2,"c:/xml2.0.length"

 what value I should use for offset and length for the xml files?

Thanks,

Jingli