Configuring the JDBC Adapter Use 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 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 ind icator 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 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 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, Password, enter the logon data for the database to be read.
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 ind icator 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 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 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, 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. 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 Statement to remain in the database unchanged after being sent successfully, success fully, enter . 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 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 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)
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.
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.
If this indicator is not set, the resultset looks as follows: column-value column-value column-value
If this indicator is set, the resultset looks as follows: column-value column-value column-value 25. Specify additional Parameter Names and Parameter Values in the table. Due to messages of large size, it is possible that there could be out of memory errors, which could lead to JEE server node failures. 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. When this parameter is set to true, JDBC adapter does not process the message of size higher than the values specified through maxMsgSize and maxRowSize. If msgLimit is set to true then, maxMsgSize and maxRowSize parameters are mandatory. ● maxMsgSize : This parameter is used to limit the message size to an optimal value. JDBC adapter does not process the message size of more than the value provided for maxMsgSize, if encountered at runtime. The value of the parameter should be provided in KB. More information on configuring the maximum message size: SAP note 1253826. ● maxRowSize : This parameter is used to provide the maximum row size. This parameter is used to calculate the maximum number of rows that could be sent through channel in one interval. The value of the parameter should be provided in KB. More information on configuring the maximum row size: SAP note 1253826. ● 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. This reduces the load on the system. The parameter value should be provided in seconds.
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: column-value column-value column-value column-value column-value column-value
Quality of Service The sender of a message uses the attribute Quality of Service (QoS) to determine how a message is delivered. The following types of quality of service are supported: •
BE (Best Effort) The message is sent synchronously. The sender waits for a response before it continues processing.
•
EO (Exactly Once) The message is sent asynchronously. The sender does not wait for a response. The Advanced Adapter Engine guarantees that the message is sent and processed exactly once.
•
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. Message processing is asynchronous in this case.
In the case of quality of service BE an error occurs if more than one receiver is determined for a message. In the case of delivery types EO and EOIO, the message is copied correspondingly and sent to the individual receivers.
Configuring the Receiver JDBC Adapter
You configure the receiver JDBC adapter to convert XML messages from the Integration Server or the PCK into database table content. Procedure
1. The Transport Protocol is JBDC 2.0. 2. Select the Message Protocol . o
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.
o
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 3. 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
1. Select the Connection tab page. 2. 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. 3. Under Connection, specify the address with which you can open a database connection using the JDBC driver. 4. 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
1. Select the Processing tab page. 2. 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
1. To make conditions mandatory in the key tag of the XML document, select Key Tags Mandatory. 2. Select how empty text fields are to be handled under Interpretation of Empty String Values. o
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. o
Empty String
In the case of INSERT and UPDATE statements, empty texts are inserted in the columns. 4. Defining Exactly-Once Handling
1. Specify where Persistence for Exactly Once is to take place. o
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. o
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
1. 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. 2. 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
1. Specify an operating system Command Line that is to be executed following successful database operations. 2. 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. 3. If the adapter is to terminate the executing program in the event of a timeout, selectTerminate 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
1. Select the Advanced tab page.
2. Set the adapter to Active to enable messages to be exchanged. 8. Defining Parameters in Advanced Mode
1. To specify additional parameters, select Advanced Mode. 2. 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. 3. To define how transactions running in parallel are able to influence each other, chooseTransaction Isolation Level . There are different levels of database transactions known as isolation levels. The options correspond to the JDBC constants: o o
Default (default setting of the respective database) None
o
read_uncommitted (weakest setting)
o
read_committed
o
repeatable_read
o
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. 4. 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. 5. If you want the database connection to be released and reestablished before each Poll Interval , select Disconnect from Database After Processing Each Message. 6. 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: o o
SELECT UPDATE_INSERT
o
EXECUTE
o
SQL_QUERY
7. Specify additional Parameter Names and Parameter Values in the table. Note Additional parameters are published in SAP Note 801367. 8. 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.
Defining XML Documents for Message Protocol XML SQL Format Use You can modify one or more database tables by means of an XI message. Depending on the content of the message payload, you can insert (INSERT), update (UPDATE), or delete (DELETE) data in database tables. You can also include results from queries (SELECT) in the response in XML format for synchronous messages. You use EXECUTE to execute stored procedures and SQL QUERY to forward more complex SQL statements to the database by using the adapter. Structure of Message Payload
realDbTableName
val1val2val2oldval4val2old2 ... ...
...
Procedure 1. Define a mapping that converts the payload of an XI message to the required XML structure. Defining the XML Document Structure
2. Define a tag. You can use a name of your choice for the tag. 3. Under the tag, you can define one or more statements. You can use names of your choice for the tags for statements. Each statement contains the description of a database action. With the exception of the execution description for a stored procedure, you use a common structure for all statements.
4. Enter the name of the database table in the element under the statement element (dbTableName) and the attribute action with the value UPDATE, INSERT, UPDATE_INSERT, DELETE, SELECT, EXECUTE, or SQL-QUERY. 5. If you use the optional
element, the specified value is used as the database table name. Do not change the name of the tag.
must be the first element in the block within . This enables you, for example, to define table names that contain non-XML-compatible characters or characters that cannot be used in interface definitions in the Integration Builder. 6. 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. In the above example, these elements are called keyN. ○ Enter the access element first and define the table columns that are to be accessed. Do not change the name of the tag. ○ Use the elements to describe the condition for accessing the table columns. 7. 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 Elements
8. You have the option of setting the following attributes in the elements: ○ compareOperation= You use this attribute to set the logical compare operation for the respective element. The following values are permitted: •
■
EQ: Equal (default value)
•
■
NEQ: Not equal
•
■
LT: Less than
•
■
LTEQ: Less than or equal to
•
■
GT: Greater than
•
■
•
■
GTEQ: Greater than or equal to
LIKE: Likeness (of strings). In the corresponding value, the SQL placeholders “%” or “_” can then also be used.
Here is an example of how you can use the parameters: val2oldval%
“ 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 quotation marks (text column types) or not (numerical column types). In a few cases (for example, when using functions), you may have to override this. •
■
•
■
To always set quotation marks, set YES.
To never set quotation marks round values with this attribute in the SQL syntax, set NO.
○ 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 did not exist.
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 response is put in a separate element for each statement element. The structure of the response documents is contained in the descriptions of the statements.
Defining XML Documents for Message Protocol Native SQL Format Use This protocol is primarily for test purposes. Instead of an XML document format, a text is expected that represents any valid SQL statement.
Procedure For example, to add a row to a table, enter the following: „ INSERT INTO tableName (column-name1, column-name2, column-name3) VALUES(‘columnvalue1’, ‘column-value2’, ‘column-value3’)
Defining an UPDATE Statement Use You use an UPDATE statement to change existing table values. The statement corresponds to an SQL UPDATE statement. Format of UPDATE Statement
realDbTableName
val1val2newval2oldval4val2old2
Procedure 1. Enter the new column values in the element. Enter exactly one element. 2. In the element, enter the condition that can find the data records whose column values are to be changed. ○ You can use any number of elements to formulate your condition. ○ Column values within a element are combined with a logical AND; different elements are combined with a logical OR. ○ If you do not define the element, or if you define an empty element, this means that no condition is specified and that the entire table is to be changed. If you want to ensure this does not happen, select Key Tags Mandatory in the adapter configuration. ○ If you have not formulated a condition in the elements, but have selected Key Tags Mandatory, this results in an error in message processing with a corresponding error output.
Result The corresponding SQL statement in the XML structure above is as follows:
“ UPDATE dbTableName SET col1=’val1’, col2=’val2new’ WHERE ((col2=’val2old’ AND col4=’val4’) OR (col2=’val2old2’)) “ The column type STRING is used for all columns. The character “ may be missing in other column types. The response document contains the following element as well as the number of updated table rows, including 0. count
Defining an INSERT Statement
Use You use an INSERT statement to add table values. The statement corresponds to an SQL INSERT statement. Format of INSERT Statement
realDbTableName
val1val2val11
Procedure 1. Enter the new column values in the block. The statement must have at least one element. 2. Do not enter a element.
Result The corresponding SQL statement in the XML structure 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 of inserted table rows, including 0.
count
Defining a DELETE Statement Use You use this statement to delete table values. Format of DELETE Statement
val2oldval4val2old2
Procedure 1. Enter the condition under which the table values are to be deleted in one or more elements. Column values within a element are combined with a logical AND; different elements are combined with a logical OR. 2. If you do not enter a element, or if you enter an empty element, no condition is specified. The entire table is deleted. If you want to ensure this does not happen, select Key Tags Mandatory in the adapter configuration. If you have not formulated a condition in the elements, but have selected Key Tags Mandatory, this results in an error in message processing with a corresponding error output.
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’))
“ The response document contains one element: count
Defining an UPDATE_INSERT Statement Use You use an UPDATE_INSERT statement to change and add table values. Format of UPDATE_INSERT Statement
realDbTableName
val1val2newval2oldval4val2old2
Procedure 1. Enter the new column values in the element.
Enter exactly one element. 2. In the element, enter the condition that can find the data records whose column values are to be changed. ○ You can use any number of elements to formulate your condition. ○ Column values within a element are combined with a logical AND; different elements are combined with a logical OR. ○ If you do not define the element, or if you define an empty element, this means that no condition is specified and that the entire table is to be changed. If you want to ensure this does not happen, select Key Tags Mandatory in the adapter configuration. If you have not formulated a condition in the elements, but have selected Key Tags Mandatory, this results in an error in message processing with a corresponding error output. If no change can be made to the database table in this action (the formulated condition does not apply to any table entry), the values described in the element are added to the table in accordance with the description for the INSERT statement. elements are ignored in this case. See: Defining an INSERT Statement
Result The response document has the following format, where one of the two values is 0 because either an UPDATE or an INSERT action is always executed: countcount
Defining a SELECT Statement Use You use this statement to select table values. The statement corresponds to an SQL SELECT statement. Format of SELECT Statement
realDbTableName
val2oldval4val2old2
Procedure 1. Enter the column names to be selected in the block. A statement with the action SELECT must have exactly one element. 2. In a element, enter the condition that can find the data records whose column values are to be selected. You can define any number of elements. Column values within a element are combined with a logical AND; different elements are combined with a logical OR. ○ If you do not define the element, or if you define an empty element, this means that no condition is specified and that the entire table is to be selected. If you want to ensure this does not happen, select Key Tags Mandatory in the adapter configuration. ○ If you have not formulated a condition in the elements, but have selected Key Tags Mandatory, this results in an error in message processing with a corresponding error output.
Result The corresponding SQL statement for the XML structure above is as follows: “ SELECT col1,col2,col3 FROM dbTableName WHERE ((col2=’val2old’ AND col4=’val4’) OR (col2=’val2old2’))
“ The response document contains the result of the action in XML format as follows: “ value11value12 ... ... valueN1valueN2 ... “
Defining an EXECUTE Statement Use You use this statement to execute a stored procedure. Format of EXECUTE Statement
realStoredProcedureeName
val1
Procedure
1. Enter the name of the stored procedure in the database before the action. 2. If you use the optional
element, the value specified here is used as the stored procedure name. This enables you, for example, 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. ○ Enter
as the first element of the block within . 3. Specify the parameters for the stored procedure. ○ You have the option of specifying the attribute isInput=“1“(input parameter) or isOutput=“1“ (output parameter) for the parameters. 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. ○ You must specify the attribute type= for all parameter types (IN, OUT). It describes the valid SQL data type. 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 connection with the Oracle JDBC driver)
The binary data for BLOB is hexadecimal encoded.
Result All return values are returned in an XML structure. The results within the stored procedure are returned either as a table or as the element . This depends on the SQL statements executed within the stored procedure. The return parameters of a stored procedure are appended in a separate structure.
Defining an SQL_QUERY Statement Use You use this statement to transfer complex SQL statements directly to the database by using the adapter. You have the option of using placeholders in these SQL statements, which can be listed in the keyblock. This enables you to generate complex, parameterisable SQL statements. Format of SQL_QUERY Statement
SQL-String with optional placeholder(s)value1value2
Procedure 1. Specify a name of your choice for the structure. Unlike in the usual statement types, no table name or stored procedure name is expected in the default setting. 2. 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. 3. You must enter the element first. The content of represents a valid SQL call for the respective mode, with the option of placeholders. ○ If you use placeholders, list them within the element. The names of the placeholder elements must be identical to those used in the SQL string (where they still have the $ character). ○ In the XML structure above, the strings $placeholder1$ and $placeholder2$ contained in the SQL string are replaced with value1 or value2 before the SQL statement is executed. ○ If you do not use placeholders, you can omit the element or set it as empty. In both cases, you must not select the Key Tags Mandatory field in the configuration, as this will cause runtime errors. Using placeholders is not restricted to individual field values. You can set any parts of the SQL statement in this way. You can also influence the logic of the statement.
Surplus and undefined placeholders are tolerated in the element. Undefined placeholders are left unchanged in the SQL string. This can lead to syntax errors or to unexpected results in the database.
Result The following example does not contain any placeholders: UPDATE Customers SET CompanyName='Firma', Address='Strasse 3' WHERE CustomerID='FI'
The unchanged SQL statement is executed in the database: UPDATE Customers SET CompanyName='Firma', Address='Strasse 3' WHERE CustomerID='FI'
The following example contains placeholders: UPDATE Customers SET CompanyName=’$NAME$’, Address=’$ADDRESS$' WHERE CustomerID='$KEYFIELD$’ Firma Strasse 3 FI
After the placeholders have been replaced, the same SQL statement is executed in the database as above: UPDATE Customers SET CompanyName='Firma', 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. 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:
realDbTableName
val1val2newval2oldval4val2old2
realDbTableName
val1val2val11val2oldval4val2old2
realDbTableName
val2oldval4val2old2
realStoredProcedureeName
val1SQL-String with optional placeholder(s)value1value2 Comments
. Within this tag there are one or more ● The document contains a tag with the arbitrary name statement elements that also have arbitrary names. Each of these statements contains the description of a database action. With the exception of the execute description for a stored procedure (shown in the example under the element ), 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 , UPDATE, UPDATE_INSERT, DELETE, or SELECT. If you use the optional
element, the value specified is used as a database table name. This enables you, for example, to define table names containing non-XML-compatible characters or characters that prevent them from being used in interface definitions in the Integration Builder. If specified, .
must be the first element in the block within
○
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. In the above example, these elements are called keyN. The access element contains the table 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 are specified, access proceeds without any conditions. In the case of UPDATE and DELETE, this can lead to the entire table being updated or deleted respectively. If you want to ensure this does not happen, select Key Tags Mandatory in the adapter configuration.
○
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 response is put in a separate element for each statement element.
● action=UPDATE Statements with this action cause existing table values to be updated. Therefore, the statement corresponds to an SQL UPDATE statement. The block contains the new column values and a element contains the columns whose values must be identical with the specified value to get the new column values. The name of the element is arbitrary. Column values within a element are combined with a logical AND; different elements are combined with a logical OR.
A statement with the action UPDATE must have exactly one element. The number of 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. The character “ may be missing in other column types. The response document contains the following element as well as the number of updated table lines, including 0. count
If there is no element, or if there is a 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=INSERT Statements with this action cause table values to be inserted. Therefore, the statement corresponds to an SQL INSERT statement. The block contains the new column values. A statement with the action INSERT must have at least one element. It cannot have a 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 of inserted table lines, including 0. count
● action=UPDATE_INSERT The 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 this action (the condition does not apply to any
table entry), values of the table described in the element are inserted in accordance with the description of the action INSERT. 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: countcount
● action=DELETE Statements with this action cause existing table values to be deleted. One or more elements formulate the condition for which table values are deleted. The names of elements are arbitrary. Column values within a element are combined with a logical AND; different elements are combined with a logical OR. 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: count
If there is no element, or if there is a 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=SELECT Statements with this action cause existing table values to be selected. Therefore, the statement corresponds to an SQL SELECT statement. The block contains the column names to be selected, a element contains the columns whose values must be identical with the specified value to get the new column values. The name of the element is arbitrary. Column values within a element are combined with a logical AND; different elements are combined with a logical OR. A statement with the action SELECT must have exactly one element. The number of 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 element, or if there is a 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:
“ value11value12 ...
... valueN1valueN2 ...
“ ● action=EXECUTE Statements 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
element, the value specified here is used as the stored procedure name. This enables you, for example, 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. If specified,
must be the first element in the block within . The elements within the stored procedure are interpreted as parameters. They can optionally have the attribute isInput=“1“ (input parameter) or isOutput=“1“ (output parameter) 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= , 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 stored procedure are returned either as a table or as the element . This depends on the SQL statements executed within the stored procedure. The return parameters of a stored procedure are attached in a separate structure.
● action= SQL_QUERY | SQL_DML This structure enables you to transfer more complex SQL statements to the database directly using the adapter. You have the option of using placeholders in these SQL statements, which can be listed in the subsequent 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 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 the name . The names of the placeholder elements must be identical to those used in the SQL string (where they still have the $ character). In the above example , the strings $placeholder1$ and $placeholder2$ contained in the SQL string are replaced with value1 or value2 before the SQL statement is executed.
○
If you are not using placeholders, then the block can be omitted or left empty. In both cases, you must not select the Key Tags Mandatory field in the configuration, as this will cause runtime errors. Example (Without Placeholders):
UPDATE Customers SET CompanyName='Company', Address='Street 3' WHERE CustomerID='CO' The unchanged SQL statement is executed in the database:
UPDATE Customers SET CompanyName='Company', Address='Street 3' WHERE CustomerID='CO' Example (with Placeholders):
UPDATE Customers SET CompanyName=’$NAME$’, Address=’$ADDRESS$' WHERE CustomerID='$KEYFIELD$’ Company Street 3 CO 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' WHERE CustomerID='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 influence the logic of the statement.
○
Surplus and undefined placeholders are tolerated in the section. Undefined placeholders are left unchanged in the SQL string. This can lead to syntax errors or to unexpected results in the database. Attributes in the Elements
The XML elements in the elements can have the following optional attributes:
● compareOperation= This attribute enables the logical compare operation to be set for the respective element. The following values are permitted: Values for compareOperation
Attribute
Value and Check
EQ
Equals (default value)
NEQ
Does not equal
LT
Less than
LTEQ
Less than or equal to
GT
Greater than
GTEQ
Greater than or equal to
LIKE
Like (strings). In the corresponding value, the SQL placeholders “%” or “_” can then also be used.
In the above example XML document, the block is changed for the SELECTstatement (StatementName4) as follows:
val2oldval% 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 quotation marks (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 this attribute is set in the SQL syntax. If NO, quotation marks are never set. Only use this attribute in individual cases.
● 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 is often difficult to represent in mapping programs. XML Document Format for the Message Protocol Native SQL Format
This 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’) “
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. We will discuss a simple example wherein we will use sender and receiver JDBC adapter to talk to an Oracle Database. The JDBC adapter is used to connect to different database systems via SAP PI. The adapter converts database content to XML messages and vice versa.
Sender JDBC Adapter is used to read data from databases while the receiver JDBC adapter writes data from SAP PI to the relevant databases. Database content can be read with any SQL statement. To write data to database, a predefined XML format needs to be used. You can execute SQL INSERT, UPDATE, SELECT, DELETE, or stored procedure statements. Direct arbitrary SQL statement can as well be used. However, this should be used only for test purposes. Point to note is that a message is always processed in exactly one database transaction. To be able to use JDBC adapter with a particular database, you must install the corresponding JDBC drivers on your SAP PI server. The driver files can be obtained from the database vendor. Now let us configure a simple scenario to understand JDBC adapters. Let’s say we have a simple table which stores employee details in an Oracle Database. The table has 4 fields corresponding to Employee Number, Name, Department and a status field to indicate new entries. Our aim is to read new entries from this table using SAP PI. After reading a record, the status field should be set appropriately so that the record is not picked up again. We will configure this using a JDBC sender adapter. Now whenever an employee is transferred from one department to other, this data should get updated back in the Oracle database. We will accomplish this using JDBC receiver adapter.
Configuring the Sender JDBC Adapter The figure below shows a typical configuration for JDBC sender adapter. JDBC Driver and Connection parameters shown correspond to Oracle database. These parameters differ depending on the database you are using. The correct parameters for your database can be found out from the database vendor.
Poll Interval specifies how often JDBC adapter polls the database. Query SQL Statement should contain the actual SQL SELECT statement using which you want to query the database. e.g in our case – SELECT E_NO as Emp_Number, E_NAME as Emp_Name, DEPT as Emp_Dept from EMP_TAB where E_NEW = 'Y' order by E_NO
Corresponding to your SQL SELECT statement’s output, you need to create a data type and a message type in IR/ESR. In our case it would look something like below:
The standard format shown must be followed. The JDBC adapter always returns records in the XML format like one shown below. Note that is in all lowercase. ... ...
... ... ... ...
Document Name in the adapter configuration specifies the Message Type. 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.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. In this case, put the string (including the angle brackets) in the Update SQL Statement field.
Configuring the Receiver JDBC Adapter Now we want to update the database from PI. Following figure shows required configuration for JDBC receiver adapter.
