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
Various SQL / MS SQL Server Routines 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
Delphi 2.x
User Rating
No Votes
# Votes
DSP, Administrator
Reference URL:
			 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.


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:
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.
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   ==========================================================}
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;
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 ';
38    buf := QueryType;
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];
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:
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.}
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 ';
96      Result := Result + buf;
97    end;
98    Result := Copy(Result, 1, Length(Result) - 4);
99  end;
101 //This will build an IN statement
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] + ', ';
116   buf := Copy(buf, 1, Length(TrimRight(buf)) - 1);
118   Result := fldName + ' IN (' + buf + ')';
119 end;
121 //This will build a LIKE statement
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 ';
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:
142 // =============================================================================
143 // Returns the SQL DB path of the specified BDE alias.
144 // =============================================================================
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;
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:
209 //Uses Session.GetTableNames to get a list of tables from the specified database
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;
230 //Uses GetTableList to see if a particular table exists in database.
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;
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.
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;
279 //Will return a TStrings list containing the results of a query
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!

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