Select the product you need help with

FIX: ODBC Driver Manager Incorrectly Reports SQL_NO_DATA_FOUND for ANSI Driver

Article ID: 319243 - View products that this article applies to.

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.

Back to the top | Give Feedback

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.

Back to the top | Give Feedback

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

(http://support.microsoft.com/kb/290211/EN-US/ )

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

Back to the top | Give Feedback

Workaround

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

Back to the top | Give Feedback

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.

Back to the top | Give Feedback

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:

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,'')

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

Back to the top | Give Feedback