Database operations

ColdFusion MX uses JDBC drivers to interact with databases; for example, to query, write, and update a database. (JDBC is an Application Programming Interface (API) for Java programs to access data.) To connect to Open Database Connectivity (ODBC) data sources, you can use the bundled Merant Type 4 database drivers or the SQL Link Type 3 database drivers. ColdFusion MX no longer uses the JDBC-ODBC bridge driver.

This section describes the known incompatibilities between the ODBC features of ColdFusion 5 and the JDBC features of ColdFusion MX, in addition to those that are documented in "CFML tags and attributes" and "CFML functions and variables" (for example, cfquery, cfcatch, and cfreport).

For more information about the Merant JDBC drivers included with ColdFusion MX, see http://www.merant.com.

Connecting to a data source with ODBC Socket

In ColdFusion 5, an ODBC Data Source Name (DSN) entry that was created with the ODBC Data Source Administrator would automatically show up in the list of data sources in the ColdFusion Administrator. It does not in ColdFusion MX; you must manually add the ODBC DSN entry.

To manually add the ODBC DSN entry:

In the ColdFusion Administrator, select Data & Services > Data Sources.
In the Add New Data Source Name box, do the following:
1. In the Data Source Name text box, enter the data source name.
  You can use the same name as the ODBC DSN entry.
2. In the Driver drop-down list box, select ODBC Socket.
3. Click Add.
In the ODBC DSN drop-down list box, select the ODBC DSN name.
Click Submit.

For more information about data source management, see Administering ColdFusion MX.

Session management

ColdFusion MX now uses J2EE sessions as its default session management technique. For more information about using persistent data and locking, see Developing ColdFusion MX Applications with CFML.

QueryColumn object

The QueryColumn object is even more like an array than in ColdFusion 5, as shown by the following examples:

The QueryColumn object is derived by referencing a query using associative array notation; for example:
```
query1['firstName']
```
You can set a query column to an array element; for example:
```
<cfset query1['col'] = array2['moo']>
```
The following code returns True in ColdFusion MX, and False in ColdFusion 5:
```
#isArray(query1['firstName'])#
```
The following code works in ColdFusion MX, and produces an error in ColdFusion 5:
```
<cfset joeArray[1] = query1['firstName']
```

Data type of query results

ColdFusion 5 stores data in the query object as a string, regardless of how the data is stored in the database. When it outputs a piece of data, it just writes out the string. ColdFusion MX stores data in the query object differently, depending on its database type (for example, it may store a number as a Java Double). ColdFusion MX outputs the data by converting the stored type to a string, which might differ from the string that ColdFusion 5 outputs. If you need your output to be in a particular format, use the number and/or date format functions.

Appearance of query results

Certain database and operating system combinations can lead to query data being returned with space characters appended to the end. If you encounter this whitespace padding, you can remove it using the SQL command rtrim(), if your database supports the SQL rtrim function. For example, if SELECT ColA, ColB FROM TableA returns results for ColB with whitespace padding, you can rewrite the query as follows:

SELECT ColA, rtrim(ColB) AS ColB FROM TableA

Migrating ColdFusion 5 Applications
Migrating ColdFusion 5 Applications