Updated DataDirect JDBC drivers (version 3.4)

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

The new drivers, Version 3.4, Build 50, provided in this TechNote can replace the 3.3 drivers shipped with ColdFusion MX 6.1, ColdFusion MX 7, or JRun 4.

• ColdFusion MX 6.1
• ColdFusion MX 7
• JRun 4

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

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

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

• 60039 - When SQLNET.AUTHENTICATION_SERVICES was disabled on the Oracle server, the driver was unable to connect.
• 60066 - TIMSTAMP WITH LOCAL TIME ZONE datatype data was incorrectly reported
• 57661 - Oracle expired password gives "Net8 protocol error"
• 59202 - Oracle 16 second hang on resultset.close() when setMaxRows() is used
• 57263 - Oracle clob hang. 16 second delay most records
• 54831,55217 - Oracle clob hang when trailing null in clob. (NLS_Characterset UTF8)
• 55313 - Net8 Protocol error with incorrectly registered output param.
• 54994 - Drivers return 16 bytes for char(8) on Oracle (NLS_Characterset JA16SJIS)
• 55605 - Cannot insert Japanese characters into a clob column. (NLS_Characterset UTF8)
• 55308 - 2k limit for a LONG returned from a stored procedure (32k now)
• 55546 - CFTRANSACTION throws ORA-01453
• 53182 - Clobs of around 3-6MB return 0 bytes
• 53366 - Charset "ELM8MSWIN1253" hang while retrieving CLOBs
• 53357 - Clob retrieval returns 0 bytes from EL8MSWIN1253 , JA16SJIS
• 52262 - Clob update with 2000+ chars fails with EL8MSWIN1253, JA16SJIS CharacterSets.
• 53466 - CLOB select throws java.lang.ArrayIndexOutOfBoundsException. CharacterSet: JA16SJIS.
• 53477 - "No more data available to read" connected to Oracle 9i JA16SJIS database.
• 53758 - ArrayIndexOutOfBoundsException on CLOB SELECT Oracle9i CharacterSet:JA16SJIS.
• 53752 - Hang with CLOB retrieve and WE8ISO8859P15 charset.
• 53750 - Driver will not accept VARCHAR out params > 2000 char.
• 53357 - CLOB Update fails with NLS_CharacterSet WE8ISO8859P15.
• 49775 - Net8 Protocol error with large amounts of client variable data. (light load)

• 58950 - Windows-specific (Type 2) Windows authentication failed with branded drivers with "cannot find DDJDBCAuth.DLL"
• 56302 - SQLServer @@Nextlevel always returns 3
• 57050 - Certain collations (210-217) not supported.
• 51266 - No more data available to read error when maximum connections are reached
• 52851 - CPU spins when SQL server unexpectedly closes a connection using 3.2 drivers
• 53556,53280 - ArrayIndexOutOfBoundsException on 2,048 char SQL Statement.

• 53223 - Date retrieval 'String to date conversion error' fixed by specifying new DBDATE URL parameter.
• 53592 - Informix SELECT INTO TEMP fails

• 57539 - Qualifying stored proc name qualified with DB name gives error

• 54536 - Unable to connect to database with Cp1114 encoding
• 53347 - "StripNewLines" for DB2 not handling white space
• 53442 - "StripNewLines" with Tabs.

• 54937 - Modified connection exception to identify relevant host and port.
• 54826 - ArrayIndexOutOfBounds if columns aren't specified in an INSERT
• 53947 - CFQUERY timeout in tight query loop under load causes too many worker monitor threads.

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

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:, this would beC:\CFusionMX7\lib\macromedia_drivers.jar.

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:, this would beC:\JRun4\servers\cfusion\cfusion-ear\cfusion-war\WEB-INF\cfusion\lib\macromedia_drivers.jar.

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

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, DDJDBCAuth.dll. Place this DLL in the same directory as macromedia_drivers.jar. A server restart is required.

To use NT authentication, leave both the username 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 will be 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

For ColdFusion MX 7, AuthenticationMethod=Type2 can be added to the URL in the Advanced Settings > Connection String box.

For ColdFusion MX 6.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 username for the query. If a username is specified, SQLServer username/password authentication is used. SQL Server should be configured for mixed authentication in this case.

A new connection option, DBDate, was added 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 may 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 and, 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.

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

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 comprises 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 because the nodes in a RAC system share a common service name.

If you encounter the following error:

with data sources migrated from ColdFusion 5, please see "Database performance issues when migrating from ColdFusion 5 to ColdFusion MX" (TechNote tn_19570).