Unable to insert unicode characters through freetds unixODBC in MS SQL 2008

Question

I have a c++ application built on RHEL 5 that connects to MS SQL 2008 through freeTDS and unixODBC situated on a windows machine.

This is the query the application sends to the database.

INSERT INTO mytable (SAMPLE) VALUES(N'乕乭乺丕')

Um@D@Iz is actually inserted into the database when the above query is invoked.

Following are the configurations which I am using -

== freetds.conf ==
[myserver.mydomain.com]
client charset = UTF-16
debug flags = 0xffff
dump file = /tmp/dump.log
dump file append = yes
host = 127.0.01
port = 1433
tds version = 7.3

== odbcinst.ini ==
[FreeTDS Driver]
Description     = FreeTDS
Driver          = /usr/lib64/libtdsodbc.so.0

== odbc.ini ==
[mydsn]
Description     = MS SQL connection to 'mydb' database
Driver          = FreeTDS Driver
Servername      = myserver.mydomain.com
Port            = 1433
TDS_Version     = 7.3
Database        = mydb
UserName        = sa
Password        = mypassword
Trace           = Yes
TraceFile       = /tmp/odbc.log
ForceTrace      = Yes

I can directly insert the data into the database through

INSERT INTO mytable (SAMPLE) VALUES(N'乕乭乺丕') but not through freeTDS and unixODBC

Please find the code which i am using below:

#include <iostream>

#ifdef WIN32
  #include <windows.h>
#endif

#include <sql.h>
#include <sqlext.h>
#include <sqltypes.h>

#include "unicode/ustdio.h"

using namespace std;

int main (int argc, char* argv[])
{
  SQLHSTMT hSQLStatement = 0;
  SQLHENV hSQLEnvironment = 0;
  SQLHDBC hSQLODBC = 0;

  SQLRETURN sqlRet = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hSQLEnvironment);

  if(SQL_SUCCEEDED(sqlRet))
  {
    sqlRet = SQLSetEnvAttr(hSQLEnvironment, 
                           SQL_ATTR_ODBC_VERSION,
                           (void*)SQL_OV_ODBC3, 
                           0);

    if(SQL_SUCCEEDED(sqlRet))
    {
        sqlRet = SQLAllocHandle(SQL_HANDLE_DBC, 
                                hSQLEnvironment, 
                                &hSQLODBC);
    }
    else
    {
      cout << "Error in SQLAllocHandle for SQL_HANDLE_DBC" << endl;
    }
  }
  else
  {
    cout << "Error in SQLAllocHandle for SQL_HANDLE_ENV" << endl;
  }

  UnicodeString DSNName = "mydsn";
  UnicodeString UserName = "sa";
  UnicodeString Password = "mypassword";

  UnicodeString Value = "";

  UChar32 character = 20053;
  Value.append(character);

  character = 20077;
  Value.append(character);

  character = 131140;
  Value.append(character);

  character = 131145;
  Value.append(character);

  character = 20090;
  Value.append(character);

  character = 19989;
  Value.append(character);

  UnicodeString SQLStatement = "INSERT INTO mytable (sample) VALUES(N";
  SQLStatement.append("'");
  SQLStatement.append(Value);
  SQLStatement.append("'");
  SQLStatement.append(")");

  if(0 != hSQLODBC)
  {
    SQLRETURN sqlRet = SQLConnectW(hSQLODBC, 
                                   (SQLWCHAR*)DSNName.getTerminatedBuffer(),
                                   SQL_NTS, 
                                   (SQLWCHAR*)UserName.getTerminatedBuffer(),
                                   SQL_NTS,
                                   (SQLWCHAR*)Password.getTerminatedBuffer(),
                                   SQL_NTS);

    if(SQL_SUCCEEDED(sqlRet))
    {
      cout << "Connection to database successful" << endl;

      SQLRETURN sqlRet = SQLAllocHandle(SQL_HANDLE_STMT, 
                                        hSQLODBC, 
                                        &hSQLStatement);

      if(SQL_SUCCEEDED(sqlRet))
      {
        sqlRet = SQLExecDirectW(hSQLStatement, 
                                (SQLWCHAR*)SQLStatement.getTerminatedBuffer(), 
                                SQL_NTS);

        if(SQL_SUCCEEDED(sqlRet))
        {
          cout << "Query Execution successful" << endl;
        }
        else
          cout << "Query Execution failed" << endl;
      }
    }
    else
    {
      cout << "Connection to database failed" << endl;
    }
  }

  return 0;
}

