Save Chinese + English text in MS Sql table,

[Mukesh Khandelwal]

How to insert and update Chinease + English text together in a SQL table nvarchar field ( foxpro Memo column)
Thanks

 

[Chris Miller]

You got the main ingredient, using nchar/nvarchar fields the n prefix stands for unicode.

Now you got to provide unicode text strings in VFP, too. VFP will only display Ansi, so either you use Unicode capable controls, have a html frontend shown in a web browser control or similar things.

And then there's nothing special about Unicode strings, so you just do you insert sql statement or your tableupdate() with unicode texts and be done.

There's little more ingredients, VFP on Chinese Windows supports the Chinese Ansi codepages 936, Chinese Simplified (PRC, Singapore) Windows, and 950, Traditional Chinese (Hong Kong SAR, Taiwan) Windows.

Whatever you use I think has both chinese characters and the latin alphabet, STRCONV(vfptext,5) will convert that to unicode for MSSQL unicode fields, or you use SYS(987,.t.) , see https://www.west-wind.com/wconnect/weblog/ShowEntry.blog?id=608

 

[Mukesh Khandelwal]

Hi
Thanks for the reply

I am able to upload\add data to SQL table with Chinese text( Memo field contains Chinese text)
I am able to query data from SQL table with Chinese text ( Memo field contains Chinese text)
But when i try to update, it becomes junk data in SQL Table.

How to update from Memo Field of a DBF to SQL table?

Thanks

 

[Chris Miller]

What are you using? Updatable remote view? an sql string containing an update-sql statement you execute via SQLExec? A Cursor adapter? If using an SQL string, do you place the literal string in to the SQL or use parameterization?

One thing that might play a role is when you specify chinese text in code in string literals like in UPDATE sometable SET nvarcharfield='VFPAnsitext' the SYS(987) set right will convert VFP Ansi text to Unicode, but a Unicode string literal must be N'Unicodetext' not 'Unicodetext, the N prefix before the begnning quote is not optional in T-SQL, All that becomes unimportant with parameterization.

Other problems are that unless the connection is made with QUOTED_IDENTIFIER OFF the double quote " is not handled as a string delimiter in T-SQL, but as name or identifier delimiter, so you can, for example use column names with spaces and refer to them as "my column" instead of naming such a column MyColumn or my_column, as is necessary in VFP naming conventions, which don't allow spaces in names. It's not only that aspect but now "chinese text" isn't interpreted as chinese text as 'chinese text' would at least work for that english text and N'actual chinese text/letters/symbols' , but looking - depending on context - for a column or table or other object with that name/identifier. I also never saw N"string", I think it would work with QUOTED_IDENTIFIER OFF, but better leave it at only using single quotes and N'string' for Unicode.

By default QUOTED_IDENTIFIER is ON and so T-SQL only uses single quotes as string delimiters and N prefix for unicode strings in code or SQL statements. VFP can choose quote, double quote and even square brackets as string delimiters, but T-SQL doesn't. With QUOTED_IDENTIFIER OFF you can use double quotes, but then have no mean to specify names having spaces. Which is one reason a good MS SQL database developer doesn't make use of such names, depsite being able to make them work.

 

[Mukesh Khandelwal]

Hi

I am using SIngle quotes and T-SQL ( Update statement ) for updating records.

One more issue i am facing, how to insert chinese text in a text box. currently when i copy paste\input any chinese text it becomes "????"
Previously it was working, don't know the reason why its not working now from last few days.

Thanks
for your help and suggestions will try them

 

[Chris Miller]

I am using SIngle quotes and T-SQL ( Update statement ) for updating records.

Thanks, but that only answers partly apspects of my questions. When you use remote views or cursor adapter they must be based on T-SQL toio, not only SQL you run in SQLExec, so I still don't know what you use.

I'd recommend using parameterization.

Regarding the clipboard (cpoy/paste) issue, you depend on VFP using an ansi codepage that's capable of displaying the unicode characters you paste into it. As there are two codepages for Chinese they can't cover everything in one, so some Unicode of your source of copying text won't transpose.