Query result into a string list

Problem/Question/Abstract:

Have you ever needed to load the result of a query into a string ?
Here's how to load the result of a query into a string list.

Answer:

Have you ever needed to load the result of a query into a string ?
Here's how to load the result of a query into a string list.

Let's say we have a table named 'Contact' which holds the fields 'first_name', 'last_name', 'phone', 'salutation'.
Let's say you just need to load these result once into your application, you can either keep a permanent connection to access the data or you can load it once, or whenever necessary, into memory and then free the connection.

Let's choose to load the data into memory, otherwise this article would not have any reason for existing! :)

What I show here is a very simple "trick", using a TQuery and TStringList, I show how to load each record from the TQuery's result set into a string of the TStringList.
So, let's say we need the last name and from the contact table.
You know a simple

SELECT last_name FROM contact

will do the job, all you need to do is to loop the result and add it to the string list.
But, how about if we need the salutation, last name and contact fields all at once in only one string ?  Well, the solution is also simple, for record a loop through the requeted attributes is also done!

Before I show the code to do this simple task, I'll explain how it will be achieved:

1. Receiver the database name, table name, attributes, field separator and a string list.
2. Split the attributes string into a list of strings
3. Run the database query
4. Loop in the result set
4.1. For each result set, loop the attributes
4.2. Add all attributes from the result set into the string list

And now, a possible implementation of this:

You will require these units: dbtables, stdctrls and classes.

// - One Attribute for each array position, sequentially -

procedure FillRecordSL(DBName, T, A, C, FS: string; var SL: TStringList);
var
  Attrs: TStringList;
  F: ShortInt;

  // - Split Attributes -
  procedure SplitAttributes(A: string; var Attrs: TStringList);
  var
    X: Integer;
    S: string;
  begin
    if not (Assigned(Attrs)) then
      Attrs := TStringList.Create;

    S := '';
    X := 1;
    while (X <= Length(A)) do
    begin
      if (A[X] = ',') then
      begin
        Attrs.Add(Trim(S));
        S := '';
      end
      else
        S := S + A[X];

      Inc(X);
    end;
    Attrs.Add(Trim(S + A[X]));

  end;

begin
  Attrs := TStringList.Create;
  SlitAttributes(A, Attrs);

  with TQuery.Create(nil) do
  begin
    DatabaseName := DBName;
    FilterOptions := [foCaseInsensitive];
    SQL.Add('SELECT ' + A + ' FROM ' + T);
    if Length(C) > 0 then
      SQL.Add('WHERE ' + C);
    Prepare;
    while not (Prepared) do
      ;
    Open;
    First;
    try
      while not (EOF) do
      begin
        AuxStr := '';
        for F := 0 to Attrs.Count - 1 do
          AuxStr := AuxStr + FS + Fields[F].AsString;
        Delete(AuxStr, 1, Length(FS));
        SL.Add(AuxStr);
        Next;
      end;
      Close;
    finally
      Free;
    end;
  end;

  Attrs.Free;
end;

Let's assume that your database name is MyDB and you already have a SL variable of type TStringList.
Now some examples, to access the salutation, last name and contact, all you have to do is to call the procedure this way:

FillRecordSL('MyDB', 'contact', 'salutation, last_name, contact', '', ' ', SL);

Now the SL varibale helds someting like this:

SL[0] = 'Mr. Kong 098765432'
SL[1] = 'Mrs. Chita 098765431'
SL[2] = 'Miss Tarzan 123456789'

FillRecordSL('MyDB', 'contact', 'salutation, first_name, last_name, contact',
  'salutation = ''Mrs.''', '; ', SL);

Now the SL varibale helds someting like this:

SL[1] = 'Mrs.; Mila; Chita; 098765431'

FillRecordSL('MyDB', 'contact', 'last_name, first_name, contact', '', ', ', SL);

Now the SL varibale helds someting like this:

SL[0] = 'Kong, King, 098765432'
SL[1] = 'Chita, Mila, 098765431'
SL[2] = 'Tarzan, Jane, 123456789'

You can expand this procedure to increase its capabilities, what I ment to show here was just a starting point.
Hope it helps you.