Mega Code Archive
Dynamic sql creation using a tstrings descendant to create a sql statement on the fly
<![CDATA[
How do I retrieve the text from a list box to add to the SQL property of a TQuery then create both a Paradox and dBase table?
--------------------------------------------------------------------------------
One thing I love about Delphi is that since it's object oriented, you can perform a lot of quick and dirty code tricks that wouldn't be possible with other languages. For instance, the ability to assign values of like properties from one object to another saves so much coding time. Take a list box, for example, as in your question.
What you essentially want to do is create a SQL statement from fields listed in a list box. If you think about it, a list box's Items property and a TQuery's SQL property are both TStrings descendants. This means that you can do a direct assignation between the two.
Actually, that's only half true. You have to format the fields into a proper SQL statement format first, and that requires an intermediate TStrings object.
Luckily though, we can easily accomplish the conversion for field list to SQL statement with a simple function. The function listed below takes a list of fields, a Boolean value to determine whether or not the query is a DISTINCT select, and a table name, and puts all of those together into a valid SQL statement that can easily be
assigned to a TQuery's SQL property. Here's the listing:
{==========================================================
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;
To use this, let's say you have a list box call ListBox1, and a query called Query1. You also have a TEdit called Edit1 that holds the table name value. Here's how you'd make the call:
with Query1 do begin
Active := False;
SQL.Clear;
//This will create a SELECT DISTINCT statement
SQL := CreateSelect(True, Edit1.Text,
ListBox1.Items);
Open;
Okay, now that we've finished creating the statement and running the query, we have to move the answers to both Paradox an dBase. This is easily accomplished with a TBatchMove component.
Building on the previous example,. let's say you have a TBatchMove component embedded on your form. We'll call it BatchMove1. To move the answer to a Paradox and a dBase table, you need to use the BatchMove to move the contents of the answer from Query1 to two new tables. The listing below lists an entire procedure that will accomplish this:
procedure GetFieldsAndMove;
var
tblPdox,
tbldBas : TTable;
begin
with Query1 do begin
Active := False;
SQL.Clear;
//This will create a SELECT DISTINCT statement
SQL := CreateSelect(True, Edit1.Text, ListBox1.Items);
Open;
end;
tblPdox := TTable.Create(nil);
with tblPdox do begin
Active := False;
DatabaseName :=
ExtractFilePath(Application.EXEName);
TableName := 'MyPdoxTable';
TableType := ttParadox;
end;
tbldBas := TTable.Create(nil);
with tbldBase do begin
Active := False;
DatabaseName :=
ExtractFilePath(Application.EXEName);
TableName := 'MydBaseTable';
TableType := ttDBase;
end;
try
with BatchMove1 do begin
Source := Query1;
Destination := tblPdox;
Execute;
end;
with BatchMove1 do begin
Source := Query1;
Destination := tbldBase;
Execute;
end;
finally
tblPdox.Free;
tbldBase.Free;
end;
end;
Again, this is pretty straight-forward stuff. If you need more information on the TBatchMove component, it is well-documented in the online help.
]]>