Configuring the LWJDBC Adapter to output a SQL database query to the Process Data

Body

It many situations customers would like to add the content of a Primary Document to the Process Data. In order to achieve this, you would do the following:

When configuring the "Message to Service" section in the LWJDBC adapter, make sure that the "Output Msg" parameter is configured to "Obtain Message first, then Process Data".

Select the appropriate database pool.

The LWJDBC Adapter supports the following query types: SELECT, INSERT, UPDATE and ACTION. It is also able to run stored procedures and functions. Choose "SELECT" as query type, since data will be extracted from the Database.

It may happen that LWJDBC adapter errors out when using count(*) in sql query and DocToDOM. If this happens please look at the following Technote (specific for customers using GIS 4.3):

http://www-01.ibm.com/support/docview.wss?uid=swg21558732

Enter the name of the Root Tag element that should appear in the XML document returned from the Lightweight JDBC adapter after a query. This document is the Primary Document that contains the results from the SQL query.

Enter the name of the Row Tag XML element containing all the column tags returned from the SQL query. The column tag names are not configurable; they are generated by the column name returned in the result set. The result is written in the Primary Document.

Enter the SQL query that will inquire the database. The SQL statement can be hard-coded or it can be dynamic by declaring parameters with their corresponding parameter types in the provided configuration fields. When declaring parameters they can later be called in the SQL query.

When configuring the "Message from Service" section in the LWJDBC adapter, make sure that the "Input Msg" parameter is configured to "Allow message write".

In the Message from Service section, enter the name and value of the Root XML Tag that will be displayed in the Process Data. Activate the XPATH feature to add the SQL query result to the newly created Root XML Tag.

When the SQL query result has any of following data types: BLOB, LONGVARBINARY, CLOB, LONGVARCHAR or LONGNVARCHAR, the result is written to a new document called "doc-1".

Add the following parameter in LWJDBC Adapter setup to make sure that the result is written in the Primary Document and not in a new document:

Confirm that the SQL Result XML path in the Input Message variable points to the value obtained in the Primary Document. The obtained values will be extracted from the Primary Document and placed on the Process data.

After saving the LWJDBC Adapter configuration run the Business Process to check that the desired value is extracted from the database and correctly written into the Process Data. If the corresponding value is written in the Process data, you have successfully configured the LWJDBC Adapter.

In this example we extracted the profile count from the SCI_PROFILE table and wrote the extracted value into the Process Data. However, using this procedure you will also be able to add one or more parameters to the Process Data by modifying the SQL query to the Database.

For more on DocToDOM function and usage, see:

Sterling B2B Integrator 5.2.0 > Business Processes > BPML and Sterling B2B Integrator > XPath and Process Data > DocToDOM Function