Chapter 1 Configuring JDBC/ODBC Drivers
This topic provides detailed information on configuring JDBC/ODBC drivers for your database or external system connection. It includes the following sections:
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 eWay when using the native Oracle driver. The JDBC eWay does not support some of the functions available in the Oracle eWay 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 eWay. The JDBC driver available for download from the Microsoft web site may not contain the latest version from the vendor.
Not all drivers support Updatable 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.
Configuring JDBC/ODBC Drivers
This document provides database configuration information and environment properties specifications for specific JDBC/ODBC drivers. You should use the information listed in the included tables to define values for required input parameters.
-
Sequelink DataDirect Informix ODBC Driver Configuration Properties
-
Sequelink DataDirect MS Access ODBC Driver Configuration Properties
AS/400 Toolbox Driver Configuration Properties
OTD Wizard: Database Connection Information
To connect to AS/400, use the information provided in Table 1–1 to complete the Connect to Database step of the JDBC/ODBC OTD Wizard. To access DB2, it is recommended to use the DB2 eWay Adapter or the DB2 Connect eWay Adapter.
Table 1–1 AS/400 Database Connection Information|
Parameter |
Value |
|---|---|
|
Driver Jar Files |
jt400.jar |
|
Driver Java Class Name |
com.ibm.as400.access.AS400JDBCDriver |
|
URL Connection String |
jdbc:as400://server-name:server-port/ Note – NOTE: Default server port is 446. |
|
User Name |
Login name of the account used to access the AS/400 database. |
|
Password |
Password associated with the login account name used to connect to the AS/400 database. |
Environment Properties
Use Table 1–2 to configure the environment properties for the specified JDBC/ODBC driver.
Table 1–2 AS/400 Database Environment Properties|
Parameter |
Value |
|---|---|
|
Description |
JDBC Connection Pool Datasource |
|
ClassName |
com.ibm.as400.access.AS400JDBCConnectionPoolDataSource |
|
ClassNamefor OtherInterfaces | |
|
ServerName |
Server name of the machine hosting the database. |
|
PortNumber |
server-port Note – NOTE: Default server port is 446. |
|
DatabaseName | |
|
User |
Login name of the account used to access the database. |
|
Password |
Password associated with the login account name used to connect to the database. |
|
DriverProperties | |
|
Delimiter |
The default is #. |
|
DataSourceName | |
|
MinPoolSize |
The default is 0. |
|
MaxPoolSize |
The default is 10. |
|
MaxIdleTime |
The default is 0. |
Attunity Driver Configuration Properties
OTD Wizard: Database Connection Information
To connect to Attunity, use the information provided in Table 1–3 to complete the Connect to Database step of the JDBC/ODBC OTD Wizard.
Table 1–3 Attunity Driver Database Connection Information|
Parameter |
Value |
|---|---|
|
Driver Jar Files |
nvjdbc2.jar |
|
Driver Java Class Name |
com.attunity.jdbc.NvDriver |
|
URL Connection String |
jdbc:attconnect://server-name;DefTdpName=database-logical-name; OneTdpMode=1 Note – The database-logical-name is created in the Attunity server. |
|
User Name |
Leave password field blank. Value configured when the database entry is created in the Attunity Server. |
|
Password |
Leave password field blank. Value configured when the database entry is created in the Attunity Server. |
Environment Properties
Use Table 1–4 to configure the environment properties for the specified JDBC/ODBC driver.
Table 1–4 Attunity Driver Database Environment Properties|
Parameter |
Value |
|---|---|
|
Description |
JDBC Connection Pool Datasource |
|
ClassName |
com.attunity.jdbc.NvXADataSource |
|
ClassNamefor OtherInterfaces | |
|
ServerName |
Server name of the machine hosting the database. |
|
PortNumber |
server-port Note – NOTE: Default server port is 2551. |
|
DatabaseName |
<database-name> |
|
User |
Leave user field blank. Value configured when the database entry is created in the Attunity Server. |
|
Password |
Leave password field blank. Value configured when the database entry is created in the Attunity Server. |
|
DriverProperties |
setDefTdpName#database-logical-name##setWorkspace#Navigator## |
|
Delimiter |
The default is #. |
|
DataSourceName | |
|
MinPoolSize |
The default is 0. |
|
MaxPoolSize |
The default is 10. |
|
MaxIdleTime |
The default is 0. |
MySQL Connector/J Driver Configuration Properties
OTD Wizard: Database Connection Information
To connect to MYSQL, use the information provided in Table 1–5 to complete the Connect to Database step of the JDBC/ODBC OTD Wizard.
Table 1–5 MySQL Connector/J Driver Database Connection Information|
Parameter |
Value |
|---|---|
|
Driver Jar Files |
mysql-connector-java-3.0.11-stable-bin.jar |
|
Driver Java Class Name |
com.mysql.jdbc.Driver |
|
URL Connection String |
jdbc:mysql://server-name:server-port/database-name Note – NOTE: Default server port is 3306 |
|
User Name |
Login name of the account used to access the database. |
|
Password |
Password associated with the login account name used to connect to the database. |
Environment Properties
Use Table 1–6 to configure the environment properties for the specified JDBC/ODBC driver.
Table 1–6 MySQL Connector/J Driver Environment Properties|
Parameter |
Value |
|---|---|
|
Description |
JDBC Connection Pool Datasource |
|
ClassName |
ccom.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource |
|
ClassNamefor OtherInterfaces | |
|
ServerName |
Server name of the machine hosting the database. |
|
PortNumber |
server-port Note – NOTE: Default server port is 3306. |
|
DatabaseName |
database-name |
|
User |
Login name of the account used to access the database. |
|
Password |
Password associated with the login account name used to connect to the database. |
|
DriverProperties | |
|
Delimiter |
The default is #. |
|
DataSourceName | |
|
MinPoolSize |
The default is 0. |
|
MaxPoolSize |
The default is 10. |
|
MaxIdleTime |
The default is 0. |
Note –
It is not mandatory to enter driver properties in the Outbound JDBC eWay Environment properties for MySQL.
PostgreSQL Driver Configuration Properties
OTD Wizard: Database Connection Information
To connect to SQL, use the information provided in Table 1–7 to complete the Connect to Database step of the JDBC/ODBC OTD Wizard. To access SQL, it is recommended to use the SQL Server eWay Adapter.
Table 1–7 PostgreSQL Driver Connection Information|
Parameter |
Value |
|---|---|
|
Driver Jar Files |
postgresql-8.0-310.jdbc3.jar |
|
Driver Java Class Name |
org.postgresql.Driver |
|
URL Connection String |
jdbc:postgresql://server-name:server-port/database-name Note – NOTE: Default server port is 5432. |
|
User Name |
Login name of the account used to access the database. |
|
Password |
Password associated with the login account name used to connect to the database. |
Environment Properties
Use Table 1–8 to configure the environment properties for the specified JDBC/ODBC driver.
Table 1–8 PostgreSQL Driver Environment Properties|
Parameter |
Value |
|---|---|
|
Description |
JDBC Connection Pool Datasource |
|
ClassName |
org.postgresql.jdbc3.Jdbc3ConnectionPool |
|
ClassNamefor OtherInterfaces | |
|
ServerName |
Server name of the machine hosting the database. |
|
PortNumber |
server-port Note – NOTE: Default server port is 5432. |
|
DatabaseName |
database-name |
|
User |
Login name of the account used to access the database. |
|
Password |
Password associated with the login account name used to connect to the database. |
|
DriverProperties | |
|
Delimiter |
The default is #. |
|
DataSourceName | |
|
MinPoolSize |
The default is 0. |
|
MaxPoolSize |
The default is 10. |
|
MaxIdleTime |
The default is 0. |
Note –
It is not mandatory to enter driver properties in the Outbound JDBC eWay Environment properties for PostgreSQL.
SyBase JConnect Driver Configuration Properties
OTD Wizard: Database Connection Information
To connect to Sybase, use the information provided in Table 1–9 to complete the Connect to Database step of the JDBC/ODBC OTD Wizard. To access Sybase, it is recommended to use the Sybase eWay Adapter.
Table 1–9 Sybase JConnect Driver Database Connection Information|
Parameter |
Value |
|---|---|
|
Driver Jar Files |
jconn2.jar |
|
Driver Java Class Name |
com.sybase.jdbc2.jdbc.SybDriver |
|
URL Connection String |
jdbc:sybase:Tds:server-name:server-port Note – NOTE: Default server port is 4100. |
|
User Name |
Login name of the account used to access the database. |
|
Password |
Password associated with the login account name used to connect to the database. |
Environment Properties
Use Table 1–10 to configure the environment properties for the specified JDBC/ODBC driver.
Table 1–10 Sybase JConnect Driver Environment Properties|
Parameter |
Value |
|---|---|
|
Description |
JDBC Connection Pool Datasource |
|
ClassName |
com.sybase.jdbc2.jdbc.SybConnectionPoolDataSource |
|
ClassNamefor OtherInterfaces | |
|
ServerName |
Server name of the machine hosting the database. |
|
PortNumber |
server-port Note – NOTE: Default server port is 4100. |
|
DatabaseName |
database-name |
|
User |
Login name of the account used to access the database. |
|
Password |
Password associated with the login account name used to connect to the database. |
|
DriverProperties | |
|
Delimiter |
The default is #. |
|
DataSourceName | |
|
MinPoolSize |
The default is 0. |
|
MaxPoolSize |
The default is 10. |
|
MaxIdleTime |
The default is 0. |
Note –
It is not mandatory to enter driver properties in the Outbound JDBC eWay Environment properties for Sybase.
Sequelink DataDirect Informix ODBC Driver Configuration Properties
OTD Wizard: Database Connection Information
The settings in Table 1–11 describe how to use the DataDirect Sequelink JDBC/ODBC bridge with the JDBC/ODBC eWay. This information demonstrates how Sequelink can be used to interface with the ODBC driver. To connect to an Informix database, it is recommended to use the Informix eWay Adapter.
Table 1–11 Sequelink DataDirect Informix ODBC Driver Database Connection Information|
Parameter |
Value |
|---|---|
|
Driver Jar Files |
sljc.jar |
|
Driver Java Class Name |
com.ddtek.jdbc.sequelkink.SequeLinkDriver |
|
URL Connection String |
jdbc:sequelink://server-name:server-port Note – NOTE: Default server port is 19996. |
|
User Name |
Login name of the account used to access the database. |
|
Password |
Password associated with the login account name used to connect to the database. |
Environment Properties
Use Table 1–12 to configure the environment properties for the specified JDBC/ODBC driver.
Table 1–12 Sequelink DataDirect Informix ODBC Driver Environment Properties|
Parameter |
Value |
|---|---|
|
Description |
JDBC Connection Pool Datasource |
|
ClassName |
com.ddtek.jdbcx.sequelkink.SequeLinkDataSource |
|
ClassNamefor OtherInterfaces | |
|
ServerName |
Server name of the machine hosting Sequelink. |
|
PortNumber |
server-port Note – NOTE: Default server port is 19996. |
|
DatabaseName | |
|
User |
Login name of the account used to access the database. |
|
Password |
Password associated with the login account name used to connect to the database. |
|
DriverProperties | |
|
Delimiter |
The default is #. |
|
DataSourceName | |
|
MinPoolSize |
The default is 0. |
|
MaxPoolSize |
The default is 10. |
|
MaxIdleTime |
The default is 0. |
Note –
It is not mandatory to enter driver properties in the Outbound JDBC eWay Environment properties for Sequelink DataDirect Informix ODBC.
Sequelink DataDirect MS Access ODBC Driver Configuration Properties
OTD Wizard: Database Connection Information
To connect to Microsoft Access, via the Microsoft Access ODBC driver, use the information provided in Table 1–13 to complete the Connect to Database step of the JDBC/ODBC OTD Wizard.
Table 1–13 MS Access ODBC Driver Database Connection Information|
Parameter |
Value |
|---|---|
|
Driver Jar Files |
sljc.jar |
|
Driver Java Class Name |
com.ddtek.jdbc.sequelink.SequeLinkDriver |
|
URL Connection String |
jdbc:sequelink://server-name:server-port Note – NOTE: Default server port is 19996. |
|
User Name |
Login name of the account used to access the database. |
|
Password |
Password associated with the login account name used to connect to the database. |
Environment Properties
Use Table 1–14 to configure the environment properties for the specified JDBC/ODBC driver.
Table 1–14 MS Access ODBC Driver Environment Properties|
Parameter |
Value |
|---|---|
|
Description |
JDBC Connection Pool Datasource |
|
ClassName |
com.ddtek.jdbcx.sequelkink.SequeLinkDataSource |
|
ClassNamefor OtherInterfaces | |
|
ServerName |
Server name of the machine hosting Sequelink. |
|
PortNumber |
server-port Note – NOTE: Default server port is 19996. |
|
DatabaseName | |
|
User |
Login name of the account used to access the database. |
|
Password |
Password associated with the login account name used to connect to the database. |
|
DriverProperties | |
|
Delimiter |
The default is #. |
|
DataSourceName | |
|
MinPoolSize |
The default is 0. |
|
MaxPoolSize |
The default is 10. |
|
MaxIdleTime |
The default is 0. |
Note –
It is not mandatory to enter driver properties in the Outbound JDBC eWay Environment properties for Sequelink DataDirect MS Access ODBC.
Teradata Driver Configuration Properties
OTD Wizard: Database Connection Information
To connect to Teradata, via the Teradata driver, use the information provided in Table 1–15 to complete the Connect to Database step of the JDBC/ODBC OTD Wizard.
Table 1–15 Teradata Driver Database Connection Information|
Parameter |
Value |
|---|---|
|
Driver Jar Files |
teradata.jar |
|
Driver Java Class Name |
com.ncr.teradata.TeraDriver |
|
URL Connection String |
jdbc:teradata://server-name:server-port/database-server-name Note – NOTE: Default server port is 6666 for the Type-3 driver Gateway. |
|
User Name |
Login name of the account used to access the database. |
|
Password |
Password associated with the login account name used to connect to the database. |
Environment Properties
Use Table 1–16 to configure the environment properties for the specified JDBC/ODBC driver.
Table 1–16 Teradata Driver Environment Properties|
Parameter |
Value |
|---|---|
|
Description |
JDBC Connection Pool Datasource |
|
ClassName |
com.ncr.teradata.TeraConnectionPoolDataSource |
|
ClassNamefor OtherInterfaces | |
|
ServerName |
Server name of the machine hosting the database. |
|
PortNumber |
server-port Note – NOTE: Default server port is 6666 for the Type-3 driver Gateway. |
|
DatabaseName |
database-name |
|
User |
Login name of the account used to access the database. |
|
Password |
Password associated with the login account name used to connect to the database. |
|
DriverProperties |
setURL#jdbc:teradata://server-name:server-port/database-server-name ##setDSName#database-server_name## |
|
Delimiter |
The default is #. |
|
DataSourceName | |
|
MinPoolSize |
The default is 2. |
|
MaxPoolSize |
The default is 10. |
|
MaxIdleTime |
The default is 0. |
Troubleshooting
Refer to the following when troubleshooting Driver issues.
-
The ReceiveOne operation in BPEL is not supported when using inbound functions with some drivers.
-
Some drivers do not support Updatable ResultSets. If you find this to be the case, use a Prepared Statement to Update, Insert, and Delete data.
-
Not all drivers provide metadata information such as column names and data types. If your table does not have column names and data types, add them before saving the OTD.
Installing JDBC/ODBC Drivers
The database drivers specified in your projects need to be installed on both the Enterprise Designer machine and the Logical host machine. When installing the drivers on the Enterprise Designer machine, you must specify the absolute path to the driver. When installing the drivers on the Logical Host, place the driver into the Logical Host
stcis
directory: <JavaCAPS52>\logicalhost\is\lib; or
<JavaCAPS52>\logicalhost\is\domains\domain1\lib
|
where JavaCAPS51 is the location of your Sun Java Composite Application Platform Suite installation.
The driver file must be copied to the latter folder if you are running multiple domains and wish to specify a driver for each domain. Otherwise, you only need to copy the driver file to the former folder address.
For procedures on how to install database drivers, see Configuring JDBC/ODBC Drivers
- © 2010, Oracle Corporation and/or its affiliates