Click here to Skip to main content
Click here to Skip to main content
Go to top

Raw OLEDB Class Library

, 13 Jun 2005
Rate this:
Please Sign up or sign in to vote.
This class provides a raw OLEDB class library.

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):

Type Description
DBSettings Use the following to supply the info needed to access the database including the host/server, catalog, user and related password.
ClDBValidate A helper to validate dates.
ClDBField Support for managing output fields and their required info needed for later binding.
ClDBFields Holds a collection of ClDBFields.
ClDBParams Support for managing command input / output parameters and the required info needed for later binding.
ClDBConnection Support for handling a connection, errors and other session related management.
ClDBCommand Support for submitting and managing a DB request.
ClDBColumnInfo Provided to obtain detailed information about columns.
ClDBFastLoad Support for FastLoad (Bulk requests).
ClDBResult Support for managing requests result sets.

The following is the list of files included:

File Description
ClOleDb.h Declarations and class definitions.
ClOleDb.cpp Class method definitions not provided in include file.
FString.h String / Buffer handling definitions for parsing and conversion.
FString.cpp Class method definitions for classes in FString.h.
Get.h Commonly used helper functions for managing strings / chars, conversions and so on.
Get.cpp Class method definitions for classes in Get.h.
SysLib.h Commonly used macros and definitions.

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.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

esob
Web Developer
Puerto Rico Puerto Rico
C/C++ programmer since 1984.

Comments and Discussions

 
Questiondoes this library work well with mssql 2008? Pinmemberbingcaihuang8-Jun-13 4:05 
GeneralMy vote of 4 Pinmemberthelvaci27-Apr-13 10:07 
QuestionTransaction rollback or commit Pinmemberthelvaci27-Apr-13 10:05 
Hi ESOB,
 
How can I apply transaction-commit or rollback methods with your code?
Thank you very much for your feedback.
Regards,
Tahir Helvaci
GeneralGetResult failing Pinmemberproject_manager24-Sep-07 21:30 
GeneralShare Connection PinmemberAlessandro H Machado9-May-07 9:19 
GeneralSql - CE PinmemberAlexEvans2-Apr-07 20:40 
Generalbetter performance Pinmemberneverd29-Mar-07 11:22 
General, Pinmemberyafan14-Jun-05 3:39 
GeneralRe: , Pinmemberyafan14-Jun-05 3:45 
GeneralCan't compile Pinmemberdspeziale13-Jun-05 22:02 
GeneralRe: Can't compile PinmemberJerry Evans13-Jun-05 23:25 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web02 | 2.8.140916.1 | Last Updated 14 Jun 2005
Article Copyright 2005 by esob
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid