Writing UNICODE CString to SQLServer UCS-2
Im needing to write a line of text to a sqlserver table,
The line of text could come from any webpage using any encoding.
Previously I had a problem displaying this text within my application, but could use some conversion functions to display it correctly such as:
CStringA alpha = W2A(mFromText.c_str());
CStringW wide = CA2WEX<>(alpha, GetCodePage());
My program is built using _UNICODE.
I am dynamically constructing an sql statement and using an odbc driver.
Now when the line of text is some other text such as cyrillic alls that is stored in the db is ??????
Prior to the sql being executed the sql string contains the correct text.
How do I store a UNICODE string to sql-server and it preserve its content correctly?
Originally my column type was varchar and then changed to nvarchar but no difference?
Thanks.
Re: Writing UNICODE CString to SQLServer UCS-2
>> Im needing to write a line of text ... from any webpage using any encoding.
You need to know what the encoding is in order to convert it.
>> Previously I had a problem displaying this text within my application ... [which] is built using _UNICODE.
The text should be converted to Window's Unicode (UTF16LE) - which is what a CStringW holds. "Displaying correctly" has other dependencies, like using wide Win-API functions and having the correct fonts installed.
>> I am dynamically constructing an sql statement and using an odbc driver.
There are two things to consider here: the data type of the column where the text is to be stored, and how that text will be encoded before storing it in the db.
gg
Re: Writing UNICODE CString to SQLServer UCS-2
Well im completely lost on this one. Can we please have a specific example and then work out a more generic one.
I am using CStringW to hold my cyrillic string, this is fine
How do I write this to an sql server database?
As when I look at the column in sql server it is just ?????
I dont know what column type i need it was initially varchar, fine for western text
I then add some cyrillic and its just showing as ?????
I change the type to nvarchar and no difference?
Re: Writing UNICODE CString to SQLServer UCS-2
You should use field type for UNICODE texts. The may be:
Re: Writing UNICODE CString to SQLServer UCS-2
First you should decide what column type to use and in what encoding to store the strings.
http://msdn.microsoft.com/en-us/libr...SQL.90%29.aspx
On Windows, with SQL Server, n[var]char is what you should probably use.
To convert text from a particular codepage, or UTF8 via CP_UTF8, use the following:
Code:
CStringW wide = CA2WEX<>(cp_text, cp);
Where cp_text is a char* string which is encoded with the given codepage identifier, cp.
>> How do I write this to an sql server database?
How are you doing it now?
gg
Re: Writing UNICODE CString to SQLServer UCS-2
Here is an extract of the dynamic sql I have constructed
"UPDATE WebResearchPage SET FromText='Пошук', ToText=NULL, WHERE (ID = 653)"
Held in a CStringW called sql. As you can see the column FromText is set to some cyrillic.
But the string here is correctly represented.
connection->ExecuteSQL(sql);
After which I look in the cell but just has ?????? values. This is currently set to nvarchar - default window collation.
So would seem problem is on the SQL server side. FromText could hold text from any webpage from anywhere in the world using any encoding, so the column type with sqlserver needs to be generic enough to hold anything?
Is this possible?
Or must i used a varbinary?
If i must use varbinary can its contents be added to a dynamically constructed SQL string as above?
Thanks.
Re: Writing UNICODE CString to SQLServer UCS-2
Quote:
Originally Posted by
PRMARJORAM
... After which I look in the cell but just has ?????? values. This is currently set to nvarchar - default window collation.
So would seem problem is on the SQL server side. FromText could hold text from any webpage from anywhere in the world using any encoding, so the column type with sqlserver needs to be generic enough to hold anything?
Is this possible?
Or must i used a varbinary?
1. Yes, it is the "problem is on the SQL server side"
2. No, you don't need (have) to use "a varbinary".
3. You have already got these two answers yesterday with the way how to solve the problem. So why don't you read them? :confused:
Quote:
Originally Posted by
VictorN
You should use field type for UNICODE texts. The may be:
Quote:
Originally Posted by
Codeplug
First you should decide what column type to use and in what encoding to store the strings.
http://msdn.microsoft.com/en-us/libr...SQL.90%29.aspx
On Windows, with SQL Server,
n[var]char is what you should probably use.
To convert text from a particular codepage, or UTF8 via CP_UTF8, use the following:
Code:
CStringW wide = CA2WEX<>(cp_text, cp);
Where cp_text is a char* string which is encoded with the given codepage identifier, cp.
>>
How do I write this to an sql server database?
How are you doing it now?
gg
Re: Writing UNICODE CString to SQLServer UCS-2
Quote:
Originally Posted by
VictorN
1. Yes, it is the "problem is on the SQL server side"
2. No, you don't need (have) to use "a varbinary".
3. You have already got these two answers yesterday with the way how to solve the problem. So why don't you read them? :confused:
(or you mean that "Чукча не читатель, чукча - писатель!"?)
I keep telling you the type of the sql server column is nvarchar, why dont you read my responses?
My string on the program side is already in the correct format you can see this from the dynamic sql constructed.
1 Attachment(s)
Re: Writing UNICODE CString to SQLServer UCS-2
Quote:
Originally Posted by
PRMARJORAM
I keep telling you the type of the sql server column is nvarchar, why dont you read my responses?
Sorry, my bad! :blush:
I tested your problem yesterday in SQL Server 2008 Express. The result is in the attached image. I typed the text in, so, perhaps, your problem is in OBDC drivers?
Try to do the same with ADO or OLEDB
Re: Writing UNICODE CString to SQLServer UCS-2
Quote:
Originally Posted by
VictorN
Sorry, my bad! :blush:
I tested your problem yesterday in SQL Server 2008 Express. The result is in the attached image. I typed the text in, so, perhaps, your problem is in OBDC drivers?
Try to do the same with ADO or OLEDB
Thanks VictorN, this confirms what im thinking.
What i cannot do is change all my code to use ADO or something at this point just to write UNICODE strings. As I understand it does seem from initial web searches that ODBC drivers do support UNICODE.
But it must be converting the wide sql string back to ansi if it detects it does not have a wide version to call?
So my problem has changed to how to write unicode strings using SQLServer ODBC driver.
Im wondering if there is a option we have to set to enable this?
My version is SQLServer 2000.85.1132.00 date 14/04/2008
They should rename it all UNIHELL!
Re: Writing UNICODE CString to SQLServer UCS-2
As I understand I am using the latest version of MDAC 2.8 SP1 on WINDOWS XP SP3
via component checker
I have found one other thread where they seemed to be having the same issue
http://objectmix.com/ado-dao-rdo-rds...e-capable.html
Unfortunately, an answer was not posted.
I have stepped through the code and is handling wide strings upto the odbc call
LPTSTR pszSQL = const_cast<LPTSTR>(lpszSQL);
AFX_ODBC_CALL(::SQLExecDirect(hstmt, reinterpret_cast<SQLTCHAR *>(pszSQL), SQL_NTS));
Without a solution looking possible within a realistic time frame:
I think what I will look at now is converting all text to ascii characters and writing them out to varchar(wont make sense looking at it in the sql server column), when reading back in using the codepage also stored with the text to convert back to wide chars.
Re: Writing UNICODE CString to SQLServer UCS-2
I believe you need "N'Пошук'"
http://support.microsoft.com/kb/239530
>> I have stepped through the code ...
Make sure "SQLExecDirectW" is being called and not "SQLExecDirect".
http://msdn.microsoft.com/en-us/libr...8VS.85%29.aspx
Define both "UNICODE" and "_UNICODE" if it isn't.
gg
Re: Writing UNICODE CString to SQLServer UCS-2
Quote:
Originally Posted by
Codeplug
CodePlug you are a hero!
It was all down to that N.
Thanks for everyone's help in resolving this difficult issue.
If i had put something like 'sqlserver unicode string constant' into google thats top of SERP.
Some painful lessons have be learnt.
Re: Writing UNICODE CString to SQLServer UCS-2
Another way is using parameterized sql query:
Code:
UPDATE WebResearchPage SET FromText= ?, ToText=NULL, WHERE (ID = 653)