TechNote (Archived)

ColdFusion MX: Updating SQL Server 2000 unicode datatypes using JDBC drivers

Issue

---

When using the cfquery tag to update or insert data into a SQL Server 2000 database for the nchar, ntext and nvarchar unicode datatypes, a special SQL syntax needs to be used so that the correct data is entered into the database.

The Microsoft SQL Server 2000 requirement for JDBC drivers is that any unicode string literal needs to be preceded by the letter "N". If the regular SQL syntax is used when inserting or updating a SQL Server 2000 database column that is a unicode datatype, the data is entered incorrectly in the database as question marks.

When upgrading from previous versions of Macromedia ColdFusion to ColdFusion MX, you'll also need to modify any SQL queries which manipulate data in Unicode datatype columns on SQL Server 2000 databases.

Note: This syntax change only applies to thecfquery tag. You do not need to modify the SQL syntax when using cfupdate and cfinsert tags.

Solution

---

In order for the correct unicode string literal to be inserted or updated into the database when using the cfquery tag, modify the SQL syntax so that the letter 'N' is added in front of any unicode strings being inserted or updated. This syntax change ensures that the characters will be entered into the database correctly.

Here is an example of a modified UPDATE SQL statement updating Japanese characters:

Here is an example of a modified INSERT SQL statement inserting Japanese characters:

Additional Information

---

See the Internationalizing ColdFusion MX Applications tutorial to learn more about internationalizing ColdFusion MX applications.

Search Support

---