adoutils.cpp

vc ADO 连接数据库

// ADOUTILS:  Helper functions for manipulating ADO within 
//            Visual C++ via #import

#include "stdafx.h"

#include "hlb.h"
#include "log.h"

#include "AdoUtils.h"

    // Need two distinct "empty" VARIANTs and BSTR for various Methods
    _variant_t  vtEmpty (DISP_E_PARAMNOTFOUND, VT_ERROR);
    _variant_t  vtEmpty2(DISP_E_PARAMNOTFOUND, VT_ERROR);
    _bstr_t     bstrEmpty(L"");

    // Connection and SQL Strings
    _bstr_t    bstrAccessConnect        ( L"DSN=AdoDemo;UID=admin;PWD=;" );
    _bstr_t    bstrOpenAccess           ( L"SELECT * FROM Authors" );
    _bstr_t    bstrOpenAccessWithParam  ( L"SELECT * FROM Authors WHERE Au_ID < ?" );
    _bstr_t    bstrSQLCreate            ( L"create proc sp_AdoTest( @InParam int, @OutParam int OUTPUT ) "
                                          L"as " 
                                          L"select @OutParam = @InParam + 10SELECT * FROM Authors WHERE "  
                                          L"State <> 'CA' " 
                                          L"return @OutParam +10" );
    _bstr_t    bstrSQLDrop              ( L"if exists " 
                                          L"(select * from sysobjects where " 
                                          L"id = object_id('dbo.sp_AdoTest') and "
                                          L"sysstat & 0xf = 4)" 
                                          L"drop procedure dbo.sp_AdoTest" );
    _bstr_t    bstrStoredProc           ( L"sp_Adotest" );

    // Initialization of OLE
    struct InitOle {
      InitOle()  { ::CoInitialize(NULL); }
      ~InitOle() { ::CoUninitialize();   }
    } _init_InitOle_;       // Global Instance to force load/unload of OLE

//--------------------------
// Error/Exception Handlers 
//--------------------------

HRESULT AdoErrorEx(CHorzListBox &List1, ADODB::_ConnectionPtr Conn1)
{
    // Local Error Objects
    ADODB::ErrorsPtr   Errs1 = NULL;
    ADODB::ErrorPtr    Err1  = NULL;

    CString     strTmp;
    long        nCount;

    // Try/Catch Block
    try
    {
        if( Conn1              == NULL )
            return S_OK;

        // For any error condition, show results to user
        List1.AddString( "*** ADO ERROR CONDITION ***" );

        // Enumerate Errors Collection and display properties of each object.
        Errs1  = Conn1->GetErrors();
        nCount = Errs1->GetCount();

        // Loop through Errors Collection
        for( long i = 0; i < nCount; i++ )
        {
            // Get Error Item
            Err1 = Errs1->GetItem((_variant_t)((long)i) );

            // Get Error Number
            strTmp.Format( "\tNumber %x", Err1->GetNumber() );
            List1.AddString( strTmp );

            // Get Error Description
            strTmp.Format( "\t\tDescription = %s",  (LPCTSTR) _bstr_t( Err1->GetDescription() ) );
            List1.AddString( strTmp );

            // Get Error Source
            strTmp.Format( "\t\tSource      = %s",  (LPCTSTR) _bstr_t( Err1->GetSource() ) );
            List1.AddString( strTmp );

            // Get Help File
            strTmp.Format( "\t\tHelpFile    = %s",  (LPCTSTR) _bstr_t( Err1->GetHelpFile() ) );
            List1.AddString( strTmp );

            // Get Help File Context
            strTmp.Format( "\t\tHelpContext = %ld", Err1->GetHelpContext() );
            List1.AddString( strTmp );

            // Get Native Error
            strTmp.Format( "\t\tNativeError = %ld", Err1->GetNativeError() );
            List1.AddString( strTmp );

            // Get SQL State
            strTmp.Format( "\t\tSQLState    = %s",  (LPCTSTR) _bstr_t( Err1->GetSQLState() ) );
            List1.AddString( strTmp );

            // Release Error Object
            Err1->Release();
            Err1 = NULL;
        }
    }
    catch( CException *e )
    {
        List1.AddString( "*** UNABLE TO LOG ADO ERROR *** (MFC Exception was raised while logging exception)" );

        //Non-ADO Native error/Exception Handler
        LogReset();
        LogException( e );
        LogDisplay( List1 );
        e->Delete();
    }
    catch( SEH_Exception &e )
    {
        List1.AddString( "*** UNABLE TO LOG ADO ERROR *** (Win32 Exception was raised while logging exception)" );

        //Non-ADO Native error/Exception Handler
        LogReset();
        LogException( e );
        LogDisplay( List1 );
    }
    catch(...)
    {
        List1.AddString( "*** UNABLE TO LOG ADO ERROR *** (Unknown Exception was raised while logging exception)" );

        //Non-ADO Native error/Exception Handler
        LogReset();
        LogException();
        LogDisplay( List1 );
    }

    // Clean-up
    if( Errs1 != NULL ) LOGQ( Errs1->Release(); )
    if( Err1  != NULL ) LOGQ( Err1->Release();  )

    return S_OK;
}

