How to update\\insert data into a NVARCHAR column using ODBC API

[Former Member]

I am trying to update a Sybase table via Microsofts ODBC API. The following is the basics of the C++ I am trying to execute. In table, TableNameXXX, ColumnNameXXX has a type of NVARCHAR( 200 ).

    SQLWCHAR updateStatement[ 1024 ] = L"UPDATE TableNameXXX SET ColumnNameXXX = N'Executive Chair эюя' WHERE PKEYXXX = 'VALUE'";  
   if( ret = SQLExecDirect( hstmt, ( SQLWCHAR* ) updateStatement, SQL_NTS ) != SQL_SUCCESS )
   {
       // Handle Error
   }


The Sybase database has a CatalogCollation of 1252LATIN1, CharSet of windows-1252,  Collation of 1252LATIN1, NcharCharSet of UTF-8 and an NcharCollation of UCA.
Once this works for the Sybase ODBC connection I need to get it to work in various other ODBC drivers for other databases.
I do not get any errors however - the data in the database is not the unicode I attempted to update.
Does anyone know how to get this to work? What am I missing?

[jeff_albion · Accepted solution]

Hi David,

SQL statements are always parsed in the character set of the database (so Windows-1252 in this case). If you wish to pass Unicode/NCHAR data, you need to do one of the following (from: http://dcx.sap.com/index.html#sa160/en/dbadmin/natlang-s-5322854.html )

• Use the UNISTR function to specify the Unicode character values
• Use a host variable to specify the Unicode character values ('?')
• Use UTF-8 as the database character set

So for ODBC, you will want to use prepared execution with SQLPrepare, SQLBindParameter, and SQLExecute.

[Former Member · Accepted solution]

Sorry... but... the second option "Use a host variable to specify the Unicode character values ('?')" is that the same as using a prepared execution?

[Former Member · Accepted solution]

Got it working with SELECT UNISTR...

SQLWCHAR updateStatement[ 1024 ] = L"UPDATE TableNameXXX SET ColumnNameXXX = ( SELECT UNISTR( 'Executive Chair \\u044F\\u044D\\u044E\\u044F' ) ) WHERE PKEYXXX = 'VALUE'";

Thanks for your help.

[jeff_albion · Accepted solution]

Yes - the second option is using a host variable and then binding it with data (a.k.a prepared execution).