Microsoft KB Archive/293659

= BUG: SQL Server ODBC Driver Incorrectly Maps Unicode Datatypes with SQL_C_DEFAULT =

Article ID: 293659

Article Last Modified on 12/5/2003

-

APPLIES TO


 * Microsoft ODBC Driver for Microsoft SQL Server 3.7
 * Microsoft ODBC Driver for Microsoft SQL Server 3.5
 * Microsoft ODBC Driver for Microsoft SQL Server 3.6
 * Microsoft ODBC Driver for Microsoft SQL Server 3.7
 * Microsoft Data Access Components 2.1
 * Microsoft Data Access Components 2.1 Service Pack 2
 * Microsoft Data Access Components 2.1 Service Pack 1
 * Microsoft Data Access Components 2.1 Service Pack 2
 * Microsoft Data Access Components 2.5
 * Microsoft Data Access Components 2.5 Service Pack 1
 * Microsoft Data Access Components 2.6
 * Microsoft Data Access Components 2.7

-



This article was previously published under Q293659



SYMPTOMS
Client applications that call the ODBC API SQLBindCol function to bind a Unicode data column using SQL_C_DEFAULT will be bound as SQL_C_CHAR.

When you call SQLDescribeCol on a Unicode datatype, the column's datatype will be reported as SQL_WCHAR, SQL_WVARCHAR, or SQL_WLONGVARCHAR. Also, the datalength of the column will be reported at 50% of the actual column length from the ODBC API function SQLDescribeCol.



CAUSE
The SQL Server ODBC driver intentionally maps these Unicode datatypes to SQL_C_CHAR to protect older applications that have been written to use SQL_C_DEFAULT.

Mapping columns bound with SQL_C_DEFAULT to Unicode datatypes of SQL_WCHAR, SQL_WVARCHAR, or SQL_LONGVARCHAR could potentially break older applications.



RESOLUTION
Applications that require access to extended characters should not bind the Unicode datatype columns as SQL_C_DEFAULT. Instead, these programs should bind Unicode columns as SQL_WCHAR, SQL_WVARCHAR, or SQL_LONGVARCHAR in order to avoid this behavior in the SQL Server ODBC driver.



STATUS
Microsoft has confirmed that this is a bug in the Microsoft products that are listed at the beginning of this article.



MORE INFORMATION
The following sample code demonstrates the problem:
 * 1) define UNICODE


 * 1) include 
 * 2) include 
 * 3) include 
 * 4) include 
 * 5) include 

main {   HSTMT hstmt; HENV henv; HDBC hdbc; SDWORD cbValueMax; SQLRETURN sr; wchar_t buffer[200]; long keyval; BYTE outbuff[102]; SQLINTEGER StrLen_or_Ind1 = sizeof( long ), StrLen_or_Ind2;

//Allocate environment handle. sr = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);

//Set the ODBC version. sr = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0);

//Allocate connection handle. sr = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);

//Connect. sr=SQLConnect(hdbc,L&quot;LocalServer&quot;,SQL_NTS,L&quot;sa&quot;,SQL_NTS,L&quot;&quot;,SQL_NTS);

//Allocate statement handle. sr = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);

wcscpy( buffer, L&quot;select * from ntesttab&quot; ); cbValueMax = strlen( ( char * ) buffer); memset( outbuff, 0, 100 ); //Execute the statement. sr = SQLExecDirect(hstmt, buffer, SQL_NTS );

//Bind the columns. sr = SQLBindCol( hstmt, 1, SQL_C_LONG, & keyval, sizeof( long ), & StrLen_or_Ind1 );

//Here we are binding the WCHAR column to SQL_C_DEFAULT. //If you do this, you will see that the value in outbuff is truncated. //You will see the truncation only when you have some Unicode data. //Also, the test table ntesttab has a field which is nchar(10). //So StrLen_or_Ind2 should report 20, but due to this bug it will //report 10. sr = SQLBindCol( hstmt, 2, SQL_C_DEFAULT, & outbuff, 100, & StrLen_or_Ind2 );

//Comment the above, and uncomment the following line to correct //this problem. If you bind it to SQL_C_WCHAR, the outbuff will //contain Unicode data and StrLen_or_Ind2 will correctly report 20.

//sr = SQLBindCol( hstmt, 2, SQL_C_WCHAR, & outbuff, 100, & StrLen_or_Ind2 );

//Fetch the data. sr = SQLFetch( hstmt );

//Examine the memory for outbuff here to see the returned value as char, not wchar.

//Free connection/statement/environment. SQLFreeStmt( hstmt, SQL_CLOSE ); SQLFreeStmt( hstmt, SQL_DROP ); SQLDisconnect( hdbc ); SQLFreeHandle( SQL_HANDLE_DBC, hdbc ); SQLFreeHandle( SQL_HANDLE_ENV, henv );

return (0); } The following is the script to create the table nTestTab: CREATE TABLE [dbo].[ntesttab] (   [id] [int] IDENTITY (1, 1) NOT NULL,    [unicodeText] [nchar] (10)  NULL ) ON [PRIMARY]

