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.
<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>
...
...
1. Define a mapping that converts the payload of an XI message to the required XML structure.
See: Message Mappings
2. Define a <root> tag. You can use a name of your choice for the tag.
3. Under the <root> tag, you can define one or more statements.
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.
4. Enter the name of the database table in the element under the statement element (dbTableName) and the attribute action with the value UPDATE, INSERT, UPDATE_INSERT, DELETE,SELECT, EXECUTE, or SQL-QUERY.
5. If you use the optional <table> element, the specified value is used as the database table name.
Do not change the name of the tag. <table> must be the first element in the block within <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.
6. At the next level there is (except for in the DELETEaction) an element with the name accessand one or more elements with arbitrary names. In the above example, these elements are called keyN.
○ Enter the access element first and define the table columns that are to be accessed.
Do not change the name of the tag.
○ Use the <key> elements to describe the condition for accessing the table columns.
7. Define one or more statements for editing database tables:
○ Defining an UPDATE Statement
○ Defining an INSERT Statement
○ Defining an UPDATE_INSERT Statement
○ Defining an EXECUTE Statement
○ Defining an SQL_QUERY Statement
8. You have the option of setting the following attributes in the <key>elements:
○ compareOperation= <compareType>
You use this attribute to set the logical compare operation for the respective element. The following values are permitted:
■ EQ: Equal (default value)
■ NEQ: Not equal
■ LT: Less than
■ LTEQ: Less than or equal to
■ GT: Greater than
■ GTEQ: Greater than or equal to
■ LIKE: Likeness (of strings). In the corresponding value, the SQL placeholders “%” or “_” can then also be used.
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’))
“
○ hasQuot= YES|NO
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.
■ To always set quotation marks, set YES.
■ To never set quotation marks round values with this attribute in the SQL syntax, set NO.
○ isNull= TRUE
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.