INF: Using Smart Pointers to Simplify SQL-DMO
ID: Q164292
|
The information in this article applies to:
-
Microsoft SQL Server version 6.5
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 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
}
}
===========================================================================
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 : kbprg SSrvDMO SSrvProg
Version : 6.5
Platform : WINDOWS
Issue type : kbhowto