Writing UNICODE CString to SQLServer UCS-2
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 15 of 17

Thread: Writing UNICODE CString to SQLServer UCS-2

Hybrid View

  1. #1
    Join Date
    Apr 2005
    Posts
    78

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

  2. #2
    Join Date
    Nov 2003
    Posts
    1,825

    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

  3. #3
    Join Date
    Apr 2005
    Posts
    78

    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?

  4. #4
    VictorN's Avatar
    VictorN is offline Super Moderator Power Poster
    Join Date
    Jan 2003
    Location
    Wallisellen (ZH), Switzerland
    Posts
    17,611

    Re: Writing UNICODE CString to SQLServer UCS-2

    You should use field type for UNICODE texts. The may be:
    • nchar
    • nvarchar
    • ntext
    Victor Nijegorodov

  5. #5
    Join Date
    Nov 2003
    Posts
    1,825

    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

  6. #6
    Join Date
    Apr 2005
    Posts
    78

    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.

  7. #7
    VictorN's Avatar
    VictorN is offline Super Moderator Power Poster
    Join Date
    Jan 2003
    Location
    Wallisellen (ZH), Switzerland
    Posts
    17,611

    Re: Writing UNICODE CString to SQLServer UCS-2

    Quote Originally Posted by PRMARJORAM View Post
    ... 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?

    Quote Originally Posted by VictorN View Post
    You should use field type for UNICODE texts. The may be:
    • nchar
    • nvarchar
    • ntext
    Quote Originally Posted by Codeplug View Post
    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
    Last edited by VictorN; October 15th, 2009 at 07:01 AM.
    Victor Nijegorodov

  8. #8
    Join Date
    Apr 2005
    Posts
    78

    Re: Writing UNICODE CString to SQLServer UCS-2

    Quote Originally Posted by VictorN View Post
    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?
    (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.

  9. #9
    VictorN's Avatar
    VictorN is offline Super Moderator Power Poster
    Join Date
    Jan 2003
    Location
    Wallisellen (ZH), Switzerland
    Posts
    17,611

    Re: Writing UNICODE CString to SQLServer UCS-2

    Quote Originally Posted by PRMARJORAM View Post
    I keep telling you the type of the sql server column is nvarchar, why dont you read my responses?
    Sorry, my bad!
    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
    Attached Images Attached Images  
    Last edited by VictorN; October 15th, 2009 at 07:00 AM.
    Victor Nijegorodov

  10. #10
    Join Date
    Nov 2003
    Posts
    1,825

    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

  11. #11
    Join Date
    Apr 2005
    Posts
    78

    Smile Re: Writing UNICODE CString to SQLServer UCS-2

    Quote Originally Posted by Codeplug View Post
    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.

  12. #12
    Join Date
    Nov 2000
    Location
    Voronezh, Russia
    Posts
    6,072

    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)
    Best regards,
    Igor

  13. #13
    Join Date
    Apr 2005
    Posts
    78

    Question Related problem

    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?

  14. #14
    Join Date
    Nov 2003
    Posts
    1,825

    Re: Writing UNICODE CString to SQLServer UCS-2

    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

  15. #15
    Join Date
    Apr 2005
    Posts
    78

    Smile Re: Writing UNICODE CString to SQLServer UCS-2

    Quote Originally Posted by Codeplug View Post
    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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Windows Mobile Development Center


Click Here to Expand Forum to Full Width

This is a CodeGuru survey question.


Featured


HTML5 Development Center