Migrating an Existing Application to accept Unicode.

Question

We have in the process of upgrading our application to full Unicode comptibility as we have recently got Delphi 2009 which provides this out of the box. I am looking for anyone who has experience of upgrading an application to accept Unicode characters. Specifically answers to any of the following questions.

• We need to change VarChars to NVarchar, Char to NChar. Are there any gotchas here.
• We need to update all sql statements to include N in front of any sql strings. So Update tbl_Customer set Name = 'Smith' must become Update tbl_Customer set Name = N'Smith' . Is there any way to default to this for certain Fields. It seems extraordinary this is still required.
• Is it possible to get any defaults set up in SQLServer that will make this simpler?

Thanks.

ps We also need to upgrade our Oracle code

Answer 1

On point 2, use parameterised queries.

Seriously, are people still banging SQL together in code? Okay, in some circumstances SQL injection may not be a concern, but surely practicing doing things the safe way would make it a habit?

Answer 2

Damien

I'm not sure how useful your answer is. We have a large 700,000 lines of compiled codebase that was written over the last ten years which contains a large number of sql queries. Most are standardised down to a few functions which are the basis for most of the updates on the database. These can be updated quite simply. However we also need to check every where clause for CustomerName = '%s' which should now be CustomerName = N'%s'

This is a real question which needs a real answer.

Answer 3

Oracle doesn't require you to use nvarchar to store Unicode strings—the server can be configured to store varchar2 in UTF-8. If you only supported ASCII before, it should be transparent. That should prevent the need for all the application-side search-and-replace for ' to N'.

As for Damien's point: it might not help you now, but you should really make it a priority to get rid of non-parameterized queries. They are nothing but a drag on your system from a maintenance, performance, and safety standpoint.

Answer 4

Obvious with SQL Server is that the limits for nchar/nvarchar are half of their char/varchar counterparts (unless you migrate everything > 4000 to nvarchar(max))