Raw OLEDB Class Library

• Download source - 43 Kb

Overview

Raw OLEDB is discussed scarcely and working with COM is a nightmare for many and at times for me too. Anyways, if you can’t do it in raw it simply can’t be done with wrappers templates or whatever. I spend around a weekend working on the provided raw OLEDB code to create a set of classes that could show you how to work with raw OLEDB.

The following code has been used since 2001 and many production projects successfully, therefore many bugs or issues have been resolved. Don’t expect to have it perfectly, still I have tried to be clear on the use of OLEDB with lots of code comments where it merits. Your constructive comments and notes about the use, issues or suggestions will be greatly appreciated.

The following are some comments that I wrote by 2001:

I decided to implement a ClOleDb<x> class family using raw OLEDB to be used when performance is an issue. The classes here implement a similar set of classes as ADO COM, still since we are using OLEDB directly we should have better performance.

A secondary benefit is that we are spending time to implement database access in raw OLEDB that will help us to understand the details and capabilities of this technology and provide us with additional functionality and control beyond the use of ADO.

About the experience, I should tell that since I had implemented a similar class family using ODBC (v2.0, v3.5) and had been working with raw ODBC for the past 2 years, I will say that implementing access with OLEDB has been relatively painless.

To help me on working with OLEDB, I have used the following references:

• Microsoft OLE DB 2.0 Programmer's Reference and Data Access SDK (1998), Microsoft Press.
• MSDN Library October 2000 and January 2000. OLE DB and ODBC Developer's Guide, Wood Chuck (1999), M&T Books.

Understanding ClOleDb Implementation

I don’t plan to provide detailed class library documentation; still here are some notes on the implemented classes (see ClOleDb.h header):

The following is the list of files included:

Select Command Sample

I compiled and updated the code to work in VS2005 and also wrote the following sample for you to try it. Just change the command text and setup connection parameters to something meaningful for you.

void TestClOleDb()
{
   CoInitialize(NULL) ;

   // connect to database
   ClDBConnection dbConn ;
   if (!dbConn.Connect(
        TEXT("openk3"),TEXT("BMv4Model"), 
        TEXT("sa"),TEXT("password")))
      goto DieNow ;

   // prepare command, fields and paramaters
   TCHAR *pQry = TEXT("select userid, lastname" 
                 " from sysuser where deptid=?") ;

   ClDBFields *pFields = dbConn.AllocFields(2) ;
   ClDBParams *pParams = dbConn.AllocParams(1) ;

   if ((!pFields) && (!pParams))
      goto DieNow ;

   TCHAR UserId[UserIdLen+1] ;
   TCHAR LastName[LastNameLen+1] ;
   TCHAR Dept[DeptLen+1] ;

   pFields->Set(0,UserId,UserIdLen) ;
   pFields->Set(1,LastName,LastNameLen) ;
   pParams->Set(0,Dept,DeptLen) ;
   _tcscpy_s(Dept,TEXT("006")) ;

   // prepare command and obtain results
   ClDBResult *pRes = dbConn.Prepare(pQry,pFields,pParams) ;
   if (pRes) {

      // execute command
      if (pRes->Execute()) {

         // work with results
         if (pRes->GetResult()) {
            pRes->Open() ;
            if (pRes->Next(pFields)) {
               while (!pRes->Eof()) {
                  pRes->CopyString(UserId,UserIdLen,0) ;
                  pRes->CopyString(LastName,LastNameLen,1) ;
                  _tprintf(_T("%s (%s)\n"),UserId,LastName) ;
                  pRes->Next() ;
               }
            }
         }
      }

      // release some resources...
      pRes->Close() ;
   }

   // please always cleanup

   delete pParams ;
   delete pFields ;
   delete pRes ;

   dbConn.Disconnect() ;

   DieNow:

   CoUninitialize() ;
}  // end of TestClOleDb

Stored Procedure Command Execution Sample

Given the following stored procedure:

CREATE PROCEDURE TestGetUserNo1
   @UserId CHAR(4),
   @OutFirstName VARCHAR(20) OUTPUT
AS 
BEGIN
   SET NOCOUNT ON
   IF EXISTS(SELECT * FROM SysUser WHERE UserId = @UserId)
   BEGIN
      SELECT @OutFirstName = FirstName
        FROM SysUser
       WHERE UserId = @UserId
      RETURN 1
   END
   ELSE
      RETURN 0
END
GO

Use the following code to execute the procedure and fetch the return value and output parameter value:

