Title: Query result into a string list.
Question: 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 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.