INF: Using Smart Pointers to Simplify SQL-DMO (164292)

MORE INFORMATION

   #include "afx.h"         //     Standard includes
   #include "afxole.h"
   #include "stdio.h"
   #include "initguid.h"        //       Make sure the IID's are corrected
   #include "c:\mssql\sqldmo\include\sqloleid.h" //     SQL-DMO includes
   #include "c:\mssql\sqldmo\include\sqlole.h"

   //========================================
   //      Smart Pointer template definition
   //========================================
   template <class cUserInterface> class CISmartPointer
   {
    public:
       // 
       //    Constructor
       // 
       CISmartPointer()
       {
           m_pI = NULL;
       }

       // 
       //    Destructor
       // 
       ~CISmartPointer()
       {
           Release();
       }

       // 
       //    Release
       // 
       void  Release()
       {
           if(NULL != m_pI)
           {
             // 
             //  Make sure to clear the local variable so it does not come
             //  here again on the destructor if explicitly released.
             // 
             cUserInterface * pTemp = m_pI;
             pTemp->Release();
             m_pI = NULL;
           }
       }

       // 
       //    Operators
       // 
       cUserInterface * operator->()
       {
           return m_pI;
       }

    protected:

       // 
       //    Leave this as the first data element so all & references
       //    will use this member variable.
       // 
       cUserInterface *   m_pI;
   };

   //========================================
   //      Function declarations
   //========================================
   HRESULT hrDisplayError(HRESULT hRes);
   BOOL      bSetDefaults(CISmartPointer<ISQLOLEServer> & spSQLServer);
   BOOL      bConnect(CISmartPointer<ISQLOLEServer> & spSQLServer);
   void      vDisconnect(CISmartPointer<ISQLOLEServer> & spSQLServer);
   void      vDoQuery(CISmartPointer<ISQLOLEServer> & spSQLServer,
      LPCTSTR strQuery);
   void      vDisplayResults(CISmartPointer<ISQLOLEQueryResults>
      & spQueryResults);

   //========================================
   //      Macros and Defines
   //========================================
   // 
   //      Make it easy to check for SUCCEEDED and print error
   //      when encountered.
   // 
   #define IS_OKAY(a)     SUCCEEDED(hrDisplayError(a))
   #define _MAX_COL    25
   #define _MAX_COL_FMT   "%-25s "

   #define _SERVER   "."   //      Local server
   #define _USER   "sa"
   #define _PWD   ""

   // 
   //      MAIN
   // 
   void main(void)
   {
    printf("\n\nSQL-DMO Smart Pointer Sample\n");

    // 
    //     Use the appropriate Interface for LPSQLOLESERVERS.
    // 
    CISmartPointer<ISQLOLEServer>  spSQLServer;
    if(SUCCEEDED(OleInitialize(NULL)))
    {

       // 
       //    Get a SQL Server object.
       // 
       if(IS_OKAY(CoCreateInstance(CLSID_SQLOLEServer,
                 NULL,
                 CLSCTX_INPROC_SERVER,
                 IID_ISQLOLEServer,
                 (LPVOID *)&spSQLServer)))
       {
           if(TRUE == bSetDefaults(spSQLServer))
             vDoQuery(spSQLServer, "Select * from pubs.dbo.authors");

           // 
           //  Because the spSQLServer was created in main, it will not
           //  go out of scope until the end of main.
           // 
           //  However, this is after the OleUninitilize is called,
           //  and causes an error.
           // 
           //  For this special case, call the .Release to
           //  clean up the pointer and do the work.
           // 
           spSQLServer.Release();
       }
       OleUninitialize();
    }
    else
    {
       printf("\nCall to OLEInitialize failed.");
    }
    printf("\n");
   }

   // 
   //      Check for and display rich error text.
   // 
   HRESULT hrDisplayError(HRESULT hRes)
   {
    LPERRORINFO lpErrorInfo     =  NULL;
    BSTR     bstrDesc;
    BSTR     bstrSource;

    GetErrorInfo(0, &lpErrorInfo);
    if(lpErrorInfo)
    {
       lpErrorInfo->GetDescription(&bstrDesc);
       lpErrorInfo->GetSource(&bstrSource);
       printf("\n%S\n\t%S\n", bstrSource, bstrDesc);

       lpErrorInfo->Release();

       SysFreeString(bstrDesc);
       SysFreeString(bstrSource);
    }
    return hRes;
   }

   // 
   //      bSetDefaults
   // 
   //      Accepts the current address so the RefCount does not need
   //      to be touched. There is no reason to increment it on entry
   //      and then decrement it on exit, just to be doing it.
   // 
   BOOL bSetDefaults(CISmartPointer<ISQLOLEServer> & spSQLServer)
   {
    BOOL   bRC = FALSE;
    printf("\nSetting SQL Server object properties.");

    if(    IS_OKAY(spSQLServer->SetLoginTimeout(10)) &&
       IS_OKAY(spSQLServer->SetApplicationName("SmartPointers")) &&
       IS_OKAY(spSQLServer->SetHostName("Test")) &&
       IS_OKAY(spSQLServer->SetNetPacketSize(1024)) )
    {
       bRC = TRUE;
    }
    return bRC;
   }

   // 
   //      Connect to the SQL Server.
   // 
   BOOL bConnect(CISmartPointer<ISQLOLEServer> & spSQLServer)
   {
    BOOL   bRC  = FALSE;
    printf("\nAttempting to connect to %s as %s", _SERVER, _USER);

    if(IS_OKAY(spSQLServer->Connect(_SERVER, _USER, _PWD)))
       bRC = TRUE;
    return bRC;
   }

   // 
   //      Disconnect from the SQL Server.
   // 
   void vDisconnect(CISmartPointer<ISQLOLEServer> & spSQLServer)
   {
    // 
    //     Wrap with IS_OKAY so if an error is encountered it is printed
    //     out.
    // 
    printf("\nDisconnecting from %s", _SERVER);
    IS_OKAY(spSQLServer->Close());
   }

   // 
   //      vDoQuery
   // 
   void vDoQuery(CISmartPointer<ISQLOLEServer> & spSQLServer, LPCTSTR
   strQuery)
   {
    CISmartPointer<ISQLOLEQueryResults>  spQueryResults;
    if(TRUE == bConnect(spSQLServer))
    {
       printf("\nExecuting %s", strQuery);
       if(IS_OKAY(spSQLServer->ExecuteWithResults(strQuery,
   (LPSQLOLEQUERYRESULTS*)&spQueryResults)))
           vDisplayResults(spQueryResults);
       vDisconnect(spSQLServer);
    }
   }

   // 
   //      Dump out result set(s) information.
   // 
   void vDisplayResults(CISmartPointer<ISQLOLEQueryResults> &
   spQueryResults)
   {
    long      lTotalSets    =   0;
    long      lTotalCols    =   0;
    long      lTotalRows    =   0;
    long      lMaxColLen    =   0;
    char *    pchColName    =   NULL;
    char *    pchRowInfo    =   NULL;
    char      strDisplayData[_MAX_COL +1] = "";
    BOOL      bDone        =    FALSE;

    if(IS_OKAY(spQueryResults->GetResultSets(&lTotalSets)))
    {
       // 
       //    Loop through the results sets.
       // 
       for(long lSet = 0; lSet < lTotalSets && FALSE == bDone; lSet++)
       {
           if(IS_OKAY(spQueryResults->SetCurrentResultSet(lSet)))
           {
             printf("\n >>> Result set #%ld\n", lSet + 1);
             // 
             //  Print out the column headers.
             // 
             if(IS_OKAY(spQueryResults->GetColumns(&lTotalCols)))
             {
              for(long lCols = 0; lCols < lTotalCols && FALSE == bDone;
               lCols++)
              {
               if(IS_OKAY(spQueryResults->GetColumnName(lCols,
               &pchColName)))
               {
                  strncpy(strDisplayData, pchColName, _MAX_COL);
                  printf(_MAX_COL_FMT, strDisplayData);
             SQLOLEFreeString(pchColName);
               }
               else
               {
                  bDone = TRUE;
               }
              }
              printf("\n\n");

              // 
              //  Show the data.
              // 
              if(FALSE == bDone &&
         IS_OKAY(spQueryResults->GetRows(&lTotalRows)))
              {
               printf("\n------------------------------\n");
               for(long lRows = 0; lRows < lTotalRows && FALSE == bDone;
            lRows++)
               {
                for(long lCols = 0; lCols < lTotalCols; lCols++)
                {
                 if(IS_OKAY(spQueryResults->GetColumnString(lRows, lCols,
            &pchRowInfo)))
                 {
                     strncpy(strDisplayData, pchRowInfo, _MAX_COL);
                   printf(_MAX_COL_FMT, strDisplayData);
            SQLOLEFreeString(pchRowInfo);
                 }
                 else
                 {
                     bDone = TRUE;
                 }
                 }
                printf("\n");      //    End of row
               }
     // End of result set
                  printf("\nRows processed (%ld)", lTotalRows);
              }
              else
              {
                  bDone = TRUE;
              }
             }
             else
             {
               bDone = TRUE;
             }
           }
           else
           {
               bDone = TRUE;
           }
       }     // End of FOR looping
    }
   }
				

   // 
   //    This is a sample application showing the use of Smart pointers
   //    to help simplify and maintain Interface pointers
   //    for SQL-DMO components.
   // 
   //    It uses the built-in Visual C 5.0 _com_ptr_t support, and it can
   //    adapt to use the CComPtr or CComQIPtr if you prefer.
   // 
   //    The _com_error calls ->Release automatically when it
   //    goes out of scope to clean itself up. There is no need to call
   //    delete or release.
   // 

   #include "afx.h"        //    Standard includes
   #include "afxole.h"
   #include "stdio.h"
   #include <comdef.h>     //    COM Compiler support for _bstr_t
   #import "c:\\mssql\\binn\\Sqlole65.tlb"   no_namespace

   //========================================
   //  Function declarations - used & syntax so we do not call AddRef again
   //========================================
   void     vDisplayError(_com_error & pCE);
   BOOL     bSetDefaults(_SQLServerPtr & spSQLServer);
   BOOL     bConnect(_SQLServerPtr & spSQLServer);
   void     vDisconnect(_SQLServerPtr & spSQLServer);
   void     vDoQuery(_SQLServerPtr & spSQLServer, LPCTSTR strQuery);
   void     vDisplayResults(QueryResultsPtr & spQueryResults);

   //========================================
   //    Macros and Defines
   //========================================
   // 
   //    Make it easy to check for SUCCEEDED and print error
   //    when encountered.
   // 
   #define _MAX_COL     25
   #define _MAX_COL_FMT "%-25s "
   #define _SERVER      "."      //    Local server
   #define _USER        "sa"
   #define  _PWD        ""

   // 
   //    MAIN
   // 
   void main(void)
   {
      printf("\n\nSQL-DMO Smart Pointer Sample\n");
      if(SUCCEEDED(OleInitialize(NULL)))
      {
         // 
         //    Use the appropriate Interface for LPSQLOLESERVERS.
         // 

         try
         {
            _SQLServerPtr  spSQLServer;
            if(SUCCEEDED(spSQLServer.CreateInstance(__uuidof(SQLServer))))
            {
               try
               {
                  if(TRUE == bSetDefaults(spSQLServer))
                     vDoQuery(spSQLServer,
         "Select * from pubs.dbo.authors");

                  // 
                  //    Because the spSQLServer was created in main, it
                  //    will not go out of scope until the end of main.
                  // 
                  //    However, this is after the OleUninitilize is called
                  //    and causes an error.
                  // 
                  //    For this special case, call the .Release to
                  //    clean up the pointer and do the work.
                  // 
                  spSQLServer.Release();
               }
               catch(_com_error pCE)
               {
                  vDisplayError(pCE);
                  spSQLServer.Release();     //    Free the interface
               }
            }
            else
            {
               printf("\nUnable to create the SQL Server object.");
            }
         }
         catch(_com_error pCE)
         {
            vDisplayError(pCE);
         }
         OleUninitialize();
      }
      else
      {
         printf("\nCall to OLEInitialize failed.");
      }
      printf("\n");
   }

   // 
   //    Check for and display rich error text.
   // 
   void vDisplayError(_com_error & pCE)
   {
      // 
      //    Assuming ANSI build at this time.
      // 
      printf(  "\n%s Error: %ld\r\n"
            "%s\r\n"
            "%s\r\n",
            (char *)pCE.Source(),
                  pCE.Error(),
            (char *)pCE.Description(),
            (char *)pCE.ErrorMessage());
   }

   // 
   //    bSetDefaults
   // 
   //    Accepts the current address so the RefCount does not need
   //    to be touched. There is no reason to increment it on entry
   //    and then decrement it on exit, just to be doing it.
   // 
   BOOL bSetDefaults(_SQLServerPtr & spSQLServer)
   {
      BOOL  bRC   =  FALSE;
      printf("\nSetting SQL Server object properties.");

      try
      {
         spSQLServer->PutLoginTimeout(10);
         spSQLServer->PutApplicationName("SmartPointers");
         spSQLServer->PutHostName("Test");
         spSQLServer->PutNetPacketSize(1024);
         bRC = TRUE;
      }
      catch(_com_error pCE)
      {
         vDisplayError(pCE);
      }
      return bRC;
   }

   // 
   //    Connect to the SQL Server.
   // 
   BOOL bConnect(_SQLServerPtr & spSQLServer)
   {
      BOOL  bRC      =  FALSE;

      printf("\nAttempting to connect to %s as %s", _SERVER, _USER);
      try
      {
         spSQLServer->Connect(_SERVER, _USER, _PWD);
         bRC = TRUE;
      }
      catch(_com_error pCE)
      {
         vDisplayError(pCE);
      }
      return bRC;
   }

   // 
   //    Disconnect from the SQL Server.
   // 
   void vDisconnect(_SQLServerPtr & spSQLServer)
   {
      // 
      //    Wrap with IS_OKAY so if an error is encountered, it is printed
      //    out.
      // 
      printf("\nDisconnecting from %s", _SERVER);
      try
      {
         spSQLServer->Close();
      }
      catch(_com_error pCE)
      {
         vDisplayError(pCE);
      }
   }

   // 
   //    vDoQuery
   // 
   void vDoQuery(_SQLServerPtr & spSQLServer, LPCTSTR strQuery)
   {
      QueryResultsPtr      spQueryResults;

      if(TRUE == bConnect(spSQLServer))
      {
         printf("\nExecuting %s", strQuery);
         try
         {
            spQueryResults = spSQLServer->ExecuteWithResults(strQuery);
            vDisplayResults(spQueryResults);
         }
         catch(_com_error pCE)
         {
            vDisplayError(pCE);
         }
         vDisconnect(spSQLServer);
      }
   }

   // 
   //    Dump out result set(s) information.
   // 
   void vDisplayResults(QueryResultsPtr & spQueryResults)
   {
      long        lMaxColLen        =  0;
      char        strDisplayData[_MAX_COL +1]   =  "";
      _bstr_t     bstrColName;
      _bstr_t        bstrRowInfo;
      try
      {

         // 
         //    Loop through the results sets.
         // 
         for(long lSet = 1; lSet <= spQueryResults->GetResultSets();
            lSet++)
         {
            spQueryResults->PutCurrentResultSet(lSet);
            printf("\n >>> Result set #%ld\n", lSet + 1);

            // 
            //    Print out the column headers.
            // 
            for(long lCols = 1; lCols <= spQueryResults->GetColumns();
            lCols++)
            {

               // 
               //    You used to have to call the SQLOLEFreeString ==>
               //    SysFreeString.
               //    However, the copy operator of the _bstr_t performs a
               //    free on any prior value and then handles the new data.
               // 
               //    This prevents a memory leak when using it in a loop.
               // 
               //    Look at the _bstr_t copy coperator in COMUTIL.H for
               //    complete details.
               // 
               bstrColName = spQueryResults->GetColumnName(lCols);

               // 
               //    Assuming ANSI build for sample so casing as char *
               // 
               strncpy(strDisplayData, (char *)bstrColName, _MAX_COL);
               printf(_MAX_COL_FMT, strDisplayData);
            }
            printf("\n\n");

            // 
            //    Show the data.
            // 
            printf("\n------------------------\n");
            for(long lRows = 1; lRows <= spQueryResults->GetRows();
         lRows++)
            {
               for(long lCols = 1; lCols <= spQueryResults->GetColumns();
            lCols++)
               {
                  bstrRowInfo = spQueryResults->GetColumnString(lRows,
                  lCols);
                  strncpy(strDisplayData, (char *)bstrRowInfo, _MAX_COL);
                  printf(_MAX_COL_FMT, strDisplayData);
               }
               printf("\n");     // End of row
            }
            printf("\nRows processed (%ld)", spQueryResults->GetRows());
         }     // End of FOR looping
      }
      catch(_com_error pCE)
      {
         vDisplayError(pCE);
      }
   }