Configuring Properties for JDBC Replace Functions

A JDBC replace function is a replace function in which the match and replace values are taken from columns of the specified database table or from the results of an SQL statement run on the specified database. For example, you can configure Transformer to use a database customer information table, reading the value from the "Full Name" column and replacing it with the value from the "Nickname" column. Unlike the regular replace functions where the match and replace pairing is static, the JDBC replace functions read the data from the specified columns of the next database row each time the transformation processing loops past the JDBC replace function while producing your output document.

Replace functions are similar to custom functions in that you must first define the function and then, apply it to the output node mapping value definition that you want to affect. In the case of replace functions, this can be accomplished using the @REPLACE and @SIMPLE_REPLACE functions.

For more advanced database functionality, such as matching across multiple input nodes or running complex SQL queries, see Using the @JDBCLOOKUP Function.

The process of defining a particular replace function accounts for setting up the value to match, but not the mapping of it. You must map the replace functions to the specific node using the Output Node Mapping Builder. For more information, see Using the Output Node Mapping Builder.

JDBC Replace Functions as Input Data Sources

Since a JDBC replace function can be defined and used no matter what the data format of your input is, by using JDBC replace functions you can access multiple input data sources. For example, if the input data format of your transform is XML, your incoming document is likely an XML file. By defining and using JDBC replace functions, you can read additional input data from the database. In fact, if you define multiple JDBC replace functions, you can use as many database sources as you like.

Top of page

Procedure: How to Define a JDBC Lookup

To define a JDBC lookup:

From the menu bar, select Project and then, Properties.
The Project Properties dialog box opens.
Click the @REPLACE Function category in the left pane.
The following image shows the Project Properties dialog box with the @REPLACE Functions category selected in the left pane.
Click the JDBC Lookup tab.
Click New.
The Add New JDBC Lookup dialog box opens.

To access the particular database, you must provide the necessary information that is required for your JDBC lookup.
1. In the Name field, specify a name for the JDBC lookup.
2. In the URL field, specify the database URL.
  Note: To define a pre-defined function (for example, @SREG) as the URL, click the Browse button to the right of the URL field, which opens the Mapping Builder.
3. From the Driver drop-down list, select an available JDBC driver.
  Note: Transformer does not ship with any of the pre-defined drivers. The driver must be registered with Transformer. For more information on how to register the JDBC driver in Transformer, see JDBC Drivers Category.
4. Specify a valid user name and password for accessing the database.
5. In the Match Column field, type the name of the source match column.
6. In the Replace Column field, specify either a database table name or an SQL statement to run on the database.
7. From the Select Fields drop-down list, select one of the following methods to acquire the match and replace values:
  If you select From Table, specify a database table name in the Table Name field.
  
  If you select From SQL Query, specify an SQL statement to run on the database in the SQL Query field.
  
  If you specify an SQL statement, the match and replace values are taken from the result of the SQL statement.

Top of page

Procedure: How to Modify a JDBC Lookup

To change or update any information in your JDBC lookup:

Select the JDBC lookup, for example, JDBC1, and click Properties.
The JDBC Lookup Properties dialog box opens.
Click OK once you have completed the necessary modifications.

Top of page

Procedure: How to Delete a JDBC Lookup

To delete a JDBC lookup:

Select the JDBC lookup, for example, JDBC1, and click Delete.
The following confirmation dialog box opens.
Click Yes.
The JDBC lookup is removed from the list.

Using the @JDBCLOOKUP Function

The @JDBCLOOKUP function returns a matched value from a database using an SQL statement, also called a JDBC lookup. This function accepts the JDBC data source name and an SQL statement as parameters. Database connectivity is managed through the data sources that are defined in the JDBC Data Source property of the Transform. JDBC data source names identify the data source to be used for evaluating JDBC lookup queries. To avoid naming conflicts, each data source name must be unique.

