Updated DataDirect JDBC drivers | Version 3.5

This TechNote provides updated DataDirect JDBC version 3.5 drivers, which resolve issues in earlier versions of the drivers.

The new drivers, version 3.5 Build 13, provided in this TechNote replace the 3.3 or 3.4 drivers shipped with ColdFusion MX 6.1, ColdFusion MX 7, or JRun 4.

Note: These drivers are newer than the ones in JRun4 Updater 6 and ColdFusion MX 7.0.1.

Products Supported

• ColdFusion MX 6.1
• ColdFusion MX 7
• JRun 4

Databases Supported

• Oracle 8i (8.1.6 and 8.1.7), Oracle 9i (R1-R2), Oracle 10g R1, Oracle 10g R2
• Microsoft SQL Server 7; 2000 (including SP1, SP2, and SP3a), Microsoft SQL Server 2000 Desktop Engine (MSDE 2000)
• Informix Dynamic Server 9.2, 9.3, 9.4 and 10
• Sybase Adaptive Server 11.5, 12.0, 12.5, 12.5.1, 12.5.2, 12.5.3
• IBM DB2 UDB 7.x and 8.x for Windows, Unix, and Linux; IBM DB2 UDB 7.x, 8.1 for OS/390 and z/OS; IBM DB2 UDB V4R5, V5R1, V5R2,V5R3 for iSeries (AS/400)

Windows NT Authentication

• Type 4 JDBC driver support for MS SQL Server and Oracle. (requires JDK 1.4)

ColdFusion MX 6.1 considerations

If the installation uses a combination of JDBC and ODBC drivers, upgrade the ODBC Server to version 5.4 before applying the 3.5 driver pack. The Sequelink driver contained in the 3.5 driver pack is not compatible with the 6.1 Sequelink ODBC server. See Upgrade DataDirect Sequelink ODBC Server to version 5.4 and ColdFusion MX 7.0 and 7.0.1: Hot fix available for SequeLink 5.4 Socket server.

Problems corrected

The following problems are corrected in this release of the DataDirect drivers.

Oracle

• 61145 - Oracle 10g R2 not supported. cfstoredproc calls that return result sets hang the server with both the 3.3, 3.4 and 3.5 drivers until this version. The 3.5 build 13 drivers support Oracle 10g R2.

SQL Server

• 61161 - Insert trigger causes CPU to spike to 100% using the 3.4/3.5 drivers.

Since all fixes are cumulative, all the bugs fixed in the 3.4 build 50 driver pack are included in the 3.5 driver.

Known issues

61315 - SQL Server - CFQUERY INSERT does not return a query object (resultset) when the table contains an IDENTITY type column. With 3.3 drivers included with 7.0 and 7.0.1, the query object returned would contain the identity key value and could be referenced with normal #Query.Column# syntax.

Update to 61315: A new URL parameterAlwaysReportTriggerResults was added during the 3.4 release. For the old 3.3 behavior, add this parameter to the URL and set it to true. (Because a bug in the "Connection String" field processing, for ColdFusion MX 6.1, 7.0 and 7.0.1, create an "Other" data source to add the parameter.)

Solution

To install the new DataDirect database drivers, follow the instructions in the appropriate section below.

ColdFusion MX 6.1 or MX 7 Server Configuration

ColdFusion MX loads the JDBC drivers inmacromedia_drivers.jar in the directorycf_root/lib.

For example, with ColdFusion MX 7 Server Configuration on Windows installed on drive C:, it would beC:\CFusionMX7\lib\macromedia_drivers.jar.

ColdFusion MX 7 Multiserver Configuration

ColdFusion MX 7 Multiserver Configuration loads the JDBC drivers in macromedia_drivers.jar in /jrun4/servers/cfusion/cfusion-ear/cfusion-war/WEB-INF/cfusion/lib. For example, with ColdFusion MX 7 Multiserver Configuration on Windows installed on drive C:, it would beC:\JRun4\servers\cfusion\cfusion-ear\cfusion-war\WEB-INF\cfusion\lib\macromedia_drivers.jar.

JRun 4

JRun uses the JDBC drivers in macromedia_drivers.jar in /jrun4/lib. For example, with JRun 4 on Windows installed on drive C:, it would beC:\JRun4\lib\macromedia_drivers.jar.

JDBCSpy built in to the driver

Spy passes calls that a running application issues  to an underlying DataDirect Connect for JDBC driver. Then, it logs detailed information about those calls, information you can use for troubleshooting problems. Configuration requires addingSpyAttributes to the JDBC URL through the "Connection String" field. Unfortunately, due to bug 60098, the "Connection String" field doesn't work for Oracle, SQL Server, Sybase, or Informix built-in drivers. For these drivers, create an "Other" type data source and fill in all the data source parameters manually.

