The JDBC Connector provides database access to a variety of systems. To reach a system using JDBC we need a JDBC driver from the system provider. This provider is typically delivered with the product in a jar or zip file. These files must be in your path or copied to the jars/ directory of our TDI installation; otherwise you may get cryptic messages like "Unable to load T2 native library", indicating that the driver was not found on the classpath.
We will also need to find out which of the classes in this jar or zip file implements the JDBC driver; this information goes into the JDBC Driver parameter.
The JDBC Connector also provides multi-line input fields for the SELECT, INSERT, UPDATE and DELETE statements. When configured, the JDBC connector will use the value for any of these instead of its own auto-generated statement. The value is a template expanded by the parameter substitution module that yields a complete SQL statement. The template has access to the connector configuration as well as the searchcriteria and conn objects. The work object is not available for substitution, since the connector does not know what work contains. Additional provider parameters are also supported in the connector configuration.
The JDBC Connector supports the following modes: AddOnly, Update, Delete, Lookup, Iterator, Delta.
This Connector in principle can handle secure connections using the SSL protocol; but it may require driver-specific configuration steps in order to set up the SSL support. Refer to the manufacturer's driver documentation for details.
The JDBC connector makes a connection to the specified data sources during the connector initialization. While making a connection to the specified data source extra provider parameters are checked for, and set if they are specified. The auto-commit flag setting is also handled and set during connection initialization.
The JDBC connector builds SQL statements internally using a predefined mapping table. The connector flow behaves the same way as other connectors in AddOnly, Update, Delete, Iterator and Lookup modes.
In addition, this Connector supports Delta mode; the delta functionality for the JDBC connector is handled by the ALComponent (a generic building block common to all Connectors). The ALComponent will do a lookup and apply the delta Entry to a target Entry before doing an update, and then decide what the correct database operation must be. The Connector will then use the SQL statements for add, modify or delete, corresponding to what the operation is.
In order for the JDBC Connector to access a relational database, it needs to access a driver, a set of subroutines or methods contained in a Java classlibrary. This library must be present in the CLASSPATH of TDI, otherwise TDI will not be able to load the library when initializing the Connector, and hence be unable to talk to the Relational Database (RDBMS). A good way to install a JDBC driver library such that TDI can use it is to copy it into the TDI_install_dir/jars directory, or a directory of the choosing subordinate to this, for example TDI_install_dir/jars/local.
Notes:
There are 4 fundamental ways of accessing an RDBMS through JDBC (these are often referred to as driver types):
To configure ODBC, see Specifying ODBC database paths.
The JDBC-ODBC bridge may be present in any of the different platform-dependent JVM's that IBM ships with the product. However, IBM supports the JDBC-ODBC bridge on Windows platforms only. In addition, performance is likely to be sub-optimal compared to a dedicated, native ("Type 4") driver. Commercial ODBC/JDBC bridges are available. If we need an JDBC-ODBC bridge, consider purchasing a commercially available bridge; see also the JDBC-ODBC bridge drivers discussion at http://java.sun.com/products/jdbc/driverdesc.html..
With the exception of the JDBC-ODBC bridge on Windows, we only use Type 4 drivers with IBM TDI. We will discuss other types as well--in the context of each of the supported databases--for a better understanding.
JDBC Type 3 and Type 4 drivers use a network protocol to communicate to their back-ends. This usually implies a TCP/IP connection; this will either be a straight TCP/IP socket, but if the driver supports it, it can be a Secure Socket Layer (SSL) connection.
When working with custom prepared statements, make sure that the JDBC used driver is compliant with JDBC 3.0. There is a known issue with IBM SolidDB 6.5, since the driver implements only JDBC 2.0. If the Use custom SQL prepared statements option is enabled when working with this database, a java.lang.NullPointerException will be thrown.
The IBM driver for JDBC and SQLJ bundled with TDI was obtained from http://www-306.ibm.com/software/data/db2/java. It is JDBC 1.2, JDBC 2.0, JDBC 2.1 and JDBC 3.0 compliant.
Information about the JDBC driver for IBM DB2 is available online; a starting point and example for configuration purposes is the section on "How JDBC applications connect to a data source" in the DB2 Developer documentation. This driver may or may not suit your purpose.
Driver Licensing
This driver does not need further licensing for DB2 database systems (that is, the appropriate license file, db2jcc_license_cu.jar is already included), except DB2 for z/Series and iSeries. In order for the driver to be able to communicate with the latter two systems you would need to obtain the DB2 Connect product, and copy its license file, db2jcc_license_cisuz.jar, to the jars/3rdparty/IBM directory. In addition, since this driver is a FAT client with natively compiled code (.dll/.so), the DB2 Connect install path needs to be added to the PATH variable for these libraries to be used.
Based on the JDBC driver architecture DB2 JDBC drivers are divided into four types.
This is an DB2 ODBC (not JDBC) driver, which you connect to using a JDBC-ODBC bridge driver. This driver is essentially not used anymore.
A JDBC Type 1 driver can be used by JDBC 1.2 JDBC 2.0, and JDBC 2.1.
To configure ODBC, see Specifying ODBC database paths.
The DB2JDBC Type 2 driver is quite popular and is often referred to as the app driver. The app driver name comes from the notion that this driver will perform a native connect through a local DB2 client to a remote database, and from its package name (COM.ibm.db2.jdbc.app.*).
In other words, we have to have a DB2 client installed on the machine where the application that is making the JDBC calls runs. The JDBC Type 2 driver is a combination of Java and native code, and will therefore usually yield better performance than a Java-only Type 3 or Type 4 implementation.
This driver's implementation uses a Java layer that is bound to the native platform C libraries. Programmers using the J2EE programming model will gravitate to the Type 2 driver as it provides top performance and complete function. It is also certified for use on J2EE servers.
The implementation class name for this type of driver is com.ibm.db2.jdbc.app.DB2Driver.
The JDBC Type 2 drivers can be used to support JDBC 1.2, JDBC 2.0, and JDBC 2.1.
The JDBC Type 3 driver is a pure Java implementation that must talk to middleware that provides a DB2 JDBC Applet Server. This driver was designed to enable Java applets to access DB2 data sources. An application using this driver can talk to another machine where a DB2 client has been installed.
The JDBC Type 3 driver is often referred to as the net driver, appropriately named after its package name (COM.ibm.db2.jdbc.net.*).
The implementation class name for this type of driver is com.ibm.db2.jdbc.net.DB2Driver.
The JDBC Type 3 driver can be used with JDBC 1.2, JDBC 2.0, and JDBC 2.1.
The JDBC Type 4 driver is also a pure Java implementation. An application using a JDBC Type 4 driver does not need to interface with a DB2 client for connectivity because this driver comes with Distributed Relational Database Architecture™ Application Requester (DRDA AR) functionality built into the driver.
The implementation class name for this type of driver is com.ibm.db2.jcc.DB2Driver.
The latest version of this driver (9.1) supports SSL connections; this requires setting a property in the Extra Provider Parameters field. For more information see http://publib.boulder.ibm.com/infocenter/db2luw/v9/topic/com.ibm.db2.udb.apdv.java.doc/doc/rjvdsprp.htm. Note that the target database must be set up such that it accepts incoming SSL connections.
If you are running DB2 on a z/OS platform, and the database is not configured correctly with the required stored procedure for retrieving the schema, you might encounter some problems using the JDBC Connector. If the JDBC Connector's query schema throws an exception, or the Add/Update action on JDBC tables fails for BLOB data types, contact your database administrator and request that the required stored procedure for retrieving the schema be installed. For more information about accessing DB2 from Java, see also Overview of Java Development in DB2 UDB for Linux, UNIX, and Windows.
If you install the Informix Client SDK, we will also install Informix ODBC drivers which allow you to use a JDBC-ODBC bridge driver. This driver is not recommended for production use. To configure ODBC, see Specifying ODBC database paths.
However, we recommend we use the Informix JDBC driver, version 3.0. It is a pure-Java (Type 4) driver, which provides enhanced support for distributed transactions and is optimized to work with IBM WebSphere Application Server.
It consists of a set of interfaces and classes written in the Java programming language. Included in the driver is Embedded SQL/J which supports embedded SQL in Java.
The implementation class for this driver is com.informix.jdbc.IfxDriver. For information how to install the Informix driver, see http://publib.boulder.ibm.com/infocenter/idshelp/v111/index.jsp?topic=/com.ibm.conn.doc/jdbc_install.htm
Based on the JDBC driver architecture the following types of drivers are available from Oracle.
This is an Oracle ODBC (not JDBC) driver, that you connect to using a JDBC-ODBC bridge driver. Oracle does supply an ODBC driver, but does not supply a bridge driver. Instead, you can use the default JDBC-ODBC bridge that is part of the JVM, or get one of the JDBC-ODBC bridge drivers from http://java.sun.com/products/jdbc/drivers.html. This configuration works fine, but a JDBC Type 2 or Type 4 driver will offer more features and will be faster.
To configure ODBC, see Specifying ODBC database paths.
There are two flavors of the Type 2 driver.
This driver uses Java native methods to call entrypoints in an underlying C library. That C library, called OCI (Oracle Call Interface), interacts with an Oracle database. The JDBC OCI driver requires an Oracle client installation of the same version as the driver. The use of native methods makes the JDBC OCI driver platform specific. Oracle supports Solaris, Windows, and many other platforms. This means that the Oracle JDBC OCI driver is not appropriate for Java applets, because it depends on a C library. Starting from Version 10.1.0, the JDBC OCI driver is available for installation with the OCI Instant Client feature, which does not require a complete Oracle client-installation. Please refer to the Oracle Call Interface for more information.
This driver uses Java native methods to call entrypoints in an underlying C library. That C library is part of the Oracle server process and communicates directly with the internal SQL engine inside Oracle. The driver accesses the SQL engine by using internal function calls and thus avoiding any network traffic. This allows your Java code to run on the server to access the underlying database in the fastest possible manner. It can only be used to access the same database.
Again, there are two flavors of the Type 4 driver.
This driver uses Java to connect directly to Oracle. It implements Oracle's SQL*Net Net8 and TTC adapters using its own TCP/IP based Java socket implementation. The JDBC Thin client-side driver does not require Oracle client software to be installed, but does require the server to be configured with a TCP/IP listener. Because it is written entirely in Java, this driver is platform-independent. The JDBC Thin client-side driver can be downloaded into any browser as part of a Java application. (Note that if running in a client browser, that browser must allow the applet to open a Java socket connection back to the server.)
This is the most commonly-used driver. In general, unless we need OCI-specific features, such as support for non-TCP/IP networks, use the JDBC Thin driver.
The implementation class for this driver currently is oracle.jdbc.driver.OracleDriver.
This driver uses Java to connect directly to Oracle. This driver is used internally within the Oracle database, and it offers the same functionality as the JDBC Thin client-side driver, but runs inside an Oracle database and is used to access remote databases. Because it is written entirely in Java, this driver is platform-independent. There is no difference in your code between using the Thin driver from a client application or from inside a server.
For more information about accessing Oracle from Java, see also Java, JDBC & Database Web Services, and the Oracle JDBC FAQ.
The Microsoft SQL Server 2005 driver for JDBC supports the JDBC 1.22, JDBC 2.0 and JDBC 3.0 specification. It is a Type 4 driver.
The implementation class for this driver is com.microsoft.sqlserver.jdbc.SQLServerConnection. It is contained in the driver file sqljdbc.jar, typically obtained from the MS SQL Server 2005 installation, at <Microsoft SQL Server 2005-Install-Dir>\sqljdbc_1.1.1501.101_enu\sqljdbc_1.1\enu\sqljdbc.jar.
We can also use other third party drivers for connecting to Microsoft SQL Server.
The jTDS JDBC 3.0 driver distributed under the GNU LGPL is a good choice. This is a Type 4 driver and supports Microsoft SQL Server 6.5, 7, 2000, and 2005. jTDS is 100% JDBC 3.0 compatible, supporting forward-only and scrollable/updateable ResultSets, concurrent (completely independent) Statements and implementing all the DatabaseMetaData and ResultSetMetaData methods. It can be downloaded freely from http://jtds.sourceforge.net. More information about this driver is available from the Web site.
The jConnect for JDBC driver by Sybase provides high performance native access (Type 4) to the complete family of Sybase products including Adaptive Server Enterprise, Adaptive Server Anywhere, Adaptive Server IQ, and Replication Server.
jConnect for JDBC is an implementation of the Java JDBC standard; it supports JDBC 1.22 and JDBC2.0, plus limited compliance with JDBC 3.0. It provides Java developers with native database access in multi-tier and heterogeneous environments. We can download jConnect for JDBC quickly, without previous client installation, for use with thin-client Java applications - like IBM TDI.
The implementation class name for this driver is com.sybase.jdbc3.jdbc.SybDriver.
We can also use other third party drivers for connecting to Sybase.
The jTDS JDBC 3.0 driver distributed under the GNU LGPL is a good choice. This is a Type 4 driver and supports Sybase 10, 11, 12 and 15. jTDS is 100% JDBC 3.0 compatible, supporting forward-only and scrollable/updateable ResultSets, concurrent (completely independent) Statements and implementing all the DatabaseMetaData and ResultSetMetaData methods. It can be downloaded freely from http://jtds.sourceforge.net. More information about this driver is available from the Web site.
Derby is a relational database, modeled after IBM DB2, written entirely in Java™. This database product as well as its drivers are bundled with TDI. The network driver is a Type 4 driver: native Java code.
The implementation class name for this driver is org.apache.derby.jdbc.ClientDriver.
Refer to the Derby Developer's Guide, Conventions for specifying the database paths, for more information about how to construct your JDBC URLs when using Derby.
IBM SolidDB is a relational in-memory database that offers enhanced performance compared to Derby. Thus, it can be used as System Store instead of the Derby database, to boost the performance of the components relying on it.
The driver provided by IBM SolidDB is Type 4 (completely implemented in Java). It can be obtained from the database installation, from SolidDB_install_dir/jdbc/SolidDriver2.0.jar.
Detailed information on SolidDB can be found at http://publib.boulder.ibm.com/infocenter/soliddb/v6r3/index.jsp.
The driver for IBM SolidDB is not JDBC 3.0 compliant, but implements JDBC 2.0 only. This may cause problems if we use Custom Prepared Statements.
When we use ODBC connectivity using the JDBC-ODBC bridge (supported on Windows systems only) we can specify a database or file path the ODBC driver must use, if the ODBC driver permits. This type of configuration avoids having to define a data source name for each database or file path your Connector uses.
The syntax of this parameter is dependent on the following conditions:
jdbc:odbc:MS Access Database;dbq=C:\Documents and Settings\username\My Documents\mydb.mdb
jdbc:odbc:Driver={MS Access Driver
(*.mdb)};dbq=C:\Documents and Settings\username\My Documents\mydb.mdb
Alternatively, use the Windows System DSN utility, available under Administrative Tools -> Data Sources (ODBC). Once you define a System DSN, use a jdbcSource parameter like the this:
jdbc:odbc:myDSNNameHere
Check the Driver list that you get in the utility. Your JDBC URL must exactly match the wording found in this list.
In Iterator and Lookup modes the JDBC Connector schema depends on the metadata information read from the database for the table name specified. If no table name is given the schema is retrieved using the SQL Select/Lookup statements (if defined; see Customizing select, insert, update and delete statements).
In AddOnly, Delete, Update and Delta the JDBC Connector schema depends on the metadata information read from the database for the table name specified.
The Connector needs the following parameters:
| RDBMS | Example connection URL |
|---|---|
| IBM DB2 (using the DRDA driver) | "jdbc:db2://hostname:port/dbname" |
| Informix Dynamic Server 10.0 | "jdbc:informix-sqli://hostname:port/dbname:informixserver=<Informix Server Name>" |
| Oracle (using the "thin driver") | "jdbc:oracle:thin:@hostname:1521:SID", using "host:port:sid" syntax, TNSListener accepting connections on port 1521 |
| Microsoft SQL Server (using Microsoft's driver) | "jdbc:sqlserver://hostname:1433;databasename=dbname;", SQL Server listening for connections on port 1433 |
| Sybase 15 (also older versions from v. 10), using jConnect 6.05 | "jdbc:sybase:Tds:hostname:port/" |
| Derby | "jdbc:derby://hostname:port/<server path to database>;options" |
| IBM SolidDB 6.3 | "jdbc:solid://hostname:port" |
| RDBMS | Driver implementation class name |
|---|---|
| IBM DB2, type 2 or 4 | com.ibm.db2.jcc.DB2Driver |
| Oracle, type 4 | oracle.jdbc.driver.OracleDriver |
| Informix Dynamic Server 10.0 | com.informix.jdbc.IfxDriver |
| Microsoft SQL Server, type 4 | com.microsoft.sqlserver.jdbc.SQLServerDriver |
| Sybase 15 (also older versions from v. 10) | com.sybase.jdbc3.jdbc.SybDriver |
| Derby | org.apache.derby.jdbc.ClientDriver |
| IBM SolidDB 6.3 | solid.jdbc.SolidDriver |
Throughout the TDI documentation, we will find the term Schema used to mean the data definition of the object you are accessing. However, in the RDBMS world, the term Schema has a different meaning, namely the overall collection of data definitions, tables and objects grouped under one identifier (username). For this particular parameter in this particular Connector, we use it in the RDBMS sense.
The option After every database operation (Including Select) has been provided for those databases which lock database tables in transactions even when they only have been Selected for read operations (notably DB2).
The button marked "..." to the right of the SQL Select parameter presents a Link Criteria dialog where we can fill out the link criteria form and generate the proper SQL Where clause.
Use the Add button to add more rows to build your selection criteria. The Match any checkbox will generate an OR expression rather than the default AND expression. Note that this is a one way helper: anything you already have in the SQL select parameter will be replaced by the generated expression. If the SQL select parameter contains a "where" clause, then only the Where-clause is replaced.
"SET NLS_FORMAT 'YYYY-MM-DD'"
securityMechanism:KERBEROS_SECURITY loginTimeout:20 readOnly:true
The Auto-create table option will make the connector create a simple table based on the attribute map and schema for the connector. This is only done when the table does not exist in the database.
When auto-creating a table the connector will first derive the column names from the attribute map. If the attribute map is empty, the schema is used to get the list of column names. Once the column names are determined a SQL CREATE TABLE statement is generated with each of the column names. If the schema has a definition for the column name it will be consulted to determine the syntax for the column. There are two parts in the schema that will determine the syntax for the column. First, if the "Native Syntax" is specified it is used as-is. Next, if there is no native schema provided the connector uses the "Java Class" to derive the syntax. The Java-class field in the schema should specify any of the following values:
| Value | Generated SQL type |
|---|---|
| Integer or java.lang.Integer | INT |
| String or java.lang.String | VARCHAR(255) |
| Double or java.lang.Double | DOUBLE |
| Date or java.util.Date | TIMESTAMP |
If there is no schema information about the column, or if the value is not recognized the connector will use "VARCHAR(255)" in the generated create table statement.
{ignoreFieldErrors}
If getting field values causes an error, this flag causes the Connector to return the Java exception object as the value instead of throwing the exception (that is, calling the Connectors *Fail EventHandlers).
Link criteria specified in the Connector's configuration for Lookup, Delete, Update and Delta modes are used to specify the WHERE clause in the SQL queries used to interact with the database.
The TDI operand Equal is translated to the equal sign ( = ) in the SQL query, while the Contains, Start With and End With operators are mapped to the like operator.
The JDBC Connector supports the Skip Lookup general option in Update or Delete mode. When it is selected, no search is performed prior to actual update and delete operations. Special code in the Connector retrieves the proper number of entries affected when doing update or delete.
The JDBC connector has the ability to expand a SQL template before executing any of its SQL operations. There are five operations where the templates can be used. These operations are:
| Operation | Connector Parameter name | Description | Mode(s) |
|---|---|---|---|
| SELECT | SQL Select | Used in Iterator mode (no search criteria). | Iterator |
| INSERT | SQL Insert | Used when adding an entry to the data source. | Update, AddOnly, Delta |
| UPDATE | SQL Update | Used when modifying an existing entry in the data source. | Update, Delta |
| DELETE | SQL Delete | Used when deleting an existing entry in the data source. | Delete, Delta |
| LOOKUP | SQL Lookup | A SELECT statement with a WHERE clause. Used when searching the data source. | Lookup, Delete, Update |
If the template for a given operation is not defined (for example, null or empty), the JDBC connector will use its own internal template.
When there is a template defined for an operation, the template must generate a complete and valid SQL statement. The template can reference the standard parameter substitution objects (for example, mc, config, work, Connector), as well as the JDBC schema for the table configured for the connector and a few other convenience objects.
The template for the LOOKUP operation can contain a WHERE clause filtering the elements that will be returned by the query. But when the connector is in Lookup, Update or Delete mode the Link Criteria parameter is mandatory, as it is used to assemble a WHERE clause for the executed query. If Link Criteria is omitted an exception will be thrown:
java.lang.Exception: CTGDIS143E No criteria can be built from input (no link criteria specified).
at com.ibm.di.server.SearchCriteria.buildCriteria(Unknown Source) Therefore
if a configuration is created and it uses a WHERE clause in the LOOKUP
template the provide a Link criteria although one
will not be needed. The connector will simply ignore it and the template
query will be used. In order to save you from adding unneeded "dummy"
conditions in the Link criteria,the solution is to check
the option Build criteria from custom script and
leave the displayed script area empty.
The information about JDBC field types is provided as an Entry object named metadata. Each attribute in the metadata Entry object corresponds to a field name and the value will be that field's corresponding type. For example, a table with the following definition:
CREATE TABLE SAMPLE (
name varchar(255), age numeric(10), )
could be referenced in the following manner, during parameter substitution:
{javascript<<EOF
metadata = params.get("metadata");
if (metadata.getAttribute("name").equals("varchar"))
return "some sql statement";
else
return "some other sql statement";
EOF
}
The LinkCriteria values are available in the link object. The link object is an array of link criteria items. Each item has fields that define the link criteria according to configuration. If the configured link criteria is defined as cn equals john doe then the template could access this information with the following substitution expressions:
link[0].name > "cn" link[0].match > "=" link[0].value > "john doe" link[0].negate > false
A complete template for a SELECT operation could look like this:
SELECT * FROM {config.jdbcTable} WHERE {link[0].name} = '{link[0].value}'
Generating the WHERE clause or the list of column names is not easy without resorting to JavaScript™ code. As a convenience, the JDBC Connector makes available the column names that would have been used in an UPDATE and INSERT statement as columns; this does not apply to SELECT and LOOKUP statements. This value is a comma-delimited list of column names. The textual WHERE clause is available as "whereClause" to simplify operations. Below is an example of how to use both:
SELECT {columns} from {config.jdbcTable} WHERE {whereClause}
for example, SELECT a,b,c from TABLE-A WHERE a > 1 AND b = 2
| Object | SELECT | LOOKUP | INSERT | DELETE | UPDATE |
|---|---|---|---|---|---|
| config | yes | yes | yes | yes | yes |
| Connector | yes | yes | yes | yes | yes |
| metadata | no | maybe | maybe | yes | yes |
| conn | no | no | yes | yes | yes |
| columns | no | no | yes | yes | yes |
| link | no | yes | no | yes | yes |
| whereClause | no | yes | no | yes | yes |
The JDBC connector uses PreparedStatement to efficiently execute an SQL statement on a connected RDBMS server. However, there maybe cases when the JDBC driver may not support PreparedStatements. As a fall back mechanism a config parameter (jdbcPreparedStatement, labelled Use prepared statements in the configuration panel) is available in the configuration of the JDBC connector. The config parameter is a Boolean flag that indicates whether the JDBC connector should use PreparedStatements. If this is set the connector will use PreparedStatement and will fall back to normal Statements (java.sql.Statement) in case of an exception. If this is not set, normal Statement will be used by the JDBC connector while executing SQL queries. This checkbox gives an option to a TDI solution developer to handle situations when there are problems due to use of PreparedStatements. The checkbox will be set by default, meaning that the JDBC connector will use PreparedStatement.
The findEntry, putEntry, deleteEntry and
the modEntry methods of the JDBC connector check for the
value of usePreparedStatement flag to determine whether to use PreparedStatements
or Statements. If a connector config does not have this flag (as
in an older version of the config), the value of this param will be true by
default. This ensures that there are no migration issues or impact.
When we use the JDBC connector without custom SQL statements, it uses Prepared Statements internally for
faster access to the JDBC target database. The connector builds simple
SQL prepared statements to perform the connector operations (for example, SELECT
* from TABLE WHERE x = ?) and then uses the JDBC API to provide
values for the placeholders (the question marks) in the statement.
This makes it easy to provide a variety of Java objects to the database
without having to do complex string encoding of values.
Every now and then, the user needs to override the SQL statements
the JDBC connector creates. This is where the SQL Insert/Update/etc.
configuration parameters come into play. The user can specify the
exact SQL statement used for a specific operation. The SQL statement
is provided to the JDBC driver as a standalone statement, which basically
means that the SQL statement contains values for columns used in the
statement. This also means that the user must build the statement
including values for columns in the configuration itself, including
any complex string encoding of values.
With the custom prepared statements feature, the user can now use
proper prepared statements. This will make custom statements much
easier since it removes the encoding requirement. Also, if the statement
does not change between calls, the prepared statement is reused, which
results in faster execution.
The JDBC connector has a parameter named Use custom prepared
statements. The checkbox is to enable/disable prepared statements
and is false by default. When this checkbox is enabled use
proper syntax in the custom SQL field. While we can still use constants
in the SQL statement either properly escape any question
marks in the statement or provide an expression for the prepared statement
placeholder. Type "?" or use ctrl-<space> to bring up the
code completion helper that lets us choose from the list of attributes
we have in the output map as well as other common expressions. Once
we have chosen an expression and press Enter the
editor will insert that string between two question marks. Also note
the syntax highlights that will provide feedback as to what is being
interpreted as a placeholder expression:
Note that it is also possible to use expressions in the statement
where you normally don't have placeholders. Prepared Statements can
also be provided by means of an API; sometimes this may be the easiest
option to use. See APIs to allow specification of Prepared Statements for more information.
If we use the JDBC Connector with a JDBC 2.0 driver, in particular
with IBM solidDB, also see Connecting to IBM SolidDB.
Background
To more fully explain the need for the above functionality, consider
the following:
The current format for custom SQL statements requires the user
to enter a complete SQL statement. Often, this can be tedious as well
as near impossible if the values are complex or binary in nature.
An option is present in the JDBC connector (Use custom prepared
statements) where the user can toggle between Prepared Statement
mode and the current plain string mode. Prepared statement mode applies
a different syntax to the custom SQL statements. When prepared statement
mode is chosen, no substitution is done to the string as is the case
when non prepared statement mode is selected.
As an example let's use a simple SELECT statement to illustrate
the problem with building a complete SQL statement:
This
statement contains a where clause filtering on modifed_date being
greater than a given date. This example shows the problematic nature
of SQL statements: Is the date march 3rd 2009 or april 4th 2009? There
are other cases where building a complete SQL statement becomes even
more problematic.
With the Use custom prepared statements option, the user
can use a slightly modified SQL prepared statement. A SQL prepared
statement in JDBC terms is a complete SQL statement with placeholders
for values. The placeholder is the question mark and is replaced with
a value at runtime.
However, the JDBC connector needs to know which value to provide for each placeholder.
To make the prepared statement as syntactically correct as possible
while also providing the ability to specify which values are provided
at runtime, the prepared statement syntax is slightly modified:
This
is not a valid prepared statement syntax, but the JDBC connector will
parse this string and replace "{expression}" with a single question
mark before executing the statement. The "{expression}" is a Tivoli
Directory Integrator expression that provides the value for the prepared
statement placeholder. The text field editors for the custom SQL statements
provide additional functionality to aid the user in building the statement.
When custom prepared statements are used, the user
must also provide the WHERE clause where applicable.
Apart from the standard functions exposed by all Connectors, this Connector also exposes several other functions we can use in
your scripts. We could call them using the special variable thisConnector, for example, thisConnector.commit(); -- when called
from any scripting location in the Connector.
The Connector's Table
Name parameter must be empty for this to work correctly.
The above functions do not interfere with the normal flow of entries
and attribute mappings for the Connector.
The above subsections describe how the JDBC Connector has access
to its parameter substitution capabilities in insert, update, and
delete SQL commands that are executed by the Connector. In certain
cases, this causes issues because your customized SQL can end up with
substrings (starting with a "{" and ending with a "}") that will be
acted upon by the parameter substitution mechanism, and should not.
The JDBC Connector exposes an API so we can disable or enable parameter
substitution for the SQL statements that will be executed by the JDBC
Connector.
An alternative to using this API to avoid unwanted parameter substitution
is using escape characters.
The escape character is a "\". If a "\" is encountered in the
character directly preceding a {ArgumentIndex} and {TDIReference}
(that is, \{ArgumentIndex}and \{TDIReference}), then the parameter
substitution will not take place (will not be processed). Instead, the escape character will be removed and the parameter substitution
will not occur. For example, \{TDIReference} would simply be {TDIReference}
after being processed.
For power-users, it may be easier to just use an API to specify
the correct Prepared Statement that they would like to use and also
all the values that should be used. The following new methods have
been added to the JDBCConnector:
With
these methods, the user can have code like this to for example do
a special select:
The Javadocs for the methods give
more examples.
If we want to store a timestamp value containing both a date and
a time, make sure you provide an object of type java.sql.Timestamp, as we can with this Attribute Mapping:
The java.sql.Timestamp type can also come in handy if
for some reason storing DATE fields in tables causes trouble, for
example the Oracle error ORA-01830: date format picture
ends before converting entire input string. Normally, if you
try to store date/time values which are in the form of strings, the Date Format parameter comes into play to convert
the string into the DATE type the underlying database expects, and
if there is a mismatch between this parameter and your date/time value
formatted as a string, problems will ensue.
To troubleshoot your problem:
Traditionally, the JDBC Connector would pad data to be added in
the CHAR datatype column if the length of data was less than the column
width. This was the default behavior and there was no option for configuring
the padding.
With the advent of the UTF-8 character set, this could result
in unexpected behavior since the Connector was not able to determine
the exact length of UTF-8 data. This, in turn, resulted in the adding
of an indeterminate amount of whitespaces, and the data length became
bigger than the column width which resulted in an exception thrown
from the database.
To get around this problem we provide us with the option of optionally
disable padding for various operations performed by the Connector, namely Insert, Update and Lookup operations, in AddOnly, Lookup, Update, Delete and Delta modes. See the Configuration section
for the parameters selecting this functionality.
For UTF-8 data the padding should be disabled. For Latin-1 characters
the padding can be enabled or disabled.
The JDBC Connector's "getConnection()" method gives you access
to the JDBC Connection object created when the connector has successfully
initialized.
In other words, if your JDBC connector is named DBconn in your
AL,
will
give you access to the JDBC Connection object (an instance of java.sql.Connection).
When called from anywhere inside the connector itself, you
can also use the thisConnector variable.
Here is a code example illustrating how we can invoke a stored
procedure on that database:
If we have columns with special characters in their names and
use the AddOnly or Update modes:
The necessity of using this functionality might be dependent on
the JDBC driver you are using, but standard MS Access 2000 has this
problem.
This section describes how the Connector creates SQL queries. You
can skip this section unless you are curious about the internals.
For a database, the Connector uses prepared statements or dynamic
query depending on the situation:
See Appendix B. AssemblyLine Flow Diagrams for more information about what
happens when a Connector has a link criteria returning multiple entries.
For the JDBC Connector in Delete or Update mode, if we have used
the setCurrent() method of the Connector and not added extra logic, all entries matching the link-criteria are deleted or updated.
The JDBC Connector takes advantage of the Reconnect engine that
is part of IBM TDI 7.1. In addition to the standard behavior this
engine provides, the JDBC Connector has a number of additional built-in
rules. The Connector specific built-in rules will perform a reconnect
if a java.sql.SQLException is thrown and the exception contains the
following messages, evaluated using Regular Expressions:
These rules are visible in the Connection
Errors pane in the Connector's configuration.
Custom Prepared Statements
Select * from table where modified_date > ?{javascript return new java.util.Date()}
and something_else < ?{conn.a}
SELECT * FROM TABLE_NAME WHERE modified_date > 03/04/09
SELECT * FROM TABLE_NAME WHERE modified_date > ?
SELECT * FROM TABLE_NAME WHERE modified_date > {expression}
Additional JDBC Connector functions
API to disable or enable parameter substitution
/**
* set enableParamSubstitute parameter
*
*
*/
public void setParameterSubstitution(boolean val)
{
enableParamSubstitute = val;
}
/**
* Returns value of enableParamSubstitute parameter
*
*
*/
public boolean getParameterSubstitution()
{
return enableParamSubstitute ;
}
APIs to allow specification of Prepared Statements
public PreparedStatement setPreparedModifyStatement(String preparedSql)
public PreparedStatement setPreparedDeleteStatement(String preparedSql)
public PreparedStatement setPreparedInsertStatement(String preparedSql)
public PreparedStatement setPreparedFindStatement(String preparedSql)
public PreparedStatement setPreparedSelectStatement(String preparedSql)
ps = thisConnector.connector.setPreparedSelectStatement("Select * from tableName where fieldName = ? and field2= ?")
ps.setInteger(1, someValue)
ps.setObject(2, someObject)
Timestamps
ret.value = java.sql.Timestamp(java.util.Date().getTime());
ret.value = java.sql.Timestamp(java.util.Date().getTime());
and
see if this helps. If it does, then we will be able to use
ret.value = java.sql.Timestamp(system.parseDate(work.getString("yourDate"), "yyyyMMddHHmmssz").getTime());
Padding
Calling Stored Procedures
var con = DBconn.getConnector().getConnection();
// Stored procedure call
command = "{call DBName.dbo.spProcedureName(?,?)}";
try {
cstmt = con.prepareCall(command);
// Assign IN parameters (use positional placement)
cstmt.setString(1, "Christian");
cstmt.setString(2, "Chateauvieux");
cstmt.execute();
cstmt.close();
// TDI will close the connection, but you might want to force a close now.
DBConn.close();
}
catch(e) {
main.logmsg(e);
}
SQL Databases: column names with special characters
Using prepared statements
On Multiple Entries
Additional built-in reconnect rules
See also