Author: Lou Adler
How to sort a Table 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:
1
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...
19
20 uses DBIProcs, DBITypes, DBIErrs {You must add these to your uses section!!!}
21
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.
35
36 ====================================================================================
37 =}
38
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}
67
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;
77
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));
87
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.}
92
93 DBISetDirectory(hDb, dName);
94
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;
110
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;
121
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 ===============================================================================}
126
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.
|
