Find this article useful?
Printer Friendly View
   
 

Using Unicode in Visual FoxPro
Web and Desktop Applications


By Rick Strahl
www.west-wind.com
rstrahl@west-wind.com

 

Last Update:

December 14, 2008


 

What's covered:


 

Code for this article:

http://www.west-wind.com/presentations/foxunicode/foxunicode.zip

 

 

If you find this article useful, consider making a small donation to show your support  for this Web site and its content.

 

 

 

Unicode provides a universal display mechanism for strings in an application. A common problem for applications is to display all the different languages and character sets associated with them easily and consistently. Unicode provides a single character set that can display all languages with a single character representation mechanism that eases the complexities of displaying very different languages such as Chinese, Korean, Russian and Western Languages like English in a single form or Web page. Unfortunately, Visual FoxPro doesn't have native Unicode support, which means you have to do a bit of extra work and understand how to integrate strings from multiple character sets and how to work with Unicode data in Visual FoxPro.

 

In this article, I describe how you can use Unicode with your application in the context of supporting multiple languages simultaneously. Although Visual FoxPro can’t display Unicode directly, it can display different character set through use of CodePages – a locale specific character mapping - which VFP readily supports. This works well for applications that display content only from a single language/locale. But this approach has serious limitations if you need to display strings from multiple languages simultaneously. I start with an overview of the issues and how to work with Unicode in general, then show you how to retrieve and update Unicode data, and finally, show you how to get the Unicode content to display both in your Web and desktop user interfaces.

Please note that most of the solutions offered in this article rely on a couple of new Visual FoxPro 9.0 features, so VFP 9 or later is highly recommended. The native VFP data access and Sql Passthrough samples can work in VFP 8 or later.

Recently I was involved in a project where one of the requirements was to create content that displays in a variety of languages including Asian and Eastern European languages, using Unicode data stored in a SQL Server database. My particular problem involved reading input from various languages, saving the data to SQL Server and then displaying and capturing the data from multiple languages on a single Web page.

 

Visual FoxPro doesn’t support Unicode directly so dealing with extended character sets like Cyrillic or any of the Asian sets is rather tough and somewhat confusing. It doesn’t help that there isn’t much documentation on this subject. In this article I’ll describe my discoveries of working with Unicode data in Visual FoxPro and how I eventually was able to address this problem with several solutions. Although the focus in this particular project was with Web data I’ll also briefly discuss Unicode inside of the FoxPro user interface towards the end of the article.

What’s the problem?

Visual FoxPro uses ANSI characters, which is a non Unicode character display mechanism. Instead it uses an n-byte (known confusingly as multi-byte, but it means “1 or 2 bytes”) representation of characters. Multi-byte character sets are based on code pages that represent a specific character set mapped to one or more languages. For example, CodePage 1252 – Windows Western – maps to most English and Western European languages. 1251 maps to Eastern European languages like Polish. and so on. This pre-Unicode mechanism was common in the days of DOS and Win16 and is still in use for non-Unicode applications running on 32 bit Windows today. The problem with this mechanism is that this scheme of code pages makes it very difficult to display content of multiple code pages at the same time. So if you want to display Russian and Chinese text on the same page it  is impossible to do because you can only use one code page at a time.

 

When Win32 came around, Microsoft pushed hard for a full Unicode implementation and Windows 95 and later are based primarily on Unicode characters. At the time this caused quite a stir because most development tools of the day did not support Unicode yet. Unicode at Microsoft really didn’t come into full fruition until Visual Basic and COM came into being in the early 1990’s. Microsoft’s COM component interface was entirely based on a Unicode based string system that was totally transparent to the developer.

 

Visual FoxPro, however, stayed with internal multi-byte character presentations and, to this day, has only minimal support for Unicode. The extent of its Unicode functionality is basically a few functions that allow you to convert strings (in a fairly limited manner) between multi-byte and Unicode, as well as a few string functions that can operate on Unicode strings. However, VFP has no way to actually display Unicode strings using its native controls.

 

Visual FoxPro can, however, consume Unicode data to some extent by performing automatic code page translations. By default Visual FoxPro uses a code page that matches the system settings. Every time any sort of conversion needs to take place – when reading Unicode data from a data source like SQL Server or accessing a COM object – Visual FoxPro automatically converts the string from Unicode into the appropriate code page, and vice-versa.. This works fine as long as the data you’re working with maps uniquely into this particular code page. So if I work with English/Western  data all day long I will never have a problem with Unicode data. That is until you start dealing with extended character sets that don’t match your current system’s code page.  You have some control over this process by setting VFP’s code page, and using SYS(3101)  - new in VFP 9 - which lets you configure how Visual FoxPro translates strings from COM objects. You can also convert strings directly to and from Unicode by using STRCONV() with nConversionSetting options 5 and 6 respectively. These functions allow you take a FoxPro string and convert it into Unicode and back using an optional code page (or locale identifier) parameter to specify how the translation occurs.


All this means that if you are running Windows in a Korean code page and you retrieve Korean data from the database VFP can deal with this data just fine both in string format and in the user interface. However, if you are running in the English version of Windows and are trying to read that same Korean data you will find that VFP cannot read or display this data (you get a bunch of question marks). While VFP can be coerced to convert strings using specific code pages (with STRCONV() or SYS(3101) for example) the FoxPro UI can only deal with a single code page at time.

 

You can add a secondary ‘multi-byte’ conversion language to your system in the Control Panel | Regional Settings | Advanced as shown in Figure 1. Unfortunately this setting is a pain to set – you have to reboot after changing it.

 

