Mega Code Archive
Sql
<![CDATA[
Section One:
The Basics.
(Richard Ebbs)
USING AN SQL QUERY
PREAMBLE: we use a DIY 'detail line' object in the function
below, so we need to understand how that works before we
look at the SQL code proper...
since we don't know how many result lines (ie database records)
we will want to store at the outset, things are set up here so
that we dynamically create a detail line object each time we
encounter a new record
type
{object to temporarily hold database details before
handing them to the ListView component for display}
TDetailLine = class(TObject)
styleName: String;
fileName: String;
end;
private
{private declarations}
detailLine: TDetailLine;
end;
FUNCTION WITH SQL CODE IN
procedure TSideSectionStyleForm.LoadSideSectionStyleTable;
{load data from SSStyle.db (using SQL) and store it in
the 'detail line' variable so we can display it in the
ListView later...}
var
dbFullFileName: String;
sssTQuery: TQuery;
exeFileName, pathToExe: String;
queryString: String;
begin
dLineList.Clear;
sssTQuery := TQuery.Create(nil);
exeFileName := Application.ExeName;
pathToExe := ExtractFilePath(exeFileName);
dbFullFileName := pathToExe + dbTablesDataDir + sideSectionStylesTableName;
sssTQuery.DatabaseName := ExtractFileDir(dbFullFileName);
if (FileExists(dbFullFileName)) then
begin
{note that #39 is a single apostrophe (and #34 is a double one)...}
queryString := 'SELECT StyleName, FileName FROM '
+ #39 + dbFullFileName + #39;
sssTQuery.sql.Add(queryString);
{open the table}
sssTQuery.Open;
if sssTQuery.RecordCount > 0 then
begin
sssTQuery.First;
while not sssTQuery.EOF do
begin
detailLine := TDetailLine.Create;
detailLine.refNo := (sssTQuery.FieldByName('OrderReference').AsString);
detailLine.custName := (sssTQuery.FieldByName('CustomerName').AsString);
detailLine.styleName := (sssTQuery.FieldByName('StyleName').AsString);
detailLine.orderDate := (sssTQuery.FieldByName('OrderDate').AsString);
detailLine.thumbFileName := (sssTQuery.FieldByName('ThumbFileName').AsString);
dLineList.Add(DetailLine);
sssTQuery.Next;
end;
end;
end;
sssTQuery.Free;
end;
procedure TSideSectionStyleForm.FormDestroy(Sender: TObject);
var
listIdx: Integer;
dListPointer: TDetailLine;
begin
for listIdx := 0 to (dLineList.Count - 1) do
begin
dListPointer := dLineList.Items[listIdx];
dListPointer.Free;
end;
end;
*******************************************************************************************
Various Other SQL / MS SQL Server Routines
(Someone else).
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:
{==========================================================
This function will create a SELECT or SELECT DISTINCT SQL
statement given input from a TStrings descendant like a
list. It will properly format the list into field decla-
rations of a SQL statement then, using the supplied
TableNm parameter, will construct an entire statement that
can be assigned to the SQL property of a TQuery.
Params: Distinct SELECT DISTINCT or regular SELECT
TableNm Table name: Should either be a fully
qualified table name, or preceeded by
an alias (ie, ':DbName:MyTable.db')
FieldList Any TStrings descendant will work here,
like the Items property of a TListBox.
==========================================================}
function CreateSelect(Distinct : Boolean;
TableNm : String;
const FieldList : TStrings)
: TStrings;
var
Sql : TStringList;
I : Integer;
buf,
QueryType : String;
begin
//First, instantiate the SQL lines list
Sql := TStringList.Create;
//Determine whether or no this is a regular SELECT
//or a SELECT DISTINCT query.
if Distinct then
QueryType := 'SELECT '
else
QueryType := 'SELECT DISTINCT ';
buf := QueryType;
try
//Now add the fields to the select statement
//Notice that if we're on the last item,
//we don't want to add a trailing comma.
for I := 0 to (FieldList.Count - 1) do
if (I <> FieldList.Count - 1) then
buf := buf + FieldList[I] + ', '
else
buf := buf + FieldList[I];
//Now, put the query together
Sql.Add(buf);
Sql.Add('FROM "' + TableNm + '"');
Result := Sql;
finally
Sql.Free;
end;
end;
Here's a code snippet from one of my programs that implements the function:
var
qry : TQuery;
begin
qry := TQuery.Create(nil);
with qry do begin
Active := False;
DatabaseName := 'MyLocalDatabase';
SQL := CreateSelect(True, ExtractFilePath(ParamStr(0)) + 'Process.DB', lbSelectedFlds.Items);
try
Open;
....other code....
finally
Free;
end;
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:
{Returns a '(FldName = FldValue) OR (FldName = FldValue)' etc string from
a list of values. Useful for translating TListBox values into SQL strings
IsValString is a boolean to test if the list values are a value string; that is,
it contains spaces, in which case, you would want double-quotes.}
function BuildSQLSetString(fldName : String; const List : TStrings;
IsValString : Boolean) : String;
var
I : Integer;
buf : String;
begin
Result := '';
for I := 0 to (List.Count - 1) do begin
if IsValString then
buf := '(' + fldName + ' = ''' + List[I] + ''') OR '
else
buf := '(' + fldName + ' = ' + List[I] + ') OR ';
Result := Result + buf;
end;
Result := Copy(Result, 1, Length(Result) - 4);
end;
//This will build an IN statement
function BuildSQLINString(fldName : String; const List : TStrings;
IsValString : Boolean) : String;
var
I : Integer;
buf : String;
begin
Result := '';
for I := 0 to (List.Count - 1) do
if IsValString then
buf := buf + '''' + List[I] + ''', '
else
buf := buf + List[I] + ', ';
buf := Copy(buf, 1, Length(TrimRight(buf)) - 1);
Result := fldName + ' IN (' + buf + ')';
end;
//This will build a LIKE statement
function BuildSQLLikeString(fldName : String; const List : TStrings;
IsValString : Boolean) : String;
var
I : Integer;
buf : String;
begin
Result := '';
for I := 0 to (List.Count - 1) do begin
if IsValString then
buf := '(' + fldName + ' LIKE ''' + TrimRight(List[I]) + '%'') OR '
else
buf := '(' + fldName + ' LIKE ' + List[I] + '%) OR ';
Result := Result + buf;
end;
Result := Copy(Result, 1, Length(Result) - 4);
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:
// =============================================================================================
// Returns the SQL DB path of the specified BDE alias.
// =============================================================================================
function GetSQLDBPath(const AliasName: ShortString): ShortString;
var
ParamList: TStrings; // List of parms from the BDE
Path : ShortString; // Path returned from the BDE
begin
Result := '';
ParamList := TStringList.Create;
try
Session.GetAliasParams(AliasName,ParamList);
Path := ParamList[0];
Result := Copy(Path,Pos('=',Path) + 1,Length(Path) - Pos('=',Path) + 1) ;
finally
ParamList.Free;
end;
end;
function GetSQLTableRows(SesName : String; DB : TDatabase; TableName : String) : Integer;
var
qry : TQuery;
begin
qry := TQuery.Create(nil);
with qry do begin
Active := False;
SessionName := sesName;
DatabaseName := DB.DatabaseName;
SQL.Add('SELECT DISTINCT I.Rows');
SQL.Add('FROM ' + GetSQLDBPath(DB.DatabaseName) + '..SysIndexes AS I INNER JOIN');
SQL.Add(' ' + GetSQLDBPath(DB.DatabaseName) + '..SysObjects AS O ON (I.ID = O.ID)');
SQL.Add('WHERE (O.Type = ''U'') AND (O.Name = ''' + TableName + ''')');
try
Open;
Result := FieldByName('Rows').AsInteger;
finally
Free;
end;
end;
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:
var
qry : TQuery;
begin
qry := TQuery.Create(nil);
with qry do begin
Active := False;
SessionName := ClassSession.SessionName;
DatabaseName := DBName;
SQL.Add('SELECT DISTINCT T.PatientID, T.Symptom);
SQL.Add('INTO ' + EvalTemp1);
SQL.Add('FROM ' + SymptomTable + ' T (TABLOCK), HoldingTable H (TABLOCK)');
SQL.Add('WHERE (H.PatientID = T.PatientID) AND (Age > 65) ');
SQL.Add('AND (Thiazides_Now IS NULL) AND (GOUT IS NULL)');
try
ExecSQL;
finally
Free;
end;
end;
....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:
//Uses Session.GetTableNames to get a list of tables from the specified database
procedure GetTableList(DBName: String; const TableList: TStrings);
var
db : TDatabase;
begin
db := TDatabase.Create(Application);
with db do
begin
DatabaseName := DBName;
LoginPrompt := False;
Connected := True;
end; { with }
try
Session.GetTableNames(db.DatabaseName, '', False, False, TableList);
db.Close;
finally
db.Free;
end;
end;
//Uses GetTableList to see if a particular table exists in database.
function TableExists(var ProcSession : TSession; var DB : TDatabase; TableName : String) : Boolean;
var
TableList : TStrings;
begin
Result := False;
TableList := TStringList.Create;
try
ProcSession.GetTableNames(DB.DatabaseName, '', False, False, TableList);
if (TableList.IndexOf(TableName) > 0) then
Result := True;
finally
TableList.Free;
end;
end;
//Performs a series of drops for all table names contained in the input array.
//Very useful for cleaning up a bunch of temporary tables at once.
procedure CleanMSSQLTbl(var ProcSession : TSession; DBName: String;
TableNames : Array of String); overload;
var
sqlEI : TQuery;
I : Integer;
begin
for I := Low(TableNames) to High(TableNames) do begin
sqlEI := TQuery.Create(nil);
with sqlEI do begin
SessionName := ProcSession.SessionName;
DatabaseName := DBName;
with SQL do begin
Add('if exists (select * from sysobjects where ' +
'id = object_id(''' + TableNames[I] + ''') and sysstat & 0xf = 3)');
Add('drop table ' + TableNames[I]);
try
ExecSQL;
finally
Free;
end;
end;
end;
end;
end;
//Will return a TStrings list containing the results of a query
function SQLLoadList(SesName, TblName, FldName : String) : TStrings;
var
qry : TQuery;
begin
Result := TStringList.Create;
qry := TQuery.Create(nil);
with qry do begin
Active := False;
if (SesName <> '') then
SessionName := SesName;
DatabaseName := ExtractFilePath(TblName);
SQL.Add('SELECT DISTINCT ' + FldName);
SQL.Add('FROM "' + TblName + '"');
try
Open;
while NOT EOF do begin
Result.Add(FieldByName(FldName).AsString);
Next;
end;
finally
Free;
end;
end;
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!
]]>