Lightweight Java Database Connectivity (JDBC) adapter

How the Lightweight JDBC Adapter Works

The Lightweight JDBC adapter can start a business process or it can be used in a business process. This service can be scheduled to run at weekly or timed intervals.

The following figure shows how the Lightweight JDBC adapter communicates with a JDBC-compliant database:

Business Scenario

Your company receives information from an external database about a customer that needs to be either added (if the customer does not exist) or updated in your internal customer database. The customer information arrives in XML format.

Business Solution Example

GPM Example

The following example shows a solution to the business scenario using the GPM.

A Lightweight JDBC adapter has been configured and scheduled to retrieve records from an external database. The result from the query is the primary document to this business process.

The Assign statements are used to retrieve the customer information from the primary document and place the information into process data. The parameters specified for the Lightweight JDBC adapter, which includes customer information retrieved from process data and SQL statements, are input to the adapters. A primary document containing the results from the SQL statements issued against the database is output from the Lightweight JDBC adapter instances and is passed to the next step in the business process.

For information about the Lightweight JDBC parameters, see Implementing the Lightweight JDBC Adapter.

Business Process Modeling Language (BPML) Example

The following example shows the corresponding business process solution using BPML.

For information about the Lightweight JDBC parameters, see Implementing the Lightweight JDBC Adapter. For additional Lightweight JDBC adapter examples, including examples for using Oracle stored procedures and functions, see Lightweight JDBC Business Process Usage.

Implementing the Lightweight JDBC Adapter

The information in this section applies to both implementation types.

Setting Up a Connection to an External Database

You must set up a connection to an external database for the Lightweight JDBC adapter. You can use any of the databases supported by the application for internal use or other JDBC-compliant databases, such as Sybase. For information about databases supported by the application, see System Requirements.

Adding New Database Pools

To define a new database pool for use by the Lightweight JDBC adapter, you must add settings for the pool to the jdbc_customer.properties.in file, which is located in the application's /install_dir/properties directory.

In jdbc_customer.properties.in, specify the database server name, port number, database/catalog name, user ID, and password. To encrypt your database password, use the encrypt_string.sh or encrypt_string.cmd utility in the bin directory. Then place the encrypted password, prefixed by an encryption indicator, in your properties file.

After you enter the information in the jdbc_customer.properties.in file, re-start the application.

If the database you want to connect to resides on a database server type that is not the same as the application's database server type, you also need to install a JDBC driver using the install3rdparty.sh or install3rdparty.cmd utility.

Select a table and column in your database to use in the test on reserve function. This function causes the application to test the database connection using a quickly run query before attempting to use it. This function ensures that idle connections are revived. The column referenced in the query should be of the type varchar and should be at least five characters in length.

Connecting to an External Database

Installing a Sybase Driver

This procedure should result in a successful connection to your Sybase database. However, if the database has been configured as character set ROMAN8, it is likely that you will see the following message in the application system log, because of a limitation in the Sybase driver:

java.sql.SQLWarning: JZ0IB: The server's default
charset of roman8 does not map to an encoding that is available
in the client Java environment. Because jConnect will not be able
to do client-side conversion, the connection is unusable and is
being closed. Try using a later Java version or try including your
Java installation's i18n.jar or charsets.jar file in the clasps

One resolution of this issue is to configure the primary Adaptive Server with a default character set that maps to one of the character sets supported by jConnect for JDBC (for example, UTF-8). For more details, refer to the Sybase documentation.

Properties File Examples for Specific Databases

Oracle 8i/9i

For Oracle 8i/9i, enter the following parameters in the jdbc_customer.properties.in file, where databasePool is the name of the pool you are adding. Sample values are italicized; enter the correct value for your environment instead of the sample value. Values that are not italicized are the actual values that you should enter for the parameter:

databasePool.driver=oracle.jdbc.driver.OracleDriver 
databasePool.url=jdbc:oracle:thin:@servername:0000:servername 
databasePool.user=username 
databasePool.password=password 
databasePool.catalog=catalogname 
databasePool.type=local 
databasePool.testOnReserve=true 
databasePool.testOnReserveQuery=SELECT TestConnection from 
     Connection_tb WHERE TestConnection = ? 