void MfcErrorEx( CException *e, CHorzListBox &List1, ADODB::_ConnectionPtr Conn1 )
{
    // Try/Catch Block
    try
    {
        // ADO Error/Exception Handler
        AdoErrorEx( List1, Conn1 );

        //Non-ADO Native error/Exception Handler
        List1.AddString( "*** ERROR CONDITION! ***" );
        LogReset();
        LogException( e );
        LogDisplay( List1 );
    }
    catch( SEH_Exception &e )
    {
        List1.AddString( "*** UNABLE TO LOG ERROR *** (Win32 Exception was raised while logging exception)" );

        //Non-ADO Native error/Exception Handler
        LogReset();
        LogException( e );
        LogDisplay( List1 );
    }
    catch(...)
    {
        List1.AddString( "*** UNABLE TO LOG ERROR *** (Unknown Exception was raised while logging exception)" );

        //Non-ADO Native error/Exception Handler
        LogReset();
        LogException();
        LogDisplay( List1 );
    }

    e->Delete();
}

void Win32ErrorEx( SEH_Exception &e, CHorzListBox &List1, ADODB::_ConnectionPtr Conn1 )
{
    // Try/Catch Block
    try
    {
        // ADO Error/Exception Handler
        AdoErrorEx( List1, Conn1 );

        //Non-ADO Native error/Exception Handler
        List1.AddString( "*** ERROR CONDITION! ***" );
        LogReset();
        LogException( e );
        LogDisplay( List1 );
    }
    catch(...)
    {
        List1.AddString( "*** UNABLE TO LOG ERROR *** (Unknown Exception was raised while logging exception)" );

        //Non-ADO Native error/Exception Handler
        LogReset();
        LogException();
        LogDisplay( List1 );
    }
}

void UnknownErrorEx( CHorzListBox &List1, ADODB::_ConnectionPtr Conn1 )
{
    // Try/Catch Block
    try
    {
        // ADO Error/Exception Handler
        AdoErrorEx( List1, Conn1 );

        //Non-ADO Native error/Exception Handler
        List1.AddString( "*** ERROR CONDITION! ***" );
        LogReset();
        LogException();
        LogDisplay( List1 );
    }
    catch(...)
    {
        List1.AddString( "*** Unhandled Exception in Exception Handler ***" );

        //Non-ADO Native error/Exception Handler
        LogReset();
        LogException();
        LogDisplay( List1 );
     }
}

//-------------------
// Utility Functions
//-------------------

