Microsoft KB Archive/319243

= FIX: ODBC Driver Manager Incorrectly Reports SQL_NO_DATA_FOUND for ANSI Driver =

Article ID: 319243

Article Last Modified on 6/27/2006

-

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 &quot;NULL&quot; 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:

Download 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 &quot;NO_DATA&quot; 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:
 * 1) include 
 * 2) include 
 * 3) include 
 * 4) include 

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,&quot;DSN=YourANSIDSN;UID=YorUid;Pwd=YourPassword&quot;); 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*) &quot;Select ID,COL1 FROM Test&quot;,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(&quot;SQL_NO_DATA_FOUND has been returned.\n Press Enter key to continue...\n&quot;); 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...

</ol>

Additional query words: MYSQL

Keywords: kbdownload kbbug kbfix kbsqlserv2000presp3fix kbqfe kbsqlserv2000sp3fix kbhotfixserver KB319243

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.