Document Formats for the Receiver JDBC Adapter

转自：http://help.sap.com/saphelp_nw70/helpdata/en/2e/96fd3f2d14e869e10000000a155106/content.htm


XML Document Format for the Message Protocol XML SQL Format
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>

<dbTableName action=”INSERT”>

<table>realDbTableName</table>

<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>



  <StatementName6>

<anyName action=” SQL_QUERY” | “SQL_DML”>

<access>SQL-String with optional placeholder(s)</access>

<key>

  <placeholder1>value1</placeholder1>

  <placeholder2>value2<placeholder2>

</key>

</anyName > 

  </StatementName6>

</root>

Comments
●      The document contains a tag with the arbitrary name <root>. 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 <StatementName5>), 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 action with the value INSERT, UPDATE, UPDATE_INSERT, DELETE, or SELECT. 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 containing non-XML-compatible characters or characters that prevent 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 in the DELETE action) an element with the name access and one or more elements with arbitrary names. In the above example, these elements are called keyN. The access element contains the table columns which are to be accessed. It must be specified as the first element. The key elements describe a condition for access. If no such elements are specified, access proceeds without any conditions. In the case of UPDATE and DELETE, this can lead to the entire table being updated or deleted respectively.

If you want to ensure this does not happen, select Key Tags Mandatory in the adapter configuration.

○       The response documents described below 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.

●      action=UPDATE

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

The <access> block 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 other 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 table values to be inserted. Therefore, the statement corresponds to an SQL INSERT statement.

The <access> block 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 always 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 for which table values are deleted. 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 <access> block 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 here is used as the stored procedure name. This enables you, for example, to define stored procedure names containing non-XML-compatible characters or characters that prevent them from being used in interface definitions in the Integration Builder/PCK.  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 those of 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 (input and output),CLOB (input and output), CURSOR (output; only in conjunction with the Oracle JDBC driver).



The binary data for BLOB is hexadecimal encoded.

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.

●      action= SQL_QUERY | SQL_DML  This structure enables you to transfer more complex SQL statements to the database directly using the adapter. You have the option of using placeholders in these SQL statements, which can be listed in the subsequent keyblock. This makes it easy to generate complex, parameterisable SQL statements.

Details on the structure:

○       The name of the structure is arbitrary. Unlike in the usual statement types, no table name or stored procedure name is expected in the default setting.

○       If the SQL statement represents a query to the database (SELECT), choose Action=SQL_QUERY.

○       If it represents a call from the SQL Data Manipulation Language (UPDATE, INSERT, DELETE), choose Action=SQL_DML.

○       The first element in the structure must have the name <access> and contain a valid SQL call for the respective mode, optionally with placeholders (see below).

○       If you use placeholders, these must be listed in the element with the name <key>. The names of the placeholder elements must be identical to those used in the SQL string (where they still have the $ character). In the above example <StatementName6>, the strings $placeholder1$ and $placeholder2$ contained in the SQL string are replaced with value1 or value2 before the SQL statement is executed.

○       If you are not using placeholders, then the <key>block can be omitted or left empty. In both cases, you must not select the Key Tags Mandatory field in the configuration, as this will cause runtime errors.

Example (Without Placeholders):

<root>

  <stmt>

     <Customers action="SQL_DML">

      <access> UPDATE Customers SET CompanyName='Company', Address='Street 3' WHERE CustomerID='CO'

      </access>

    </Customers>

  </stmt>

</root>

The unchanged SQL statement is executed in the database:

UPDATE Customers SET CompanyName='Company', Address='Street 3' WHERE CustomerID='CO'

Example (with Placeholders):

<root>

  <stmt>

    <Customers action="SQL_DML">

      <access> UPDATE Customers SET CompanyName=’$NAME$’, Address=’$ADDRESS$' WHERE CustomerID='$KEYFIELD$’

      </access>

      <key>

        <NAME>Company</NAME>

        <ADDRESS>Street 3 </ADDRESS>

        <KEYFIELD>CO</KEYFIELD>

      </key>

    </Customers>

  </stmt>

</root>

After the placeholders have been replaced, the same SQL statement is executed in the database as above:

UPDATE Customers SET CompanyName='Company', Address='Street 3' WHERE CustomerID='CO'



Comments:

○       Using placeholders is not restricted to individual field values, as in this example. You can set any parts of the SQL statement in this way. You can also influence the logic of the statement.

○       Surplus and undefined placeholders are tolerated in the <key> section. Undefined placeholders are left unchanged in the SQL string. This can lead to syntax errors or to unexpected results in the database.

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 and Check

EQ
Equals (default value)

NEQ
Does not equal

LT
Less than

LTEQ
Less than or equal to

GT
Greater than

GTEQ
Greater than or equal to

LIKE
Like (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 SELECTstatement (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.

●      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 does not exist. This is often difficult to represent in mapping programs.

XML Document Format for the Message Protocol Native SQL Format
This protocol 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 as follows:

„

INSERT INTO tableName  (column-name1, column-name2, column-name3) VALUES(‘column-value1’, ‘column-value2’, ‘column-value3’)

“