Dynamic SQL Creation: Using a TStrings Descendant to Create a SQL Statement on the Fly

Problem/Question/Abstract:

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?

Answer:

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

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.