Recently I had to deal with the problem of displaying and saving Unicode data in a Visual FoxPro form. As we all know, Visual FoxPro does not support Unicode, so this was quite a challenge for me. I would never have solved this problem by myself if Gregory Adam from the UniversalThread would not lend me his generous support and practically solved the problem of saving for me. I want to show the full solution we came to together as this may be a very important topic for the remaining Visual FoxPro developers (and because Koen asked me to write this blog post).

Ok, we all know the classical and very comprehensive article on this topic written by Rick Strahl Using Unicode in Visual FoxPro Web and Desktop Applications. So, I started from this article. Our application uses SQL Server to hold data and uses sql pass thru technique to send information from and to SQL Server.

Now, since I am describing the solution I used for the particular application, I will start with the SQL Server table I was using. That table was named Prefs_sl and it was a flat table with 1 record containing all preferences for a sales point. Because of its flat nature it was badly normalized. Say, it had 11 language fields language00 – language10 (nvarchar(100)) to hold name of the language the Sales Point can display and 11 image lngImage00-lngImage10 (varbinary(max)) fields to hold the image of the country flag. So, this was the table I was working with on the SQL Server side.

Now, I wanted to display these 11 languages with their corresponding flags on the form, so the logical choice was to create a class with the textbox for the unicode text and image for the image flag and then instantiate this class on the form.

The first question was what to chose for displaying unicode data? Rick mentioned MS Edit Box control ActiveX, but I didn’t find it in the list of my installed ActiveX. But I found MS Forms2 TextBox ActiveX control and since I also remembered this thread on foxite forum, I decided to try this ActiveX control.
The first problem I encountered with this ActiveX as that it didn’t show up until I clicked on it when I was running the form. Luckily I found the following solution – invoke SetFocus of this control (I called it txtLanguage in my class) and then it was displayed correctly. I am not sure if my solution is the best to handle this ActiveX behavior quirks, but hey, it works.

Ok, so far so good. Now, following Rick’s article I used the following in my form to get the data in the binary format:

LOCAL lcSQL
TEXT TO lcSQL noshow
SELECT pri_key      
      ,defltlang
      ,cast(language00 as varbinary(100)) as language00
      ,lngimage00      
      ,cast(language01 as varbinary(100)) as language01
      ,lngimage01
      ,cast(language02 as varbinary(100)) as language02
      ,lngimage02
      ,cast(language03 as varbinary(100)) as language03
      ,lngimage03
      ,cast(language04 as varbinary(100)) as language04
      ,lngimage04
      ,cast(language05 as varbinary(100)) as language05
      ,lngimage05
      ,cast(language06 as varbinary(100)) as language06
      ,lngimage06
      ,cast(language07 as varbinary(100)) as language07
      ,lngimage07
      ,cast(language08 as varbinary(100)) as language08
      ,lngimage08
      ,cast(language09 as varbinary(100)) as language09
      ,lngimage09
      ,cast(language10 as varbinary(100)) as language10
      ,lngimage10      
  FROM dbo.prefs_sl
endtext  
mysqlexec(m.lcSQL, 'Prefs_sl', program())

mysqlexec here is a wrapper for the sqlexec function.

Now I found the first discrepancy with Rick’s article – I found that I don’t need any conversion after that, I can assign the field’s content to the text property of this ActiveX (or ControlSource property) and the control correctly displays the unicode data!

I was happy – the first part of displaying data turned out to be quite easy!

Here is the code I used to display the data:

FOR EACH loObject IN thisform.Objects foxobject
   IF loObject.Baseclass = 'Container'
      loObject.LanguageField = SUBSTR(loObject.name,4,LEN(loObject.name))
      loObject.ImageField = 'lngImage' + RIGHT(loObject.Name,2)
   ENDIF 
next   

thisform.cntLanguage01.SetFocus()

And in the container itself the following code in the LanguageField assign method:

this.lblLanguage.caption = this.lblLanguage.caption + ' ' + transform(val(right(tLanguageField,2)))
  this.txtLanguage.Enabled = .t.
  this.txtLanguage.TabStop = .t.   
  this.lProgrammaticChange = .t.
  this.txtLanguage.text = evaluate('prefs_sl.' + tLanguageField)
  this.lProgrammaticChange = .f.

Everything was good so far. However, I haven’t expected how hard (for me) it will be the ‘saving’ data part. I was spending days trying various ideas from Rick’s article and was still unable to achieve the desired result. I was about to throw all I had away and try to switch to ADO for data retrieval, but luckily Gregory Adam helped me here and published a working sample.

First of all, we need to make sure there is no UT8 translation to the current code page. For this we need to do the following

COMPROP(this,'UTF8',1)

In the txtLanguage Init method.

We also need the following functions:

&& StringConversion
&& Gregory Adam 2011
*===============================================================================
#define true	.T.
#define false	.F.