Figure 1: Configuring your default Unicode to Multi-byte translation language. The language you choose here will be VFP’s default extended character set  used to display text.

 

With the settings made I would be able to do conversions for Korean and I would be able to use and display strings in Korean format in my VFP applications. But I still wouldn’t be able to do the same for Chinese or Russian at the same time. In other words, the above option only works for a single language, which might be fine for an application that has to work only with a single language at a time.

 

Doing conversions this way is also very limited as your application has to be very aware which code page you are converting to and from in Visual FoxPro – without this information Unicode conversions from upper character sets that are very different from the machine default code page will result in lost or modified characters – a loss of data.

 

It’s very likely that if you convert Unicode characters from a non-default code page, you will not be able to convert these strings to a useful Visual FoxPro string in the VFP environment. If your machine is configured for code page 1252, and you read a Korean Unicode string and convert it to a FoxPro string with STRCONV() you will find that you get a string full of ???. The ? character indicates that VFP didn’t know how to convert this character into something that can be represented in the current code page. And this is really the crux of the problem with VFP’s Unicode support.

 

You can apply a CodePage or Locale to the conversion in which case Visual FoxPro will actually generate the right locale specific character sequence for you. Try out the following:

 

*** Korean Text as binary Unicode

lcBinStr=STRCONV("IAAgACAAIADIwG24jKwgAIzBHKwY" + ;

         "tJSyIABLusTJIADostC5MK4gAHzOfLcgAA==",14)

 

*** Convert to UTF-8

lcUTF8Str = STRCONV(lcBinStr,10)

? lcUTF8Str

 

*** Plain STRCONV() - gives ???

lcRawFoxString = STRCONV(lcUTF8Str,11)

? lcFoxString

 

*** Convert UTF-8 to Korean DBCS text

*** Creates ‘gibberish’ text in English, but OK in Korean

lcFoxString = STRCONV(lcUTF8Str,11,1042)

? lcFoxString

 

Although the latter approach would work to create a string in VFP that can display in a Korean system, it will be just another encoded format for your English version. The latter also requires that you know beforehand what locale you’re dealing with which is difficult if you work with multiple languages – which requires storing the locale with string somehow (database field etc.).

 

If you need to deal with strings that come from multiple character sets at a the same time (for example an application that uses English, Russian, Chinese and Korean all at the same time) the only way to work with this string data is by not converting it into the current code page, but rather use a neutral representation of the string instead. There are two ways that I’ve used for this: binary or UTF-8. Binary is a raw binary representation of the Unicode characters. UTF-8 is an encoded Unicode format that can be represented in two bytes. UTF-8 works by using multiple character combinations for upper Unicode characters that can take as much as 4 characters. You can pass Unicode data around the system in one of these formats without losing the formatting of the string. This maintains the string’s integrity, but it also means that the string can likely not  be used as you would normally use it, since it is not the actual string but a representation of the string. Anytime you need to work with the string you’ll have to convert the string. For example comparing two strings might require you to convert both strings into a common format that VFP can work with. Doing a STRTRAN() replacement on a UTF-8 encoded string might not get you the results you expect, for example.

 

Another big problem is that you can’t display these strings in FoxPro user interface controls, because the native Fox controls only work with current character set encodings. I’ll show you a workaround for this towards the end by using ActiveX controls and SYS(3101,65001) to force VFP to pass strings in convert strings to UTF-8 format to be passed to the ActiveX control. VFP performs the UTF-8 to Unicode and back conversions for us.

 

If you are working with Web applications, the display issue is a bit easier than with Windows Forms because web browsers are natively Unicode and UTF-8 enabled. Most web pages you see online are UTF-8 encoded and if you generate output in UTF-8 format you can easily display multiple language representations on a single page. UTF-8 is easy to generate and decode from in Visual FoxPro using STRCONV. You can convert to and from UTF-8 both from VFP’s native multi-byte characters as well as to and from Unicode.

Essential Conversions

Enough theory. Let’s look at some examples.

 

Before we can look at data access it’s important to understand how VFP can convert strings between Unicode and multi-byte. It’s also helpful to understand how you can deal with Unicode data coming from COM in VFP.

The key mechanism for converting strings is the STRCONV() function. To convert a string from VFP multi-byte to Unicode you can simply do:

 

lcUnicode = STRCONV("Hello World",5)

_cliptext = lcUnicode
? lcUnicode

 

The following converts a plain ANSI string to Unicode. The output of this conversion is:

H e l l o  W o r l d

 

Unicode is a double byte character set so every character is presented by at least two bytes. The spaces between the words aren’t actually spaces but CHR(0)s.

 

To go the other way we can now use 6 as the parameter:

 

lcAnsi = STRCONV(lcUnicode,6)

? lcAnsi

 

And voila we have our Hello World string back.

 

The example above works because we’re working within the default locale of the machine. The call to STRCONV() used the default locale, which in my case would be code page 1252 (ANSI – Western Windows). If I tried the above conversion with a string that contained Chinese characters the conversion would fail.

 

Worse, VFP can’t represent an extended string in the UI. For example, if I pick up some Chinese text from a Web page and paste it into VFP I only get a bunch of question marks like this:

 

??????

 

To help working with other locales than the currently installed one, you can pass a Locale Id (such as 1033 for US English) or a codepage (like 1252 for Western Windows) in the third parameter of STRCONV(). Be careful with these values though, and understand that they will produce locale encoded strings that won’t display properly in VFP unless you are running in that specific locale. If you take a Chinese string and convert it to DBCS with the Chinese code page, the string won’t contain ??? but it will contain something human unreadable and certainly not Chinese looking in your English version of VFP. So still no Chinese for you. At the same time realize though that the data is, in fact, a Chinese double byte representation of your text.

 