databasePool.testOnReserveInterval=60000 
databasePool.max8177RetryCount=n 
databasePool.dbvendor=oracle 
databasePool.buffersize=n 
databasePool.maxsize=n 
databasePool.initsize=n 
databasePool.behaviour=n 
databasePool.lifespan=0 
databasePool.idletimeout=86400000 
databasePool.housekeepinginterval=3600000 
databasePool.waittime=n databasePool.storedProcClassName= 
     com.sterlingcommerce.woodstock.util.frame.jdbc.OracleNoAppStoredProcQuery 
databasePool.varDataClassName=com.sterlingcommerce.woodstock.util.
     frame.jdbc.OracleVarData 
databasePool.factory=com.sterlingcommerce.woodstock.util.frame.jdbc.
     ConnectionFactory

DB2

For DB2, enter the following parameters in the jdbc_customer.properties.in file, where databasePool is the name of the pool you are adding. Sample values are italicized; enter the correct value for your environment instead of the sample value. Values that are not italicized are the actual values that you should enter for the parameter:

databasePool.driver=com.ibm.as400.access.AS400JDBCDriver 
databasePool.url=jdbc:as400://serverName/DB2Database;translate binary=true;
     transaction isolation=none; 
databasePool.catalog=DB2Database 
databasePool.varDataClassName=com.sterlingcommerce.woodstock.util.frame.
     jdbc.DB2ISeriesVarData 
databasePool.user=username 
databasePool.password=password 
databasePool.maxconn=20 
databasePool.testOnReserve=true 
databasePool.testOnReserveQuery=SELECT SI_VERSION from SI_VERSION where 
      SI_VERSION = ? 
databasePool.testOnReserveInterval=60000 
databasePool.blobPageSize=1024000 
databasePool.compressBlob=true 
databasePool.dbvendor=db2iSeries 
databasePool.buffersize=500 
databasePool.maxsize=20 
databasePool.initsize=0 
databasePool.factory=com.sterlingcommerce.woodstock.util.frame.jdbc.
     ConnectionFactory 
databasePool.behaviour=2 
databasePool.lifespan=0 
databasePool.idletimeout=86400000 
databasePool.housekeepinginterval=3600000 
databasePool.waittime=1000

MS SQL 2000

For MS SQL 2000, enter the following parameters in the jdbc_customer.properties.in file, where databasePool is the name of the pool you are adding. Sample values are italicized; enter the correct value for your environment instead of the sample value. Values that are not italicized are the actual values that you should enter for the parameter:

databasePool.driver=com.microsoft.jdbc.sqlserver.SQLServerDriver 
databasePool.url=jdbc:microsoft:sqlserver://servername:0000;
     DatabaseName=SQLdatabase;SelectMethod=cursor  
databasePool.user=username 
databasePool.password=password 
databasePool.catalog=catalogname 
databasePool.type=local 
databasePool.testOnReserve=true 
databasePool.testOnReserveQuery=SELECT TestConnection from 
     Connection_tb WHERE TestConnection = ? 
databasePool.testOnReserveInterval=60000 
databasePool.dbvendor=mssql 
databasePool.buffersize=n 
databasePool.maxsize=n 
databasePool.initsize=n 
databasePool.behaviour=n 
databasePool.lifespan=0 
databasePool.idletimeout=86400000 
databasePool.housekeepinginterval=3600000 
databasePool.waittime=n  
databasePool.storedProcClassName=com.sterlingcommerce.woodstock.util.
     frame.jdbc.GenericStoredProcQuery 
databasePool.varDataClassName=com.sterlingcommerce.woodstock.util.
      frame.jdbc.MSSQLVarData 
databasePool.factory=com.sterlingcommerce.woodstock.util.frame.jdbc.
      ConnectionFactory

Sybase

For Sybase, enter the following parameters in the jdbc_customer.properties.in file, where databasePool is the name of the pool you are adding. Sample values are italicized; enter the correct value for your environment instead of the sample value. Values that are not italicized are the actual values that you should enter for the parameter:

