Sorting aTable Using DBISortTable

Problem/Question/Abstract:

How to sort aTable using DBISortTable

Answer:

I'm not a masochist by nature, but having started to delve into the Borland Database Engine has made me rethink that. Well, I shouldn't be too hard on myself. Let me just say that I think Borland should come out with a manual that is specific to Delphi regarding DBI calls. The current manual is written for C/C++ programmers, so if you're not all that familiar with the syntax (or really rusty with it like I am), it's a long process in making the translation to Pascal using the examples. Actually, it really sucks! but that's beside the point. I'll add, though, that once you do learn how to pass the myriad parameters to the functions, it becomes relatively easy - I say relatively because there's a lot that can go wrong, and you'd never know it until you see the results. For example, I was passing the wrong type of parameter in the pSortOrder param. The function ran without a hitch, only to empty my table! ARRRGH!

Before I go on, I advise you to purchase the Borland Database Engine manual from Borland. I think it's only US$15.00, and it's worth it. I will not be discussing the data types, just how to make the call. In any case, here's the code.

The DBIPROCS.INT file lists the function call as follows:

function DbiSortTable({ Sort table }
  hDb: hDBIDb; { Database handle }
  pszTableName: PChar; { Table name of source }
  pszDriverType: PChar; { Driver type /NULL }
  hSrcCur: hDBICur; { OR cursor of table to sort }
  pszSortedName: PChar; { Destination table (NULL if sort to self) }
  phSortedCur: phDBICur; { If non-null, return cursor on destination }
  hDstCur: hDBICur; { OR cursor of destination }
  iSortFields: Word; { Number of sort fields }
  piFieldNum: PWord; { Array of field numbers }
  pbCaseInsensitive: PBool; { Which fields should sort c-i (Opt) }
  pSortOrder: pSORTOrder; { Array of Sort orders (Opt) }
  ppfSortFn: ppfSORTCompFn; { Array of compare fn pntrs (Opt) }
  bRemoveDups: Bool; { TRUE : Remove duplicates }
  hDuplicatesCur: hDBICur; { Cursor to duplicates table (Opt) }
  var lRecsSort: Longint { in/out param. - sort this number }
  ): DBIResult;

And here's a method that uses the call. Mind you, that this will sort only on one field because that was all I needed it to do. If you want to sort on more fields, all you have to do is increase the size of the array (the piFieldNum param) and make sure you make the right field number assignments to the array elements (see the comments in the code below). Okay, here's the code...

uses DBIProcs, DBITypes, DBIErrs {You must add these to your uses section!!!}

{====================================================================================
Sorts a table using the DBISortTable method. The trick here was setting the sort direction.
The pSortOrder is a pointer to an enumerated type. So first you have to set a var that is of that type to an appropriate value, then set a pointer's value to equal the value of the var. It's a real pain.
Note  : This sorts STANDARD driver tables only. To any type, you'd set up a PChar to hold the valid driver type and insert the pointer as a param for driver type in      the DBISortTable declaration. Also, this will sort on only ONE field. Furthermore,
the method will not sort Paradox tables to self (which this does) if the table has a primary index.
   =====================================================================================}

procedure SortATable(dbName, tblName, {Database and Table Name}
  sortOrd: string; {'A' = Ascending 'D' = Descending}
  fldNum: Integer); {The field number to sort on}
