Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

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


View products that this article applies to.

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().

↑ Back to the top


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.

↑ Back to the top


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.

↑ Back to the top


Status

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

↑ Back to the top


More information

The following sample code demonstrates the problem:
#define UNICODE

#include <windows.h>
#include <sql.h>
#include <sqlext.h>
#include <stdio.h>
#include <string.h>

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"LocalServer",SQL_NTS,L"sa",SQL_NTS,L"",SQL_NTS);

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

    wcscpy( buffer, L"select * from ntesttab" );
    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]
				

↑ Back to the top


References

ODBC API Programmer's Reference, MSDN, Platform SDK Documentation

↑ Back to the top


Keywords: kbbug, kbdatabase, kbnofix, KB293659

↑ Back to the top

Article Info
Article ID : 293659
Revision : 5
Created on : 12/5/2003
Published on : 12/5/2003
Exists online : False
Views : 397