TechNote

Updated DataDirect JDBC drivers (version 3.5)

Issue

---

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, the ODBC Server must be upgraded to version 5.4 before applying the 3.5 driver pack because 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.

Reason

---

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. (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 in order 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

1. Stop ColdFusion MX.
2. Backup your existing macromedia_drivers.jar file.
3. Unzip macromedia_drivers.zip into the same directory, overwriting the previous macromedia_drivers.jar.
   A new file, DDJDBCAuth03.dll, which is required for Windows Authentication, is also included in the zip file replacing the older DDJDBCAuth.DLL.
4. Restart ColdFusion MX.

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

1. Stop ColdFusion MX.
2. Backup your existing macromedia_drivers.jar file.
3. Unzip macromedia_drivers.zip into the same directory, overwriting the previous macromedia_drivers.jar.
   A new file, DDJDBCAuth03.dll, which is required for Windows Authentication, is also included in the zip file replacing the older DDJDBCAuth.DLL
4. Restart ColdFusion MX.

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 4

1. Stop JRun 4.
2. Backup your existing macromedia_drivers.jar file.
3. Unzip macromedia_drivers.zip into the same directory, overwriting the previous macromedia_drivers.jar.
   A new file, DDJDBCAuth03.dll, which is required for Windows Authentication, is also included in the zip file replacing the older DDJDBCAuth.DLL.
   
   Note: java.library.path must be modified (either in the jvm.config file or through the JMC) to include {application.home}/lib so that DDJDBCAuth03.DLL loads correctly.
4. Restart 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:, this would beC:\JRun4\lib\macromedia_drivers.jar.

---

JDBCSpy built into the driver

Spy passes calls issued by a running application to an underlying DataDirect Connect for JDBC driver and 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, users need to 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:

 SpyAttributes=(log=(file)C:\\temp\\spy.log;logTName=yes;timestamp=yes) 

Unix/Linux/MacOSX:

 SpyAttributes=(log=(file)/tmp/spy.log;logTName=yes;timestamp=yes) 

For Oracle, SQL Server, Sybase and Informix:
JDBCSpy URL for "other" type data sources:

 jdbc:macromedia:sqlserver://Server1:1433;databaseName=pubs;sendStringParametersAsUnicode=false;SpyAttributes=(log=(file)C:\\temp\\spy.log;logTName=yes;timestamp=yes) jdbc:macromedia:oracle://Server1:1521;serviceName=ORCL;SpyAttributes=(log=(file)C:\\temp\\spy.log;logTName=yes;timestamp=yes) jdbc:macromedia:sybase://Server1:5000;databaseName=pubs;SpyAttributes=(log=(file)C:\\temp\\spy.log;logTName=yes;timestamp=yes) jdbc:macromedia:informix://Server1:1529;InformixServer=ol_machine1;databaseName=stores7;DBDATE=Y4MD-;SpyAttributes=(log=(file)C:\\temp\\spy.log; logTName=yes;timestamp=yes) 

Driver class:

 macromedia.jdbc.MacromediaDriver 

Sample output:

 spy(Thread-25)(2005/10/11 22:29:30.913)>> Connection[1].setTransactionIsolation(int level) spy(Thread-25)(2005/10/11 22:29:30.913)>> level = 2 spy(Thread-25)(2005/10/11 22:29:30.944)>> OK spy(web-1)(2005/10/11 22:29:30.944)>> Connection[1].isReadOnly() spy(web-1)(2005/10/11 22:29:30.944)>> OK (false) spy(web-1)(2005/10/11 22:29:30.944)>> Connection[1].setAutoCommit(boolean autoCommit) spy(web-1)(2005/10/11 22:29:30.944)>> autoCommit = true spy(web-1)(2005/10/11 22:29:30.944)>> OK spy(web-1)(2005/10/11 22:29:30.944)>> Connection[1].setTransactionIsolation(int level) spy(web-1)(2005/10/11 22:29:30.944)>> level = 2 spy(web-1)(2005/10/11 22:29:30.975)>> OK spy(web-1)(2005/10/11 22:29:30.975)>> Connection[1].setReadOnly(boolean readOnly) spy(web-1)(2005/10/11 22:29:30.991)>> readOnly = false spy(web-1)(2005/10/11 22:29:30.991)>> OK spy(web-1)(2005/10/11 22:29:31.913)>> Connection[1].isClosed() spy(web-1)(2005/10/11 22:29:31.913)>> OK (false) spy(web-1)(2005/10/11 22:29:31.913)>> Connection[1].setAutoCommit(boolean autoCommit) spy(web-1)(2005/10/11 22:29:31.913)>> autoCommit = true spy(web-1)(2005/10/11 22:29:31.913)>> OK spy(web-1)(2005/10/11 22:29:31.913)>> Connection[1].setTransactionIsolation(int level) spy(web-1)(2005/10/11 22:29:31.913)>> level = 2 spy(web-1)(2005/10/11 22:29:31.944)>> OK spy(web-1)(2005/10/11 22:29:31.960)>> Connection[1].setReadOnly(boolean readOnly) spy(web-1)(2005/10/11 22:29:31.960)>> readOnly = false spy(web-1)(2005/10/11 22:29:31.960)>> OK 

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 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

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 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.

---

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 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.

Oracle 10g users

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.

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 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.

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)

Oracle users should use the latest 3.5 drivers for Oracle 10g R2 support and because all known issues with clobs are resolved in this set of drivers.

Additional Information

---

If you encounter the following error:

 [Macromedia][SQLServer JDBC Driver]The property value given
		

	 is not a valid choice : sendStringParametersAsUnicode=0 

with data sources migrated from ColdFusion 5, please see TechNote tn_19570.

Search Support

---