var
  msg: string;
  hDb: hDBIDb;
  pOptFldDesc: pFLDDesc;
  pOptParams: pBYTE;
  dbRes: DBIResult;
  dName,
    tName: PChar;
  sOrd: sortOrder;
  pSort: pSortOrder;
  arrFlds: array[0..0] of Integer;
    {This is the array of fieldnums. Note it's only one element large}
  boolVal: Boolean;
  pRecs: LongInt;
begin
  {Initialize vars}
  arrFlds[0] := fldNum; {Set the element to the field number to sort on}
  boolVal := True;
  New(pSort);
  if (sortOrd = 'A') then
    sOrd := sortASCEND
  else
    sOrd := sortDESCEND;
  pSort^ := sOrd; {set the value of the pointer to whatever was passed}
  DBIInit(nil); {initialize the database engine}

  {Now, get a handle to the default database. We won't specify a path just yet }
  dbRes := DBIOpenDatabase(nil, nil, dbiREADWRITE, dbiOPENSHARED, nil, 0,
    @pOptFldDesc, @pOptParams, hDb);
  case dbRes of
    DBIERR_UNKNOWNDB: msg := 'Database specified is unknown. Check your drivers.';
    DBIERR_NOCONFIGFILE: msg := 'No IDAPI.CFG file for this machine. Install BDE.';
    DBIERR_DBLIMIT: msg := 'Maximum number of databases have been opened.
                                                                                                                Close down one and retry';
  end;

  if (dbRes <> DBIERR_NONE) then
  begin
    raise Exception.Create(msg);
    Exit;
  end;
  GetMem(tName, SizeOf(PChar) * 256);
  GetMem(dName, SizeOf(PChar) * 256);
  StrPCopy(tName, tblName);
  StrPCopy(dName, GetAliasPath(dbName));

  {Now set the directory to the specified path of the alias passed.
        Why do this when we can pass the alias to DBIOpenDatabase directly?
  Well, I ran across some really problems doing that, so I decided
  to do it after I got the handle.}

  DBISetDirectory(hDb, dName);

  {Make the call to DbiSortTable, passing the appropriate parameters.
        Note that about half of the parameters are nil.
  That's because they're optional for simple sorts, and since they're pointers,
  you can pass nils.}
  try
    dbRes := DbiSortTable(hDb, tName, nil, nil, nil, nil, nil, 1,
      @arrFlds, @boolVal, pSort, nil, False, nil, pRecs);
    case dbRes of
      DBIERR_INVALIDHNDL: msg := 'Invalid database handle - alias bad';
      DBIERR_INVALIDFILENAME: msg := 'Invalid file name specified';
      DBIERR_UNKNOWNTBLTYPE: msg := 'The source driver type was not provided.';
      DBIERR_INVALIDPARAM: msg := 'The specified number of sort fields is invalid.';
      DBIERR_NOTSUPPORTED: msg := 'DBISortTable does not support sorting to self on a '  +  'Paradox table with a primary index.';
    end;

    if (dbRes <> DBIERR_NONE) then
      raise Exception.Create(msg);
  finally
    {Free up all memory used.}
    DbiCloseDatabase(hDb);
    Dispose(pSort);
    FreeMem(tName, SizeOf(PChar) * 256);
    FreeMem(dName, SizeOf(PChar) * 256);
  end;
end;

{===============================================================================
  Gets the path of an existing alias. Will produce an error message if the alias
  doesn't exist. I threw this in from the previous page.
===============================================================================}

function GetAliasPath(aliasName: string): string;
var
  cfgRec: DBDesc;
  dbRes: DBIResult;
  tempStr: array[0..255] of char;
begin
  result := '';
  dbRes := DBIGetDatabaseDesc(StrPCopy(tempStr, aliasName), @cfgRec);
  if dbRes = DBIERR_OBJNOTFOUND then
  begin
    raise Exception.create('The database alias input is not a valid BDE alias.');
  end
  else
    result := strPas(cfgRec.szPhyName);
end;

Note: This is an OLD Delphi 1.0 method for sorting a table. If you're going to use this in your Delphi 2+ applications, make sure you use the BDE uses file instead of the DBIProcs, etc. declarations in your uses section. Furthermore, you don't need to trap the errors yourself. Instead, enclose the BDE calls in the Check function to trap errors. It's a much cleaner implemenation. Note that I could use compiler directives to make this compatible with older versions of Delphi, but time is of the essence, and this has been sitting in my home directory for quite awhile.