While this may suffice for applications work in a single locale (ie. an application always running in Chinese), this is a problem for applications that may display data from multiple locales. In that scenario you’d have to keep track of the locale information for each record, which becomes a mess quickly. For this reason, it’s much better to bypass this situation altogether and deal with Unicode inside of VFP as binary or UTF-8 data because it is essentially a universal format that works for all locales.

 

So how can we get Chinese data into our application? You need to represent the data as binary. So if you read Unicode data from a database in binary format you can assign it as a binary value to a VFP variable. You can also do this manually with a base64 or HexDecimal representations. For example:

 

lcUnicode = STRCONV("rpt3gmlfcoJnUW5/b5g6eU9c",14)

 

This assigns a Chinese Unicode string to the lcUnicode variable. If you were working with Chinese data all around you’ll be best served to pass the data through your application as binary data rather than trying to convert it into specific code page as you are bound to loose the string in the VFP code page conversions.

 

If you think this is messy, you’re right.

COM Objects

COM objects always accept and represent strings in Unicode format. So every time VFP calls a COM object a conversion happens behind the scenes converting VFP strings to and from Unicode. By default it uses the current locale settings for the conversion, so if data from an extended locale comes back VFP will have problems presenting that locale’s data in a VFP string.

 

To work around this issue Visual FoxPro 9.0 introduces a new SYS(3101) function that tells VFP to return the COM data to you in UTF-8 format (or any specific locale you specify):

 

SYS(3101,65001)

 

This tells VFP to convert all data to and from UTF-8. If you are retrieving extended character strings it makes sense to pull the data out in UTF-8 format and then perform any additional conversions to Unicode when you need to store the data to the database. As you’ll see a little later this option is very useful when dealing with data passed through ADO, because ADO is an all-COM interface to the data.

Working with Unicode Data in Visual FoxPro

If you need to work with Unicode data in Visual FoxPro, you want to treat the Unicode data inside of Visual FoxPro as binary or UTF-8. The following examples all rely on Visual FoxPro 9 because it makes the type casting a lot easier – you can do most tasks with VFP 8 as well, but it’s a little more verbose and less consistent. The VFP 9 CAST() function is very useful for consistent type conversions.

Visual FoxPro doesn’t have any native Unicode field types. Unlike SQL Server or most other SQL data bases Visual FoxPro can’t represent Unicode data internally unless you use a binary format to store it.

 

Using binary data is not optimal because you loose the ability to treat the data like a string. String comparisons are not easy, the data cannot sort according to language sorting patterns and anytime you do anything with the data it requires that you convert. But you can at least store this data. The following code snippet demonstrates how create a table and get some Unicode binary data into it:

 

IF !FILE("wwdemo\ForeignData.dbf")

   *** Create table with binary text fields!

   CREATE TABLE wwdemo\FOREIGNDATA ;

      (     ID          C(10),;

            DESCRIPT    Blob,;

            LDESCRIPT   Blob);

   USE ForeignData Shared           

 

   *** Load up sample data as binary Unicode!

   INSERT INTO  ForeignData ;

   ( id,descript,lDescript) VALUES;

   ( SYS(2015), STRCONV("Korean",5),;

     STRCONV("IAAgACAAIADIwG24jKwgAIzBHKwYtJSyIABLusTJIADostC5MK4gAHzOfLcgAA==",14))

 

   INSERT INTO  ForeignData ;

   ( id,descript,lDescript) VALUES;

   ( SYS(2015),STRCONV( "Chinese",5) ,;

     STRCONV("rpt3gmlfcoJnUW5/b5g6eU9c",14)  )

  

   INSERT INTO  ForeignData ;

   ( id,descript,lDescript) VALUES;

   ( SYS(2015),STRCONV( "Russian",5),;

     STRCONV("EQQ+BDsETARIBD4EOQQgAEYEMgQ1BEIEPQQ+BDkEIAAyBD0EQwRCBEAENQQ9BD0EOAQ5BCAANAQ4BEEEPwQ7BDUEOQQ=",14)  )

ELSE

  USE ForeignData IN 0

ENDIF           

SELECT ForeignData

 

 

As you can see the strings are being loaded using Base64 encoded text rather than the actual string. That’s because VFP can’t represent the string directly, so to get these strings I ran a query that returned binary data and encoded it into Base64. You can also use HexBinary encoding ( STRCONV(,15) ) in the same way. Obviously if you’re capturing this data in your UI a different approach will be needed. We’ll get to that shortly. The above loads up the data in binary format – but remember that this data is merely a binary Unicode string representation.

 

If you were to look at the Descript field in the table you’d see double spaced characters. The binary data for the exotic languages on the other hand will look like binary garbage. You will not see a character representation because VFP cannot deal with these extended Unicode characters. But the data is actually there as Unicode. Note that you don’t get ???? in the field, because the data is binary.

 

Next let’s retrieve this data and use it. To so we’ll query and convert the data to UTF-8. We can simply retrieve this data with:

 

select Id,Descript as Note,LDescript as Description ;

     FROM ForeignData INTO CURSOR TFData

 

This returns the data as binary. To convert it to UTF-8 we can re-query and use STRCONV():

 

SELECT Id,;

 CAST(STRCONV(Note,10) as M)  as Note ,;

 CAST(STRCONV(Description,10) as M) as Description;

 FROM TFData ;

 INTO CURSOR TFData2  

 

