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.

>> 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

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?

You should use field type for UNICODE texts. The may be:

• nchar
• nvarchar
• ntext

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

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.

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:

• nchar
• nvarchar
• ntext

---

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

---

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.

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

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!

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.

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

Quote:

---

Originally Posted by Codeplug

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

---

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.

Another way is using parameterized sql query:

Code:

---

UPDATE WebResearchPage SET FromText= ?, ToText=NULL, WHERE (ID = 653)

---

I have a related problem now.

Initially im downloading the bytes for this webpage into a char array as in:
char buffer[2000];
while(count = mSourceFile->Read( buffer, 2000 - 1)){
....
I then display just the text from this webpage using the encoding of the webpage

CStringW wide = CA2WEX<>(alpha, code_page);

In the particular instance we have been discussing the encoding(code_page) has been cyrillic.
hence we get to see Пошук after this conversion.

When I subsequently download this webpage again im looking for Пошук but its failing because the downloaded webpage has not been converted it is still in its raw byte form.

Now rather than converting the whole webpage it would be much more efficient for me to convert Пошук back to its raw byte form. Is this possible?

There isn't really a "raw byte form". The characters are encoded somehow, in all forms.
http://en.wikipedia.org/wiki/Charact...odings_in_HTML

If you want to go back to a codepage encoded string:

Code:

---

CStringA cp_text = CW2AEX<>(wide_text, cp);

---

gg

Quote:

---

Originally Posted by Codeplug

There isn't really a "raw byte form". The characters are encoded somehow, in all forms.
http://en.wikipedia.org/wiki/Charact...odings_in_HTML

If you want to go back to a codepage encoded string:

Code:

---

CStringA cp_text = CW2AEX<>(wide_text, cp);

---

gg

---

Thanks again CodePlug.

You just provide the same codepage parameter(1251 cyrillic, in this instance)
but convert back to ASCII.