MS Forms 2 Textbox and Unicode data 1

[markros]

In the closed thread184-1624353 I found a reference to MS Forms 2 Textbox control. By specifying the ControlSource property of this control, I can display data (which are varbinary) just fine. My question is how to save the input data?

Thanks.

PluralSight Learning Library

 

[Olaf Doschke]

Here's a round trip, which works for me:

prerequisites:

An sql server table with nvarchar or nchar fields.
A form with a Microsoft Forms 2.0 Textbox as olecontrol1 on it.

#Define ccConnection "DRIVER={SQL Server};SERVER=..." && add your connection string here
Sys(987,.F.)
Sys(3101,65001)
Thisform.addproperty("nHandle", SQLSTRINGCONNECT(ccConnection))

*change accordingly to your data
SQLExec(thisform.nHandle,"Select Cast(nText1 as varbinary(8000)) as nText1 From unicodetext","curUnicode")

thisform.olecontrol1.text = createbinary(curUnicode.nText1)
* remark: SQLExec still creates a Ansi 1252 codepage cursor, therefore this is important to not make a false transformation

And now the save button, saving to a new record:

*** UTF-8 to Unicode conversion (not needed, as it's still unicode)
pcSavedText1 = Strconv(Thisform.olecontrol1.Text,12)

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

*** SQL Server requires CASTS on the server!

SqlExec(thisform.nHandle,"insert into unicodetext (nText1) values (CAST(?pcSavedText1 as nVarChar(MAX)))")

So the main problem I see is SQLExec() still creates a cursor with Ansi 1252 coedepage. Sys(987,.f.) does not change that, so a conversion of unicode to ansi is still done and most probably even not successful. Rick forces the nonconversion by casting to varbinary, that is still a solution on top of Sys(987,.f.). Still odbc creates a normal memo field, no blob or varbinary, that's the most imnportant part of the problem.

That's why I think Rick has also mentioned using ADO for data retreival. You should check the option to use a Cursoradatper, as that can have predefined field types in the result cursor, eg blob, varbinary or memo(binary), char (binary). Then you could skip the CreateBinary() part, perhaps.

Bye, Olaf.

 

[Olaf Doschke]

I jkust tried my last proposal to go for cursoradapter, and with the SQL Native Client Driver used in conjunction with cursoradapter.lusecursorschema=.T. with a scheme containing Q or W fields, this works out fine and retrieves unicode into varbinary or blob fields you then can use as data source for unicode controls.

Sys(987,.f.) or Sys(987,.t.) still makes no difference then, so this makes me stil wonder how sys(987) makes unicode data easier to cope with for Rick Strahl.

Nevertheless that is what I would suggest using now:

1. Using SQL Native Client driver
2. Using Cursoradapters with cursor schema
3. Using schema containing varbinary=Q for nchar/nvarchar and and Blob=W fields for nvarchar(max) fields.
4. Setting ActiveX control properties via these binary fields, this prevents any automatic conversion, no need for createbinary() or cast(), not even within SQL-Selects.
5. Sys(3101,65001) for retrieving UTF-8 from ActiveX controls
6. Strconv(Activex.proeperty,12) to convert UTF-8 coming from ActiveX back to Unicode, this step is needed nevertheless.

And then writing back to sql server simply is Tableupdate() of the cursoradapter cursor.

Bye, Olaf.

 

[markros]

I like the simplicity of your sql pass thru test. In my tests I never did this step
thisform.olecontrol1.text = createbinary(curUnicode.nText1)
* remark: SQLExec still creates a Ansi 1252 codepage cursor, therefore this is important to not make a false transformation

Could it be the main reason my attempts were unsuccessful?

I'll add your way to the blog as well since I tried it now and it also worked (sql pass thru method).

PluralSight Learning Library

 

[Olaf Doschke]

I don't know your attempts, but could be.
If you just set controlsource or set .text = field, the value displayed looks like a conversion of unicode to unicode, which fails.

olecontrol1.text = 0h20044304410441043A0438043904

Also works, because that already is binary.

So maybe yes, the codepage of the result cursor is having an effect in the unicode value in the ansi cursor being interpreted as ansi string, not as binary. CreateBinary() doesn't make anything with the value itself, it just changes the interpretation and conversion.

Bye, Olaf.