For ODBC Socket and DB2 (7.0.1):

Add the following parameters to the data source Connection String field:

Windows:

Unix/Linux/MacOSX:

For Oracle, SQL Server, Sybase, and Informix:

JDBCSpy URL for "other" type data sources:

Driver class:

Sample output:

SQL Server NT authentication users

These JDBC drivers support SQL Server NT authentication, designated as "type 2". Macromedia expects this feature will replace the less robust ODBC/Sequelink/ODBC Socket Server solution for many users requiring SQL Server NT Authentication on Windows platforms.

Use of the JDBC NT authentication feature requires a shareable library, DDJDBCAuth03.dll. Place this DLL in the same directory as macromedia_drivers.jar. A server restart is required.

To use NT authentication, leave both the user name and the password blank, in both the data source and incfquery.

When ColdFusion or JRun is started from the command prompt, the login credentials for the command prompt user are used. If ColdFusion or JRun is started as a Windows service, the credentials of the Windows service are used for authentication. It is not possible to specify a Windows account at the time of the query.

To enable NT Authentication, add the following to the JDBC URL:

AuthenticationMethod=Type2

Due to a bug in the "Connection String" field processing, for ColdFusion MX 6.1, 7.0 and 7.0.1, create an "Other" data source with the following parameters (example):

URL: (enter as one line)

jdbc:macromedia:sqlserver://[dbserver]:1433;databaseName=pubs; SelectMethod=direct;sendStringParametersAsUnicode=false; MaxPooledStatements=0;AuthenticationMethod=Type2;

Driver Class:

macromedia.jdbc.MacromediaDriver

For queries that do not require NT Authentication, supply a user name for the query. If a user name is specified, SQLServer user name/password authentication is used. Configure SQL Server for mixed authentication in this case.

Informix DBDATE parameter

A new connection option, DBDate, was added in 3.4 to set the value of the Informix DBDate server environment variable. This value controls how the driver interprets literal date values when inserting or updating data in DATE columns and how it formats date strings retrieved from DATE columns. Using this option, you can customize:

• The Order in which the month, day, and year fields appear in a date string
• The Year field to contain two or four digits
• The Separator character used to separate the date fields

Valid values are:

DMY2Y4DM

DMY4Y4MD

MDY2Y2DM

MDY4Y4MD

where D is a 2-digit day field, M is a 2-digit month field, Y2 is a 2-digit year field, and Y4 is a 4-digit year field.

If unspecified, the driver does not set the value of the Informix DBDate environment variable.

Optionally, a separator character can be specified as the last character of the value. Valid separator characters are:

Hyphen (-)

Period (.)

Forward slash (/)

If a separator is not specified, a forward slash (/) is used to separate the fields. For example, a value of Y4MD- specifies a date format that has a 4-digit year, followed by the month and then by the day. The date fields are separated by a hyphen (-).

For example: 2004-02-15.

This option does not affect the format of the string in the date escape syntax. Dates that are specified using the date escape syntax always use the JDBC escape format yyyy-mm-dd.

NOTES:

• The Oracle JDBC driver now supports NT Authentication. Although Macromedia has not tested this configuration, the behavior is similar to the SQL Server authentication discussed above.
• Supplying [DOMAIN\username]/password with data sources and queries is not supported, but is an enhancement planned for a later version of the drivers.

Oracle 10g users

These drivers have been designed to work with Oracle 10g Real Application Clusters. However, Macromedia has not yet tested Oracle 10g RAC with these drivers and ColdFusion MX. Using an Oracle RAC is unsupported.

Oracle 10g RAC users

For Oracle RAC, create an "Other" type data source usingServiceName in the URL instead of SID:

ServiceName

The ServiceName is the global database name - a name that typically includes the database name and domain name.

For example: sales.us.acme.com

ServiceName is used to specify connections to an Oracle Real Application Cluster (RAC) system rather than a specific Oracle instance. The nodes in a RAC system share a common service name.

Driver History

JRun 4 Updater 4,5 - 3.3 build 31

JRun 4 Updater 6 - 3.4 build 50

ColdFusion 6.1 Updater 1 - 3.3 build 31

ColdFusion 7.0 - 3.3 build 48

ColdFusion 7.0.1 - 3.3 build 48

3.4 build 50 released 7/2005. (includes Informix DBDATE)

For Oracle, use the latest 3.5 drivers for Oracle 10g R2 support. All known issues with clobs are resolved in this set of drivers.