JDBC Adapter in SAP PI

Configuring the JDBC AdapterUse The JDBC (Java Database Connectivity) adapter enables you to connect database systems to the Integration Server or the PCK. The adapter converts database content to XML messages and the other way around. You can read database content with any SQL statement, even stored procedures. You define a special XML format for content from the Integration Server or the PCK. This format enables SQL INSERT, UPDATE, SELECT, DELETE, or stored procedure statements to be processed. A message is always processed in exactly one database transaction. For FAQs about the JDBC adapter, see SAP Note 831162. Prerequisites ● To be able to use the JDBC adapter, you must have installed the JDBC driver for the database to which you want to connect. The required Java libraries are product-specific and must be obtained from the database vendor. ● Deploy the Java libraries in AS Java following installation so that the JDBC adapter can find the required Java classes at runtime. For more information, see the chapter Providing External Drivers for the JDBC and JMS Adapters (in the SAP NetWeaver Library Function-Oriented View under Process Integration ® Configuring Process Integration (PI) After Installation ® Integration of Business Systems without Integration Engine ® Integration Using the Advanced Adapter Engine). Procedure 1. Create a Communication Channel in the Integration Directory. 2. To configure the adapter, select the Parameters tab page. 3. Select JDBC as the Adapter Type. 4. Specify the Direction (Sender/Receiver) of the adapter. Configuring the Sender JDBC Adapter Configuring the Receiver JDBC Adapter Configuring the Sender JDBC Adapter Use You configure the sender JDBC adapter to be able to send content from databases to the Integration Server or to the PCK. Prerequisites ● There must be exactly one sender agreement for the defined communication channel. ● You must add an indicator that specifies the processing status of each data record in the adapter (processed/not processed) to the database table. ● The UPDATE statement must alter exactly those data records that have been selected by the SELECT statement. You must use the same WHERE clause in the UPDATE and the SELECT statements. See below under Defining Processing Parameters, SQL Statement for Query, and SQL Statement for Update. The example shows the correct specification of the SELECT and UPDATE statement: SQL statement for query: SELECT * FROM table WHERE processed = 0; SQL statement for update: UPDATE table SET processed = 1 WHERE processed = 0; processed is the indicator in the database. ● Processing can only be performed correctly when the Transaction Isolation Level is set to repeatable_read or serializable. Procedure 1. The Transport Protocol is JBDC 2.0. 2. The Message Protocol is JDBC. Additional values may be added for the message protocol in future versions. 3. Select the Adapter Engine on the Integration Server, or select a noncentrally installed Adapter Engine. This selection is not available in the PCK. Defining the Database Connection 4. Select the Connection tab page. 5. Under JDBC Driver, enter the Java class of the JDBC driver. The JDBC adapter must load the class to be able to access the driver. 6. Under Connection, specify the address with which you can open a database connection using the JDBC driver. 7. Under User Name and Password, enter the logon data for the database to be read. The entries for JDBC Driver and Connection depend on the JDBC driver. You will find this information in the documentation from the provider. Defining Processing Parameters 8. Select the Processing tab page. 9. Under Quality of Service, specify how a message is to be processed by the Integration Engine/PCK. For Quality of Service Exactly Once In Order, enter the Queue Name. See: Quality of Service 10. Specify the following for the poll interval: ○ Poll Interval (secs): Number of seconds that the adapter must wait if no files are found for processing ○ Poll Interval (msecs): Number of milliseconds that the adapter must wait if no files are found for processing If you set Poll Interval (secs) to null, processing times are short and close to real time. If you set Poll Interval (secs) and Poll Interval (msecs) to null, the adapter is called once. ○ Retry Interval (secs): Number of seconds that the adapter is to wait before an SQL statement processed with errors is processed again If you set the value to null, the adapter is canceled if an error occurs, even if a value greater than null is specified for Poll Interval (secs). If you do not enter a value, the value from Poll Interval (secs) is used. 11. The specification for Query SQL Statement must correspond to the SQL variant supported by the respective JDBC driver. It can also contain table JOINs. ○ Enter a valid SQL statement that is to be applied to the database once the data (determined from the Query SQL Statement) has been successfully sent to the Integration Server/PCK. It must be an INSERT, UPDATE, or DELETE statement. ○ Specify an SQL EXECUTE statement to execute a stored procedure, which contains exactly one SELECT statement. 12. Enter the Document Name. The document name is inserted in the message as the main XML tag. 13. Enter the Document Namespace. The namespace is added to the document name. 14. Enter the Update SQL Statement. ○ Enter a valid SQL statement that is to be applied to the database once the data (determined from the Query SQL Statement) has been successfully sent to the Integration Server/PCK. The SQL statement must be an INSERT, UPDATE, or DELETE statement. ○ If you want the data determined from the Query SQL Statement to remain in the database unchanged after being sent successfully, enter <TEST>. This is recommended if the data has not only been read, but also changed by a stored procedure entered under Query SQL Statement. Defining an Operating System Command 15. Specify an operating system Command Line that is to be executed following successful database operations. 16. Under Timeout (secs), specify the maximum runtime of the executing program in seconds. When this time interval is exceeded, the adapter continues processing. The executing program continues to run in the background. 17. If the adapter is to terminate the executing program in the event of a timeout, select Terminate Program After Timeout. The adapter writes the output (STDOUT and STDERR) for the operating system command in the system trace. Message processing is independent of any errors that occur during the execution of a configured operating system command. Defining the Adapter Status 18. Select the Advanced tab page. 19. Set the adapter to Active to enable messages to be exchanged. Defining Additional Parameters in Advanced Mode 20. To specify additional parameters for the adapter configuration, select Advanced Mode. 21. To define how transactions running in parallel are to influence each other, select the Transaction Isolation Level. You can run database transactions at different levels, known as isolation levels. The options correspond to the JDBC constants: ○ Default (default setting of the respective database) ○ None ○ read_uncommitted (weakest setting) ○ read_committed ○ repeatable_read ○ serializable (strongest setting) select Remove Empty Tags. 23.Only reduce the security level when necessary and as far as necessary. Do not set this indicator if the JDBC driver supports transactions. you must find another way to ensure the consistency of the data in the database. If you want the database connection to be released and reestablished before each Poll Interval. To do this. The logical unit of work ensures data consistency. If you want to remove the empty tags from the resultset of the sender adapter. the resultset looks as follows: <resultset> <row> <column-name1>column-value</ column-name1> <column-name2></column-name2> <column-name3>column-value</ column-name3> <column-name4></column-name4> </row> <row> <column-name1>column-value</ column-name1> <column-name2></ column-name2> </row> </resultset> If this indicator is set. You must find another way to make sure that data inconsistencies cannot be generated in the database. the resultset looks as follows: <resultset> . usually by preventing parallel access. usually by preventing parallel access. If the JDBC driver does not support any transactions. If the JDBC driver does not support any transactions. 22. choose Database Auto-Commit-Enabled (No Transaction Handling). This helps to reduce the size of XML documents. 24. deactivate the logical unit of work. If this indicator is not set. select Disconnect from Database After Processing Each Message. it is possible that there could be out of memory errors. maxMsgSize and maxRowSize parameters are mandatory. This parameter is used to calculate the maximum number of rows that could be sent through channel in one interval. ● maxLimitErrorInterval : This parameter is used to provide a special interval to change the next polling interval incase an error occurs when a maximum message size is reached for the current interval. The value of the parameter should be provided in KB. Following are the parameters to be set in the table to limit the message size: ● msgLimit : This parameter is used to enable the max message size limit feature for JDBC adapter. ● maxMsgSize : This parameter is used to limit the message size to an optimal value. The parameter value should be provided in seconds. Additional parameters are published in SAP Note 801367. The value of the parameter should be provided in KB. If msgLimit is set to true then. When this parameter is set to true. More information on configuring the maximum row size: SAP note 1253826. JDBC adapter does not process the message of size higher than the values specified through maxMsgSize and maxRowSize. This reduces the load on the system. More information on configuring the maximum message size: SAP note 1253826. Due to messages of large size. Specify additional Parameter Names and Parameter Values in the table. ● maxRowSize : This parameter is used to provide the maximum row size. The document looks like this: <resultset> . which could lead to JEE server node failures. JDBC adapter does not process the message size of more than the value provided for maxMsgSize. if encountered at runtime. Example The system converts the table resulting from the query SQL statement into a valid XML document and sends it to the Integration Engine/PCK.<row> <column-name1>column-value</ column-name1> <column-name3>column-value</ column-name3> </row> <row> <column-name1>column-value</ column-name1> </row> </resultset> 25. The following types of quality of service are supported: • BE (Best Effort) The message is sent synchronously. The sender does not wait for a response. The sender waits for a response before it continues processing. In the case of delivery types EO and EOIO. • EOIO (Exactly Once In Order) Messages are delivered with the same queue names (supplied by the application) in the same sequence that they were sent from the sender system. In the case of quality of service BE an error occurs if more than one receiver is determined for a message. the message is copied correspondingly and sent to the individual receivers. The Advanced Adapter Engine guarantees that the message is sent and processed exactly once. • EO (Exactly Once) The message is sent asynchronously. Message processing is asynchronous in this case.<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> Quality of Service The sender of a message uses the attribute Quality of Service (QoS) to determine how a message is delivered. Configuring the Receiver JDBC Adapter . Under Connection. Default value is 1 and this means only one message can be processed at a time by the receiver channel. or delete table values in one or more tables. Select the Processing tab page. change. You can find this information in the documentation from the respective provider. Choose the Adapter Engine on the Integration Server. The JDBC adapter must load the class to be able to access the driver. In the case of synchronous queries. 2.You configure the receiver JDBC adapter to convert XML messages from the Integration Server or the PCK into database table content. . o If you want to specify a SQL statement of your choice in the message payload. Under JDBC Driver. You can call stored procedures in the database by using transfer parameters. Depending on the message protocol. then two messages are processed in parallel. if you enter the value 2. Procedure 1. 3. For example. or choose a noncentrally installed Adapter Engine. select XML SQL Format. the adapter expects special XML document formats in the payload of the XI message. 2. The Transport Protocol is JBDC 2. More information: Defining XML Documents for Message Protocol XML SQL Format Defining XML Documents for Message Protocol Native SQL Format 3. specify the address with which you can open a database connection using the JDBC driver. enter the number of messages to be processed in parallel by the receiver channel. to be transferred unchanged to the database for processing. o If you want to insert. 2. Defining Processing Parameters 1.0. results from database queries or return values of stored procedures can also be transferred. Under User Name and Password. 1. 2. Under Maximum Concurrency. The entries for JDBC Driver and Connection depend on the JDBC driver. Select the Message Protocol. Defining the Database Connection 1. This selection is not available in the PCK. 4. select Native SQL String. enter the Java class of the JDBC driver. enter the logon data for the database to be read. Select the Connection tab page. . are handled correctly. All error statuses of the adapter. when the first attempt at message processing is interrupted by an irregular termination of AS Java immediately after the database commit. even program terminations initialized externally. If an external program termination occurs during a database commit. o Local Messages of type Exactly Once are handled by status information management for these messages in AS Java. o Database If no field is designated as a primary key in the database table. 2. Without this setting.  Error If an error occurs when processing again. This situation is identified when the application is restarted. because the status of the message is not changed until the database commit is complete. Specify where Persistence for Exactly Once is to take place. the adapter continues to resend the message and the error continues to occur. Defining Exactly-Once Handling 1. To make conditions mandatory in the key tag of the XML document. this is reported as an error to the caller system. empty texts are inserted in the columns. empty fields are handled like NULL fields (do not exist) and are not inserted in the database. This problem can only be solved if message processing and status information management take place in the same database so that the processing steps have the same commit cycle. The database interface then triggers the error duplicate insert if at least one table field is defined as a primary key. 4. Defining XML Schema Interpreter for Message Protocol XML SQL Format 1.  Redo This setting enables editing for the receiver adapter to be completed successfully if the error occurs. o Empty String In the case of INSERT and UPDATE statements. Specify how terminated message processing is to be handled under Conflict Resolution. because the message was saved in the database when it was first processed and it is still located there. This entry is only applied when handling errors that occur when a message is being processed for a second time after initial processing remained in the unclear status described above. Select how empty text fields are to be handled under Interpretation of Empty String Values. the status of message processing is unclear initially. or if the data has already been processed by another application and then deleted. a message can be duplicated. select Key Tags Mandatory. o NULL Value In the case of INSERT and UPDATE statements.3. Defining the Adapter Status 1. 5. if names can contain special characters (such as "). the adapter triggers an error message (an SQL exception) for the SQL syntax that is generated by the database. the adapter triggers an error message (an SQL exception) for the SQL syntax that is generated by the database. Under Timeout (secs). 2. select Terminate Program After Timeout. These replacement characters can be database-specific. Message processing is independent of any errors that occur during the execution of a configured operating system command. Depending on the database being used. 6. specify the following:  Database Table Name Enter the name of the table. 7.  Key Column Name Enter the name of the column in which the key is entered. The executing program continues to run in the background. Typical replacement characters are \ or '' (default value). 3. for example. Defining SQL Syntax Parameters 1.In the database where the write-to tables are located you must create an additional table with two columns for this purpose. Select the Advanced tab page. If you use a character that is invalid for the database being used.  Value Column Name Enter the name of the column in which the key value is entered. the adapter continues processing. Enter the Column Name Delimiter. . If the adapter is to terminate the executing program in the event of a timeout. Enter the Escape Symbol for Apostrophe. To define the table. Defining an Operating System Command 1. When this time interval is exceeded. column names can be enclosed by a special delimiter character. Specify an operating system Command Line that is to be executed following successful database operations. 2. The adapter writes the output (STDOUT and STDERR) for the operating system command in the system trace. specify the maximum runtime of the executing program in seconds. If a character occurs that is invalid for the database being used. The apostrophe character (') is a reserved character in SQL syntax and is replaced by an escape character if it occurs within value strings. . It may be the case that not every available JDBC driver is able to run batch processing. you must find another way to ensure the consistency of the data in the database. select Advanced Mode. 3. The logical unit of work ensures data consistency. If the JDBC driver does not support any transactions. Set the adapter to Active to enable messages to be exchanged. the message is only processed again when the Integration Server/PCK sends it again. If the number of retries is exceeded. 4. usually by preventing parallel access. choose Transaction Isolation Level. There are different levels of database transactions known as isolation levels. Defining Parameters in Advanced Mode 1. the last status is reported to the sender Integration Server or the sender PCK. This can improve performance considerably. 2. To do this. choose Database Auto-Commit-Enabled (No Transaction Handling). If an error occurs. 8. If the JDBC driver does not support any transactions. specify how often the system is to attempt to reestablish the database connection and access the database in the event of an SQL exception. Caution Do not set this indicator if the JDBC driver supports transactions. select Batch Mode. usually by preventing parallel access. The options correspond to the JDBC constants: o o o o o o Default None (default setting of the respective database) (weakest setting) read_uncommitted read_committed repeatable_read serializable (strongest setting) Caution Only reduce the isolation level when necessary and as far as necessary. If you want to collect SQL statements in a batch. 5.2. Under Number of Retries of Database Transaction on SQL Error. If you want the database connection to be released and reestablished before each Poll Interval. 6. To define how transactions running in parallel are able to influence each other. Find another way to make sure that data inconsistencies cannot be generated in the database. deactivate the logical unit of work. select Disconnect from Database After Processing Each Message. To specify additional parameters. MMM = Jul. yy = 96 MMMM = July. Note Additional parameters are published in SAP Note 801367. The string format corresponds to the Java class java. Tue PM 0 24 0 12 30 . MM = 07 27 2 186 10 2 Tuesday.SimpleDateFormat.text. Specify the Date/Time Formats for Stored-Procedure Calls. Letter Date or Time Component Type G y M w W D d F E a H k K h m Epoch name Year Month in year Week in year Week in month Day in year Day in month Day of week in month Day in week am/pm marker Hour in day (0-23) Hour in day (1-24) Hour in am/pm (0-11) Hour in am/pm (1-12) Minute in hour Text Year Month Number Number Number Number Number Text Text Number Number Number Number Number Examples G = AD yyyy = 1996.Batch processing is not supported for the following statements: o o o o SELECT UPDATE_INSERT EXECUTE SQL_QUERY 7. 8. Specify additional Parameter Names and Parameter Values in the table. K:mm a corresponds to 0:30 PM. hh. Defining XML Documents for Message Protocol XML SQL Format Use You can modify one or more database tables by means of an XI message. you can insert (INSERT).07. You use EXECUTE to execute stored procedures and SQL QUERY to forward more complex SQL statements to the database by using the adapter. for example.s S z Z Second in minute Millisecond Time zone Time zone Example Number Number 55 978 General time zone Pacific Standard Time.MM. for example. or delete (DELETE) data in database tables. GMT-08:00 RFC 822 time zone -0800 dd.mm corresponds to 09. Structure of Message Payload <root> <StatementName1> <dbTableName action=”ACTION> <table>realDbTableName</table> <access> <col1>val1</col1> <col2>val2</col2> . for example.12. update (UPDATE).yyyy corresponds to 10. PST. Depending on the content of the message payload.2005. You can also include results from queries (SELECT) in the response in XML format for synchronous messages. </root> Procedure 1. Define a mapping that converts the payload of an XI message to the required XML structure. . Each statement contains the description of a database action. You can use names of your choice for the tags for statements..... You can use a name of your choice for the tag. </StatementName2> . you can define one or more statements.. Under the <root>tag. Defining the XML Document Structure 2. Define a <root>tag.. you use a common structure for all statements.</access> <key1> <col2>val2old</col2> <col4>val4</col4> </key1> <key2> <col2>val2old2</col2> </key2> </dbTableName> </StatementName1> <StatementName2> . With the exception of the execution description for a stored procedure. 3. . UPDATE_INSERT. At the next level there is (except for in the DELETE action) an element with the name access and one or more elements with arbitrary names. Do not change the name of the tag. 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 specified value is used as the database table name. ○ Enter the access element first and define the table columns that are to be accessed. Do not change the name of the tag. Define one or more statements for editing database tables: ○ Defining an UPDATE Statement ○ Defining an INSERT Statement ○ Defining an UPDATE_INSERT Statement ○ Defining a DELETE Statement ○ Defining a SELECT Statement ○ Defining an EXECUTE Statement ○ Defining an SQL_QUERY Statement Defining Attributes in the <key>Elements 8. these elements are called keyN. The following values are permitted: • • • • • ■ EQ: Equal (default value) ■ NEQ: Not equal ■ LT: Less than ■ LTEQ: Less than or equal to ■ GT: Greater than . 6. 7. EXECUTE. SELECT. for example. If you use the optional <table> element. DELETE. to define table names that contain non-XML-compatible characters or characters that cannot be used in interface definitions in the Integration Builder. <table> must be the first element in the block within <dbTableName>. In the above example.4. Enter the name of the database table in the element under the statement element (dbTableName) and the attribute action with the value UPDATE. 5. or SQL-QUERY. This enables you. INSERT. ○ Use the <key> elements to describe the condition for accessing the table columns. • • ■ GTEQ: Greater than or equal to ■ LIKE: Likeness (of strings). • • ■ To always set quotation marks. set YES. set NO. . In the corresponding value. In a few cases (for example. Result 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 structure of the response documents is contained in the descriptions of the statements. you may have to override this. when using functions). This attribute has the same effect as if the respective value did not exist.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). 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. ■ To never set quotation marks round values with this attribute in the SQL syntax. ○ isNull= TRUE Values with this attribute are ignored during construction of the WHERE condition. the SQL placeholders “%” or “_” can then also be used.col2. The response is put in a separate element <StatementName_response> for each statement element. Defining XML Documents for Message Protocol Native SQL Format Use This protocol is primarily for test purposes. enter the following: „ INSERT INTO tableName (column-name1. ‘column-value2’. a text is expected that represents any valid SQL statement. Instead of an XML document format. The statement corresponds to an SQL UPDATE statement. ‘column-value3’) Defining an UPDATE Statement Use You use an UPDATE statement to change existing table values. to add a row to a table. column-name3) VALUES(‘columnvalue1’. Procedure For example. column-name2. Format of UPDATE Statement <StatementName> <dbTableName action=”UPDATE”> <table>realDbTableName</table> <access> <col1>val1</col1> <col2>val2new</col2> </access> <key1> <col2>val2old</col2> <col4>val4</col4> </key1> . If you want to ensure this does not happen.<key2> <col2>val2old2</col2> </key2> </dbTableName> </StatementName> Procedure 1. ○ If you have not formulated a condition in the <key> elements. enter the condition that can find the data records whose column values are to be changed. select Key Tags Mandatory in the adapter configuration. The response document contains the following element as well as the number of updated table rows. this means that no condition is specified and that the entire table is to be changed. but have selected Key Tags Mandatory. or if you define an empty <key> element. different <key> elements are combined with a logical OR. <update_count>count</update_count> Defining an INSERT Statement . this results in an error in message processing with a corresponding error output. col2=’val2new’ WHERE ((col2=’val2old’ AND col4=’val4’) OR (col2=’val2old2’)) “ The column type STRING is used for all columns. In the <key> element. The character “ may be missing in other column types. ○ Column values within a <key> element are combined with a logical AND. including 0. Enter exactly one <access> element. ○ If you do not define the <key>element. Result The corresponding SQL statement in the XML structure above is as follows: “ UPDATE dbTableName SET col1=’val1’. 2. ○ You can use any number of <key> elements to formulate your condition. Enter the new column values in the <access> element. The statement corresponds to an SQL INSERT statement. Enter the new column values in the <access> block. ‘val2’) INSERT INTO dbTableName (col1) VALUES(‘val11’) “ The response document contains the following element as well as the number of inserted table rows. Format of INSERT Statement <StatementName> <dbTableName action=”INSERT”> <table>realDbTableName</table> <access> <col1>val1</col1> <col2>val2</col2> </access> <access> <col1>val11</col1> </access> </dbTableName> </StatementName> Procedure 1. The statement must have at least one <access> element. Result The corresponding SQL statement in the XML structure above is as follows: “ INSERT INTO dbTableName (col1. col2) VALUES(‘val1’. Do not enter a <key> element. 2.Use You use an INSERT statement to add table values. . including 0. no condition is specified. but have selected Key Tags Mandatory. The entire table is deleted. If you have not formulated a condition in the <key> elements. Enter the condition under which the table values are to be deleted in one or more <key> elements.<insert_count>count</insert_count> Defining a DELETE Statement Use You use this statement to delete table values. different <key> elements are combined with a logical OR. Format of DELETE Statement <StatementName> <dbTableName action=”DELETE”> <key1> <col2>val2old</col2> <col4>val4</col4> </key1> <key2> <col2>val2old2</col2> </key2> </dbTableName> </StatementName3> Procedure 1. Result The corresponding SQL statement for the XML structure above is as follows: “ DELETE FROM dbTableName WHERE ((col2=’val2old’ AND col4=’val4’) OR (col2=’val2old2’)) . select Key Tags Mandatory in the adapter configuration. or if you enter an empty <key> element. If you do not enter a <key> element. 2. this results in an error in message processing with a corresponding error output. If you want to ensure this does not happen. Column values within a <key> element are combined with a logical AND. Enter the new column values in the <access> element.“ The response document contains one element: <delete_count>count</delete_count> Defining an UPDATE_INSERT Statement Use You use an UPDATE_INSERT statement to change and add table values. Format of UPDATE_INSERT Statement <StatementName> <dbTableName action=”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> </StatementName> Procedure 1. . this means that no condition is specified and that the entire table is to be changed. If no change can be made to the database table in this action (the formulated condition does not apply to any table entry). If you want to ensure this does not happen. ○ Column values within a <key> element are combined with a logical AND. 2. ○ If you do not define the <key>element. the values described in the <access> element are added to the table in accordance with the description for the INSERT statement. In the <key> element. enter the condition that can find the data records whose column values are to be changed. <key> elements are ignored in this case. ○ You can use any number of <key> elements to formulate your condition. If you have not formulated a condition in the <key> elements. or if you define an empty <key> element. The statement corresponds to an SQL SELECT statement. different <key> elements are combined with a logical OR. select Key Tags Mandatory in the adapter configuration. See: Defining an INSERT Statement Result The response document has the following format. but have selected Key Tags Mandatory. where 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> Defining a SELECT Statement Use You use this statement to select table values. this results in an error in message processing with a corresponding error output. Format of SELECT Statement <StatementName> <dbTableName action=”SELECT”> <table>realDbTableName</table> <access> <col1/> .Enter exactly one <access>element. col3 FROM dbTableName WHERE ((col2=’val2old’ AND col4=’val4’) OR (col2=’val2old2’)) . but have selected Key Tags Mandatory. Enter the column names to be selected in the <access> block. If you want to ensure this does not happen. Result The corresponding SQL statement for the XML structure above is as follows: “ SELECT col1.col2. or if you define an empty <key> element.<col2/> <col3/> </access> <key1> <col2>val2old</col2> <col4>val4</col4> </key1> <key2> <col2>val2old2</col2> </key2> </dbTableName> </StatementName> Procedure 1. this means that no condition is specified and that the entire table is to be selected. Column values within a <key> element are combined with a logical AND. ○ If you have not formulated a condition in the <key>elements. enter the condition that can find the data records whose column values are to be selected. You can define any number of <key> elements. different <key> elements are combined with a logical OR. this results in an error in message processing with a corresponding error output. In a <key> element. 2. ○ If you do not define the <key>element. select Key Tags Mandatory in the adapter configuration. A statement with the action SELECT must have exactly one <access> element. ... <row> <column1>valueN1</column1> <column2>valueN2</column2> ... Format of EXECUTE Statement <StatementName> <storedProcedureName action=” EXECUTE”> <table>realStoredProcedureeName</table> <param1 [isInput=”true”] [isOutput=true] type=SQLDatatype>val1</param1> </storedProcedureName > </StatementName> Procedure ..“ The response document contains the result of the action in XML format as follows: “ <row> <column1>value11</column1> <column2>value12</column2> . </row> . </row> “ Defining an EXECUTE Statement Use You use this statement to execute a stored procedure. CHAR. FLOAT. Result All return values are returned in an XML structure. TIME. BLOB (input and output). Format of SQL_QUERY Statement .1. for example. BINARY. 3. REAL. Specify the parameters for the stored procedure. ○ You must specify the attribute type=<SQLDatatype> for all parameter types (IN. The return parameters of a stored procedure are appended in a separate structure. ○ Enter <table> as the first element of the block within <dbTableName>. LONGVARCHAR. CLOB (input and output). It describes the valid SQL data type. This enables you to generate complex. VARCHAR. BIGINT. TINYINT. This depends on the SQL statements executed within the stored procedure. DATE. LONGVARBINARY. parameterisable SQL statements. SMALLINT. ○ You have the option of specifying the attribute isInput=“1“(input parameter) or isOutput=“1“ (output parameter) for the parameters. NUMERIC. which can be listed in the keyblock. the value specified here is used as the stored procedure name. The following SQL data types are supported: INTEGER. If both attributes are missing. VARBINARY. 2. DOUBLE. Defining an SQL_QUERY Statement Use You use this statement to transfer complex SQL statements directly to the database by using the adapter. Enter the name of the stored procedure in the database before the action. You have the option of using placeholders in these SQL statements. STRING. to define stored procedure names that contain non-XML-compatible characters or characters that stop them from being used in interface definitions in the Integration Builder. BIT. DECIMAL. the element is interpreted as an input parameter. If you use the optional <table> element. This enables you. TIMESTAMP. only in connection with the Oracle JDBC driver) The binary data for BLOB is hexadecimal encoded. The results within the stored procedure are returned either as a table or as the element <update_count>. OUT). CURSOR (output. The parameter names must be identical to those of the stored procedure definition. . list them within the <key> element. The content of <access> represents a valid SQL call for the respective mode. as this will cause runtime errors. DELETE). Unlike in the usual statement types. You can set any parts of the SQL statement in this way. you can omit the <key> element or set it as empty. with the option of placeholders. the strings $placeholder1$ and $placeholder2$ contained in the SQL string are replaced with value1 or value2 before the SQL statement is executed.<root> <StatementName> <anyName action=” SQL_QUERY” | “SQL_DML”> <access>SQL-String with optional placeholder(s)</access> <key> <placeholder1>value1</placeholder1> <placeholder2>value2<placeholder2> </key> </anyName > </StatementName> </root> Procedure 1. Using placeholders is not restricted to individual field values. The names of the placeholder elements must be identical to those used in the SQL string (where they still have the $ character). In both cases. no table name or stored procedure name is expected in the default setting. If it represents a call from the SQL Data Manipulation Language (UPDATE. 3. INSERT. ○ If you do not use placeholders. 2. choose action=SQL_QUERY. ○ If you use placeholders. you must not select the Key Tags Mandatory field in the configuration. If the SQL statement represents a query to the database (SELECT). Specify a name of your choice for the structure. ○ In the XML structure above. choose action=SQL_DML. You must enter the <access> element first. You can also influence the logic of the statement. Address=’$ADDRESS$' WHERE CustomerID='$KEYFIELD$’ </access> <key> <NAME>Firma</NAME> <ADDRESS>Strasse 3 </ADDRESS> <KEYFIELD>FI</KEYFIELD> . Address='Strasse 3' WHERE CustomerID='FI' The following example contains placeholders: <root> <stmt> <Customers action="SQL_DML"> <access> UPDATE Customers SET CompanyName=’$NAME$’. Address='Strasse 3' WHERE CustomerID='FI' </access> </Customers> </stmt> </root> The unchanged SQL statement is executed in the database: UPDATE Customers SET CompanyName='Firma'. Undefined placeholders are left unchanged in the SQL string. Result The following example does not contain any placeholders: <root> <stmt> <Customers action="SQL_DML"> <access> UPDATE Customers SET CompanyName='Firma'.Surplus and undefined placeholders are tolerated in the <key> element. This can lead to syntax errors or to unexpected results in the database. the same SQL statement is executed in the database as above: UPDATE Customers SET CompanyName='Firma'. Depending on the content of the message. or delete (DELETE) the data. you can either insert (INSERT).</key> </Customers> </stmt> </root> After the placeholders have been replaced. Results from queries (SELECT) can also be included in the response in XML format for synchronous messages. update (UPDATE). 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> . Address='Strasse 3' WHERE CustomerID='FI' Document Formats for the Receiver JDBC Adapter XML Document Format for the Message Protocol XML SQL Format You can modify one or more database tables by means of a message. </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”> . UPDATE. select Key Tags Mandatory in the adapter configuration. ○ 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. <table> must be the first element in the block within <dbTableName>. Each of these statements contains the description of a database action. this can lead to the entire table being updated or deleted respectively. access proceeds without any conditions. the value specified is used as a database table name. ○ 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 access element contains the table columns which are to be accessed. DELETE. This enables you. With the exception of the execute description for a stored procedure (shown in the example under the element <StatementName5>). 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. to define table names containing non-XML-compatible characters or characters that prevent them from being used in interface definitions in the Integration Builder. 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. . Therefore. If you want to ensure this does not happen. Column values within a <key> element are combined with a logical AND.<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>. If no such elements are specified. for example. The key elements describe a condition for access. If specified. The name of the <key> element is arbitrary. In the above example. The response is put in a separate element <StatementName_response> for each statement element. the statement corresponds to an SQL UPDATE statement. Within this tag there are one or more statement elements that also have arbitrary names. different <key> elements are combined with a logical OR. or SELECT. If you use the optional <table> element. ● action=UPDATE Statements with this action cause existing table values to be updated. these elements are called keyN. It must be specified as the first element. UPDATE_INSERT. In the case of UPDATE and DELETE. col2) VALUES(‘val1’. ● action=INSERT Statements with this action cause table values to be inserted. 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 number of <key> elements with arbitrary names is not restricted. The <access> block contains the new column values. including 0. the same action is executed as for UPDATE. The character “ may be missing in other column types. including 0. The corresponding SQL statement for StatementName2 in the example above is as follows: “ INSERT INTO dbTableName (col1. The corresponding SQL statement for StatementName1 in the example above is as follows: “ UPDATE dbTableName SET col1=’val1’. the column type String is used for all columns. The response document contains the following element as well as the number of updated table lines. If no update to the database table can be made for this action (the condition does not apply to any . Therefore. Initially. the statement corresponds to an SQL INSERT statement. then no condition is specified and the entire table is to be updated. A statement with the action INSERT must have at least one <access> element. col2=’val2new’ WHERE ((col2=’val2old’ AND col4=’val4’) OR (col2=’val2old2’)) “ As in the other examples. ‘val2’) INSERT INTO dbTableName (col1) VALUES(‘val11’) “ The response document contains the following element as well as the number of inserted table lines. <update_count>count</update_count> If there is no <key> element.A statement with the action UPDATE must have exactly one <access> element. or if there is a <key> element but it is empty. It cannot have a <key> element. <insert_count>count</insert_count> ● action=UPDATE_INSERT The statement has the same format as for the UPDATE action. The names of <key> elements are arbitrary. 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.table entry). the statement corresponds to an SQL SELECT statement. 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. ● action=SELECT Statements with this action cause existing table values to be selected. The response document has the following format. different <key> elements are combined with a logical OR. values of the table described in the <access> element are inserted in accordance with the description of the action INSERT. or if there is a <key> element but it is empty. The corresponding SQL statement for StatementName4 in the example above is as follows: “ SELECT col1. Therefore. <key> elements are ignored in this case. then no condition is specified and the entire table is to be deleted. Column values within a <key> element are combined with a logical AND. A statement with the action SELECT must have exactly one <access> element. a <key> element contains the columns whose values must be identical with the specified value to get the new column values.col3 FROM dbTableName WHERE ((col2=’val2old’ AND col4=’val4’) OR (col2=’val2old2’)) “ . One or more <key> elements formulate the condition for which table values are deleted. The name of the <key> element is arbitrary. The number of <key> elements with arbitrary names is not restricted.col2. different <key> elements are combined with a logical OR. 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. The <access> block contains the column names to be selected. Column values within a <key> element are combined with a logical AND. STRING. NUMERIC. for example. </row> . DOUBLE. the element is interpreted as an input parameter. REAL..If there is no <key> element. <table> must be the first element in the block within <dbTableName>. TIME. SMALLINT. LONGVARBINARY. BINARY. DATE. . They can optionally have the attribute isInput=“1“ (input parameter) or isOutput=“1“ (output parameter) or both (INOUT parameter). The following SQL data types are supported: INTEGER. If specified. VARCHAR. LONGVARCHAR. which describes the valid SQL data type. FLOAT. only in conjunction with the Oracle JDBC driver). The response document contains the result of the action in XML format as follows: “ <row> <column1>value11</column1> <column2>value12</column2> . INOUT). OUT. is mandatory for all parameter types (IN. 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 value specified here is used as the stored procedure name... CHAR. CURSOR (output.CLOB (input and output). The parameter names must be identical to those of the stored procedure definition. BIGINT.. VARBINARY. This enables you. or if there is a <key> element but it is empty. The name of the element is interpreted as the name of the stored procedure in the database. 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. DECIMAL. <row> <column1>valueN1</column1> <column2>valueN2</column2> .. BLOB (input and output). TIMESTAMP. TINYINT. then no condition is specified and the entire table is to be selected. </row> “ ● action=EXECUTE Statements with this action result in a stored procedure being executed.. If you use the optional <table> element. The attribute type=<SQL-Datatype> . If both attributes are missing. BIT. The elements within the stored procedure are interpreted as parameters. you must not select the Key Tags Mandatory field in the configuration. choose Action=SQL_DML. ○ If it represents a call from the SQL Data Manipulation Language (UPDATE. ● action= SQL_QUERY | SQL_DML This structure enables you to transfer more complex SQL statements to the database directly using the adapter. the strings $placeholder1$ and $placeholder2$ contained in the SQL string are replaced with value1 or value2 before the SQL statement is executed. as this will cause runtime errors. parameterisable SQL statements. This depends on the SQL statements executed within the stored procedure. Unlike in the usual statement types. The names of the placeholder elements must be identical to those used in the SQL string (where they still have the $ character). ○ If you use placeholders. In the above example <StatementName6>. no table name or stored procedure name is expected in the default setting. these must be listed in the element with the name <key>.The binary data for BLOB is hexadecimal encoded. Address='Street 3' WHERE CustomerID='CO' </access> </Customers> </stmt> </root> The unchanged SQL statement is executed in the database: . This makes it easy to generate complex. In both cases. DELETE). All return values are returned in an XML structure. The return parameters of a stored procedure are attached in a separate structure. Example (Without Placeholders): <root> <stmt> <Customers action="SQL_DML"> <access> UPDATE Customers SET CompanyName='Company'. INSERT. which can be listed in the subsequent keyblock. You have the option of using placeholders in these SQL statements. ○ The first element in the structure must have the name <access> and contain a valid SQL call for the respective mode. ○ If you are not using placeholders. Details on the structure: ○ The name of the structure is arbitrary. optionally with placeholders (see below). ○ If the SQL statement represents a query to the database (SELECT). The results within the stored procedure are returned either as a table or as the element <update_count>. then the <key>block can be omitted or left empty. choose Action=SQL_QUERY. 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. 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. as in this example. the same SQL statement is executed in the database as above: UPDATE Customers SET CompanyName='Company'. You can also influence the logic of the statement. ○ Surplus and undefined placeholders are tolerated in the <key> section. Address='Street 3' WHERE CustomerID='CO' Example (with Placeholders): <root> <stmt> <Customers action="SQL_DML"> <access> UPDATE Customers SET CompanyName=’$NAME$’. Undefined placeholders are left unchanged in the SQL string. This can lead to syntax errors or to unexpected results in the database. You can set any parts of the SQL statement in this way.UPDATE Customers SET CompanyName='Company'. Address='Street 3' WHERE CustomerID='CO' Comments: ○ Using placeholders is not restricted to individual field values. The following values are permitted: Values for compareOperation . the table column type determines whether the default is to set the values in quotation marks (text column types) or not (numerical column types).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. In the corresponding value. the SQL placeholders “%” or “_” can then also be used.col2. quotation marks are never set. ● 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 attribute enables you to do this. Instead of an XML document format. it may be necessary to override this. If YES. In a few cases (for example. when using functions). a text is expected that represents any valid SQL statement. If NO. This is often difficult to represent in mapping programs. 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. Only use this attribute in individual cases.Attribute EQ NEQ LT LTEQ GT GTEQ LIKE Value and Check Equals (default value) Does not equal Less than Less than or equal to Greater than Greater than or equal to Like (strings). quotation marks are always set round the values for which this attribute is set in the SQL syntax. . XML Document Format for the Message Protocol Native SQL Format This protocol is primarily for test purposes only. Name. To be able to use JDBC adapter with a particular database. After reading a record. We will configure this using a JDBC sender adapter. To write data to database. you must install the corresponding JDBC drivers on your SAP PI server. The driver files can be obtained from the database vendor. DELETE. this data should get updated back in the Oracle database. However. The table has 4 fields corresponding to Employee Number. Now let us configure a simple scenario to understand JDBC adapters. ‘column-value3’) “ How to Configure JDBC Adapter in SAP PI This article will help you understand how to configure the sender and receiver JDBC adapter in SAP PI/XI. Point to note is that a message is always processed in exactly one database transaction. Database content can be read with any SQL statement. ‘column-value2’. this should be used only for test purposes. Configuring the Sender JDBC Adapter The figure below shows a typical configuration for JDBC sender adapter. SELECT. Let’s say we have a simple table which stores employee details in an Oracle Database. These parameters differ depending on the database you are using. Our aim is to read new entries from this table using SAP PI. You can execute SQL INSERT. column-name2.When inserting a line into a table the corresponding document looks as follows: „ INSERT INTO tableName (column-name1. the status field should be set appropriately so that the record is not picked up again. Sender JDBC Adapter is used to read data from databases while the receiver JDBC adapter writes data from SAP PI to the relevant databases. Department and a status field to indicate new entries. The correct parameters for your database can be found out from the database vendor. UPDATE. The JDBC adapter is used to connect to different database systems via SAP PI. JDBC Driver and Connection parameters shown correspond to Oracle database. column-name3) VALUES(‘column-value1’. or stored procedure statements. Direct arbitrary SQL statement can as well be used. We will accomplish this using JDBC receiver adapter. a predefined XML format needs to be used. Now whenever an employee is transferred from one department to other. The adapter converts database content to XML messages and vice versa. . We will discuss a simple example wherein we will use sender and receiver JDBC adapter to talk to an Oracle Database. DEPT as Emp_Dept from EMP_TAB where E_NEW = 'Y' order by E_NO Corresponding to your SQL SELECT statement’s output. e. Note that <row> is in all lowercase.. Query SQL Statement should contain the actual SQL SELECT statement using which you want to query the database..g in our case – SELECT E_NO as Emp_Number. . E_NAME as Emp_Name. <MessageType> <row> ...Poll Interval specifies how often JDBC adapter polls the database. In our case it would look something like below: The standard format shown must be followed. you need to create a data type and a message type in IR/ESR. The JDBC adapter always returns records in the XML format like one shown below. . .g... . in our case UPDATE EMP_TAB SET E_NEW = 'N' WHERE E_NEW = 'Y' For test purposes. you might want to read the same data again and again.. .</row> <row> . Update SQL Statement should contain the UPDATE statement so that previously read data is not read again when the adapter polls the database next time.. e. put the string <test> (including the angle brackets) in the Update SQL Statement field.. </MessageType> Document Name in the adapter configuration specifies the Message Type. Following figure shows required configuration for JDBC receiver adapter... </row> .. Configuring the Receiver JDBC Adapter Now we want to update the database from PI. In this case. Now simply create a new employee record in the Oracle database with E_NEW = ‘Y’ and the sender JDBC adapter will pick it up when it polls the database.When you use XML SQL Format as Message Protocol. the data type and message type created for sending the data through JDBC adapter must follow a standard XML format shown below. the above format is not necessary. The Native SQL String protocol should be used only for test purposes however. UPDATE. . you might need to configure rest of the scenario or you can send an XML message in the above format from the Test Message tab of Runtime workbench. The access node should be used to specify the fields that wish to insert/update in the database while the key node specifies the where criteria of SQL statement. In that case. To test the receiver JDBC adapter. When you use Native SQL String as Message Protocol. INSERT. When sending the message. DELETE etc. the action attribute should contain the type of SQL statement e.g. The table field should contain the actual database table name. the JDBC adapter expects the actual SQL formatted string as the payload.