You can do this in a single SQL statement, but I separate it out here for clarity. Once we have the UTF-8 data, it’s ready to be embedded into an HTML document.  We can display our multi-cultural data now by creating an HTML string:

 

TEXT TO lcHTML

<HTML>

<HEAD>

<meta http-equiv="Content-Type" content="text/html; charset=utf-8">

</HEAD>

<body>

<h1>VFP Unicode Data Representation</h1>

<hr>

ENDTEXT

 

*** UTF-8 Encode - not necessary here, but if you had extended text

*** you'd need to do this

lcHtml = STRCONV(lcHTML,9)

 

SCAN

   lcHTML = lcHTML + Note + " - " + Description + ;

            "<hr>" + CHR(13) + CHR(10)

ENDSCAN

 

lcHTML = lcHTML + "</body></html>"

 

ShowHtml(lcHtml)

 

The key is the character encoding in the Meta tag that lets the browser know that this data will be UTF-8 encoded and should display as Unicode. The result is shown in Figure 2.
 

Figure 2: Displaying the multi-cultural Unicode data retrieved from the FoxPro database’s binary fields.

 

Next we’ll need to write data captured from a Web page to the table and we need to do pretty much the same thing in reverse. I’ll use a Web form as an example, in this case using an all VFP solution with Web Connection. I’ll then look at using code from within an ASP COM component.

Writing out and Capturing data with Web Connection

If you’re using an all VFP solution any form data captured on an HTML form will come back formatted based on the CharSet of the HTML document. If we were generating the data like above for a Web page display you’d explicitly set the character set as part of the content type. This means you need to make sure that you generate your output and properly encode it.

 

This sounds simple, but it can be a bit involved if you’re using VFP data because you may have to encode the template code as well as the data. For the example shown in Figure 3 below the data is merged into a template, while the actual data for the table is generated off a cursor.

 

*** The SQL statement to retrieve the data - UTF-8 encoded

