Configuring the Receiver JDBC Adapter

Use

You configure the receiver JDBC adapter to convert XML messages from the Integration Server or the PCK into database table content.

Procedure

The Transport Protocol is JBDC 2.0 .
Select the Message Protocol .
- If you want to insert, change, or delete table values in one or more tables, select XML SQL Format .
  
  You can call stored procedures in the database by using transfer parameters. In the case of synchronous queries, results from database queries or return values of stored procedures can also be transferred.
- If you want to specify a SQL statement of your choice in the message payload, to be transferred unchanged to the database for processing, select Native SQL String .
Depending on the message protocol, the adapter expects special XML document formats in the payload of the XI message.

More information: Defining XML Documents for Message Protocol XML SQL Format

Defining XML Documents for Message Protocol Native SQL Format
Choose the Adapter Engine on the Integration Server, or choose a noncentrally installed Adapter Engine.

This selection is not available in the PCK.

1. Defining the Database Connection

Select the Connection tab page.
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.
Under Connection , specify the address with which you can open a database connection using the JDBC driver.
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 can find this information in the documentation from the respective provider.

2. Defining Processing Parameters

Select the Processing tab page.
Under Maximum Concurrency , enter the number of messages to be processed in parallel by the receiver channel.

For example, if you enter the value 2 , then two messages are processed in parallel. Default value is 1 and this means only one message can be processed at a time by the receiver channel.

3. Defining XML Schema Interpreter for Message Protocol XML SQL Format

To make conditions mandatory in the key tag of the XML document, select Key Tags Mandatory .
Select how empty text fields are to be handled under Interpretation of Empty String Values .
- NULL Value
  
  In the case of INSERT and UPDATE statements, empty fields are handled like NULL fields (do not exist) and are not inserted in the database.
- Empty String
  
  In the case of INSERT and UPDATE statements, empty texts are inserted in the columns.

4. Defining Exactly-Once Handling

Specify where Persistence for Exactly Once is to take place.
- Local
  
  Messages of type Exactly Once are handled by status information management for these messages in AS Java. All error statuses of the adapter, even program terminations initialized externally, are handled correctly.
  
  If an external program termination occurs during a database commit, the status of message processing is unclear initially, because the status of the message is not changed until the database commit is complete. This situation is identified when the application is restarted.
  
  Specify how terminated message processing is to be handled under Conflict Resolution .
  
  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.
  - Error
    
    If an error occurs when processing again, this is reported as an error to the caller system.
  - Redo
    
    This setting enables editing for the receiver adapter to be completed successfully if the error occurs, because the message was saved in the database when it was first processed and it is still located there. The database interface then triggers the error duplicate insert if at least one table field is defined as a primary key. Without this setting, the adapter continues to resend the message and the error continues to occur.
- Database
  
  If no field is designated as a primary key in the database table, or if the data has already been processed by another application and then deleted, when the first attempt at message processing is interrupted by an irregular termination of AS Java immediately after the database commit, a message can be duplicated.
  
  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.
  
  In the database where the write-to tables are located you must create an additional table with two columns for this purpose. To define the table, specify the following:
  - Database Table Name
    
    Enter the name of the table.
  - Key Column Name
    
    Enter the name of the column in which the key is entered.
  - Value Column Name
    
    Enter the name of the column in which the key value is entered.

5. Defining SQL Syntax Parameters

Enter the Escape Symbol for Apostrophe .

The apostrophe character (') is a reserved character in SQL syntax and is replaced by an escape character if it occurs within value strings. These replacement characters can be database-specific. Typical replacement characters are \ or '' (default value). If a character occurs that is invalid for the database being used, the adapter triggers an error message (an SQL exception) for the SQL syntax that is generated by the database.
Enter the Column Name Delimiter .

Depending on the database being used, column names can be enclosed by a special delimiter character, for example, if names can contain special characters (such as ").

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

6. Defining an Operating System Command

Specify an operating system Command Line that is to be executed following successful database operations.
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.
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.

7. Defining the Adapter Status

Select the Advanced tab page.
Set the adapter to Active to enable messages to be exchanged.

8. Defining Parameters in Advanced Mode

To specify additional parameters, select Advanced Mode .
Under Number of Retries of Database Transaction on SQL Error , specify how often the system is to attempt to reestablish the database connection and access the database in the event of an SQL exception.

If the number of retries is exceeded, the last status is reported to the sender Integration Server or the sender PCK. If an error occurs, the message is only processed again when the Integration Server/PCK sends it again.
To define how transactions running in parallel are able to influence each other, choose Transaction Isolation Level .

There are different levels of database transactions 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 isolation level when necessary and as far as necessary. Find another way to make sure that data inconsistencies cannot be generated in the database, usually by preventing parallel access.
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.
If you want the database connection to be released and reestablished before each Poll Interval , select Disconnect from Database After Processing Each Message .
If you want to collect SQL statements in a batch, select Batch Mode .

This can improve performance considerably. It may be the case that not every available JDBC driver is able to run batch processing.

Batch processing is not supported for the following statements:
- SELECT
- UPDATE_INSERT
- EXECUTE
- SQL_QUERY
Specify additional Parameter Names and Parameter Values in the table.

Note
Additional parameters are published in SAP Note 801367 .

Specify the Date/Time Formats for Stored-Procedure Calls .

The string format corresponds to the Java class java.text.SimpleDateFormat .

Letter	Date or Time Component	Type	Examples
G	Epoch name	Text	G = AD
y	Year	Year	yyyy = 1996; yy = 96
M	Month in year	Month	MMMM = July, MMM = Jul, MM = 07
w	Week in year	Number	27
W	Week in month	Number	2
D	Day in year	Number	186
d	Day in month	Number	10
F	Day of week in month	Number	2
E	Day in week	Text	Tuesday; Tue
a	am/pm marker	Text	PM
H	Hour in day (0-23)	Number	0
k	Hour in day (1-24)	Number	24
K	Hour in am/pm (0-11)	Number	0
h	Hour in am/pm (1-12)	Number	12
m	Minute in hour	Number	30
s	Second in minute	Number	55
S	Millisecond	Number	978
z	Time zone	General time zone	Pacific Standard Time; PST; GMT-08:00
Z	Time zone	RFC 822 time zone	-0800

Example

dd.MM.yyyy corresponds to 10.07.2005, for example.

hh.mm corresponds to 09.12, for example.

K:mm a corresponds to 0:30 PM, for example.