Document Formats for the Receiver JDBC Adapter

Document Formats for the Receiver JDBC AdapterXML Document Format for the Message Protocol XML SQL FormatYou 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 areone or more statement elements that also have arbitrary names. Each of thesestatements contains the description of a database action. With the exception of theexecute description for a stored procedure (shown in the example under theelement <StatementName5>), all statements have the same structure:○The name of the element beneath the statement element specifies the name ofthe database table and contains the attribute action with thevalue INSERT, UPDATE, UPDATE_INSERT,DELETE, or SELECT. If you use theoptional <table> element, the value specified is used as a database tablename. This enables you, for example, to define table names containingnon-XML-compatible characters or characters that prevent them from beingused in interface definitions in the Integration Builder. Ifspecified, <table> must be the first element in the blockwithin<dbTableName>.○Within this element there is (except for in the DELETE action) an element with thename access and one or more elements with arbitrary names. In the aboveexample, these elements are called keyN. The access element contains thetable 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 arespecified, access proceeds without any conditions. In the caseof UPDATE and DELETE, this can lead to the entire table being updated ordeleted respectively.If you want to ensure this does not happen, select Key Tags Mandatory in theadapter configuration.○The response documents described below can only be evaluated by theIntegration Server/PCK if the call is synchronous because the content of theresponse document is not accessible if the call is asynchronous. The responseis put in a separate element <StatementName_response> for each statementelement.●action=UPDATEStatements 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 withina <key> element are combined with a logical AND; different <key> elements arecombined with a logical OR.A statement with the action UPDATE must have exactly one <access> element. Thenumber 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. Thecharacter “may be missing in other column types.The response document contains the following element as well as the number ofupdated 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=INSERTStatements 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. Itcannot 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 ofinserted table lines, including 0.<insert_count>count</insert_count>●action=UPDATE_INSERTThe 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 thisaction (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 theaction 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=DELETEStatements with this action cause existing table values to be deleted. One ormore <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 WHER E ((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=SELECTStatements 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 withina <key> element are combined with a logical AND; different <key> elements arecombined with a logical OR.A statement with the action SELECT must have exactly one <access> element. Thenumber 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=EXECUTEStatements 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 storedprocedure name. This enables you, for example, to define stored procedure names containing non-XML-compatible characters or characters that prevent them from beingused 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“ (outputparameter) 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 storedprocedure are returned either as a table or as the element <update_count>. This depends on the SQL statements executed within the stored procedure. The returnparameters of a stored procedure are attached in a separate structure.●action=SQL_QUERY | SQL_DML This structure enables you to transfer morecomplex SQL statements to the database directly using the adapter. You have theoption of using placeholders in these SQL statements, which can be listed in thesubsequent 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 thename <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 aboveexample <StatementName6>, thestrings $placeholder1$ and $placeholder2$ contained in the SQL stringare replaced with value1 or value2before the SQL statement is executed.○If you are not using placeholders, then the <key>block can be omitted or leftempty. In both cases, you must not select the Key Tags Mandatory field in theconfiguration, 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' WHERECustomerID='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 influencethe 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 tosyntax errors or to unexpected results in the database.Attributes in the <key> ElementsThe 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 compareOperationIn 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 quotationmarks (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 thisattribute is set in the SQL syntax. If NO, quotation marks are never set. Only use thisattribute in individual cases.●isNull=TRUE Values with this attribute are ignored during construction of theWHERE 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 FormatThis 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‟)“。