Configuring the Sender JDBC Adapter (SAP Library - Configuring the JDBC Adapter in the Integration Directory/PCK)

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.

More information: Sender Agreement in Integration Server/ PCK, under Obligatory Sender Agreements

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

Example

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 Processingtab 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 Statementmust 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 Linethat 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)

Caution

Only reduce the security level when necessary and as far as necessary. You must find another way to make sure that data inconsistencies cannot be generated in the database, usually by preventing parallel access.

22. If the JDBC driver does not support any transactions, deactivate the logical unit of work. To do this, choose Database Auto-Commit-Enabled (No Transaction Handling). The logical unit of work ensures data consistency. If the JDBC driver does not support any transactions, you must find another way to ensure the consistency of the data in the database, usually by preventing parallel access.

Caution

Do not set this indicator if the JDBC driver supports transactions.

23. If you want the database connection to be released and reestablished before each Poll Interval, select Disconnect from Database After Processing Each Message.

24. If you want to remove the empty tags from the resultset of the sender adapter, select Remove Empty Tags. This helps to reduce the size of XML documents.

Example

If this indicator is not set, the resultset looks as follows:

<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, the resultset looks as follows:

<row>

<column-name1>column-value</ column-name1>

<column-name3>column-value</ column-name3>

</row>

<row>

<column-name1>column-value</ column-name1>

</row>

</resultset>

25. Specify additional Parameter Names and Parameter Values in the table.

Note

Additional parameters are published in SAP Note 801367.

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. The document looks like this:

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