Using Unicode with MS-SQL-Server 2000

[mcd]

Hello,
i want to migrate my coldfusion application to support unicode. for that i read some articles here in the knowledge bas e and on the web. So i actually tested with:
* correct use of cfcontent,cfprocessingdirective
* storing source-files with UTF8 (BOM)
* activating Unicode-handling for strings in datasource-settings of coldfusion admin
* using nvarchar instead of varchar on mssql-server

my firsts test displaying diffrent characters of different languages (chinese, japanese, arabic) look good. Tests storing and selecting example strings in that languages also look good.

but now i read, that mssql stores unicode data with the UCS-2 implementation and cannot handle UTF-8 directly. but my coldfusion-application will be converted to UTF-8.
now my question: Do i have to do special handling storing and selecting the application data or does this the mssql jdbc driver for me. What about characters in some languages which use 3 Bytes in UTF8. How are they handled when MS-SQL-Server 2000 uses UCS-2 (max 2 Bytes) instead?

Many regards for your answers
McD

[Newsgroup_User]

mcd wrote:
> now my question: Do i have to do special handling storing and selecting the
> application data or does this the mssql jdbc driver for me. What about
> characters in some languages which use 3 Bytes in UTF8. How are they handled
> when MS-SQL-Server 2000 uses UCS-2 (max 2 Bytes) instead?

no need to worry, the db driver will handle that for you. just make sure your
encoding is the same end-to-end.

also a potential gotcha has just been exposed in unicode handling for sql server
related to cfqueryparam under loads, you need to convert everything to "N"
datatypes to avoid this. see jochem's blog:

http://jochem.vandieten.net/2008/03/22/ms-sql-server-and-the-coldfusion-string-format-setting/