You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Jingli Zhang <ji...@us.ibm.com> on 2010/03/25 21:10:56 UTC

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

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

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

Posted by Rick Hillegas <Ri...@Sun.COM>.
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
>
>