adoutils.cpp

vc ADO 连接数据库

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

#include "stdafx.h"

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

#include "AdoUtils.h"

    // Need two distinct "empty" VARIANTs and BSTR for various Methods
    VARIANT  vtEmpty;
    VARIANT  vtEmpty2;
    CString  strEmpty( "" );

    // Connection and SQL Strings
    BSTR    bstrLicKey;            
    CString strAccessConnect            ( "DSN=AdoDemo;UID=admin;PWD=;" );
    CString strOpenAccess               ( "SELECT * FROM Authors WHERE Au_ID < 10" );
    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" );
    CString strLicKey                   ( "gxwaezucfyqpwjgqbcmtsncuhwsnyhiohwxz" );

    // Initialization of OLE
    struct InitOle {
      InitOle()  { ::CoInitialize(NULL);
                   bstrLicKey = strLicKey.AllocSysString();}
      ~InitOle() { SysFreeString( bstrLicKey );
                   ::CoUninitialize();   }
    } _init_InitOle_;       // Global Instance to force load/unload of OLE

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

HRESULT AdoErrorEx(CHorzListBox &List1, _Connection    Conn1)
{
    // Local Error Objects
    Errors      Errs1;
    Error       Err1;

    CString     strTmp;
    long        nCount;

    // Try/Catch Block
    try
    {
        if( Conn1.m_lpDispatch == 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( COleVariant( (long) i ) );

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

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

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

            // Get Help File
            strTmp.Format( "\t\tHelpFile    = %s",  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",  Err1.GetSQLState() );
            List1.AddString( strTmp );

            // Release Error Object
            Err1.ReleaseDispatch();
        }
    }
    // Catch Blocks
    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 );
    }

    return S_OK;
}

void MfcErrorEx( CException *e, CHorzListBox &List1, _Connection 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 Blocks
    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 Blocks
    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, _Connection 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 Blocks
    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, _Connection 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 Blocks
    catch(...)
    {
        List1.AddString( "*** Unhandled Exception in Exception Handler ***" );

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

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

void DumpProperty( CHorzListBox &List1, _Connection Conn1, Properties &Props1 )
{
    CString                 strTmp;
    long                    nCount;

    if( Props1.m_lpDispatch == NULL )
        return;

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

        // Enumerate through properties collection
        for ( int i = 0; i < nCount; i++ )
        {
            Property        Prop1;

            // Get Current Property
            Prop1 = Props1.GetItem( COleVariant( (long) i ) );

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

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

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

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

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

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

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

    return strTmp;
}


CString GetType( int e )
{
   CString strTmp;

   switch( e )
   {
   case adBigInt:
      strTmp = "(adBigInt) An 8-byte signed integer";
      break;
   case adBinary:
      strTmp = "(adBinary) A binary value";
      break;
   case adBoolean:
      strTmp = "(adBoolean) A Boolean value";
      break;
   case adBSTR:
      strTmp = "(adBSTR) A null-terminated character string (Unicode)";
      break;
   case adChar:
      strTmp = "(adChar) A String value";
      break;
   case adCurrency:
      strTmp = "(adCurrency) A currency value (8-byte signed integer scaled by 10,000)";
      break;
   case adDate:
      strTmp = "(adDate) A Date value";
      break;
   case adDBDate:
      strTmp = "(adDBDate) A date value (yyyymmdd)";
      break;
   case adDBTime:
      strTmp = "(adDBTime) A time value (hhmmss)";
      break;
   case adDBTimeStamp:
      strTmp = "(adDBTimeStamp) A date-time stamp (yyyymmddhhmmss plus a fraction in billionths)";
      break;
   case adDecimal: