More of this Feature Printer friendly version

Join the Discussion "Post your views and comments to this chapter of the free Delphi database Programming Course" Discuss!

Related Resources • free DB Course.TOC • more Database articles • TOP ADO wrappers

Elsewhere on the Web • Download MS ADO

Article submitted by: Richard Sutcliffe.

Chapter 21 of the free Delphi ADO DB Couse for beginners.

With the advent of Delphi 5 Enterprise, Borland introduced ADO Express, the first time Delphi has ever provided database access without the BDE. The reason behind this whas simple, the BDE had its day. COM development was becoming the norm and the BDE was unfortunately incompatible.

Unfortunately, the ADO Express components aren't without their flaws aither. One of the main (now fixed) bugs was the dreaded 'either BOF or EOF is true or the record has been deleted error. On top of this, the components were uncomfortably shoe-horned into BDE-style component interfaces.

Which leaves us with a quandry over what to do if we want to use Active Data Objects (ADO) in our Delphi applications; after all there are many benefits - ADO is faster, with native support for some of the more common Microsoft database engines, and you don't have to live with the old BDE distribution problem as all recent versions of Windows have support built right in.

Delphi does provide an answer, however, in its amazing support for type-libraries and all things COM, by allowing you to import the type-library and automatically creating a wrapper.

   MSADOxx.TLB - Ado Type Library
To import the ADO type library go to the File menu and select Open... From the Files of type combo select Type Library, and browse to the ADO folder (usually found in \PROGRAM FILES\COMMON FILES\SYSTEM\). The ADO type-libraries are named MSADOxx.TLB. The number on the end will depend on the version of Windows you have installed (ADO is also installed by Office and many other programs). The latest version as of writing is 2.7, which you can download from Microsoft.

Once Delphi has finished processing the MSADOxx.TLB file, it will present you with an editor showing all the types, interfaces, etc it has read from the type-library. Pressing F12 at this point will display the Pascal wrapper unit ADODB_TLB.PAS (if the file-path isn't the BORLAND\DELPHIx\IMPORTS\ folder you may wish to move this so subsequent projects can find it).

You can at this point use the ADODB_TLB unit by adding it into your projects USES clause, however there are some problems to be overcome.

   Microsoft's VARIANT type
The first is how to handle Microsoft's VARIANT type. Delphi's equivalent is OLEVARIANT, though most programmers will be used to the native STRING, INTEGER and the like. Again this is something Delphi handles with ease with an array of functions such as VarCast. Below is an example of a function that allows us to cast variants into something easier to swallow:

function oleGetStr(value: oleVariant): string;
var
  index,
  lowVal,
  highVal : integer;
  oleArray: PSafeArray;
  oleObj : oleVariant;
begin
  result := '';

  try
    case VarType(value) of
    varEmpty,
    varNull: result := '';
    varSmallint,
    varInteger,
    varByte,
    varError: result := IntToStr(value);
    varSingle,
    varDouble,
    varCurrency: result := FloatToStr(value);
    varDate: result := DateTimeToStr(value);
    varOleStr,
    varStrArg,
    varString: result := value;
    varBoolean: 
       if value then 
          result := 'True' 
       else result := 'False';
    varDispatch, // do not remove IDispatch!
    varVariant,
    varUnknown,
    varTypeMask:
    begin
      VarAsType(value, varOleStr);
      result := value;
    end;
    else
      if VarIsArray(value) then
      begin
        VarArrayLock(value);
        index := VarArrayDimCount(value);
        lowVal := VarArrayLowBound(value, index);
        highVal := VarArrayHighBound(value, index); 
        oleArray := TVariantArg(value).pArray; 

        for index := lowVal to highVal do
        begin
          SafeArrayGetElement(oleArray, index, oleObj);
          result := result + oleGetStr(oleObj) + #13#10;
        end;

        VarArrayUnlock(value);
        Delete(result, length(result) - 1, 2);
      end
      else
        result := ''; //varAny, varByRef
    end;
  except
    // do nothing, just capture
  end;
end;

Once you know how to import type-libraries & handle variant types, Microsoft's MSDN site will become your best friend. There is no way Borland could keep up with the new type-libraries and technologies churned out by Microsoft, however by utilizing MSDN you can find out exactly what each method expects and returns and the wrapper unit will allow you to see exactly how this converts into Object Pascal.

An example of this can be found in the function below:

function adoConnect(
  connectionStr: string; 
  userName: string = ''; 
  password: string = ''
 ): oleVariant;
begin
  result := CreateOleObject('ADODB.Connection');

  if (VarType(result) = varDispatch) then
    if (userName <> '') then
      result.Open(connectionStr, userName, password)
    else
      result.Open(connectionStr);
end;

This function allows us to open up an ADO connection by passing in a connection string (a collection of parameters parsed by ADO describing the database & provider), and optionally the required username and password.

The example above uses what is termed late-binding, the disadvantage of this approach being that Delphi cannot interpret what is being called until runtime, however it does allow you better support as the automatic type-library conversion process cannot convert every method available. The alternative, early-binding, utilizes the classes & methods as defined in the Delphi wrapper unit and as such allow you to use features such as code-completion, although is much more restrictive. An example of early-binding is shown below:

function adoGetDisconnectedRecordset(
    adoCon: oleVariant; 
    adoSQL: string
): oleVariant;
var
  data: RecordSet;
begin
  data := CoRecordset.Create;

  try
    data.CursorLocation := adUseClient;

    data.Open(
      adoSQL, 
      adoCon.ConnectionString,
      adOpenKeyset,
      adLockBatchOptimistic,
      adCmdText
    );

    data.Set_ActiveConnection(nil);

    result := data;
  finally
    data := nil;
  end;
end;

Now that you know how to interpret type-libraries you can apply your knowledge to other Microsoft and third-party technologies, such as XML and MTS.

   To the next chapter
If you need any kind of help at this point, please post to the Delphi Programming Forum where all the questions are answered and beginners are treated as experts.

DB Course Next Chapter >>
>> Transactions in Delphi ADO database development