Any idea what might be wrong here?

EDIT 1: Added sample code

EDIT 2: Updated as per Oliver's suggestion

What version of FreeTDS are you using? 0.95, 0.91? TDS Version 7.3 is only supported in FreeTDS 0.95. I believe 0.91 ships with RHEL 6, but I'm not sure with RHEL 5. — FlipperPA, Feb 27 '16 at 21:22
Possible duplicate of [Why is sql server storing question mark characters instead of Japanese characters in NVarchar fields?](http://stackoverflow.com/questions/761036/why-is-sql-server-storing-question-mark-characters-instead-of-japanese-character) — Gdek, Apr 25 '17 at 20:24

TT. · Answer 1 · 2016-02-26T07:32:50.653

0

SQL Server uses UTF-16 for NVARCHAR. From the information in your question it looks like you have strings in UTF-8. First convert the insert statement to UTF-16 before sending it to SQL Server.

Update: I'm seeing you added the code sample. I see you appending UChar32 values to the UnicodeString instance. Those are 4 bytes wide. AFAICT you need to append UChar values (2 bytes wide).

edited Feb 26 '16 at 07:32

answered Feb 23 '16 at 11:32

TT.

15,774
6
47
88

The same query when invoked through c++ application built on windows using `SQL Server Native Client 10.0` odbc driver is successfully inserted in the database. I am not sure what FreeTDS might be doing here. – D3XT3R Feb 25 '16 at 06:34
@D3XT3R Looking at what is actually inserted, this smells of narrowing to single bytes rather than the double bytes in a UTF-16 string. Just as a test convert the string to a byte array, and insert the string as `INSERT INTO mytable(sample)VALUES(CAST(0x AS NVARCHAR(4000))` where byte pattern is the series of bytes in HEX form for the string in **UTF-16**. – TT. Feb 25 '16 at 06:44
@D3XT3R You should really consider adding an [MVCE](https://stackoverflow.com/help/mcve) to your question, ie the code to reproduce the problem you have. Perhaps the problem is in your code rather than in the FreeDTS driver. Are you using a string type that is in UTF-16? – TT. Feb 25 '16 at 06:47
@D3XT3R That would be UTF-16 LE (Little Endian) by the way. – TT. Feb 25 '16 at 06:51
@D3XT3R I'm coming from a JAVA/SQL-Server background... I'm just going off mostly from what I know about SQL-Server and the symptoms you describe in your question. The MVCE is really needed if you want experts in C++/FreeDTS to be able to tell anything about what you might be doing wrong at that level. – TT. Feb 25 '16 at 07:03
@D3XT3R No thanks. You have to have the code inside your question, by providing a minimal/complete/verifiable/example. That's how SO works, questions should have the information in the question, not an offsite resource. – TT. Feb 25 '16 at 12:01
@D3XT3R Updated answer – TT. Feb 26 '16 at 07:36

score 0 · Answer 2 · answered Feb 25 '16 at 12:39

The ICU UnicodeString Documentation here (https://ssl.icu-project.org/apiref/icu4c/classicu_1_1UnicodeString.html#details) says "In ICU, a Unicode string consists of 16-bit Unicode code units." Since you use SQLStatement.getTerminatedBuffer() to get at that data, I read that as the data being in UTF-16 when you grab it and push it into SQLExecDirectW().

On the other hand you specify client charset = UTF-8 in your FreeTDS config. Since it works with the Native Client, I'd try to change the charset for FreeTDS Client to UTF-16.

updated my charset to UTF-16, the same issue persists. – D3XT3R Feb 25 '16 at 13:23 — D3XT3R, Feb 25 '16 at 13:23

Unable to insert unicode characters through freetds unixODBC in MS SQL 2008

2 Answers2