adoutils.cpp

vc ADO 连接数据库

// ADOUTILS:  Helper functions for manipulating ADO within 
//            Visual C++ via OLE SDK

#include "stdafx.h"

// Include ADO Interface/Class Descriptions (ADO 1.5 Version)
#include "adoid.h"
#include "adoint.h"

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

#include "AdoUtils.h"

    // Need two distinct "empty" VARIANTs and BSTR for various Methods
    VARIANT  vtEmpty;
    VARIANT  vtEmpty2;
    BSTR     bstrEmpty;

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

    // Initialization of OLE
    struct InitOle {
      InitOle()  { ::CoInitialize(NULL); 
                   vtEmpty.vt              = VT_ERROR;
                   vtEmpty.scode           = DISP_E_PARAMNOTFOUND;
                   vtEmpty2.vt             = VT_ERROR;
                   vtEmpty2.scode          = DISP_E_PARAMNOTFOUND;
                   bstrEmpty               = strEmpty.AllocSysString();
                   bstrAccessConnect       = strAccessConnect.AllocSysString();
                   bstrOpenAccess          = strOpenAccess.AllocSysString();
                   bstrOpenAccessWithParam = strOpenAccessWithParam.AllocSysString();
                   bstrSQLDrop             = strSQLDrop.AllocSysString();
                   bstrSQLCreate           = strSQLCreate.AllocSysString();
                   bstrStoredProc          = strStoredProc.AllocSysString();
      }
      ~InitOle() { SysFreeString( bstrEmpty );
                   SysFreeString( bstrAccessConnect );
                   SysFreeString( bstrOpenAccess );
                   SysFreeString( bstrOpenAccessWithParam );
                   SysFreeString( bstrSQLDrop );
                   SysFreeString( bstrSQLCreate );
                   SysFreeString( bstrStoredProc );
                   ::CoUninitialize();   }
    } _init_InitOle_;       // Global Instance to force load/unload of OLE

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

HRESULT AdoErrorEx(CHorzListBox &List1, ADOConnection *Conn1)
{
    // Local Error Objects
    ADOErrors*  Errs1   = NULL;
    ADOError*   Err1    = NULL;

    CString     strTmp;
    long        nCount;
    long        l = 0;
    BSTR        bstrTmp = NULL;
    HRESULT     hr      = S_OK;

    // 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.
        hr     = Conn1->get_Errors( &Errs1 );
        if( SUCCEEDED( hr ) ) hr = Errs1->get_Count( &nCount );

        // Loop through Errors Collection
        for( long i = 0; ( SUCCEEDED(hr)) && (i < nCount); i++ )
        {
            // Get Error Item
            hr = Errs1->get_Item( COleVariant(i), &Err1 );

            // Get Error Number
            if( SUCCEEDED( hr ) ) hr = Err1->get_Number( &l );
            if( SUCCEEDED( hr ) ) strTmp.Format( "\tNumber %x", l );
            if( SUCCEEDED( hr ) ) List1.AddString( strTmp );

            // Get Error Description
            if( SUCCEEDED( hr ) ) hr = Err1->get_Description( &bstrTmp );
            if( SUCCEEDED( hr ) ) strTmp.Format( "\t\tDescription  %s", CString( (LPCWSTR) bstrTmp ) );
            if( SUCCEEDED( hr ) ) List1.AddString( strTmp );
            if( bstrTmp != NULL ) 
            {
                SysFreeString( bstrTmp );
                bstrTmp = NULL;
            }

            // Get Error Source
            if( SUCCEEDED( hr ) ) hr = Err1->get_Source( &bstrTmp );
            if( SUCCEEDED( hr ) ) strTmp.Format( "\t\tSource       %s", CString( (LPCWSTR) bstrTmp ) );
            if( SUCCEEDED( hr ) ) List1.AddString( strTmp );
            if( bstrTmp != NULL ) 
            {
                SysFreeString( bstrTmp );
                bstrTmp = NULL;
            }

            // Get Help File
            if( SUCCEEDED( hr ) ) hr = Err1->get_HelpFile( &bstrTmp );
            if( SUCCEEDED( hr ) ) strTmp.Format( "\t\tHelpFile     %s", CString( (LPCWSTR) bstrTmp ) );
            if( SUCCEEDED( hr ) ) List1.AddString( strTmp );
            if( bstrTmp != NULL ) 
            {
                SysFreeString( bstrTmp );
                bstrTmp = NULL;
            }

            // Get Help File Context
            if( SUCCEEDED( hr ) ) hr = Err1->get_HelpContext( &l );
            if( SUCCEEDED( hr ) ) strTmp.Format( "\t\tHelpContext = %ld", l );
            if( SUCCEEDED( hr ) ) List1.AddString( strTmp );

            // Get Native Error
            if( SUCCEEDED( hr ) ) hr = Err1->get_NativeError( &l );
            if( SUCCEEDED( hr ) ) strTmp.Format( "\t\tNativeADOError = %ld", l );
            if( SUCCEEDED( hr ) ) List1.AddString( strTmp );

            // Get SQL State
            if( SUCCEEDED( hr ) ) hr = Err1->get_SQLState( &bstrTmp );
            if( SUCCEEDED( hr ) ) strTmp.Format( "\t\tSQLState     %s",  CString( (LPCWSTR) bstrTmp ) );
            if( SUCCEEDED( hr ) ) List1.AddString( strTmp );
            if( bstrTmp != NULL ) 
            {
                SysFreeString( bstrTmp );
                bstrTmp = NULL;
            }

            // 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 );
    }

    // Better check that HRESULT!
    if( FAILED( hr ) )
    {
        List1.AddString("*** Bogus HRESULT ***" );
        strTmp = LogCrackHR( hr );
        List1.AddString( strTmp );
    }

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

    return S_OK;
}