databasePool.driver=com.sybase.jdbc2.jdbc.SybDriver 
databasePool.url=jdbc:sybase:Tds:servername:0000/SybaseDB 
databasePool.user=username 
databasePool.password=password 
databasePool.catalog=catalogname 
databasePool.type=local 
databasePool.testOnReserve=true 
databasePool.testOnReserveQuery=SELECT TestConnection from 
     Connection_tb WHERE TestConnection = ? 
databasePool.testOnReserveInterval=60000 
databasePool.dbvendor=Sybase 
databasePool.buffersize=n 
databasePool.maxsize=n 
databasePool.initsize=n 
databasePool.behaviour=n 
databasePool.lifespan=0 
databasePool.idletimeout=86400000 
databasePool.housekeepinginterval=3600000 
databasePool.waittime=n  
databasePool.storedProcClassName=com.sterlingcommerce.woodstock.util.
     frame.jdbc.SybaseStoredProcQuery 
databasePool.varDataClassName=com.sterlingcommerce.woodstock.util.
     frame.jdbc.JConnectVarData 
databasePool.factory=com.sterlingcommerce.woodstock.util.frame.jdbc.
     ConnectionFactory

Encrypting Your Database Password

Configuring the Lightweight JDBC Adapter

To configure the Lightweight JDBC adapter, you must specify field settings in the application and in the GPM.

Application Configuration Parameters

The following table describes the fields used to configure the Lightweight JDBC adapter:

<RootResultXmlElement>
<ResultsFromUpdate>
<Rows_Affected>
1
</Rows_Affected>
</ResultsFromUpdate>
</RootResultXmlElement>

<RootResultXmlElement>
<ResultsFromUpdate>
<Rows_Affected>
1
</Rows_Affected>
</ResultsFromUpdate>
</RootResultXmlElement>

GPM Configuration

The following screen shows the graphical view of the GPM parameters for the Lightweight JDBC adapter. Values for the inactive fields were specified using the Lightweight JDBC adapter configuration. The active fields cannot be configured in the application or are being overridden. None of the fields on the Message From Service tab require configuration.

The following example shows the corresponding BPML parameters for the Lightweight JDBC adapter GPM parameters. This example business process takes the primary document, which contains the query results, and writes it to process data.

