Unicode to ANSI Translation in VFP 9.0 - Sys(987)

If you’re dealing with databases store Unicode data on the server you probably know that it’s kind of problematic to retrieve data from nvarchar columns in the VFP default settings and prior to VFP 9. By default VFP returns the data without performing any Unicode to ANSI conversion so you get fun stuff like this:

oSql.Execute("select abstract from wws_items")

W e s t   W i n d  W e b   S t o r e   . N e t   i s   a  f u l l

Where the ‘blank’ characters are the second byte – 0 in most cases - of the Unicode string. What’s really odd about the stock behavior is that VFP will:

• Convert nText Data
• Convert nVarChar and nchar if the length is smaller than 255 chars
• Will not convert nvarchar over 255 characters

IOW, it’s kinda tricky to decide just how to convert the data on the client especially since it's not all memo fields that will turn out with Unicode data. If you have a generic SQL engine especially this is problematic.

Luckily VFP is more forgiving on the sending end – if you send data to SQL Server Sql Server via INSERT and UPDATE commands, SQL Server automatically does the ANSI to Unicode conversions on the server.

There are additional issues with how to get non-codepage specific Unicode data into and out of the database which is an interesting scenario in multi-language applications that use different code pages. I wrote about this in some detail a long time ago (just before VFP 9 came out).

But I missed one detail about the upcoming VFP 9 at the time...

Sys(987) – Unicode to Ansi Conversion on queries

So, in VFP there’s new SYS() function – SYS(987,.T.) – that lets you enforce universal Unicode to Ansi mapping on query operations for Sql Passthrough, Remote Views and CursorAdapter calls.

So how did I miss this for so long??? This is a huge improvement as it makes Unicode data at least reasonably approachable with FoxPro code. I’ve struggled through STRCONV() madness before with several apps and in fact I wrote about Unicode issues some time back and also missed it then (this was right around the time VFP 9 came out).

But I suppose it’s easy to miss. Why in the heck is this a Sys function when just about all other functions are set as SqlSetProp() settings? I suppose the reasoning is because it’s global and works both with passthrough and the CursorAdapter, but still I totally missed this when I accidentally stumbled onto this article and reviewed some ADO.NET related issues:

http://foxcentral.net/microsoft/WhatsNewInVFP9_Chapter11.htm

Talk about buried. That's one of the only references anywhere I found in regards to this function.

That article is a good read either way – there are a lot of small changes in VFP 9's Sql Server interaction that you might very well miss and this article catches a lot of ‘em in one place.