Articles   Members Online:
-Article/Tip Search
-News Group Search over 21 Million news group articles.
Member Area
-Account Center
-Top 10 NEW!!
-Submit Article/Tip
-Forums Upgraded!!
-My Articles
-Edit Information
-Become a Member
-Why sign up!
-Chat Online!
-Indexes NEW!!
-Build your resume
-Find a job
-Post a job
-Resume Search
-Link to us
Visit Embarcadero
Embarcadero Community
How to sort aTable using DBISortTable Turn on/off line numbers in source code. Switch to Orginial background IDE or DSP color Comment or reply to this aritlce/tip for discussion. Bookmark this article to my favorite article(s). Print this article
Sort a table using DBISortTable 28-Aug-02
Delphi All Versions
User Rating
No Votes
# Votes
DSP, Administrator
Reference URL:
			Author: Lou Adler 

How to sort a Table using DBISortTable


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:
2   function DbiSortTable({ Sort table }
3     hDb: hDBIDb; { Database handle }
4     pszTableName: PChar; { Table name of source }
5     pszDriverType: PChar; { Driver type /NULL }
6     hSrcCur: hDBICur; { OR cursor of table to sort }
7     pszSortedName: PChar; { Destination table (NULL if sort to self) }
8     phSortedCur: phDBICur; { If non-null, return cursor on destination }
9     hDstCur: hDBICur; { OR cursor of destination }
10    iSortFields: Word; { Number of sort fields }
11    piFieldNum: PWord; { Array of field numbers }
12    pbCaseInsensitive: PBool; { Which fields should sort c-i (Opt) }
13    pSortOrder: pSORTOrder; { Array of Sort orders (Opt) }
14    ppfSortFn: ppfSORTCompFn; { Array of compare fn pntrs (Opt) }
15    bRemoveDups: Bool; { TRUE : Remove duplicates }
16    hDuplicatesCur: hDBICur; { Cursor to duplicates table (Opt) }
17    var lRecsSort: Longint { in/out param. - sort this number }
18    ): 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...
20  uses DBIProcs, DBITypes, DBIErrs {You must add these to your uses section!!!}
22  {===================================================================================
23  =
24  Sorts a table using the DBISortTable method. The trick here was setting the sort 
25  direction.
26  The pSortOrder is a pointer to an enumerated type. So first you have to set a var 
27  that is of that type to an appropriate value, then set a pointer's value to equal 
28  the value of the var. It's a real pain.
29  Note  : This sorts STANDARD driver tables only. To any type, you'd set up a PChar 
30  to hold the valid driver type and insert the pointer as a param for driver type in  
31      the DBISortTable declaration. Also, this will sort on only ONE field. 
32  Furthermore,
33  the method will not sort Paradox tables to self (which this does) if the table has 
34  a primary index.
36  ====================================================================================
37  =}
39  procedure SortATable(dbName, tblName, {Database and Table Name}
40    sortOrd: string; {'A' = Ascending 'D' = Descending}
41    fldNum: Integer); {The field number to sort on}
42  var
43    msg: string;
44    hDb: hDBIDb;
45    pOptFldDesc: pFLDDesc;
46    pOptParams: pBYTE;
47    dbRes: DBIResult;
48    dName,
49      tName: PChar;
50    sOrd: sortOrder;
51    pSort: pSortOrder;
52    arrFlds: array[0..0] of Integer;
53      {This is the array of fieldnums. Note it's only one element large}
54    boolVal: Boolean;
55    pRecs: LongInt;
56  begin
57    {Initialize vars}
58    arrFlds[0] := fldNum; {Set the element to the field number to sort on}
59    boolVal := True;
60    New(pSort);
61    if (sortOrd = 'A') then
62      sOrd := sortASCEND
63    else
64      sOrd := sortDESCEND;
65    pSort^ := sOrd; {set the value of the pointer to whatever was passed}
66    DBIInit(nil); {initialize the database engine}
68    {Now, get a handle to the default database. We won't specify a path just yet }
69    dbRes := DBIOpenDatabase(nil, nil, dbiREADWRITE, dbiOPENSHARED, nil, 0,
70      @pOptFldDesc, @pOptParams, hDb);
71    case dbRes of
72      DBIERR_UNKNOWNDB: msg := 'Database specified is unknown. Check your drivers.';
73      DBIERR_NOCONFIGFILE: msg := 'No IDAPI.CFG file for this machine. Install BDE.';
74      DBIERR_DBLIMIT: msg := 'Maximum number of databases have been opened.
75  Close down one and retry'
76    end;
78    if (dbRes <> DBIERR_NONE) then
79    begin
80      raise Exception.Create(msg);
81      Exit;
82    end;
83    GetMem(tName, SizeOf(PChar) * 256);
84    GetMem(dName, SizeOf(PChar) * 256);
85    StrPCopy(tName, tblName);
86    StrPCopy(dName, GetAliasPath(dbName));
88    {Now set the directory to the specified path of the alias passed. 
89  	Why do this when we can pass the alias to DBIOpenDatabase directly?
90    Well, I ran across some really problems doing that, so I decided
91    to do it after I got the handle.}
93    DBISetDirectory(hDb, dName);
95    {Make the call to DbiSortTable, passing the appropriate parameters. 
96  	Note that about half of the parameters are nil. 
97    That's because they're optional for simple sorts, and since they're pointers,
98    you can pass nils.}
99    try
100     dbRes := DbiSortTable(hDb, tName, nil, nil, nil, nil, nil, 1,
101       @arrFlds, @boolVal, pSort, nil, False, nil, pRecs);
102     case dbRes of
103       DBIERR_INVALIDHNDL: msg := 'Invalid database handle - alias bad';
104       DBIERR_INVALIDFILENAME: msg := 'Invalid file name specified';
105       DBIERR_UNKNOWNTBLTYPE: msg := 'The source driver type was not provided.';
106       DBIERR_INVALIDPARAM: msg := 'The specified number of sort fields is invalid.';
107       DBIERR_NOTSUPPORTED: msg := 'DBISortTable does not support sorting to self on 
108 a '  'Paradox table with a primary index.';
109     end;
111     if (dbRes <> DBIERR_NONE) then
112       raise Exception.Create(msg);
113   finally
114     {Free up all memory used.}
115     DbiCloseDatabase(hDb);
116     Dispose(pSort);
117     FreeMem(tName, SizeOf(PChar) * 256);
118     FreeMem(dName, SizeOf(PChar) * 256);
119   end;
120 end;
122 {===============================================================================
123   Gets the path of an existing alias. Will produce an error message if the alias
124   doesn't exist. I threw this in from the previous page.
125  ===============================================================================}
127 function GetAliasPath(aliasName: string): string;
128 var
129   cfgRec: DBDesc;
130   dbRes: DBIResult;
131   tempStr: array[0..255] of char;
132 begin
133   result := '';
134   dbRes := DBIGetDatabaseDesc(StrPCopy(tempStr, aliasName), @cfgRec);
135   if dbRes = DBIERR_OBJNOTFOUND then
136   begin
137     raise Exception.create('The database alias input is not a valid BDE alias.');
138   end
139   else
140     result := strPas(cfgRec.szPhyName);
141 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.

Vote: How useful do you find this Article/Tip?
Bad Excellent
1 2 3 4 5 6 7 8 9 10


Share this page
Download from Google

Copyright © Mendozi Enterprises LLC