<process name="ExampleLWJDBC"> 
<operation name="LWJDBC - QueryForCustomerID"> 
<participant name="ExampleLWJDBCBusinessProcess"/> 
<output message="LightweightJDBCAdapterTypeInputMessage"> 
<assign to="param1" from="number(/ProcessData/CustomerInformation/
     ResultsFromQuery/Customer[number(//NumberOfRecords)]/customer_id/text())"/> 
<assign to="paramtype1">Integer</assign> 
<assign to="query_type">SELECT</assign> 
<assign to="sql">SELECT COUNT(*) AS CUSTOMERS FROM CUSTOMER2 WHERE 
     CUSTOMER_ID = ?</assign> 
<assign to="." from="*"/> 
</output> 
<input message="inmsg"> 
<assign to="NumberOfCustomers" from="DocToDOM(PrimaryDocument)/
     ResultsFromSQL/CUSTOMERS"/> 
</input> 
</operation> 
</sequence> 
</process>

The following table describes the fields used to configure the Lightweight JDBC adapter in the GPM. This table contains only the fields that are configured in the GPM. See Configuring the Lightweight JDBC Adapter for parameters you can specify in either the GPM or in the configuration for your application.

Large Text Data Support

To insert data to the database or to update the database using the CharacterStream or CharacterStreamFromDocument values, there are two new query types: INSERT and UPDATE.

There is also a parameter that you can add to your business process manually (this parameter is not available through the GPM): write_characterstream_to_document. Valid values for this parameter are YES and NO. This parameter enables you to place the contents of large data inside of a document during a SELECT query, because XML has size limitations on pcdata. The primary document will still contain the result tree but the name of the document will replace the large data. The write_characterstream_to_document parameter defaults to YES. The LOB streaming support has a maximum limit of 4 GB, but the actual LOB streaming support limit depends on the database and the JDBC driver being used.

The following example illustrates using the parameter to write the contents of the data returned from the database directly to a document:

<operation name="LightweightJDBCAdapterType">
      <participant  name="LightweightJDBCAdapterQuery"/>
      <output message="LightweightJDBCAdapterTypeInputMessage">
        <assign to="result_name">result</assign>
        <assign to="sql">SELECT ID, XML_MESSAGE FROM CUSTOMER WHERE 
           CUSTOMER_ID=?</assign>
        <assign to="pool">mysqlPool_local</assign>
        <assign to="param1" from="customer_id/text()" />
        <assign to="paramtype1">String</assign>
        <assign to="write_characterstream_to_document">Yes</assign>
        <assign to="row_name">row</assign>
        <assign to="query_type">SELECT</assign>
        <assign to="." from="*"></assign>
      </output>
      <input message="inmsg">
        <assign to="." from="*"></assign>
      </input>
    </operation>

This is a sample of what you would see in the primary document:

<?xml version="1.0" encoding="UTF-8"?>
    <result>
     <row>
      <ID>1</ID>
      <XML_MESSAGE>doc-1</XML_MESSAGE>
     </row>
    </result>

The parameter types are used by the Lightweight JDBC adapter to insert or update large text data into the database. The insert and update statements differ in how they are implemented, so you must specify the INSERT or UPDATE action type. Several examples follow.

Example 1

Updates the column with the contents of the primary document. In this example, the parameter type is CharacterStreamFromDocument, so the parameter value is not required; instead, the value is taken from the primary document.

<operation name="LightweightJDBCAdapterType">
   <participant name="LightweightJDBCAdapterQuery"/>
   <participant name="LightweightJDBCAdapterQuery"/>
     <output message="LightweightJDBCAdapterTypeInputMessage">
     <output  message="LightweightJDBCAdapterTypeInputMessage">
       <assign to="result_name">resul</assign>t<assign to="result_name">result</assign>
       <assign to="sql">UPDATE CUSTOMER SET XML_MESSAGE=? WHERE 
            CUSTOMER_ID=</assign>?<assign to="sql">UPDATE CUSTOMER SET XML_MESSAGE=? 
            WHERE CUSTOMER_ID=?</assign>
       <assign to="paramtype1">CharacterStreamFromDocumen</assign>t<assign to
               ="paramtype1">CharacterStreamFromDocument</assign>
       <assign to="param2" from="customer_id/text()" /><assign to="param2" 
                from="customer_id/text()" />
       <assign to="paramtype2">Strin</assign>g<assign to="paramtype2">String</assign>
       <assign to="pool">mysqlPool_loca</assign>l<assign to="pool">mysqlPool_local</assign>
       <assign to="row_name">ro</assign>w<assign to="row_name">row</assign>
       <assign to="query_type">UPDAT</assign>E<assign to="query_type">UPDATE</assign>
       <assign to="." from="*"</assign>><assign to="." from="*"></assign>
     </output></output>
    <input message="inmsg"><input message="inmsg">
       <assign to="." from="*"</assign>><assign to="." from="*"></assign>
     </input></input> 
</operation>

Example 2

Updates a column with the string returned from an Xpath query against process data. In this example, the CharacterStream parameter type is used, so the parameter value is required.

<operation name="LightweightJDBCAdapterType">
      <participant name="LightweightJDBCAdapterQuery"/>
      <output message="LightweightJDBCAdapterTypeInputMessage">
        <assign to="result_name">result</assign>
        <assign to="sql">UPDATE CUSTOMER SET XML_MESSAGE = ? 
             WHERE CUSTOMER_ID=?</assign>
        <assign to="paramtype1">CharacterStream</assign>        
        <assign to="param1" from="rootNode/SampleText2/text()"></assign>
        <assign to="param2" from="customer_id/text()" />
        <assign to="paramtype2">String</assign>
        <assign to="pool">mysqlPool_local</assign>
        <assign to="row_name">row</assign>
        <assign to="query_type">UPDATE</assign>
        <assign to="." from="*"></assign>
      </output>
      <input message="inmsg">
        <assign to="." from="*"></assign>
      </input> 
</operation>

Example 3

Inserts a row with the string returned from an Xpath query against process data.

<operation name="LightweightJDBCAdapterType">
      <participant name="LightweightJDBCAdapterQuery"/>
      <output message="LightweightJDBCAdapterTypeInputMessage">
        <assign to="result_name">result</assign>
        <assign to="sql">INSERT INTO CUSTOMER (CUSTOMER_ID, XML_MESSAGE) 
               VALUES (?, ?) </assign>
        <assign to="param1" from="customer_id/text()" />
        <assign to="paramtype1">String</assign>
        <assign to="paramtype2">CharacterStream</assign>        
        <assign to="param2" from="rootNode/SampleText/text()"></assign>
        <assign to="pool">mysqlPool_local</assign>
        <assign to="row_name">row</assign>
        <assign to="query_type">INSERT</assign>
        <assign to="." from="*"></assign>
      </output>
      <input message="inmsg">
        <assign to="." from="*"></assign>
      </input> 
</operation>

Example 4

Writes the contents of the data returned from a database directly to a document (note that this is not the primary document). The primary document will still contain the XML result tree; however, the data in the document is replaced by a document name.

<operation name="LightweightJDBCAdapterType">
      <participant name="LightweightJDBCAdapterQuery"/>
      <output message="LightweightJDBCAdapterTypeInputMessage">
        <assign to="result_name">result</assign>
        <assign to="sql">SELECT XML_MESSAGE FROM CUSTOMER WHERE 
            CUSTOMER_ID=?</assign>
        <assign to="pool">mysqlPool_local</assign>
        <assign to="param1" from="customer_id/text()" />
        <assign to="paramtype1">String</assign>
        <assign to="write_characterstream_to_document">Yes</assign>
        <assign to="row_name">row</assign>
        <assign to="query_type">SELECT</assign>
        <assign to="." from="*"></assign>
      </output>
      <input message="inmsg">
        <assign to="." from="*"></assign>
      </input> 
</operation>

This is an example of what you should expect to see in the primary document:

<?xml version="1.0" encoding="UTF-8"?>
    <result>
     <row>
      <XML_MESSAGE>doc-1</XML_MESSAGE>
     </row>
    </result>

This is an Oracle example for updating a clob column. The first parameter is used to find the row in the table. The second parameter will be used to update the value.

<operation name="LightweightJDBCAdapterType">
      <participant name="LightweightJDBCAdapterQuery"/>
      <output message="LightweightJDBCAdapterTypeInputMessage">
        <assign to="result_name">result</assign>
        <assign to="sql">SELECT XML_MESSAGE FROM CUSTOMER WHERE 
               CUSTOMER_ID=?for update</assign>
        <assign to="param1" from="customer_id/text()" />
        <assign to="paramtype1">String</assign>
        <assign to="paramtype2">CharacterStream</assign>   
        <assign to="param2" from="rootNode/SampleText2/text()"></assign>
        <assign to="pool">oracleTestPool</assign>
        <assign to="row_name">row</assign>
        <assign to="query_type">UPDATE</assign>
        <assign to="." from="*"></assign>
      </output>
      <input message="inmsg">
        <assign to="." from="*"></assign>
      </input>
    </operation>

Lightweight JDBC Business Process Usage

This section contains additional examples using the Lightweight JDBC adapter, including sample configurations for using Oracle stored procedures and functions.

The following sample document is the primary document passed to the business process for the SELECT and ACTION query type examples.

<Customer Id="2"> 
	<Name/> 
	<Address/> 
	<Phone/> 
</Customer>

SELECT Query Type Example

The following example using the GPM illustrates a business process that uses the Lightweight JDBC adapter to SELECT customer information based on the customer ID passed into the business process.

The following example illustrates the same business process using BPML.

Message to the Lightweight JDBC Adapter Instance

The following example shows the message sent to the Lightweight JDBC adapter when you run the business process. The message uses the parameters defined within the <output> tags of the Lightweight JDBC adapter definition in the previous BPML example, along with the parameters defined during configuration of the adapter instance.

<LightweightJDBCAdapterTypeInputMessage> 
	<row_name>ResultsFromSQL</row_name> 
	<StartNewWorkFlow>NO</StartNewWorkFlow> 
	<result_name>RootResultXmlElement</result_name> 
	<pool>mysqlTrainingPool</pool> 
	<query_type>SELECT</query_type> 
	<XMLVersion>3.0</XMLVersion> 
	<param1>2</param1> 
	<paramtype1>Integer</paramtype1> 
	<sql>SELECT * FROM Customer WHERE CUSTOMER_ID = ?</sql> 
</LightweightJDBCAdapterTypeInputMessage>

Message from the Lightweight JDBC Adapter Instance

The following example shows the table definition used to query against the Lightweight JDBC adapter. In the previous BPML example, the assign statement within the <input> tags of the Lightweight JDBC adapter definition (<assign to="." from="."></assign>) indicates that the results are returned as a primary document.

create table customer(customer_id NUMERIC not null, 
      customer_name varchar(255), 
      customer_address varchar(100), 
      customer_phone varchar(20));

Notice how the columns from the Customer table become XML elements in the results returned from the Lightweight JDBC adapter, as shown below in the message returned from the Lightweight JDBC adapter.

ACTION Query Type Example

The following example using the GPM illustrates a business process that uses the Lightweight JDBC adapter to delete customer information from a database.

The following example illustrates the same business process using BPML.

Message to the Lightweight JDBC Adapter Instance

The following example shows the message sent to the Lightweight JDBC adapter when you run the business process. The message uses the parameters defined within the <output> tags of the Lightweight JDBC adapter definition in the previous BPML, along with the parameters defined for the Lightweight JDBC adapter instance during configuration.

<?xml version="1.0" encoding="UTF-8"?> 
<LightweightJDBCAdapterTypeInputMessage> 
 <row_name>ResultsFromSQL</row_name> 
 <StartNewWorkFlow>NO</StartNewWorkFlow> 
 <result_name>RootResultXmlElement</result_name> 
 <pool>mysqlTrainingPool</pool> 
 <query_type>ACTION</query_type> 
 <XMLVersion>3.0</XMLVersion> 
 <param1>2</param1> 
 <paramtype1>Integer</paramtype1> 
 <sql>DELETE FROM CUSTOMER WHERE CUSTOMER_ID=?</sql> 
</LightweightJDBCAdapterTypeInputMessage>

Message from the Lightweight JDBC Adapter Instance

The following example shows the message returned from the Lightweight JDBC adapter to the business process. The assign statement within the <input> tags of the Lightweight JDBC adapter definition (<assign to="." from="."></assign>) indicates that the results are returned as a primary document.

Calling Oracle Stored Procedures

Stored Procedure Example

The following example shows a database script for creating an Oracle stored procedure. The stored procedure updates the account status for a customer. Notice that the first argument of the stored procedure is declared as an output parameter and is set to a number, even though this value will not be used in the business process.

CREATE OR REPLACE PROCEDURE P_Update_Account(v_dummy OUT number, 
       v_customer_id IN number, v_account_id number, v_status IN varchar2) 
AS 
BEGIN 
	UPDATE 
 	ACCOUNT 
 	SET 
	  STATUS = v_status 
 	WHERE 
	 	ACCOUNT_ID = v_account_id 
 	AND 
	 	CUSTOMER_ID=v_customer_id; 
 	COMMIT; 
 	v_dummy:= '4'; 
END; 
/

The following sample document is the primary document passed into the example business process used to call the stored procedure.

<PurchaseOrder> 
	<Account Id="100"> 
 	<Customer Id="2"/> 
 	<Status>ACTIVE</Status> 
	</Account> 
</PurchaseOrder>

The following example using the GPM illustrates a business process that uses the Lightweight JDBC adapter to start the stored procedure.

The following example illustrates the same business process using BPML:

Calling Oracle Functions

The following example shows a database script for creating a simple Oracle function. The function retrieves and returns the number of products in the database.

CREATE OR REPLACE FUNCTION product_count 
RETURN number 
 IS 
   V_product_count number; 
BEGIN  
   SELECT count(*) INTO v_product_count  
   FROM product;   
RETURN v_product_count;  
END; 
/

The following example using the GPM illustrates a business process that uses the Lightweight JDBC adapter to start the function:

The following example illustrates the same business process using BPML:

Note: Use curly brackets ({ }) while calling a stored procedure. For example, <assign to="sql">{? = call product_count()}</assign>