Markros,
I just downloaded the sample code and ran it without success, as I am missing the WestwindAdmin database. This is not meant as a standalone sample code. Read more on the whitepaper itself, you'll find all the essential information in it:
1. explaination why you get back ??? from the Text property:
Rick Strahl said:
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 ???
2. explaination why you get back ??? from the Text property:
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.
So if you would have spent your days in reading the article as is instead of simply trying and retrying without any success, you already would know the essential Sys(3101) as first part of the solution.
3. Why SQL does not provide unicode to Foxpro
Additional to the COM<->VFP Unicode <-> ANSI conversion there is a SQL Server <-> VFP Unicode <-> ANSI conversion, Rick also mentions:
Rick Strahl said:
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.
Rick Strahl said:
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.
See Rick Strahl's whitepaper for Figure 2 and 4 mentioning the problem.
Rick also posts a solution you already know and use right beneath Figure 4:
Rick Strahl said:
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.
This is one part of the solution, but you also need SYS(3101) and writing back to SQL Server is solved by him with...
Rick Strahl said:
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:
Code:
*** 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.
So admitted, as Rick Strahl says this is complex, the solution has not just one but at least 5 essential parts now:
SQL Server to VFP: CAST as varbinary
COM to VFP: SYS(3101)
UTF-8 to Unicode: Strconv()
convert back to binary: CREATEBINARY()
binary to unicode within SQL Server: Cast again
Well, still thoroughly reading this whitepaper gives you the solution, doesn't it?
So Again: Did you really read this article?
There's one more thing, that makes some parts of this forth and back conversions unneccessary: SYS(987). Rick mentions it here:
Rick said:
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
So this stops the autoconversion from SQL Server to Foxpro and back like SYS(3101) prevents the autoconverion from unicode to ANSI on the way from Foxpro to ActiveX and back.
Now you do have only 2 essential parts:
1. SYS(3101,65001) will turn anything you read from ActiveX to Foxpro to UTF-8
2. SYS(987,.F.) prevents Unicode to Ansi conversion via SQLExec().
Besides all this: What do you think your code does?
Code:
create cursor csrTest (cVal varbinary(100), nVal varbinary(100))
insert into csrTest (cVal) values ('20044304410441043A0438043904')
If you want to write a varbinary value for the insert into, why do you write a normal string instead? The prefix for it is 0h, it's not delimited with string delimiters:
0h20044304410441043A0438043904
This stored into the .text property yields "Русский"
otherwise, well, you most probably get a display of 20044304410441043A0438043904, what else do you expect?
Bye, Olaf.
), it would be easier to help you if you provided some more details. For instance: are you saving your text into a field in a table or into a text file on disk? If it's the former - of what data type this field is? If it's the latter - what text format you are trying to have in this file?