SELECT Id,;

  PADR(STRCONV(Note,10) as Note, ;

  PADR(STRCONV(Description,10) as Description ;

 FROM TFData ;

 INTO CURSOR TFData2

 

*** Create Grid from Data – remember data is already UTF8 encoded!

loSC = CREATEOBJECT("wwShowCursor")

loSC.ShowCursor()

pcCursorText = loSC.GetOutput()

 

*** Load the template and UTF-8 Encode

lcTemplate = STRCONV( FileToStr( Request.GetPhysicalPath() ),9)

 

*** Merge template with our content – pcCursorText is already UTF-8 encoded

*** and gets embedded into the template

lcEncodedResult = MergeText(@lcTemplate)

 

*** Create HTTP header to include the proper Content Type and charset

Response.ContentType = "text/html; charset=utf-8"

 

*** Write it all out to the Web Server

Response.Write( loHeader.Getoutput() )

Response.Write(lcEncodedResult)

 

The result then is a fully UTF-8 encoded document as shown in Figure 3. Note that the Content-Type is returned explicitly as text/html with a charset of UTF-8! This is important, so the browser knows to display this page as Unicode (without character encoding) rather than the default configured character set. Without this the browser would try and guess the code page and in the case of UTF-8 the guess is usually wrong. It’s very important to set the Content-Type correctly!

 

 

Figure 3: Capturing data on a Web Form happens using the Web Page’s character encoding – in this case UTF-8. User Input is captured as Unicode in UTF-8 format and then converted and written to the database as binary.

 

This form also allows capturing of data in any language and it adds to the form. We can now capture the data from the input field like this:

 

IF Request.IsPostBack()

   *** Read the raw input data – format is UTF-8 because page is UTF-8 encoded

   pcSavedDescription = Request.Form("txtDescription")

 

   *** Convert UTF-8 to Unicode and store as BINARY string!!!

   pcSavedDescription =  STRCONV(pcSavedDescription,12)

 

   *** One more time for the title string

   pcSavedTitle = Request.Form("txtTitle")

   pcSavedTitle = STRCONV(pcSavedTitle,12)

              

   *** Now insert the BINARY string into the text as nVarChar

   insert into ForeignData(id,descript,lDescript) values ;

                  (SYS(2015),pcSavedTitle,pcSavedDescription )

 

    select Id,Descript as Note,LDescript as Description
          
FROM ForeignData INTO CURSOR TFData

ENDIF

 

Notice that the data from Request.Form() is UTF-8 encoded because the Web page’s character set is UTF-8. Any Form data posted back comes back in UTF-8 format. We now have to convert this UTF-8 into a Unicode string. Remember that the Descript and lDescript fields are binary fields and we’re taking the Unicode data and storing it as is into those binary fields.

 

You can view this entire demo online including source code at:

http://www.west-wind.com/wconnect/WcScripts/ShowForeignData.wwd

Capturing Data with a VFP COM component in ASP or ASP.NET

If you’re using ASP or ASP.NET with a Visual FoxPro COM component the behavior will be slightly different. In these environments, ASP and ASP.NET objects are COM objects that already understand Unicode. VFP still doesn’t, but you can pass data to and from these objects with UTF-8 using SYS(3101).

 

The key difference is how we can pass data to the COM object. In the Web Connection example I explicitly converted everything to UTF-8. If you have extended character sets you still have to do this inside of a VFP COM object. You’ll use the SYS(3101,65001) function to pass any data to ASP.NET in UTF-8 format. Using this function basically does two things:

 

  1. It tells the COM object that data is sent in UTF-8 format – you’re responsible for encoding to UTF-8 and VFP does the rest.
  2. It tells the COM object to return the data to you in UTF-8 format and you will be responsible for decoding it.

 

The easiest thing is to simply call SYS(3101,65001) at the being of your ASP/COM method (or in the Init of the component) and always pass and read data by UTF-8 conversion.

 

The process involved is to capture the ASP context object and retrieve the various worker objects off it. You can do this with the following code within any method of a COM component called from ASP or ASP.NET (with ASPCOMPAT set to true):

 

SYS(3101,65001)  && Return Unicode data in UTF-8 format

 

oMTS  = CreateObject("MTxAS.AppServer.1")

THIS.oScriptingContext = oMTS.GetObjectContext()

loRequest = oContext.item("Request")

 

At this point you can then retrieve data from the ASP/ASP.NET Request object.

 

IF !ISNULL( Request.Form("txtDescription").Item()  )

   *** Read the raw input data – Data will be UTF-8

   pcSavedDescription = Request.Form("txtDescription").Item()

 

   *** Convert UTF-8 to Unicode and store as BINARY string!!!

   pcSavedDescription =  STRCONV(pcSavedDescription,12)

 

   pcSavedTitle = Request.Form("txtTitle").Item()

   pcSavedTitle = STRCONV(pcSavedTitle,12)

 

   *** Now insert the BINARY string into the text as nVarChar

   insert into ForeignData(id,descript,lDescript) values ;

                  (SYS(2015),pcSavedTitle,pcSavedDescription )

 

select Id,Descript as Note,LDescript as Description

       FROM ForeignData INTO CURSOR TFData

ENDIF

 

The same is true if you’re writing output to Response.Write() or back to the Page or one of the controls on it. The following converts from Unicode to UTF-8 for display:

 

Response.Write( STRCONV( lcUniCodeString, 10) )

 

Remember that ASP and ASP.Net pages by default are UTF-8 encoded so you want write any raw output in this format. Make sure however, that the default has not been overridden at the ASP page level.

 

When working with VFP data you can choose to store the data in the database either as binary Unicode as I showed here or as UTF-8. If you are building a pure Web application it might actually make sense to use  UTF-8 as data passes through the system in UTF-8 – it might save a few conversions. The main reason I used binary data here is because it matches with the Unicode retrieval through ODBC as we’ll see next.

 

Ok, so now you’ve seen the full cycle of displaying and retrieving Unicode data with FoxPro data. Essentially you need to work with data in a non-standard way. If you use VFP data tables to hold Unicode data you will need to work with binary or UTF-8 data, which means you loose the ability to effectively index or search the data which can become very inefficient. Since the VFP data engine has no knowledge of Unicode as a data type it can only index your data in binary. This may or not be sufficient.

 

A better choice is to use a Unicode enabled back end like SQL Server. With SQL Server you actually have two choices for working with Unicode: SQL pass through, which uses ODBC, or ADO, which uses COM and OleDb. Let’s look at those two approaches…

Doing Unicode with Sql Server and  SQL Pass Through

Sql Server supports native Unicode data types in the nChar, nVarChar and nText types. These types allow storage of true Unicode strings that are fully searchable and can be properly indexed. Indexing of Unicode fields is actually a tricky matter as well because Unicode can potentially represent multiple locales and so for Unicode fields in SQL Server 2000 you can choose an indexing collation that is used to determine index order.

 

SQL pass through (SPT) is FoxPro’s oldest and in most cases most efficient access mechanism to access SQL data sources including SQL Server. SPT is effectively VFP’s only native data access mechanism to SQL Server upon which other technologies like remote views and the CursorAdapter base class are built.

 

Because VFP doesn’t provide a native Unicode type, SPT is also somewhat limited in what you can do with Unicode data retrieved from SQL Server. The problem is that while you can query data from SQL Server in Unicode fields, ODBC will try to convert that data into the default code page running on the machine. For example if I run the following code:

 

lnHandle =SQLStringCONNECT(;

   "driver={sql Server};server=(local);"+;

   "database=WestWindAdmin")

? SQLEXEC(lnHandle,"select Id,descript as Note," + ;

  "CAST(lDescript as nVarChar(120)) as Description"+;

  "from foreignData")

BROWSE

 

it retrieves the data that is displayed in Web page from Figure 2. The unacceptable Browse result is shown in Figure 4.

 

 

 

Figure 4: Unicode data returned from SQL Server may not be quite what you expect. The raw data will convert to the default code page for the system and any conversion can only do a single code page at a time.

 

This is not quite what we’re looking for. The data is returned with an auto-conversion to the current code page that the system is running in. This means I can see the English text just fine and even some of the Polish characters (those that match the 1252 character set) look OK. But if you look closely even at the Polish text you’ll see that the translation is not working and that some characters are missing their accents. For the Russian, Korean and Chinese ODBC couldn’t convert at all so the entire strings are returned as ??? characters.

 

So retrieving the data as string values into VFP is not possible from SQL Server any more than it was with VFP earlier. But as with the VFP data we can also return the data as binary:

 

SqlExec(lnHandle,[select ID,] +;

 [CAST(CAST(Descript as nVarChar(4000)) as VarBinary(8000)) as Note,] +;

 [CAST(CAST(lDescript as nVarChar(4000)) as VarBinary(8000))  as Description] +;

 [from ForeignData],"TFData")

 

When you run this SQL Statement you’ll get back a cursor with two binary memo fields and these memo fields will contain binary Unicode strings. Note that this basically converts the nVarChar and nText data into VarBinary which is the only way to return this data to VFP without encoding. Unfortunately there’s no direct conversion from nText to binary, so we have to go the intermediate route of using nVarChar. Because nVarChar is limited to a max size of 4000 characters the conversion is limited: A major limitation if you have large data fields.

 

As with the VFP code, the binary data is not all that useful so we need to convert to UTF-8 first by re-filtering the data into UTF-8:

 

SELECT Id,;

    CAST(STRCONV(Note,10) as M)  as Note , ;

    CAST(STRCONV(Description,10) as M)  as Description ;

    FROM TFData ;

INTO CURSOR TFData2  

 

At this point we can use the data the same way as we did in the Web page example earlier by simply embedding the UTF-8 text into the Web page.

 

To write the data back we need to take our UTF-8 input, convert it to binary and then cast it in SQL Server back to nVarChar. Note, that you can’t cast to nText – SQL Server doesn’t allow casting to nText or Image data. The abbreviated process looks like this:

 

*** UTF-8 to Unicode conversion

pcSavedDescription =  STRCONV(pcSavedDescription,12)

pcSavedTitle = STRCONV(pcSavedTitle,12)

 

*** Must explicitly force to binary – can also use CAST in 9.0

pcSavedTitle = CREATEBINARY(pcSavedTitle)

pcSavedDescription = CREATEBINARY(pcSavedDescription)

 

*** SQL Server requires CASTS on the server!

SqlExec([insert into ForeignData (ID,Descript,lDescript) values ] +;

       [(?pcID, CAST(?pcSavedTitle as nVarChar(4000)),] +;

  [CAST(?pcSavedDescription as nVarChar(4000) )  ) ] )

 

 

This is a fairly complicated mechanism as you really have to understand the underlying mechanics of this process to make this work. You also have to embed CASTS into every SQL statement which is a real mess for any business logic. But most importantly understand that you are limited to 4000 characters of Unicode text with this approach because of the nVarChar limit of 4000 Unicode characters, which makes this a partial solution at best. However, it can be useful if you only need Unicode functionality in one or two places in your existing application that already uses SQL pass through.

 

SQL pass through would be workable if VFP had a Unicode aware database type that could at least retrieve and write Unicode data without any sort of conversion to binary (or UTF-8) from which we could convert. This would allow reading and writing nText data to the server directly and overcome this string limit. Maybe next version…

Retrieving Unicode with ADO

To work around this shortcoming in SQL pass through, you have to work with ADO. ADO is COM-based and allows direct access to Unicode data. As mentioned earlier COM inherently uses Unicode for all strings and by using SYS(3101) we can gain more control over the Unicode strings returned from SQL Server (or other backend).

 

Visual FoxPro 9.0 required for this solution

SYS(3101) and the CursorAdapter’s ADOCodePage property are new in Visual FoxPro 9.0. Since these two features are vital to making ADO properly translate Unicode data you need to use Visual FoxPro 9.0 for this solution to work.

 

In addition, we can also use the CursorAdapter class in VFP 9 or later. The CursorAdapter can work directly against ADO, and it sports a code page property that allows you to return data in a specific code page including the 65001 code page which is UTF-8.

 

Let’s look at a couple of examples on how to retrieve and update data via ADO. The first example retrieves data using plain ADO and returning an ADO Recordset object:

 

FUNCTION AdoRecordSetQuery()

 

*** Make COM use UTF-8 code page for strings

SYS(3101,65001) 

 

LOCAL oConn as ADODB.Connection

oConn = CREATEOBJECT("ADODB.Connection")

oConn.ConnectionString = this.cADOConnectionString

oConn.Open()

 

LOCAL oCommand as ADODB.Command

oCommand = CREATEOBJECT("ADODB.Command")

oCommand.ActiveConnection = oConn

 

oCommand.CommandText = ;

  "Select Descript, lDescript as Description " + ;

  "from foreigndata"

 

LOCAL oRS as ADODB.RecordSet

oRS = oCommand.Execute()

 

lcHTML = this.HtmlHeader("ADO Recordset via COM")

***

IF !ISNULL(oRS) AND oRS.State = 1

   DO while !oRS.EOF

      lcHtml = lcHTML + ;

         oRS.Fields("Descript").Value + " - " +;

         oRS.Fields("Description").Value + "<hr>" + CHR(13) + CHR(10)

      oRS.MoveNext()

   ENDDO

ELSE

   WAIT WINDOW "SQL command failed." NOWAIT

   RETURN

ENDIF  

 

lcHTML = lcHtml  + "</body></html>"

 

ShowHtml(lcHTML)

 

The data is returned as an ADO RecordSet here. The data resides in a COM collection and we can return the results in UTF-8 format easily by way of SYS(3101,65001), which instructs VFP to pass strings to COM as UTF-8.

 

Notice that the SQL statement is passed as a plain string. In theory every string passed to the COM object now needs to be UTF-8 encoded, but since UTF-8 maps to the lower ASCII set, for plain command strings no encoding is required; if you embed string literals however you should encode your command strings. Also note that the SQL command is a straight SQL command against the Unicode data. Unlike SPT, there are no CASTs or other conversions but we simply execute the SQL statement as needed.

 

Because the result is a RecordSet we can’t use the data as a cursor. However we can loop through the RecordSet and pull out any values. Each of those values is UTF-8 encoded and ready to be embedded into a web page. The code loops and builds an HTML document that displays data in the same format as Figure 1.

 

Cool – we can now retrieve our data – but we still don’t have a cursor. To convert a RecordSet into a FoxPro Cursor we can use the CursorAdapter object in Visual FoxPro. Here is the same routine as above using the CursorAdapter:

 

FUNCTION AdoQuery()

 

pcLang="%"  && Our query parameter

 

LOCAL oConn as ADODB.Connection

oConn = CREATEOBJECT("ADODB.Connection")

oConn.Open(this.cADOConnectionString)

 

LOCAL oCommand as ADODB.Command

oCommand = CREATEOBJECT("ADODB.Command")

oCommand.ActiveConnection = oConn

 

LOCAL oRS as ADODB.RecordSet

oRS = CREATEOBJECT("ADODB.RecordSet")

oRS.ActiveConnection = oConn

 

LOCAL oCA as CursorAdapter

oCA = CREATEOBJECT("CursorAdapter")

oCA.ADOcode page = 65001

oCA.Alias = "TSqlQUery"

oCA.DataSourceType = "ADO"

oCA.DataSource = oRS

 

oCA.SelectCmd = ;

  "Select Descript, lDescript as Description " + ;

  "from foreigndata where descript like ?pcLang"

 

*** Force all data at once into cursor

oCA.FetchSize = 99999999

 

IF USED(oCA.Alias)

   USE IN (oCA.Alias)

ENDIF

 

*** Fill the cursor based on Command object

*** Must pass Command object to get parameter parsing to work

IF !oCA.CursorFill(,,,oCommand)

  AERROR(laErrors)

  ? lAERRORS[1]

  ? lAERRORs[2]

  RETURN

ENDIF

 

*** Make free standing cursor

oCA.CursorDetach()

 

BROWSE

 

When using the CursorAdapter you split your work between the CA and the ADO COM objects. Notice that the CursorAdapter’s code page property is set to 65001, which ensures that VFP communicates with ADO using UTF-8 for strings.

 

The SQL command is set on the CursorAdapter rather than the ADO Command object and the CA handles the command parsing. This makes it possible to use SQL pass through type syntax with ADO, as the CA handles the translation from SPT parameter syntax to the appropriate ADO parameter objects.

 

If you like the CursorAdapter, it can provide you with full round trip support of the data. When configured with  INSERT/UPDATE/DELETE/SELECT commands the CA can manage the full range of remote connectivity for updating a cursor and since it uses ADO and COM all your Unicode management is taken care of for you.

 

If you’re like me though, you may find that the CursorAdapter doesn’t fit with your existing business object architecture. I rather deal with SQL more directly, rather than working with the preconfigured remote view type interface that the CursorAdapter provides. To do this I need to fire non-query commands (INSERT/UPDATE/DELETE/Stored Procedures) directly against ADO.

 

Here’s an example of how to do this. The following shows the raw syntax to perform an INSERT to ADO with Unicode data that is UTF-8 encoded:

 

FUNCTION AdoInsert()

 

*** Some Russian text - must convert to UTF-8 for Insert

lDescriptBin = STRCONV(;

"EQQ+BDsETARIBD4EOQQgAEYEMgQ1BEIEPQQ+BDkEIAAyBD0EQ"+;
  "wRCBEAENQQ9BD0EOAQ5BCAANAQ4BEEEPwQ7BDUEOQQ=",14)

lcDescriptUtf8 = STRCONV(lDescriptBin,10)

 

pcID = SYS(2015)

pcDescript = "Russian"

pclDescript = lcDescriptUtf8

 

lcSql = [Insert into ForeignData ]

        [(id,descript,lDescript) values ] +;

        [(?pcId,?ocDescript,?pclDescript)]

 

SYS(3101,65001)

 

LOCAL oConn as ADODB.Connection

oConn = CREATEOBJECT("ADODB.Connection")

oConn.ConnectionString = THIS.cAdoConnectionString

oConn.Open()

 

LOCAL oCommand as ADODB.Command

oCommand = CREATEOBJECT("ADODB.Command")

oCommand.ActiveConnection = oConn

oCommand.CommandText = lcSQL

 

 

LOCAL oParameter as ADODB.Parameter

oParameter = CREATEOBJECT("ADODB.Parameter")

oParameter.Type= 202  && adVarWChar

oParameter.Value = pcId

oParameter.Size = LEN(pcID)

oCommand.Parameters.Append(oParameter)

 

 

*** Or wrappered

this.AdoAddParameter(oCommand,pcDescript)

this.AdoAddParameter(oCommand,pclDescript)

 

 

lnAffected = 0

? oCommand.Execute(@lnAffected,,128)     && No result set

 

The process should be familiar to you by now. Any string you want to pass to SQL Server should be UTF-8 encoded. The Russian text above starts in binary (because I can’t paste it into the text), is converted to UTF-8 and then assigned to a parameter object’s Value property in ADO. ADO is set to SYS(3101,65001) so all parameters are passed in as UTF-8 and internally converted to Unicode. If you watch SQL Profiler you will see that ADO indeed sends strings in Unicode format to the server when the INSERT runs.

 

The same logic applies for UPDATE commands or stored procedure calls that require parameters to be passed. Note that with native ADO you will have to explicitly assign parameters using the ADODB.Parameter object rather than the familiar SQL pass through syntax. To make parameter passing a little easier the sample code that comes with this article provides a AdoAddParameter() method that lets you pass just the essentials (value and name).

Making life easier with SQL Server Class Wrappers

To make plain SQL syntax more consistent, I’ve provided a couple of classes – wwSQL and wwADOSQL – that provide a low level SQL implementation that works with both ODBC and ADO transparently. The wwSQL class was originally built as a very thin wrapper around SQL pass through and I recently added wwADOSql with the same interface to support Unicode functionality. The core methods of this class are Execute(), ExecuteNonQuery() and ExecuteStoredProcedure() plus a common parameter implementation that is not dependent on private variables (ie. you can pass it around in distributed applications). The class also manages parameter parsing from SQL pass through syntax for all but Stored Procedure calls with OUT parameters, which must be explicitly implemented using ExecuteStoredProcedure(). This class is quite useful if you are more comfortable with a SQL pass through style direct SQL interface.

 

With this class SQL access and working with ADO UTF-8 strings becomes as simple as:

 

LOCAL o as wwADOSql

IF .T.

   o = CREATEOBJECT("wwADOSql")

   o.ncode page=65001

   ? o.Connect("Provider=sqloledb;Data Source=(local);"+;

               "Initial Catalog=WestwindAdmin;"+;

               "Integrated Security=SSPI")

ELSE

   o = CREATEOBJECT("wwSql")

   ? o.Connect("driver={sql server};server=(local);database=WestWindAdmin")

ENDIF

 

? o.Execute("select * from foreigndata") && Utf-8 data returned

if o.lError

      ? o.cErrorMsg

      RETURN

endif

BROWSE

 

o.AddParameter("Odkryj Telefon Motoroli Który Pasuje " +;

               "do Twojego Stylu Å»ycia!Odkryj Telefon "+;
               "Motoroli Który","pcDescript")  && UTF-8 encoded

? o.ExecuteNonQuery("update foreigndata " +;

                    "set ldescript=?pcDescript " +;

                    "where ID = '_1F80YXMEE' ")

 

? o.naffectedrecords

 

Watch performance

ADO is a good solution for the Unicode problem. However, realize that using ADO over SQL pass through has a fairly steep performance cost. In my tests of several applications with small data retrievals and updates the ADO throughput caused the app to loose anywhere between 30-50%. of its performance. Data retrieval of large record sets especially can be even slower than that, so be careful before jumping on the ADO wagon too quickly and unconditionally. However, if you need true Unicode data access for your application ADO is really the only way to go.

Desktop Interface

I tend to build Web applications for the most part, but as I was working on this article I figured there must be a way to display Unicode content in VFP as well. As mentioned earlier if you’re running in the default locale and the Unicode data is compatible with the default locale VFP will happily do the conversions for you and display your Unicode data in the User Interface. Problems arise only if you need to display more than one code page at a time. The VFP user interface basically allows you to display a single code page at a time. This means that if you want to display output from multiple different locales with differing character sets you’re pretty much out of luck with the native Visual FoxPro user interface.

 

However, there is a workaround: You can skip using VFP controls and use ActiveX controls instead. Figure 5 shows a very simple Visual FoxPro form of the multi-language data I’ve been working with in this article.

 

Figure 5 – Displaying Unicode in Visual FoxPro applications requires that you use ActiveX controls. Other than the default Locale, Visual FoxPro’s native controls cannot display Unicode. This form uses the DataDynamics SharpGrid and the Microsoft Forms Editbox to display the Unicode text.

 

The form uses the Microsoft Forms EditBox control and the Data Dynamics SharpGrid control to display its data. I would have preferred to use a ‘standard’ control like the ListView or even the hideous FlexGrid control for the data, but not all ActiveX controls support Unicode character sets. Specifically the MSCOMCTL controls (TreeView, ListView, etc.)  and many other of the old stock ActiveX controls that Microsoft shipped with Visual FoxPro are not capable of displaying Unicode. This leaves you with finding third party controls that do support Unicode – most of the big vendor suites (like Component Source, Developer Express etc) support Unicode in their ActiveX controls and if you’re going this route it might actually make sense to go with a whole suite as you will need ALL interface controls (labels, textboxes, lists, dropdowns etc.) and using a suite might make this process a bit more consistent.

 

This is obviously not an ideal solution, but at least it can be done if necessary.

Summary

Using Unicode in Visual FoxPro is definitely not for the faint of heart. If you are lucky and you can get away with using a single locale specific Unicode language, Visual FoxPro’s code page translation mechanism can get you reasonable language capabilities in your application without too much extra work. Problems don’t arise until you have more than one language involved.

 

With multiple languages you have to resort to pass data through the application using either binary or UTF-8 for strings. For data access true Unicode support is not available in VFP’s data engine, but requires that you use a SQL Backend like SQL Server that does support it. The only reliable way to communicate with Unicode data from a SQL Backend is through ADO whether you’re talking directly to ADO or you use a wrapper like the CursorAdapter or the wwAdoSql class provided here with this article.

 

It’s a shame VFP doesn’t have better support for Unicode. It seems that with a few enhancements – specifically a type that knows about Unicode – a lot of the ugliness I’ve shown here could be removed as we would at least be able to pass the data around the system and to the backend without conversion. If VFP could pass Unicode data directly through ODBC we would not be required to give up performance with ADO.

 

In the meantime using COM and ADO seems to be the best solution to Unicode data. I hope that this article and the samples and wwSQL class make this process a bit easier for you if you need to go this route.

 

I need to thank the following people for their significant input:

 

Craig Tucker of Centiv who got me started on this project

Aleksey Tsingauz from Microsoft  for his patience with me and his CursorAdapter help

Steven Black for some needed feedback and review

 

 

 

Code for this article:

http://www.west-wind.com/presentations/foxunicode/foxunicode.zip

 

 

If you find this article useful, consider making a small donation to show your support  for this Web site and its content.

 

 

 
  White Papers                  Home |  White Papers  |  Message Board |  Search |  Products |  Purchase | News |