Rick Strahl's Weblog
Rick Strahl's FoxPro and Web Connection Weblog
White Papers | Products | Message Board | News |

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

March 11, 2007 •

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:




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.



Posted in:

Feedback for this Weblog Entry

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

Hamlet Kraskian
March 28, 2008

Hi Rick, Thank you for your valuable advices on foxpro. I've a problem 1- I have a table (.dbf) were I've stored unicode data. 2- When I use save as html in browse window I get unicode html as I expect. 3- When I use a report to creating html (or any other listener) I recieve garbage fonts instead of unicode.

How I can change rendering of text object whithout any translation of unicode

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

December 27, 2011

Create your own listener which will override your Unicode string duplicate conversion to Unicode in records having certain User data:


TRY REPORT FORM YourReport OBJECT loListener prev CATCH TO loExc MESSAGEBOX(loExc.Message) ENDTRY


ListenerType = 1

PROCEDURE Render LPARAMETERS nFRXRecNo ; , nLeft, nTop, nWidth, nHeight ; , nObjectContinuationType ; , cContentsToBeRendered ; , GDIPlusImage

LOCAL lcExpr, lcResult, llStrConv

SET DATASESSION TO This.FRXDataSession GO nFRXRecNo *-- Check User data in report definition IF User = "" OR User = "" lcExpr = Expr llStrConv = User = "" ENDIF SET DATASESSION TO This.CurrentDataSession

IF !EMPTY(lcExpr) lcResult = EVALUATE(lcExpr) IF VARTYPE(lcResult) = "C" IF llStrConv *-- Report field is in UTF-8 - convert it to Unicode cContentsToBeRendered = STRCONV(lcResult, 12) ELSE *-- Report field is in Unicode already cContentsToBeRendered = lcResult ENDIF ENDIF ENDIF

DODEFAULT(nFRXRecNo ; , nLeft, nTop, nWidth, nHeight ; , nObjectContinuationType, cContentsToBeRendered, GDIPlusImage)



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

January 31, 2012

I'm trying to display some Unicode data stored in dbf fields/memo fiels on a form. I'm using only fox native DBF. All the examples relating to Unicode are SQL so it's complicating for me.

Could any of you pls give me a simpler way to do this. I would appreciate some simpler code that would display a form with a textbox and a dbf (not SQL).

Thanks in advance.


© Rick Strahl, West Wind Technologies, 2003 - 2021