FIX: ODBC Driver Manager Incorrectly Reports SQL_NO_DATA_FOUND for ANSI Driver (319243)


The information in this article applies to:

  • Microsoft Data Access Components 2.7
This article was previously published under Q319243

SYMPTOMS

When you use an ANSI ODBC driver, the ODBC Driver Manager may incorrectly return SQL_NO_DATA_FOUND for a column that is defined as VARCHAR or CHAR and that has an empty string in it. The problem does not occur if the column contains some data or a "NULL" value. The problem only occurs if the data is an empty string and you bind it from the client side with SQL_C_WCHAR.

CAUSE

The ODBC Driver Manager incorrectly assumes that the driver does not have any data when it tries to convert the empty string from ANSI to UNICODE.

RESOLUTION

To resolve this problem, obtain the latest service pack for Microsoft SQL Server 2000. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

290211 INF: How to Obtain the Latest SQL Server 2000 Service Pack

To resolve this problem, download the Q319243_MDAC27_x86.exe patch. The following file is available for download from the Microsoft Download Center:

DownloadDownload Q319243_MDAC27_x86.exe now

Release Date: July 12, 2002

For additional information about how to download Microsoft Support files, click the following article number to view the article in the Microsoft Knowledge Base:

119591 How to Obtain Microsoft Support Files from Online Services

Microsoft scanned this file for viruses. Microsoft used the most current virus-detection software that was available on the date that the file was posted. The file is stored on security-enhanced servers that help to prevent any unauthorized changes to the file. For more information about this patch, visit the following Microsoft Web site:

http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=6263FCF4-A2AD-4690-8F6D-3341C16B31E6


WORKAROUND

A simple workaround for this problem is to bind the column as SQL_C_CHAR instead of SQL_C_WCHAR.

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article. This problem was first corrected in Microsoft SQL Server 2000 Service Pack 3.

MORE INFORMATION

Legacy applications that use an ANSI ODBC driver may not experience this problem, because they may not bind the column as SQL_C_WCHAR. Because the Microsoft ODBC .NET Provider binds character columns as SQL_C_WCHAR by default, you may experience this problem if you use the Microsoft ODBC .NET Provider with an ANSI driver. Note that the Microsoft ODBC .NET Provider returns "NO_DATA" when this problem occurs.

Steps to Reproduce Behavior

The following native sample demonstrates the problem:
  1. Use the following script to create a table and insert one record:
    Create Table Test (ID integer, COL1 CHAR(10))
Insert Into Test VALUES (1,'')
  2. Run the following C program:
    #include <windows.h>
#include <stdio.h>
#include <sql.h>
#include <sqlext.h>

int main(int argc, char* argv[])
{
	SQLCHAR*	theDiagState = new SQLCHAR[50];
	SQLINTEGER	theNativeState;
	SQLCHAR*	theMessageText  = new SQLCHAR[255];
	SQLSMALLINT	iOutputNo;
	SQLHENV		m_SQLEnvironment;
	SQLHDBC		m_SQLConnection;
	SQLHSTMT	m_SQLStatement;
	SQLRETURN	iReturn;
	long		lStrLen;
	char		theData[1000];

	//Connect.
	iReturn = SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,&m_SQLEnvironment);
	iReturn = SQLSetEnvAttr(m_SQLEnvironment,SQL_ATTR_ODBC_VERSION,(SQLPOINTER) SQL_OV_ODBC3,0);
	iReturn = SQLAllocHandle(SQL_HANDLE_DBC,m_SQLEnvironment,&m_SQLConnection);

	//CHANGE THE DSN NAME HERE.
	char	szConnBuffer[1024];
	short   cbOutConn;
	char    szConnStr[255];

         strcpy(szConnStr,"DSN=YourANSIDSN;UID=YorUid;Pwd=YourPassword");
	
	iReturn= SQLDriverConnect(m_SQLConnection, NULL,
(SQLTCHAR*) szConnStr,strlen(szConnStr), 
(SQLCHAR*) szConnBuffer,1024, &cbOutConn, SQL_DRIVER_NOPROMPT);

	if (iReturn != SQL_SUCCESS)
	{
		SQLGetDiagRec(SQL_HANDLE_DBC,m_SQLConnection,1,theDiagState,
&theNativeState,theMessageText,100,&iOutputNo);
	}

	//Run query.
	iReturn = SQLAllocHandle(SQL_HANDLE_STMT,m_SQLConnection,&m_SQLStatement);

	//The Test table contains COL1 column which is set to empty for the first record.

	iReturn = SQLExecDirect(m_SQLStatement, (SQLCHAR*) "Select ID,COL1 FROM Test",SQL_NTS);
	iReturn = SQLFetch(m_SQLStatement);

	iReturn = SQLGetData(m_SQLStatement,2,SQL_C_WCHAR,theData,100,&lStrLen);

	//The problem does not occur if you bind the same column to SQL_C_CHAR.
	//iReturn = SQLGetData(m_SQLStatement,2,SQL_C_CHAR,theData,100,&lStrLen);

	if (iReturn != SQL_SUCCESS)
	{
		//iReturn will be 100 SQL_NO_DATA_FOUND here with NO other message.
		int CheckHere;
		CheckHere = 0;  //It will come here.
		if (iReturn == SQL_NO_DATA_FOUND)
		{
			printf("SQL_NO_DATA_FOUND has been returned.\n Press Enter key to continue...\n");
			getchar();
		}
		
	}

	//DISCONNECT.
	iReturn = SQLFreeHandle(SQL_HANDLE_STMT,m_SQLStatement);
	iReturn = SQLDisconnect(m_SQLConnection);
	iReturn = SQLFreeHandle(SQL_HANDLE_DBC,m_SQLConnection);
	iReturn = SQLFreeHandle(SQL_HANDLE_ENV,m_SQLEnvironment);

	m_SQLStatement = NULL;
	m_SQLConnection = NULL;
	m_SQLEnvironment = NULL;

	delete theMessageText; 
	delete theDiagState;

	return 1;
}
    You receive the following message:
    SQL_NO_DATA_FOUND has been returned.
    Press Enter key to continue...
Modification Type:MajorLast Reviewed:6/27/2006
Keywords:kbHotfixServer kbQFE kbdownload kbSQLServ2000sp3fix kbbug kbfix kbSQLServ2000preSP3fix KB319243
©2004 Microsoft Corporation. All rights reserved.