The JDBC data source parameters and SQL statement can be set dynamically based on the input from other functions within iWay Transformer. If more than one value matches the query, then the last matching value is returned. When no result is found in the database, an empty string is returned. When more than one result is found, the last matching result is returned. Regardless of the number of columns in the result set, the value of the first column is always returned. In the event of multiple occurrences of the @JDBCLOOKUP function within a transformation, if the SQL statements are all the same, then the returned result will be the same.

The signature of the @JDBCLOOKUP function is:

@JDBCLOOKUP(JDBC_data_source_name, SQL_statement)

Parameters:

JDBC_data_source_name: Name of the pre-configured JDBC data source that represents a JDBC connection.

SQL_statement: SQL statement defined using iWay Transformer's SQL Builder to be evaluated by this JDBC lookup.

Example:

@JDBCLOOKUP('mysql_iway', {'SELECT field1 FROM LOOKUP_TABLE WHERE field2 ' = '+@QUOTE(Customer/Person/Name)})

where:

mysql_iway: Is the name of a JDBC connection configuration.
{'SELECT field1 FROM LOOKUP_TABLE WHERE field2 ' = '+@QUOTE(Customer/Person/Name)}: Is an SQL statement.

In iWay Transformer, JDBC data sources are managed in the Project Properties dialog box, under the JDBC Data Source section.

The following table lists and describes the JDBC data source properties that are available:

Property	Description
Name	A unique name that represents the JDBC data source. This name is used by JDBC lookups to refer to this data source.
Url	JDBC connection URL of a data source.
Driver	Class name of a JDBC driver that is used to connect to a data source.
User Name	A user name that is configured for a data source connection.
Password	A password that is associated with the user name.
Timeout	If a connection pool is full, this value represents the amount of time that a given thread waits before checking if there are any released connections in the connection pool. If the value is not defined or 0, the timeout interval is evaluated to less than one millisecond (immediately).

Pre-configured JDBC data sources are available in the Output Node Mapping Builder when configuring @JDBCLOOKUP functions:

SQL statements that are created using SQL Builder do not support single quote or escape characters. In order to properly generate a single quote character for a string value in an SQL statement, you must use the @QUOTEGEN or @QUOTE function.

Select count(*) from … - The count number is returned.

Regardless of the numbers of columns in the result set, the first column will always be returned. For example:

"select * from …" - The value of the first row and first column will be returned.
"select coumn1, coulmn2 from …" - The value of column1 will be returned.

Top of page

Procedure: How to Create an SQL Statement Using SQL Builder

To create an SQL statement, which can be used for the @JDBCLOOKUP function, you must follow the instructions below:

From the menu bar, click Project and select Properties.
The Project Properties dialog box opens.
Click the JDBC Data Source category in the left pane.
The following image shows the Project Properties dialog box with the JDBC Data Source category selected in the left pane.
Click New.
The Add New JDBC Lookup dialog box opens.

Perform the following steps:
1. In the Name field, provide a name for the JDBC connection.
  This name is used as a string value for the JDBC_data_source_name parameter in the @JDBCLOOKUP function.
2. In the URL field, type the URL to the database.
  Note: To define a pre-defined function (for example, @SREG) as the URL, click the Browse button to the right of the URL field, which opens the Mapping Builder.
3. From the Driver drop-down list, select an available JDBC driver that is registered with Transformer.
  Note: Transformer does not ship with any of the pre-defined drivers. For more information on how to register JDBC drivers in Transformer, see JDBC Drivers Category.
4. Type a valid user name and password to connect to the database.
5. Specify a timeout value in milliseconds.
  Note: If the timeout value is omitted, or the value is 0 (default), then timeout is not enabled.
Click OK.
The JDBC lookup is added to the list of defined JDBC lookups.
Click OK.
In the Mapping Values pane of the Mappings tab, select an output node.
Click the ellipsis button .
The Output Node Mapping Builder opens.
From the Category drop-down list in the Functions pane of the Output Node Mapping Builder, select the Processing Functions category.
Select the @JDBCLOOKUP function, and drag it onto the workspace area of the Output Node Mapping Builder.
Double-click the JDBC_data_source_name parameter.
The JDBC Data Source dialog box opens as shown in the following image.
Select the name of a defined JDBC lookup, for example Oracle, and click OK.
The @JDBCLOOKUP function is updated as shown in the following image.
Double-click the SQL_statement parameter.
The SQL Builder opens as shown in the following image.

Since the SQL statement is an expression, the Where clause can contain the following:
- Constant
- Transformer Function
- Input Node Context
- Unlimited AND & OR
Note: In the Column Name field, you can also type * or Count (*) to return more advanced data sets.
Click OK once you have finished creating your SQL statement.
The @JDBCLOOKUP function is updated as displayed in the following image:

Top of page

JDBC Connection Information

JDBC connection details are saved within the CustomerExtensions tag in the project's template file. For example:

<CustomerExtensions>
     …
     <JDBCConnections>
     <JDBCConnection name="mysql_iway" >
        <Parameter value="com.mysql.jdbc.Driver"  name="Driver" />
          <Parameter value="jdbc:mysql://localhost:3306/iway"  name="Url" />
        <Parameter value="root"  name="Username" />
        <Parameter value="ENCR(3109311731043128313832252993153)"
         name="EncryptPassword" />
        <Parameter value="0"  name="Timeout" />
     </JDBCConnection>
     …
     </JDBCConnections>	
</CustomerExtensions>

Additional features:

The Encrypt Password option is supported.
If the Timeout parameter value is not defined or 0, the timeout interval is evaluated to less than one millisecond (immediately).
If the connection pool is full, a thread continuously checks for an available connection (according to the specified timeout setting interval) before checking if there are any released connections in the connection pool.
Multiple JDBC connections can be defined for one project.

Top of page

Logging and Error Handling

This section lists and describes logging and error handling scenarios for the @JDBCLOOKUP function.

If no result is found in the database, the @JDBCLOOKUP function returns an empty string.
The No data found ... debugging message containing SQL statement details will be logged if debugging is enabled.
If more than one result is found in the database, the @JDBCLOOKUP function returns the last result.
The More than one... debugging message containing SQL statement details will be logged if debugging is enabled.
If an error is encountered during the connection process, the TRANFORM_FUNCTION_ERROR message will be inserted into the output document instead of the @JDBCLOOKUP function’s value. Detailed debugging messages will be logged if debugging is enabled.

Top of page

JDBC Connection Pooling

The transformation engine uses its own JDBC connection pooling facility to handle multiple JDBC lookup calls within one project or multi-threaded environment. Typically, a single JDBC data source configuration is represented by one reusable connection. After each evaluation of the @JDBCLOOKUP function, this connection is returned to the connection pool and becomes available for reuse. The timeout settings for the database determine when connections within the connection pool are released. In the context of iWay Service Manager, connections within the connection pool are released when iWay Service Manager is restarted, or when the database is restarted.

In a scenario where complex SQL statements are required to run using multiple threads, each thread issues a request to the connection pool for an available connection upon execution of the @JDBCLOOKUP function. If no connection is available within the specified timeout settings for the JDBC data source, a new connection is created for that JDBC data source. This connection is added to the connection pool and is used to run the SQL statement by the given thread.

Top of page

JDBC Connection Pooling Limitations

If the JDBC connection pool is full and no connections are available, the thread continuously checks for an available connection (as specified within the timeout setting interval for the JDBC data source). Therefore, you may need to increase the size of your connection pool for consistent performance in a multi-threaded environment. This recommendation also applies if you need to evaluate multiple JBDC lookup statements with multiple JDBC data sources.

Currently, the maximum connection pool size is set to 10. This value can be changed by editing the JDBCDataSource_en_US.properties file (where en indicates the English locale for example), which is located inside the iWay Transformer .jar file. You need to change the value for the transform.jdbcDataSource.maxConnectionPoolSize setting, which is set as follows by default:

transform.jdbcDataSource.maxConnectionPoolSize=10