*_______________________________________________________________________________
function StringToUTF8(utf8Out, stringIn, codepageIn)
	
	local success
	success = true
	
	do case
	case !m.success
	
	case !StringToUTF16(@m.utf8Out, m.stringIn, m.codepageIn)
		assert false
		success = false
	
	case !StringUTF16ToUTF8(@m.utf8Out, m.utf8Out)
		assert false
		success = false
	
	endcase
	
	return m.success
	
endfunc
*_______________________________________________________________________________


*_______________________________________________________________________________
*_______________________________________________________________________________
#define CP_ACP					0
#define CP_MACCP				2
#define CP_OEMCP				1
#define CP_SYMBOL				42
#define CP_THREAD_ACP			3
#define CP_UTF7					65000
#define CP_UTF8					65001
#define MB_PRECOMPOSED			0x1
#define MB_COMPOSITE			0x2
#define MB_USEGLYPHCHARS		0x4
#define MB_ERR_INVALID_CHARS	0x8

#define WC_DEFAULTCHAR			0x00000040 
#define WC_ERR_INVALID_CHARS	0x00000080 
#define WC_NO_BEST_FIT_CHARS 	0x00000400 
*_______________________________________________________________________________
function StringToUTF16(utf16Out, stringIn, codepageIn)

	local success
	success = true
	
	do case
	case !m.success
	
	case empty(len(m.stringIn))
		utf16Out = ''
		
	otherwise
		local lpWideCharStr, result
		lpWideCharStr = space(len(m.stringIn)*2)
	
		result = MultiByteToWideChar( ;
					evl(m.codepageIn, cpcurrent()), ;
					MB_ERR_INVALID_CHARS, ;
					@m.stringIn, ;
					len(m.stringIn), ;
					@m.lpWideCharStr, ;
					len(m.lpWideCharStr) ;
				)
			
		do case
		case !m.success
		
		case empty(m.result)
			assert false
			success = false
		
		otherwise
			utf16Out = left(m.lpWideCharStr, m.result * 2) 
		
		endcase
		
	endcase
	
	return m.success
	
	
endfunc
*_______________________________________________________________________________
function StringUTF16ToUTF8(utf8Out, utf16In)

	local success
	success = true
	
	do case
	case !m.success
	
	case empty(len(m.utf16In))
		utf8Out = ''
		
	otherwise
	
		local lpMultiByteStr, lpUsedDefaultChar, result
		lpMultiByteStr = space(len(m.utf16In) * 2)
		lpUsedDefaultChar = 0
		
		result = WideCharToMultiByte( ;
					CP_UTF8, ;
					WC_ERR_INVALID_CHARS, ;
					@m.utf16In, ;
					len(m.utf16In)/2, ;
					@m.lpMultiByteStr, ;
					len(m.lpMultiByteStr), ;
					null, ;
					@m.lpUsedDefaultChar ;
				)
		
		do case
		case !m.success
		
		case empty(m.result)			
			assert false
			success = false
			
			
		otherwise
			utf8Out = left(m.lpMultiByteStr, m.result)
		
		endcase
	endcase
	
	return m.success
	
	
endfunc
*_______________________________________________________________________________
function StringUTF8ToUTF16(utf16Out, uft8In)

	return StringToUTF16(@m.utf16Out, uft8In, CP_UTF8)
	
endfunc
*_______________________________________________________________________________

function MultiByteToWideChar
	lparameters codepage, ;
				dwFlags, ;
				lpMultiByteStr, ;
				cbMultiByte, ;
				lpWideCharStr, ;
				cchWideChar

	local success
	success = true

	local result
	
	do case
	case !m.success
	
	otherwise
		try
			declare integer MultiByteToWideChar in Kernel32.dll ;
				long	codepage, ;
				long	dwFlags, ;
				string@	lpMultiByteStr, ;
				integer	cbMultiByte, ;
				string@	lpWideCharStr, ;
				integer	cchWideChar
		
			result = MultiByteToWideChar( ;
					m.codepage, ;
					m.dwFlags, ;
					@m.lpMultiByteStr, ;
					m.cbMultiByte, ;
					@m.lpWideCharStr, ;
					m.cchWideChar ;
				)
		catch
			assert false
			success = false
			
		endtry
	endcase
	
	return iif(m.success, m.result, 0)
	
