Configuring the Receiver JDBC
Adapter
Use
You need to configure the receiver JDBC adapter to convert XML messages from the Integration Server or the PCK into database table content.
Integration
You configure the
adapter on the Parameters tab page during the definition of a communication
channel in the
Integration Server
or in the PCK. In addition
to the adapter configuration, you can use the Module tab page to specify generic modules in the module
processor, which give the adapter additional functions.
Prerequisites
...
1. You have created a new communication channel or have opened an existing one.
2. On the Parameters tab page you have selected the adapter type JDBC.
3. You have selected the Receiver radio button to define the adapter as a receiver adapter.
Activities
To configure the adapter, specify the following:
Transport Protocol
JBDC 2.0
Message Protocol
● XML SQL Format
Table values can be inserted, updated, or deleted in one or more tables. In the database, stored procedures can be called using transfer parameters. In the case of synchronous queries, results from database queries or return values of stored procedures can also be transferred.
● Native SQL String
An arbitrary SQL statement is expected as the message content. This statement is transferred unaltered to the database for processing.
Depending on the message protocol you selected, the adapter expects special XML document formats in the message from the Integration Server or the PCK. For more information about the document formats, see Document Formats for the Receiver JDBC Adapter.
Adapter Engine
Select the Adapter Engine on the Integration Server, or select a non-centrally installed Adapter Engine. This selection is not available in the PCK.
Connection Tab Page
Database Connection
Field |
Entry |
JDBC Driver |
Java class of the JDBC driver that the JDBC adapter must load to be able to access the driver. The specification varies according to the JDBC driver; see material from the respective provider for exact details. |
Connection |
Address with which you can establish a database connection using the JDBC driver. The specification varies according to the JDBC driver; see material from the respective provider for exact details. |
User Name |
User name for logging on to the database. |
Password |
Password for logging on to the database, including confirmation. |
Processing Tab Page
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.
XML Schema Interpreter
Field |
Entry |
Key Tags Mandatory |
Set the indicator to specify that there must be conditions in the key tag. |
Interpretation of Empty String Values |
Specify how empty text fields are to be handled. ● 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. |
Exactly-Once Handling
Persistence |
Explanation and Further Entries |
Local |
As in the other adapters, Exactly Once messages are handled by default using status information in the J2EE server. All adapter error statuses and program terminations initialized externally are also handled in this mode. However, this does not include external program terminations during a database commit. In this case, the status of message processing is unclear since it can only be changed once the database commit has been completed. However, this kind of situation is identified when the application is started and editing of the message terminated during processing can be controlled using Conflict Resolution. This specification is only effective for handling errors that occur when a message is being processed for a second time after initial processing remained in the unclear status described above.
Conflict Resolution ● Error If an error occurs when processing again, this is reported to the caller system when Error is selected. ● Redo However, if the error occurs because the message was saved in the database when it was first processed and it is still located there, processing can be completed for the sender system using the value Redo (note that the database interface normally triggers the error duplicate insert if at least one of the table fields was defined as a primary key). Otherwise the sender system will continue to send the message and the error will continue to occur. |
Database |
Nevertheless, there is still a gap in Exactly Once processing: if there is no primary key field in the database table or if the data is already being processed by another application and it is then deleted, when the first attempt at message processing is interrupted by an irregular termination of the J2EE server 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 Specify the name of the table. ● Key Column Name Enter the key in this table column. ● Value Column Name Enter the key value in this table column. |
SQL Syntax Parameters
● Escape Symbol for Apostrophe
The apostrophe character (‘) is a reserved character in SQL syntax and is therefore replaced by an escape character if it occurs within value strings. This replacement character 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) concerning the SQL syntax that is generated by the database.
● Column Name Delimiter
Depending on the database being used, column names can be enclosed by a special delimiter character, for example, if the names can contain special characters (such as “). This character can be specified at this point. The default setting is no delimiter character. If a character occurs that is invalid for the database being used, the adapter triggers an error message (an SQL exception) concerning the SQL syntax that is generated by the database.
Run Operating System Command
● Command Line
Specify an operating system command that is to be executed following successful database operations.
● Timeout (secs.)
This specifies 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.
● Terminate Program After Timeout
Set this indicator if the adapter is to terminate the executing program in the event of a 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.
Advanced Tab Page
Adapter Status
Set the adapter to Active to enable messages to be exchanged.
Advanced Mode
To specify additional parameters, set the Advanced Mode indicator.
Number of Retries of Database Transaction on SQL Error
Specify how often the system should attempt to reestablish the database connection and access the database following an SQL exception.
If the number of retries is exceeded, the last status is reported to the sender Integration Server or the sender PCK. This means that if an error occurs, the message will only be processed again when the Integration Server/PCK sends it again.
Transaction Isolation Level
There are different levels of database transactions known as isolation levels. The isolation level determines how transactions running in parallel can influence each other. 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)
You must only lower the isolation level where necessary and as far as necessary. To avoid data inconsistencies in the database when the isolation level is lowered, ensure that multiple database transactions cannot access the database simultaneously.
Database Auto-Commit-Enabled (No Transaction Handling)
Set the indicator if you want to deactivate the logical unit of work, which the JDBC adapter requires to guarantee that the data in the database is consistent.
This option is required for JDBC drivers that do not support transactions. To avoid data inconsistencies in the database when the isolation level is lowered, ensure that multiple database transactions cannot access the database simultaneously.
Do not set this indicator if the JDBC driver supports transactions.
Disconnect from Database After Processing Each Message
Set this indicator if the database connection is to be released and reestablished before every poll interval.
Batch Mode
Set the indicator if you want to collect SQL statements in a batch. This can improve performance considerably.
Some available JDBC drivers may not be able to use this feature to improve performance.
Batch processing is not supported for the following statements:
● SELECT
● UPDATE_INSERT
● EXECUTE
● SQL_QUERY
Table
Enter additional parameter names and parameter values in the table.
Additional parameters are published in SAP Note 801367.
Date/Time Format for Stored Procedure Calls
Enter date and 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 |
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.