Friday, November 23, 2007

NVARCHAR MSSQL columns and cfqueryparam

According to CFMX 7 official documentation, using cfqueryparam's cfsqltype="cf_sql_varchar" is supposed to handle MSSQL server's varchar, nvarchar and sysname. Unfortunately, the nvarchar portion seems not to work so well, unless there's a setting I'm missing in the CF admin.

Typically, nvarchar is useful when saving unicode data. I'm dealing with diacritics and the only way to get diacritics to save properly was with the use of a following snippet:
update sometable set columnname=N'#somevariablename#'

The idea was to get rid of these poorly secured statements with the recommended SQL params instead. Unfortunately, when saving data using the cf_sql_varchar, I get ? instead of most of diacritics (things like čćpšžČĆŠĐŽ). Still looking into this thing so have reverted back to the above syntax.

Interestingly, for columns of type NTEXT using cf_sql_clob seems to work fine. Diacritics get saved properly as well. I've tried using cf_sql_longvarchar instead of cf_sql_varchar but there was no difference.

1 comment:

Philippe Randour said...

There is a solution to your problem. It depends on a setting in the ColdFusion Administrator:

Go to Data & Services > Data Sources;
Edit you data source;
Show Advanced settings;
Check String Format (Enable High ASCII characters and Unicode for data sources configured for non-Latin character);
Submit your changes.

Read more at the following address:
http://www.philipperandour.net/2008/08/cfsqlqueryparam-and-nvarchar-on-sql.html