endfunc
*_______________________________________________________________________________
function WideCharToMultiByte
	lparameters codepage, ;
				dwFlags, ;
				lpWideCharStr, ;
				cchWideChar, ;
				lpMultiByteStr, ;
				cbMultiByte, ;
				lpDefaultChar, ;
				lpUsedDefaultChar

	local success
	success = true

	local result
	
	do case
	case !m.success
	
	otherwise
		try
			declare integer WideCharToMultiByte in Kernel32.dll ;
				long	codepage, ;
				long	dwFlags, ;
				string@	lpWideCharStr, ;
				integer	cchWideChar, ;
				string@	lpMultiByteStr, ;
				integer	cbMultiByte, ;
				string	lpDefaultChar, ;
				integer	@lpUsedDefaultChar
		
			result = WideCharToMultiByte ( ;
					m.codepage, ;
					m.dwFlags, ;
					@m.lpWideCharStr, ;
					m.cchWideChar, ;
					@m.lpMultiByteStr, ;
					m.cbMultiByte, ;
					m.lpDefaultChar, ;
					@m.lpUsedDefaultChar;
				)
		catch
			assert false
			success = false
			
		endtry
	endcase
	
	return iif(m.success, m.result, 0)
	
endfunc

and then in the Change event of the ActiveX textbox I added the following code

IF this.parent.lProgrammaticChange 
    RETURN
ENDIF     
if thisform.IsChanged OR (not this.CurrentValue == this.Text)
   thisform.IsChanged = .t.
   local utf8, utf16Out
   utf8 = this.text
   if not empty(utf8)
      = StringUtf8ToUTF16(@m.utf16Out, m.utf8)
      replace (this.parent.LanguageField) with utf16Out
   else
      replace (this.parent.LanguageField) with ''
   endif
endif

and finally in the Save method of the form

IF THISFORM.IsChanged 
  LOCAL lcSQL

  TEXT TO lcSQL noshow
     UPDATE dbo.prefs_sl 
     SET defltlang = ?prefs_sl.DefltLang,
     language00 = cast(?prefs_sl.language00 as nvarchar(100)),
     lngimage00 = ?prefs_sl.lngimage00,
     language01 = cast(?prefs_sl.language01 as nvarchar(100)),
     lngimage01 = ?prefs_sl.lngimage01,
     language02 = cast(?prefs_sl.language02 as nvarchar(100)),
     lngimage02 = ?prefs_sl.lngimage02,
     language03 = cast(?prefs_sl.language03 as nvarchar(100)),
     lngimage03 = ?prefs_sl.lngimage03,
     language04 = cast(?prefs_sl.language04 as nvarchar(100)),
     lngimage04 = ?prefs_sl.lngimage04,
     language05 = cast(?prefs_sl.language05 as nvarchar(100)),
     lngimage05 = ?prefs_sl.lngimage05,
     language06 = cast(?prefs_sl.language06 as nvarchar(100)),
     lngimage06 = ?prefs_sl.lngimage06,
     language07 = cast(?prefs_sl.language07 as nvarchar(100)),
     lngimage07 = ?prefs_sl.lngimage07,
     language08 = cast(?prefs_sl.language08 as nvarchar(100)),
     lngimage08 = ?prefs_sl.lngimage08,
     language09 = cast(?prefs_sl.language09 as nvarchar(100)),
     lngimage09 = ?prefs_sl.lngimage09,
     language10 = cast(?prefs_sl.language10 as nvarchar(100)),
     lngimage10 = ?prefs_sl.lngimage10
     where pri_key = ?prefs_sl.pri_key
  ENDTEXT
 RETURN mySQLExec(m.lcSQL, '',PROGRAM())
ENDIF

As we can see, we need to convert data back from binary to nvarchar.
With all this code in place, we display the unicode data and save them back to SQL Server.

A big thanks to Gregory for helping me with the code – I don’t know where I would be without his help.

Olaf Doschke showed another way which is even simpler than implemented solution and in accordance to the original Rick’s article.

In the form’s Load we need the following:

Sys(987,.F.)
Sys(3101,65001)

Then, after getting binary data from SQL Server the same way as I show in this blog, we still need to use createbinary, e.g.

this.txtLanguage.text = createbinary(prefs_sl.Language00)

We don’t want to use COMPROP now for the ActiveX.

and then, saving data, we need to follow Rick’s steps:

pcSavedText1 = Strconv(This.Text,12)

*** Must explicitly force to binary – can also use CAST in 9.0
pcSavedText1 = CREATEBINARY(pcSavedText1)

and then convert this value back to nvarchar(max) when saving with sqlexec.

This is how the form with many languages looked like

—————————
I need to also tell, that after I finished the form and was happy to show it to my colleagues, I was up to a big disappointment. It turned out we are not going to support unicode (our main application is not up to support it yet) and so I will be re-doing this form after we will agree on the tables’ structures (they also are going to change). So, what I showed in this blog post we’re not going to use at present in production.

UPDATE. My colleague also re-designed prefs_sl table to one EAV Settings table. We know that EAV design has its own problems, but for saving various application level settings it can be used. I already re-designed the forms we’re going to use.

But in any case, it was a very important exercise and I hope it will help other people who need to display and save Unicode data in their Visual FoxPro applications.