void TestSPSupport()
{
   CoInitialize(NULL) ;

   // connect to database
   ClDBConnection dbConn ;
   if (!dbConn.Connect(TEXT("host"),TEXT("catalog"), 
                       TEXT("userid"),TEXT("password")))
      goto DieNow ;

   // get media id 
  TDBInt  ReturnValue ;
  TDBChar UserId[UserIdLen+1] ;
  TDBChar FirstName[FirstNameLen+1] ;

   // allocate and prepare parameters ...
   ClDBParams *pParams = dbConn.AllocParams(3) ;
   if (pParams) {

      // prepare parameters ...
      pParams->Set(0,&ReturnValue) ;
      pParams->Set(1,UserId,UserIdLen) ;
      pParams->Set(2,FirstName,FirstNameLen) ;

      pParams->IsForOutput(0) ;
      pParams->IsForOutput(2) ;

      _tcscpy_s(UserId,UserIdLen+1,_T("MAN")) ;
   }
   else
      return ;

   // prepare command
   ClDBResult *pRecordSet = dbConn.Prepare(
      TEXT("{ ? = CALL TestGetUserNo1(?,?) }"),
      NULL,pParams) ;

   if (pRecordSet) {

      // execute command and get first name and return-val
      bool done ;
      done = pRecordSet->ExecuteNoneQuery() ;
      if (done) {
         int returnVal ;
         pParams->GetInt(0,&returnVal) ;
         pParams->GetString(2,FirstName,FirstNameLen) ;
         _tprintf(_T("%d (%s)\n"),returnVal,FirstName) ;
      }
   }

   // release allocated resources

   pParams->Free() ;
   delete pParams ;
   if (pRecordSet)
      delete pRecordSet ;

   dbConn.Disconnect() ;

   DieNow:

   CoUninitialize() ;
}  // end of TestClOleDb

Multiple Results Sample

Given the following stored procedure:

CREATE PROCEDURE TestMultiResults1
   @UserId CHAR(4)
AS 
BEGIN
   SET NOCOUNT ON
   SELECT FirstName,LastName FROM SysUser WHERE UserId = @UserId
   SELECT FirstName,LastName FROM SysUser WHERE UserId <> @UserId
END
GO

Use the following code to execute the procedure and fetch multiple results:

void TestSPMultipleResults()
{
   CoInitialize(NULL) ;

   // connect to database
   ClDBConnection dbConn ;
   if (!dbConn.Connect(
      TEXT("openk3"),TEXT("BMv4Model"),TEXT("sa"),TEXT("password")))
      goto DieNow ;

   // get media id 
   TDBInt  ReturnValue ;
   TDBChar UserId[UserIdLen+1] ;
   TDBChar FirstName[FirstNameLen+1] ;
   TDBChar LastName[LastNameLen+1] ;

   // allocate and prepare parameters ...
   ClDBFields *pFields = dbConn.AllocFields(2) ;
   ClDBParams *pParams = dbConn.AllocParams(2) ;

   if (pFields && pParams) {

      // prepare fields 
      pFields->Set(0,FirstName,FirstNameLen) ;
      pFields->Set(1,LastName,LastNameLen) ;

      // prepare parameters ...
      pParams->Set(0,&ReturnValue) ;
      pParams->Set(1,UserId,UserIdLen) ;
      pParams->IsForOutput(0) ;

      _tcscpy_s(UserId,UserIdLen+1,_T("MAN")) ;
   }
   else
      return ;

   // prepare command
   ClDBResult *pRecordSet = dbConn.Prepare(
      TEXT("{ ? = CALL TestMultiResults1(?) }"),
      pFields,pParams) ;

   if (pRecordSet) {

      // execute command
      if (pRecordSet->Execute()) {

         // work with first results
         if (pRecordSet->GetResult()) {
            pRecordSet->Open() ;
            if (pRecordSet->Next(pFields)) {
               while (!pRecordSet->Eof()) {
                  pRecordSet->CopyString(FirstName,FirstNameLen,0) ;
                  pRecordSet->CopyString(LastName,LastNameLen,1) ;
                  _tprintf(_T("%s %s\n"),FirstName,LastName) ;
                  pRecordSet->Next() ;
               }
            }
         }
         pRecordSet->Close() ;

         // work with second results
         if (pRecordSet->GetResult()) {
            pRecordSet->Open() ;
            if (pRecordSet->Next(pFields)) {
               while (!pRecordSet->Eof()) {
                  pRecordSet->CopyString(FirstName,FirstNameLen,0) ;
                  pRecordSet->CopyString(LastName,LastNameLen,1) ;
                  _tprintf(_T("%s %s\n"),FirstName,LastName) ;
                  pRecordSet->Next() ;
               }
            }

            // release some resources...
            pRecordSet->Close() ;
         }
      }
   }

   // please always cleanup
   delete pRecordSet ;
   delete pParams ;
   delete pFields ;

   dbConn.Disconnect() ;

   DieNow:

   CoUninitialize() ;
}  // end of TestSPMultipleResults

Known Issues and Notes

What has been a bit difficult is handling MS-SQL TEXT fields since they are treated as a BLOB and I have not figured out why for some reason if they are the only piece of information been requested or is the last field in a query the data is fetched successfully. If they are not the only field requested and is not the last one in the query, the Provider returns STATUS=8 (meaning DBSTATUS_E_UNAVAILABLE). TEXT fields can be handled as BINARY or by using the ISequentialStream, still either will behave the same (as describe above). If your reader can provide some insides into this, please tell me.

The provided code was compiled in VS2005 B2 and I tried it with the UNICODE conditional compilation and found that it has some issues. Working with ANSI char (without the UNICODE conditional), it is working OK. I will be working on this issue soon and will update the article at a later time.

Also note that I am declaring my own enumerators and macros to support type mappings for later field and parameter bindings. This is good in the sense that I may take my ODBC classes and provide a ClOleDb<x> family that uses the exact properties and methods but that accesses ODBC instead of OLEDB.

Using ClOleDb Classes

Use the code as needed; if any code issue / bug are found, please send me an email and tell me about it. If you do any enhancements or modifications on the provided class please send me those too.

If you have any other questions, assistance or want to share your thoughts about the code, send me an email at esob@openk.com.