void DumpProperty( CHorzListBox &List1, ADODB::_ConnectionPtr Conn1, ADODB::PropertiesPtr &Props1 )
{
    CString                 strTmp;
    long                    nCount;

    if( Props1              == NULL )
        return;

    // Try/Catch Block
    try
    {
        // Display # of properties found
        nCount = Props1->Count;
        strTmp.Format( "\t%d Properties found", nCount );
        List1.AddString( strTmp );

        // Enumerate through properties collection
        for ( int i = 0; i < nCount; i++ )
        {
            ADODB::PropertyPtr     Prop1 = NULL;

            // Get Current Property
            Prop1 = Props1->GetItem( _variant_t( (long) i ) );

            // Display Name
            strTmp.Format  ( "\tProperty #%d:  %s", i+1, (char *) Prop1->Name );
            List1.AddString( strTmp );

            // Display Type
            strTmp.Format  ( "\t\t...Type       = %s", GetType( (int) Prop1->Type ) );
            List1.AddString( strTmp );

            // Display Value
            strTmp.Format  ( "\t\t...Value      = %s", CrackStrVariant( (tagVARIANT) Prop1->Value ) );
            List1.AddString( strTmp );

            // Display Attributes
            strTmp.Format  ( "\t\t...Attributes = %s", GetPropertyAttributes( (ADODB::PropertyAttributesEnum) Prop1->Attributes ) ); 
            List1.AddString( strTmp );

            // Release Property Object
            if( Prop1              != NULL )
            {
                Prop1->Release();
                Prop1 = NULL;
            }

        }
    }
    catch( CException *e )
    {
        MfcErrorEx( e, List1, Conn1 );
    }
    catch( _com_error &e )
    {
        ImportErrorEx( e, List1, Conn1 );
    }
    catch( SEH_Exception &e )
    {
        Win32ErrorEx( e, List1, Conn1 );
    }
    catch(...)
    {
        UnknownErrorEx( List1, Conn1 );
    }
}

CString GetPropertyAttributes( ADODB::PropertyAttributesEnum e )
{
   CString strTmp("");

   if ( e & ADODB::adPropNotSupported)  strTmp += "NotSupported ";
   if ( e & ADODB::adPropRequired)      strTmp += "Required ";
   if ( e & ADODB::adPropOptional)      strTmp += "Optional ";
   if ( e & ADODB::adPropRead)          strTmp += "Read ";
   if ( e & ADODB::adPropWrite)         strTmp += "Write ";

   return strTmp;
}


CString GetType( int e )
{
   CString strTmp;

   switch( e )
   {
   case ADODB::adBigInt:
      strTmp = "(adBigInt) An 8-byte signed integer";
      break;
   case ADODB::adBinary:
      strTmp = "(adBinary) A binary value";
      break;
   case ADODB::adBoolean:
      strTmp = "(adBoolean) A Boolean value";
      break;
   case ADODB::adBSTR:
      strTmp = "(adBSTR) A null-terminated character string (Unicode)";
      break;
   case ADODB::adChar:
      strTmp = "(adChar) A String value";
      break;
   case ADODB::adCurrency:
      strTmp = "(adCurrency) A currency value (8-byte signed integer scaled by 10,000)";
      break;
   case ADODB::adDate:
      strTmp = "(adDate) A Date value";
      break;
   case ADODB::adDBDate:
      strTmp = "(adDBDate) A date value (yyyymmdd)";
      break;
   case ADODB::adDBTime:
      strTmp = "(adDBTime) A time value (hhmmss)";
      break;
   case ADODB::adDBTimeStamp:
      strTmp = "(adDBTimeStamp) A date-time stamp (yyyymmddhhmmss plus a fraction in billionths)";
      break;
   case ADODB::adDecimal:
      strTmp = "(adDecimal) An exact numeric value with a fixed precision and scale";
      break;
   case ADODB::adDouble:
      strTmp = "(adDouble) A double-precision floating point value";
      break;
   case ADODB::adEmpty:
      strTmp = "(adEmpty) No value was specified";
      break;
   case ADODB::adError:
      strTmp = "(adError) A 32-bit Error code";
      break;
   case ADODB::adGUID:
      strTmp = "(adGUID) A globally unique identifier (GUID)";
      break;
   case ADODB::adIDispatch:
      strTmp = "(adIDispatch) A pointer to an IDispatch interface on an OLE object";
      break;
   case ADODB::adInteger:
      strTmp = "(adInteger) A 4-byte signed integer";
      break;