BUG: CDatabase::ExecuteSQL() Function Does Not Throw Exception with Multiple Result Sets (246165)

MORE INFORMATION

Steps to Reproduce Behavior

1. Create the following stored procedure in the PUBS Database:

   CREATE PROCEDURE get_author_titles @au_id id
   AS
   BEGIN
   SELECT * FROM authors where au_id = @au_id
   if @@ROWCOUNT = 0
   RAISERROR ('No author found',15,10)
   else
   SELECT title_id FROM titleauthor WHERE au_id = @au_id
   END
   					

2. Create an MFC AppWizard application with database header support. Create a menu handler.
3. Paste the following code into the handler:

   	CDatabase db;
   	db.OpenEx(_T("DSN=Pubs;UID=<username>;PWD=<strong password>;"));
   	try {
   		db.ExecuteSQL(_T("EXEC get_author_titles '998-72-3567'"));
   		db.ExecuteSQL(_T("EXEC get_author_titles '998-72-3568'"));
   	}
   	catch (CDBException* pEx)
   	{
   		TCHAR buff[80];
   		pEx->GetErrorMessage(buff,80);
   		AfxMessageBox(buff);
   		pEx->Delete();
   	}
   					

4. Change the connect string to reflect the correct values for your SQL Server server.
5. Compile and run the application. Run the code above.

Workaround

1. Create a CDatabase-derived class called CMyDatabase.
2. Add the following function to the class. This function will override the CDatabase::ExecuteSQL function. The code adds only the two lines indicated to the implementation of the CDatabase::ExecuteSQL function:

   void CMyDatabase::ExecuteSQL(LPCTSTR lpszSQL)
   {
   	USES_CONVERSION;
   	RETCODE nRetCode;
   	HSTMT hstmt;
   
   	ASSERT_VALID(this);
   	ASSERT(AfxIsValidString(lpszSQL));
   
   	AFX_SQL_SYNC(::SQLAllocStmt(m_hdbc, &hstmt));
   	if (!CheckHstmt(nRetCode, hstmt))
   		AfxThrowDBException(nRetCode, this, hstmt);
   
   	TRY
   	{
   		OnSetOptions(hstmt);
   
   		// Give derived CDatabase classes option to use parameters.
   		BindParameters(hstmt);
   
   		AFX_ODBC_CALL(::SQLExecDirect(hstmt,
   			(UCHAR*)T2A((LPTSTR)lpszSQL), SQL_NTS));
   
   		if (!CheckHstmt(nRetCode, hstmt))
   			AfxThrowDBException(nRetCode, this, hstmt);
   		else
   		{
   			do
   			{
   				SWORD nResultColumns;
   
   				AFX_ODBC_CALL(::SQLNumResultCols(hstmt, &nResultColumns));
   				if (nResultColumns != 0)
   				{
   					do
   					{
   						AFX_ODBC_CALL(::SQLFetch(hstmt));
   					} while (CheckHstmt(nRetCode, hstmt) &&
   						nRetCode != SQL_NO_DATA_FOUND);
   				}
   				AFX_ODBC_CALL(::SQLMoreResults(hstmt));
   			} while (CheckHstmt(nRetCode, hstmt) &&
   				nRetCode != SQL_NO_DATA_FOUND);
   			// ******** FIX ***********
   			if (!CheckHstmt(nRetCode, hstmt))
   				AfxThrowDBException(nRetCode, this, hstmt);
   			// *******END FIX *********
   		}
   	}
   	CATCH_ALL(e)
   	{
   		::SQLCancel(hstmt);
   		AFX_SQL_SYNC(::SQLFreeStmt(hstmt, SQL_DROP));
   		THROW_LAST();
   	}
   	END_CATCH_ALL
   
   	AFX_SQL_SYNC(::SQLFreeStmt(hstmt, SQL_DROP));
   	
   }
   						

3. Add the following #include to the MyDatabase.cpp implementation file:

   #include "atlconv.h"
   						

   This code ensures that some of the macros that are used in the implementation will be defined.
4. Change the code in the menu handler that was created previously to use the new class instead of the CDatabase class:

   CMyDatabase db;
   ...
   					

5. Insert the following #include in the implementation file where your menu handler is defined:

   #include "MyDatabase.h"
   					

6. Recompile and run the code.