Author: Lou Adler
This is a bit of a departure from my normal style of Q & A, but I thought it would
be useful for those of you who do a lot of database-related stuff.
Answer:
General Purpose Stuff
Face it, code reuse is big issue irrespective of programming in an object-oriented
environment. Especially, with database application programming, the stuff you do
over and over again can be a bit tedious. So, I've created a bunch of general
purpose functions that I use in my code - a lot.
An On-the-Fly SELECT Statement Generator
The first function CreateSelect creates a SELECT or SELECT DISTINCT statement that
can be used in whenever you create a TQuery for querying against a local table like
Paradox. It's most useful for when you want to create a query from a table at
runtime, but don't know the fields you want to include. For instance, I created an
adhoc querying tool where users could select the fields they wanted displayed in a
graph by dragging the field names from on list box into another. From there, I
passed the selected fields list to CreateSelect and constructed a SELECT statement
on the fly. It was a one-line call as opposed to the multiple lines I'd normally
have to write for a query. Here's the code:
1
2 {==========================================================
3 This function will create a SELECT or SELECT DISTINCT SQL
4 statement given input from a TStrings descendant like a
5 list. It will properly format the list into field decla-
6 rations of a SQL statement then, using the supplied
7 TableNm parameter, will construct an entire statement that
8 can be assigned to the SQL property of a TQuery.
9
10 Params: Distinct SELECT DISTINCT or regular SELECT
11 TableNm Table name: Should either be a fully
12 qualified table name, or preceeded by
13 an alias (ie, ':DbName:MyTable.db')
14 FieldList Any TStrings descendant will work here,
15 like the Items property of a TListBox.
16 ==========================================================}
17
18 function CreateSelect(Distinct: Boolean;
19 TableNm: string;
20 const FieldList: TStrings)
21 : TStrings;
22 var
23 Sql: TStringList;
24 I: Integer;
25 buf,
26 QueryType: string;
27 begin
28 //First, instantiate the SQL lines list
29 Sql := TStringList.Create;
30
31 //Determine whether or no this is a regular SELECT
32 //or a SELECT DISTINCT query.
33 if Distinct then
34 QueryType := 'SELECT '
35 else
36 QueryType := 'SELECT DISTINCT ';
37
38 buf := QueryType;
39
40 try
41 //Now add the fields to the select statement
42 //Notice that if we're on the last item,
43 //we don't want to add a trailing comma.
44 for I := 0 to (FieldList.Count - 1) do
45 if (I <> FieldList.Count - 1) then
46 buf := buf + FieldList[I] + ', '
47 else
48 buf := buf + FieldList[I];
49
50 //Now, put the query together
51 Sql.Add(buf);
52 Sql.Add('FROM "' + TableNm + '"');
53 Result := Sql;
54 finally
55 Sql.Free;
56 end;
57 end;
Here's a code snippet from one of my programs that implements the function:
58 var
59 qry: TQuery;
60 begin
61 qry := TQuery.Create(nil);
62 with qry do
63 begin
64 Active := False;
65 DatabaseName := 'MyLocalDatabase';
66 SQL := CreateSelect(True, ExtractFilePath(ParamStr(0))
67 + 'Process.DB', lbSelectedFlds.Items);
68 try
69 Open;
70 ....other code....
71 finally
72 Free;
73 end;
74 end;
75 end;
WHERE It Is....
I use a lot of TStrings types in my code because they make it easy to pass a bunch
of values at once. Especially with selection criteria in queries, having the
capability to select on multiple values is a boon to efficiency. The three
functions below can be added to a WHERE clause in a SQL statement for
multiple-value searching. All you need to do is assemble the search criteria into a
TStrings type like a TStringList or use even the Items property of a TListBox to be
able to use these functions. Respectively, they allow you to create a basic
multiple-value criteria statement (i.e. (fldName = 'thisvalue') OR (fldName =
'thatvalue'); an IN selection criteria statement; and a multiple-value LIKE
selection criteria statement. Here they are:
76
77 {Returns a '(FldName = FldValue) OR (FldName = FldValue)' etc string from
78 a list of values. Useful for translating TListBox values into SQL strings
79 IsValString is a boolean to test if the list values are a value string; that is,
80 it contains spaces, in which case, you would want double-quotes.}
81
82 function BuildSQLSetString(fldName: string; const List: TStrings;
83 IsValString: Boolean): string;
84 var
85 I: Integer;
86 buf: string;
87 begin
88 Result := '';
89 for I := 0 to (List.Count - 1) do
90 begin
91 if IsValString then
92 buf := '(' + fldName + ' = ''' + List[I] + ''') OR '
93 else
94 buf := '(' + fldName + ' = ' + List[I] + ') OR ';
95
96 Result := Result + buf;
97 end;
98 Result := Copy(Result, 1, Length(Result) - 4);
99 end;
100
101 //This will build an IN statement
102
103 function BuildSQLINString(fldName: string; const List: TStrings;
104 IsValString: Boolean): string;
105 var
106 I: Integer;
107 buf: string;
108 begin
109 Result := '';
110 for I := 0 to (List.Count - 1) do
111 if IsValString then
112 buf := buf + '''' + List[I] + ''', '
113 else
114 buf := buf + List[I] + ', ';
115
116 buf := Copy(buf, 1, Length(TrimRight(buf)) - 1);
117
118 Result := fldName + ' IN (' + buf + ')';
119 end;
120
121 //This will build a LIKE statement
122
123 function BuildSQLLikeString(fldName: string; const List: TStrings;
124 IsValString: Boolean): string;
125 var
126 I: Integer;
127 buf: string;
128 begin
129 Result := '';
130 for I := 0 to (List.Count - 1) do
131 begin
132 if IsValString then
133 buf := '(' + fldName + ' LIKE ''' + TrimRight(List[I]) + '%'') OR '
134 else
135 buf := '(' + fldName + ' LIKE ' + List[I] + '%) OR ';
136
137 Result := Result + buf;
138 end;
139 Result := Copy(Result, 1, Length(Result) - 4);
140 end;
Notice that in addition to the field name and value list, there's a parameter in
each function called IsValString. Set this to true if the field you're searching on
is a string field. Remember, discrete string values in SQL must be delimited by
single quotes.
Some MS SQL Server Stuff
A little over a year ago, I moved completely away from doing local table processing
with Paradox and started doing all my processing under a Client/Server environment
using MS SQL Server. I was in for a big surprise when I made the switch because
many of the things that I could do with Paradox tables that I took for granted,
weren't as easily done in SQL Server. Yikes! So, what I did was create some generic
routines specifically for Client/Server applications to make my life much easier.
Let's see what I've got...
How Many Was That?
For those of you who work with local tables, you know how easy it is to get the
number of records in a table: Simply connect a TTable object to the table in
question and query the RecordCount property. Well, in SQL Server, that's not so
easy because the idea of a "record" is non-existent in this set-based data
environment. But, just because you can't get the information from the table
directly, doesn't mean that it doesn't exist. In fact, the information is stored in
the system tables. Here's a function that will get you the number of rows contained
in a SQL Server table:
141
142 // =============================================================================
143 // Returns the SQL DB path of the specified BDE alias.
144 // =============================================================================
145
146 function GetSQLDBPath(const AliasName: ShortString): ShortString;
147 var
148 ParamList: TStrings; // List of parms from the BDE
149 Path: ShortString; // Path returned from the BDE
150 begin
151 Result := '';
152 ParamList := TStringList.Create;
153 try
154 Session.GetAliasParams(AliasName, ParamList);
155 Path := ParamList[0];
156 Result := Copy(Path, Pos('=', Path) + 1, Length(Path) - Pos('=', Path) + 1);
157 finally
158 ParamList.Free;
159 end;
160 end;
161
162 function GetSQLTableRows(SesName: string; DB: TDatabase; TableName: string):
163 Integer;
164 var
165 qry: TQuery;
166 begin
167 qry := TQuery.Create(nil);
168 with qry do
169 begin
170 Active := False;
171 SessionName := sesName;
172 DatabaseName := DB.DatabaseName;
173 SQL.Add('SELECT DISTINCT I.Rows');
174 SQL.Add('FROM ' + GetSQLDBPath(DB.DatabaseName) + '..SysIndexes AS I INNER
175 JOIN'
176 SQL.Add(' ' + GetSQLDBPath(DB.DatabaseName)
177 + '..SysObjects AS O ON (I.ID = O.ID)');
178 SQL.Add('WHERE (O.Type = ''U'') AND (O.Name = ''' + TableName + ''')');
179 try
180 Open;
181 Result := FieldByName('Rows').AsInteger;
182 finally
183 Free;
184 end;
185 end;
186 end;
Some of you might be wondering: "Of what use is this function?" Well, for some of
you, indeed, it might be of no use whatsoever. But in the type of applications I
write that query datasets with records numbering in the several millions, in some
steps, I only want to continue processing if my result sets aren't empty. This
function gives me a quick way of checking if I need to continue or not. So there!
To Transact or Not To Transact (SQL, that is)
If you're using SQL Server, but not using Transact SQL, you're missing out on a lot
of functionality. I know, I know, there are those cross-platform junkies out there
that will only write the most generic code so they can deploy their DB apps on
other servers. That's all well and good, but for the most of us, we only have a
single server platform, and to not use its native functionality is to deprive
ourselves of a lot of functionality. For instance, how many of you create temporary
tables when doing a series of queries? C'mon, raise yer hands high! Using strict
ANSI SQL with Delphi, you would have to create three objects: a TQuery to perform
the query, a TBatchMove to move the result table, and a TTable to receive the
results. Yikes! But with SQL Server, you can perform a SELECT INTO query use only a
single object: a TQuery. Check out the code snippet from one of my programs below:
187 var
188 qry: TQuery;
189 begin
190 qry := TQuery.Create(nil);
191 with qry do
192 begin
193 Active := False;
194 SessionName := ClassSession.SessionName;
195 DatabaseName := DBName;
196 SQL.Add('SELECT DISTINCT T.PatientID, T.Symptom);
197 SQL.Add('mp1);
198 SQL.Add('FROM ' + SymptomTable + ' T (TABLOCK), HoldingTable H (TABLOCK)');
199 SQL.Add('WHERE (H.PatientID = T.PatientID) AND (Age > 65) ');
200 SQL.Add('AND (Thiazides_Now IS NULL) AND (GOUT IS NULL)');
201 try
202 ExecSQL;
203 finally
204 Free;
205 end;
206 end;
207 // ....the rest of the code....
This simple query above accomplishes with one object using Transact-SQL that it
normally takes three objects to do with standard ANSI SQL. Notice that the query is
executed using ExecSQL as opposed to Open. If you're using Transact-SQL, everything
happens on the server, so you get no cursors back. That might present a problem in
some circumstances, but for the most part, since you're doing nothing but set
logic, having a cursor to a result set isn't necessary.
Not only does Transact-SQL make it shorter to do extracts, it has some cool syntax
that allows you to really short-circuit both code and performance. For instance,
one of the things that has always irked me about SQL is doing a two-table update;
that is, updating a field in a table from values contained in another table. In
Paradox, it's simple QBE query with matching example elements. In ANSI SQL, it
involves a subquery. Let's look at an example:
Standard ANSI SQL two-table update:
UPDATE ClaimsTbl
SET History = (SELECT Activity
FROM ActivityTbl AS A
WHERE (ClaimsTbl.Patient = A.Patient))
Not too complex, but let me tell you, it's as slow as molasses on SQL Server. On
the other hand, the following Transact-SQL UPDATE statement works lickety-split!
UPDATE ClaimsTbl
FROM ClaimsTbl C, Activity A
SET C.History = A.Activity
WHERE (C.Patient = A.Patient)
Not much different from the example above, but since SQL Server is equipped to deal
with this syntax in an optimized fashion, it works much faster.
Why have I spent so much time covering this stuff? Well, I'm a real proponent of
creating systems that run in the most optimized fashion I can make them. And that
means that I use all the tools available to me. Yes, it can be argued that I'm
locking myself into a specific platform's functionality, but since I only have one
platform that I'm dealing with, why not use it? I realize that I haven't covered
hardly any of the Transact-SQL syntax. If you've got it, I'll leave it up to you to
go hunting for the information (BTW, a great source is the SQL Server Books Online
reference that comes with the software). The bottom line is this: If you're
building systems that address a single server platform, make the best use of that
server's capabilities.
But Wait! There's More!
Below are some general purpose routines that I use on a regular basis for accessing
my SQL Server databases. Check 'em out:
208
209 //Uses Session.GetTableNames to get a list of tables from the specified database
210
211 procedure GetTableList(DBName: string; const TableList: TStrings);
212 var
213 db: TDatabase;
214 begin
215 db := TDatabase.Create(Application);
216 with db do
217 begin
218 DatabaseName := DBName;
219 LoginPrompt := False;
220 Connected := True;
221 end; { with }
222 try
223 Session.GetTableNames(db.DatabaseName, '', False, False, TableList);
224 db.Close;
225 finally
226 db.Free;
227 end;
228 end;
229
230 //Uses GetTableList to see if a particular table exists in database.
231
232 function TableExists(var ProcSession: TSession; var DB: TDatabase;
233 TableName: string): Boolean;
234 var
235 TableList: TStrings;
236 begin
237 Result := False;
238 TableList := TStringList.Create;
239 try
240 ProcSession.GetTableNames(DB.DatabaseName, '', False, False, TableList);
241 if (TableList.IndexOf(TableName) > 0) then
242 Result := True;
243 finally
244 TableList.Free;
245 end;
246 end;
247
248 //Performs a series of drops for all table names contained in the input array.
249 //Very useful for cleaning up a bunch of temporary tables at once.
250
251 procedure CleanMSSQLTbl(var ProcSession: TSession; DBName: string;
252 TableNames: array of string); overload;
253 var
254 sqlEI: TQuery;
255 I: Integer;
256 begin
257 for I := Low(TableNames) to High(TableNames) do
258 begin
259 sqlEI := TQuery.Create(nil);
260 with sqlEI do
261 begin
262 SessionName := ProcSession.SessionName;
263 DatabaseName := DBName;
264 with SQL do
265 begin
266 Add('if exists (select * from sysobjects where ' +
267 'id = object_id(''' + TableNames[I] + ''') and sysstat & 0xf = 3)');
268 Add('drop table ' + TableNames[I]);
269 try
270 ExecSQL;
271 finally
272 Free;
273 end;
274 end;
275 end;
276 end;
277 end;
278
279 //Will return a TStrings list containing the results of a query
280
281 function SQLLoadList(SesName, TblName, FldName: string): TStrings;
282 var
283 qry: TQuery;
284 begin
285 Result := TStringList.Create;
286 qry := TQuery.Create(nil);
287 with qry do
288 begin
289 Active := False;
290 if (SesName <> '') then
291 SessionName := SesName;
292 DatabaseName := ExtractFilePath(TblName);
293 SQL.Add('SELECT DISTINCT ' + FldName);
294 SQL.Add('FROM "' + TblName + '"');
295 try
296 Open;
297 while not EOF do
298 begin
299 Result.Add(FieldByName(FldName).AsString);
300 Next;
301 end;
302 finally
303 Free;
304 end;
305 end;
306 end;
Notice in most of the functions above, that they either require a TSession or a
TSession.SessionName as one of their formal parameters. This is because these
functions are thread-safe. In order to use these in a multi- threaded system, you
need to create a TSession instance for every thread, and database access within the
scope of the thread require a session name to operate under. Otherwise you'll get
access violations. Not good.
Well, that's it for now. I encourage you to use these functions and concepts in your own code. Believe me, they've saved me a lot of time!
|
