Microsoft KB Archive/164292

= INF: Using Smart Pointers to Simplify SQL-DMO =

Article ID: 164292

Article Last Modified on 10/3/2003

-

APPLIES TO


 * Microsoft SQL Server 6.5 Standard Edition

-



This article was previously published under Q164292



SUMMARY
The article contains two samples showing how you can use a "Smart Pointer" to simplify SQL Server Distributed Management Object (SQL-DMO) coding.

Smart pointers easily handle AddRef and Release scoping.

Smart Pointers are actually C++ classes, so when an exception is handled, the destructor is called. This makes it possible for a Smart Pointer to release an Interface pointer where an Interfaces alone may cause a memory leak.

For more details on Smart Pointers, please refer to either Chapter 9, "Making it Easier," in Inside COM, or Visual C/C++ 5.0 Books Online.



MORE INFORMATION
You can use the first sample below with Visual C 4.2 or Visual C 5.0 because you implement the Smart Pointer features. The second sample takes full advantage of the built-in Smart Pointer support of Visual C 5.0.

#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 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 & spSQLServer); BOOL     bConnect(CISmartPointer & spSQLServer); void     vDisconnect(CISmartPointer & spSQLServer); void     vDoQuery(CISmartPointer & spSQLServer,      LPCTSTR strQuery); void     vDisplayResults(CISmartPointer      & 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  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 & 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 & 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 & 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 & spSQLServer, LPCTSTR   strQuery) {   CISmartPointer  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 &   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 }  }

=
==============================================================

The Visual C++ 5.0 release has simplified this even more. The following sample uses the built-in smart pointer support provided by Visual C 5.0.

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

Keywords: kbhowto kbprogramming KB164292

-

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

© Microsoft Corporation. All rights reserved.