Show TOC

Document Format for the Receiver JDBC AdapterLocate this document in the navigation structure

Depending on the processing mode selected for the receiver JDBC adapter , the adapter expects particular XML document formats in the message received from the Integration Server.

The following processing types are possible:

  • XMB2DB
  • XMB2DB_XML
  • XMB2DB_RAWSQL

Additional processing modes may be possible with future versions of the receiver JDBC adapter.

XML Document Format for the Mode XMB2DB

An additional parameter is required in this mode:

  • db.table=
    <tablename>

    Specify the database table that the incoming data is to be written to. In mode XMB2DB of the JDBC adapter it is not possible to distribute the contents of an incoming document to different database tables.

    You can also specify the following parameters:

    • db.apostropheEsc=<apostropheEscapeString>
      The apostrophe character (') is a reserved character in SQL syntax and is therefore replaced by an escape character if it occurs within value strings. This replacement character can be database-specific. Typical replacement characters are
      \'''''
      or . The default setting is . If a character occurs that is invalid for the database being used, the adapter triggers an error message (an SQL exception) concerning the SQL syntax that is generated by the database.
    • db.columnNameDelimiter=<DelimiterSign>
      Depending on the database being used, column names can be enclosed by a special delimiter character, for example, if the names can contain special characters (such as
      "
      ). This character can be specified at this point. The default setting is no delimiter character. If a character occurs that is invalid for the database being used, the adapter triggers an error message (an SQL exception) concerning the SQL syntax that is generated by the database.

    The incoming message must represent a table in XML format; its contents are written to the database table specified in db.table. The same document format is required as is generated by the sender file/FTP and JDBC adapters. This means that the document looks like this:

    <resultset>
<row>
<column-name1>column-value</column-name1>
<column-name2>column-value</column-name2>
<column-name3>column-value</column-name3>
</row>
<row>
<column-name1>column-value</column-name1>
<column-name2>column-value</column-name2>
<column-name3>column-value</column-name3>
</row>
</resultset>

This is an example for 3 columns and 2 lines. The number of lines and columns is, of course, unlimited.

The column names of the database tables are taken from the element names in the XML document (for example, column-name1, column-name2, column-name3), and the corresponding values are inserted for each line.

The document does not have to contain all columns of a database table as long as the database definition accepts a null value in place of the missing columns. Element names of the XML document that do not exist as table column names are ignored.

XML Document Format for the Mode XMB2DB_XML

In this mode you can modify one or more database tables by means of a message. Depending on the content of the message, you can either insert (INSERT), update (UPDATE), or delete (DELETE) the data. Results from queries (SELECT) can also be included in the response in XML format for synchronous messages. The XML document must have the following schema in this case:

<root>
  <StatementName1>
<dbTableName action="UPDATE" | "UPDATE_INSERT">
<table>realDbTableName</table>
<access>
<col1>val1</col1>
<col2>val2new</col2>
</access>
<key1>
<col2>val2old</col2>
<col4>val4</col4>
</key1>
<key2>
<col2>val2old2</col2>
</key2>
</dbTableName>
  </StatementName1>
  <StatementName2>

<table>realDbTableName</table>

<dbTableName action="INSERT">
<access>
<col1>val1</col1>
<col2>val2</col2>
</access>
<access>
<col1>val11</col1>
</access>
</dbTableName>
  </StatementName2>
  <StatementName3>
<dbTableName action="DELETE">
<key1>
<col2>val2old</col2>
<col4>val4</col4>
</key1>
<key2>
<col2>val2old2</col2>
</key2>
</dbTableName>
  </StatementName3>
  <StatementName4>
<dbTableName action="SELECT">
<table>realDbTableName</table>
<access>
<col1/>
<col2/>
<col3/> 
</access>
<key1>
<col2>val2old</col2>
<col4>val4</col4>
</key1>
<key2>
<col2>val2old2</col2>
</key2>
</dbTableName>
  </StatementName4>
  <StatementName5>
<storedProcedureName action=" EXECUTE">
<table>realStoredProcedureeName</table>
<param1 [isInput="true"] [isOutput=true] type=SQLDatatype>val1</param1>
</storedProcedureName >
  </StatementName5>
</root>

Comments:

  • The document contains a tag with the arbitrary name
    <root><StatementName5>
    . Within this tag there are one or more statement elements that also have arbitrary names. Each of these statements contains the description of a database action. With the exception of the execute description for a stored procedure (shown in the example under the element ), all statements have the same structure:
    • The name of the element beneath the statement element specifies the name of the database table and contains the attribute 
      actionINSERTUPDATEUPDATE_INSERTDELETESELECT
      with the value , , , , or .
      If you use the optional
      <table>
      element, the value specified is used as a database table name. This enables you, for example, to define table names, which contain non-XML-compatible characters or characters that stop them from being used in interface definitions in the Integration Builder.
      If specified,
      <table>
      must be the first element in the block within <dbTableName>.
    • Within this element there is (except for 
      DELETEaccesskeyaccesskeyUPDATEDELETE
      ) an element with the name and one or more elements with the name . Both have arbitrary names. contains the table columns that are to be accessed and must be specified as the first element. describes a condition for access. If no such elements are specified, access proceeds without any conditions. In the case of and , this can lead to the entire table being updated or deleted respectively. Such consequences can be avoided by setting the following parameter:

      db.conditionRequired=YES

    • The response document described below can only be evaluated by the Integration Server 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.
  • action=UPDATE

    Statements with this action cause existing table values to be updated. Therefore, the statement corresponds to an SQL UPDATE statement.

    The block <access> contains the new column values and a <key> element contains the columns whose values must be identical with the specified value to get the new column values. The name of the <key> element is arbitrary. Column values within a <key> element are combined with a logical AND; different <key> elements are combined with a logical OR.

    A statement with the action UPDATE must have exactly one <access> element. The number of <key> elements with arbitrary names is not restricted.

    The corresponding SQL statement for StatementName1 in the example above is as follows:

    "

    UPDATE dbTableName  SET col1='val1', col2='val2new' WHERE ((col2='val2old' AND col4='val4') OR (col2='val2old2'))

    "

    As in the remaining examples, the column type String is used for all columns. The character " may be missing in other column types.

    The response document contains the following element as well as the number of updated table lines, including 0.

    <update_count>count</update_count>

    If there is no <key> element, or if there is a <key> element but it is empty, then no condition is specified and the entire table is to be updated. This may not be permitted by the configuration of the JDBC adapter for security reasons and will therefore result in an error during message processing and an appropriate error message.

  • action=INSERT

    Statements with this action cause existing table values to be inserted. Therefore, the statement corresponds to an SQL INSERT statement.

    The block <access> contains the new column values.

    A statement with the action INSERT must have at least one <access> element. It cannot have a <key> element.

    The corresponding SQL statement for StatementName2 in the example above is as follows:

    "

    INSERT INTO dbTableName  (col1, col2) VALUES('val1', 'val2')

    INSERT INTO dbTableName  (col1) VALUES('val11')

    "

    The response document contains the following element as well as the number of inserted table lines, including 0.

    <insert_count>count</insert_count>

  • action=UPDATE_INSERT

    The statement has the same format as for the UPDATE action. Initially, the same action is executed as for UPDATE. If no update to the database table can be made for this action (the condition does not apply to any table entry), values of the table described in the <access> element are inserted in accordance with the description of the action INSERT. <key> elements are ignored in this case.

    The response document has the following format; one of the two values is 0 because either an UPDATE or an INSERT action is always executed:

    <update_count>count</update_count>

    <insert_count>count</insert_count>

  • action=DELETE

    Statements with this action cause existing table values to be deleted. One or more <key> elements formulate the condition that deletes the table values. The names of <key> elements are arbitrary. Column values within a <key> element are combined with a logical AND; different <key> elements are combined with a logical OR.

    The corresponding SQL statement for StatementName3 in the example above is as follows:

    "

    DELETE FROM dbTableName  WHERE ((col2='val2old' AND col4='val4') OR (col2='val2old2'))

    "

    The response document contains the following element:

    <delete_count>count</delete_count>

    If there is no <key> element, or if there is a <key> element but it is empty, then no condition is specified and the entire table is to be deleted. This may not be permitted by the configuration of the JDBC adapter for security reasons and will therefore result in an error during message processing and an appropriate error message.

  • action=SELECT

    Statements with this action cause existing table values to be selected. Therefore, the statement corresponds to an SQL SELECT statement.

    The block <access> contains the column names to be selected, a <key> element contains the columns whose values must be identical with the specified value to get the new column values. The name of the <key> element is arbitrary. Column values within a <key> element are combined with a logical AND; different <key> elements are combined with a logical OR.

    A statement with the action SELECT must have exactly one <access> element. The number of <key> elements with arbitrary names is not restricted.

    The corresponding SQL statement for StatementName4 in the example above is as follows:

    "

    SELECT col1,col2,col3 FROM dbTableName  WHERE ((col2='val2old' AND col4='val4') OR (col2='val2old2'))

    "

    If there is no <key> element, or if there is a <key> element but it is empty, then no condition is specified and the entire table is to be selected. This may not be permitted by the configuration of the JDBC adapter for security reasons and will therefore result in an error during message processing and an appropriate error message.

    The response document contains the result of the action in XML format as follows:

    "
<row>
<column1>value11</column1>
<column2>value12</column2>
...
</row>
...
<row>
<column1>valueN1</column1>
<column2>valueN2</column2>
...
</row>
"
  • action=EXECUTE

    Statements with this action result in a stored procedure being executed. The name of the element is interpreted as the name of the stored procedure in the database.

    If you use the optional <table> element, the value specified is used as the name of the stored procedure. This enables you, for example, to define names for stored procedures, which contain non-XML-compatible characters or characters that stop them from being used in interface definitions in the Integration Builder.

    If specified, <table>must be the first element in the block within <dbTableName>.

    The elements within the stored procedure are interpreted as parameters. They can optionally have the attribute isInput="1" (input parameter) or isOutput="1" (output parameter) or both (INOUT parameter). If both attributes are missing, the element is interpreted as an input parameter. The parameter names must be identical to the stored procedure definition.

    The attribute type=<SQL-Datatype> , which describes the valid SQL data type, is mandatory for all parameter types (IN, OUT, INOUT).

    The following SQL data types are supported: INTEGER, BIT, TINYINT, SMALLINT, BIGINT, FLOAT, REAL, DOUBLE, NUMERIC, DECIMAL, CHAR, VARCHAR, STRING, LONGVARCHAR, DATE, TIME, TIMESTAMP, BINARY, VARBINARY, LONGVARBINARY, BLOB (output only),CLOB (output only).

    All return values are returned in an XML structure. The results within the stored procedure are returned either as a table or as the element <update_count>. This depends on the SQL statements executed within the stored procedure. The return parameters of a stored procedure are attached in a separate structure.

Attributes in the <key> Elements

The xml elements in the <key> elements can have the following optional attributes:

  • compareOperation= <compareType>

    This attribute enables the logical compare operation to be set for the respective element. The following values are permitted:

Values for compareOperation

Attribute Value

EQ

Check for equals (default value)

NEQ

Check for does not equal

LT

Check for less than

LTEQ

Check for less than or equal to

GT

Check for greater than

GTEQ

Check for greater than or equal to

LIKE

Check for likeness (of strings). In the corresponding value, the SQL placeholders "%" or "_" can then also be used.

In the above example XML document, the <key1> block is changed for the SELECT statement (StatementName4) as follows:

<key1>

<col2 compareOperation="NEQ">val2old</col2>

<col4 compareOperation="LIKE">val%</col4>

</key1>

The executed SQL statement is then changed as follows:

"

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), it may be necessary to override this. This attribute enables you to do this. If YES, quotation marks are always set round the values for which this attribute is set in the SQL syntax. If NO, quotation marks are never set. Only use this attribute in individual cases.

Additionally, the following parameters can be specified for the SQL syntax, which are used for all actions:

  • db.emptyStringValue=NULL|EMPTY

    In database fields of type STRING, DATE, TIME, or TIMESTAMP, this parameter defines whether empty values in the XML structure are to be interpreted as empty values (EMPTY - a database field with length null) or as NULL values (NULL - the database field is not set). Empty values are always interpreted as NULL values in all other parameter types. The default setting is NULL.

  • db.apostropheEsc=<apostropheEscapeString>
    The apostrophe character (
    '\'''''
    ) is a reserved character in SQL syntax and is therefore replaced by an escape character if it occurs within value strings. This replacement character may be database-specific. Typical characters are or . The default character is .

    If a character occurs that is invalid for the database being used, the adapter triggers an error message (an SQL exception) concerning the SQL syntax that is generated by the database.

  • db.columnNameDelimiter=<DelimiterSign>
    Depending on the database being used, column names can be enclosed by a special delimiter character, for example, if the names can contain special characters (such as 
    "
    ). This character can be specified at this point. The default setting is no delimiter character.

    If a character occurs that is invalid for the database being used, the adapter triggers an error message (an SQL exception) concerning the SQL syntax that is generated by the database.

XML Document Format for the Mode XMB2DB_RAWSQL

This mode is primarily for test purposes only. Instead of an XML document format, a text is expected that represents any valid SQL statement.

When inserting a line into a table the corresponding document looks like this:

"
INSERT INTO tableName  (column-name1, column-name2, column-name3) VALUES('column-value1', 'column-value2', 'column-value3')
"