Microsoft KB Archive/246165

= BUG: CDatabase::ExecuteSQL Function Does Not Throw Exception with Multiple Result Sets =

Article ID: 246165

Article Last Modified on 10/31/2003

-

APPLIES TO


 * Microsoft Visual C++ 6.0 Enterprise Edition
 * Microsoft Visual C++ 6.0 Professional Edition
 * Microsoft Visual C++ 6.0 Standard Edition

-



This article was previously published under Q246165



SYMPTOMS
When the CDatabase::ExecuteSQL function is called on a stored procedure that creates multiple result sets, and an error is encountered, an exception may not be thrown. Note, however, that the exception is thrown only if the error is encountered while processing the first result set. If the error happens after processing the first result set, the exception is not thrown.

The "More Information" section has an example of a stored procedure that does not cause an exception to be thrown. This stored procedure creates two result sets and calls RAISERROR between them.



CAUSE
The implementation of the ExecuteSQL function of the CDatabase class throws an exception if an error is encountered during the execution of the SQLExecDirect function. The function loops through all the remaining result sets by calling SQLMoreResults, and then exits if it reaches the last result set or if it encounters an error. The function does not, however, throw an exception if an error is encountered in a call to SQLMoreResults.



RESOLUTION
For a workaround, see the "More Information" section.



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



Steps to Reproduce Behavior
To reproduce this error with a SQL Server 6.5 or 7.0 database:   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  Create an MFC AppWizard application with database header support. Create a menu handler.  Paste the following code into the handler: CDatabase db; db.OpenEx(_T("DSN=Pubs;UID= ;PWD= ;")); 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; }                    Change the connect string to reflect the correct values for your SQL Server server. Compile and run the application. Run the code above.</li></ol>

Notice that an exception is not thrown when db.ExecuteSQL is called for the second time with an invalid parameter.

Workaround
<ol> Create a CDatabase-derived class called CMyDatabase.</li>  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)); }                       </li>  Add the following #include to the MyDatabase.cpp implementation file: This code ensures that some of the macros that are used in the implementation will be defined. </li>  Change the code in the menu handler that was created previously to use the new class instead of the CDatabase class: CMyDatabase db; ...                   </li>  Insert the following #include in the implementation file where your menu handler is defined: </li> Recompile and run the code.</li></ol>
 * 1) include "atlconv.h"
 * 1) include "MyDatabase.h"

Notice that the code throws an exception when the second SQL statement is executed as expected.

Keywords: kbbug kbdatabase kbmdacnosweep KB246165

-

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

© Microsoft Corporation. All rights reserved.