DB XML Servlet

It may be convenient to export and import DB XML data remotely from the Transportation and Global Trade Management Cloud application. This can be achieved in a number of ways; by sending XML messages via HTTP POST to a servlet on the Transportation and Global Trade Management Cloud Web Server (discussed in this section) or as a SOAP message to a Web Service on the Transportation and Global Trade Management Cloud Application Server (discussed in the next section).

The HTTP POST body should use the format defined below and be sent via HTTP POST to the glog.integration.servlet.DBXMLServlet.

The servlet requires authentication using HTTP Basic Authentication. If the network used for communication cannot be assumed to be secure, the HTTPS protocol should be used.

Additionally, the URL command parameter should specify which DBXML command should be executed i.e. xmlImport for import and xmlExport for export. A complete example URL would therefore be:

https://<OTM URL>/GC3/glog.integration.servlet.DBXMLServlet?command=xmlExport

Note: It is necessary for the XML content to be specified as "text/xml". In curl this is done by adding the option: -H "Content-Type:text/xml".

Note: The at-sign “@” prefix on the filename specified with the -d option is required to inform curl that the parameter is a filename containing the XML text to be sent, rather than the literal text itself.

For example,

curl -k -u GUEST.ADMIN:PW -d @i_transmission.db.xml -o response.xml -H "Content-Type:text/xml" https://<OTM URL>/GC3/glog.integration.servlet.DBXMLServlet?command=xmlExport

Export Message Format

Query

The following is the format for the XML message to export XML based on a SQL query:

<?xml version="1.0" encoding="UTF-8"?>
<sql2xml>
   <Query>
      <RootName>{output XML tag name}</RootName>
      <Statement>{SQL select clause}</Statement>
   </Query>
   <FootPrint>{Y|N}</FootPrint>
   <UseLOBElement>{Y|N}</UseLOBElement>
</sql2xml>

For example,

<?xml version="1.0" encoding="UTF-8"?>
<sql2xml>
   <Query>
      <RootName>Location</RootName>
      <Statement>SELECT * FROM LOCATION WHERE LOCATION_GID = ‘GUEST.MY_LOC’</Statement>
   </Query>
</sql2xml>

Note:

When using the Query option, you must account for any SQL special characters you intend to use in the Statement. Special character such as single quote (‘), ampersand (&), less than (<) and greater than (>), must be either escaped or encoded as appropriate.

Here are some examples on how to escape or encode these special characters:

Single quote can be escaped by preceding the quote with another quote:

<Statement>SELECT * FROM LOCATION WHERE LOCATION_NAME LIKE ‘%‘‘S STORE’</Statement>

Single quote can also be encoded by using 2 ':

<Statement>SELECT * FROM LOCATION WHERE LOCATION_NAME LIKE ‘%&apos;&apos;S STORE’</Statement>

Ampersand should be encoded using &:

<Statement>SELECT * FROM LOCATION WHERE LOCATION_NAME LIKE ‘JACK &amp;
          JILL’</Statement>

Less Than should be encoded using <:

<Statement>SELECT * FROM LOCATION
        WHERE rownum &lt; 10</Statement>    -- used as an
        operator

<Statement>SELECT * FROM LOCATION
          WHERE LOCATION_NAME LIKE ‘MIN &lt; MAX’</Statement>   
          -- used inside criteria string

Greater Than should be encoded using >:

<Statement>SELECT * FROM LOCATION
        WHERE PRIMARY_ADDRESS_LINE_SEQ &gt; 3</Statement>   
        -- used as an operator

<Statement>SELECT * FROM LOCATION
        WHERE LOCATION_NAME LIKE ‘MAX &gt; MIN’</Statement>   
        -- used inside criteria string

For the Not Equal To operator, <> should be encoded using %lt;> or alternatively use != :

<Statement>SELECT * FROM LOCATION
        WHERE PRIMARY_ADDRESS_LINE_SEQ &lt;&gt; 1</Statement>

<Statement>SELECT * FROM LOCATION
        WHERE PRIMARY_ADDRESS_LINE_SEQ != 1</Statement>

Migration Entity

The following is the format for the XML message to export based on a Migration Entity name:

<?xml version="1.0" encoding="UTF-8"?>
<sql2xml>
   <Entity>
      <Name>{entity name}</Name>
      <PK>{object PK}</PK>
      …more PK elements…
   </Entity>
   <FootPrint>{Y|N}</FootPrint>
   <UseLOBElement>{Y|N}</UseLOBElement>
</sql2xml>

For example,

<?xml version="1.0" encoding="UTF-8"?>
<sql2xml>
   <Entity>
      <Name>Location</Name>
      <PK>GUEST.MY_LOC1</PK>
      <PK>GUEST.MY_LOC2</PK>
   </Entity>
</sql2xml>

The response XML will be the TRANSACTION_SET XML identical to that seen in the UI.

Import Message Format

The root element for the DB XML Import message is xml2sql and will contain the following:

<?xml version="1.0" encoding="UTF-8"?>
<xml2sql Version=”21C”>
   <TransactionCode>{I|II|IU|D|RC}</TransactionCode>
   <SchemaOwner>{schema name}</SchemaOwner>
   <UpdateCache>{Y|N}</UpdateCache>
   <RaiseEvents>{Y|N}</RaiseEvents>
   <ManagedTables>
      <Table>{table name 1}</Table>
      <Table>{table name 2}</Table>
   </ManagedTables>
   <TRANSACTION_SET>
       <…table specific elements…>
       …
   </TRANSACTION_SET>
</xml2sql>

For example,

<?xml version="1.0" encoding="UTF-8"?>
<xml2sql Version=”21C”>
   <TransactionCode>I</TransactionCode>
   <SchemaOwner>GLOGOWNER</SchemaOwner>
   <UpdateCache>Y</UpdateCache>
   <RaiseEvents>Y</RaiseEvents>
   <TRANSACTION_SET>
       <LOCATION LOCATION_GID=’GUEST.MY_LOC’..etc>
          <LOCATION_CORPORATION …etc…/>
          <…etc… other child elements…/>
       </LOCATION>
   </TRANSACTION_SET>
</xml2sql>

The response XML will contain the counts for successful or error transactions.

<xml2sql>
   <SuccessCount>n</SuccessCount>
   <ErrorCount>m</ErrorCount>
   <ElapsedTime>p</ElapsedTime>
   <TimePerTransaction>q</TimePerTransaction>
</xml2sql>

Where n, m, p & q are integers and p & q are the number of milliseconds.