Question Re: How to update\\insert data into a NVARCHAR column using ODBC API in Technology Q&A

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

Former Member — Thu, 05 Feb 2015 17:59:16 GMT

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?

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

jeff_albion — Thu, 05 Feb 2015 18:55:33 GMT

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.

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

Former Member — Thu, 05 Feb 2015 19:50:04 GMT

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

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

Former Member — Thu, 05 Feb 2015 22:22:42 GMT

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.

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

jeff_albion — Fri, 06 Feb 2015 10:21:55 GMT

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