You can modify one or more database tables by means of an XI message. Depending on the content of the message payload, you can insert (INSERT), update (UPDATE), or delete (DELETE) data in database tables.
You can also include results from queries (SELECT) in the response in XML format for synchronous messages.
You use EXECUTE to execute stored procedures and SQL QUERY to forward more complex SQL statements to the database by using the adapter.
Structure of Message Payload
<root>
<StatementName1>
<dbTableName action="ACTION>
<table>realDbTableName</table>
<access>
<col1>val1</col1>
<col2>val2</col2>
</access>
<key1>
<col2>val2old</col2>
<col4>val4</col4>
</key1>
<key2>
<col2>val2old2</col2>
</key2>
</dbTableName>
</StatementName1>
<StatementName2>
...
...
</StatementName2>
...
</root>
Defining the XML Document Structure
<root>
<root>
You can use names of your choice for the tags for statements.
Each statement contains the description of a database action.
With the exception of the execution description for a stored procedure, you use a common structure for all statements.
dbTableNameactionUPDATEINSERTUPDATE_INSERTDELETE,SELECT, EXECUTESQL-QUERY
<table><dbTableName>
This enables you, for example, to define table names that contain non-XML-compatible characters or characters that cannot be used in interface definitions in the Integration Builder.
DELETEaccess
access
Do not change the name of the tag.
key>
Defining Attributes in the <key>Elements
You use this attribute to set the logical compare operation for the respective element. The following values are permitted:
Here is an example of how you can use the parameters:
<key1>
<col2 compareOperation="NEQ">val2old</col2>
<col4 compareOperation="LIKE">val%</col4>
</key1>
"
SELECT col1,col2,col3 FROM dbTableName WHERE ((col2<>'val2old' AND col4 LIKE 'val%') OR (col2='val2old2'))
"
During construction of the WHERE condition of the SQL statement, the table column type determines whether the default is to set the values in quotation marks (text column types) or not (numerical column types). In a few cases (for example, when using functions), you may have to override this.
Values with this attribute are ignored during construction of the WHERE condition. This attribute has the same effect as if the respective value did not exist.
Response documents can only be evaluated by the Integration Server/PCK if the call is synchronous because the content of the response document is not accessible if the call is asynchronous.
The response is put in a separate element <StatementName_response> for each statement element.
The structure of the response documents is contained in the descriptions of the statements.