void MfcErrorEx( CException *e, CHorzListBox &List1, ADOConnection *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, ADOConnection *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, ADOConnection *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, ADOConnection* Conn1, ADOProperties* Props1 )
{
    CString                 strTmp;
    long                    nCount;
    HRESULT                 hr = S_OK;
    BSTR                    bstrTmp;
    VARIANT                 varTmp;
    long                    l;
    DataTypeEnum            e;
 
    if( Props1              == NULL )
        return;

    // Try/Catch Block
    try
    {
        // Display # of properties found
        hr = Props1->get_Count( &nCount );
        if( SUCCEEDED( hr ) ) strTmp.Format( "\t%ld Properties found", nCount );
        if( SUCCEEDED( hr ) ) List1.AddString( strTmp );

        // Enumerate through properties collection
        for( int i = 0; (SUCCEEDED( hr )) && (i < nCount); i++ )
        {
            ADOProperty*    Prop1 = NULL;

            // Get Current Property
            hr = Props1->get_Item( COleVariant( (long) i), &Prop1 );    

            // Display Name
            if( SUCCEEDED( hr ) ) hr = Prop1->get_Name( &bstrTmp );
            if( SUCCEEDED( hr ) ) strTmp.Format  ( "\tName %s", CString( (LPCWSTR) bstrTmp ) );
            if( SUCCEEDED( hr ) ) List1.AddString( strTmp );
            if( bstrTmp != NULL ) 
            {
                SysFreeString( bstrTmp );
                bstrTmp = NULL;
            }

            // Display Type
            if( SUCCEEDED( hr ) ) hr = Prop1->get_Type( &e );
            if( SUCCEEDED( hr ) ) strTmp.Format  ( "\t\tType         %s", GetType( (int) e ) );
            if( SUCCEEDED( hr ) ) List1.AddString( strTmp );

            // Display Value
            if( SUCCEEDED( hr ) ) hr = Prop1->get_Value( &varTmp );
            if( SUCCEEDED( hr ) ) strTmp.Format  ( "\t\tValue        %s", CrackStrVariant( COleVariant( varTmp ) ) );
            if( SUCCEEDED( hr ) ) List1.AddString( strTmp );

            // Display Attributes
            if( SUCCEEDED( hr ) ) hr = Prop1->get_Attributes( &l );
            if( SUCCEEDED( hr ) ) strTmp.Format  ( "\t\tAttributes   %s", GetPropertyAttributes( (PropertyAttributesEnum) l ) );
            if( SUCCEEDED( hr ) ) List1.AddString( strTmp );

            // Release Property Object
            if( Prop1              != NULL )
            { 
                Prop1->Release();
                Prop1 = NULL;
            }
        }       
    }
    catch( CException *e )
    {
        MfcErrorEx( e, List1, Conn1 );
    }
    catch( SEH_Exception &e )
    {
        Win32ErrorEx( e, List1, Conn1 );
    }
    catch(...)
    {
        UnknownErrorEx( List1, Conn1 );
    }

    // Better check that HRESULT!
    if( FAILED( hr ) )
    {
        List1.AddString("*** Failed HRESULT ***" );
        strTmp = LogCrackHR( hr );
        List1.AddString( strTmp );

        // Dump Errors Collection, since if no exception was raised
        // This is only way we can get at it!
        if( Conn1 != NULL )
        {
            AdoErrorEx( List1, Conn1 );
        }
    }

    if( bstrTmp != NULL ) SysFreeString( bstrTmp );
}

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