Articles   Members Online: 3
-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
Dynamic SQL Creation: Using a TStrings Descendant to Create a SQL Statement on 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

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 

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:
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;

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:

58  with Query1 do
59  begin
60    Active := False;
61    SQL.Clear;
62    //This will create a SELECT DISTINCT statement
63    SQL := CreateSelect(True, Edit1.Text, ListBox1.Items);
64    Open;
65  end;

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:

66  procedure GetFieldsAndMove;
67  var
68    tblPdox,
69      tbldBas: TTable;
70  begin
72    with Query1 do
73    begin
74      Active := False;
75      SQL.Clear;
76      //This will create a SELECT DISTINCT statement
77      SQL := CreateSelect(True, Edit1.Text, ListBox1.Items);
78      Open;
79    end;
81    tblPdox := TTable.Create(nil);
82    with tblPdox do
83    begin
84      Active := False;
85      DatabaseName := ExtractFilePath(Application.EXEName);
86      TableName := 'MyPdoxTable';
87      TableType := ttParadox;
88    end;
90    tbldBas := TTable.Create(nil);
91    with tbldBase do
92    begin
93      Active := False;
94      DatabaseName := ExtractFilePath(Application.EXEName);
95      TableName := 'MydBaseTable';
96      TableType := ttDBase;
97    end;
99    try
100     with BatchMove1 do
101     begin
102       Source := Query1;
103       Destination := tblPdox;
104       Execute;
105     end;
107     with BatchMove1 do
108     begin
109       Source := Query1;
110       Destination := tbldBase;
111       Execute;
112     end;
113   finally
114     tblPdox.Free;
115     tbldBase.Free;
116   end;
117 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.

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