CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  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,902

    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
    Hanover Germany
    Posts
    20,395

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

    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
    Hanover Germany
    Posts
    20,395

    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 06: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
    Hanover Germany
    Posts
    20,395

    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 06:00 AM.
    Victor Nijegorodov

  10. #10
    Join Date
    Apr 2005
    Posts
    78

    Angry Re: Writing UNICODE CString to SQLServer UCS-2

    Quote Originally Posted by VictorN View Post
    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
    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!

  11. #11
    Join Date
    Apr 2005
    Posts
    78

    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.

  12. #12
    Join Date
    Nov 2003
    Posts
    1,902

    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

  13. #13
    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.

  14. #14
    Join Date
    Nov 2000
    Location
    Voronezh, Russia
    Posts
    6,620

    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

  15. #15
    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?

Page 1 of 2 12 LastLast

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
  •  





Click Here to Expand Forum to Full Width

Featured