About JDBC/ODBC Drivers

Drivers are uniquely different in what they do and the type of functions they support. The JDBC/ODBC Adapter allows you to pick and choose which driver is best suited for your application environment. There can be significant differences and limitations between drivers. The performance and functionality of the JDBC/ODBC Adapter depends on the selected driver(s). Certain drivers may not support all JDBC features. Consult the documentation for your respective drivers for more information.

While any standards compliant JDBC/ODBC database driver may be used, the drivers covered in this chapter are used more frequently. For runtime, only drivers that support Connection Pool Data Source and XA Data Source are supported. Connection Pool Data Source takes advantage of the Integration Service’s connection pooling in order to improve performance. For the OTD Wizard, the driver Manager Class will work. However, not all drivers support all metadata discovery methods, some of which are needed to build the OTD. Additionally, not all drivers support Updatable ResultSets, Stored Procedures, or Stored Procedures with ResultSets. Check with your driver vendor for what is supported. The ConnectionPoolDataSource should only be used for Outbound Adapters. The Inbound Adapter uses native JDBC and must use Driver Manager.

It is recommended that you use the Oracle Adapter when using the native Oracle driver. The JDBC Adapter does not support some of the functions available in the Oracle Adapter such as creating an OTD from a Prepared Statement, using a Stored Procedure with ResultSets, and CLOB support.

It is also recommended that you use the SQL Server Adapter. The JDBC driver available for download from the Microsoft web site may not contain the latest version from the vendor.

Not all drivers support updateable ResultSets. However, it does allow standard Insert and Update operations when used with the Prepared Statement feature:

Insert into employee (empno) values(?);

Remember to ensure that the input parameter data types match the data types specified in the database table targeted by the Prepared Statements as some drivers always return the data type as a string. Optionally, you may perform the data conversion in the Collaboration.