How can I get all data stored in an array

Question

I am programming a system in Delphi that should trigger automatic emails, and to store some of the emails that I have in my database, I place them in an array as follows?

function TFormMain.getEmail: string;
var
mailBill: array of string;
matriz: array of integer;
i,rec: integer;
begin
 i := 0;
 rec := FDQuerySale.RecordCount;
 SetLength(mailBill, rec);

 SetLength(matriz, Length(matriz) + 1);
 matriz[Length(matriz) - 1] := rec;

 FDQuerySale.Close;
 FDQuerySale.SQL.Clear;
 FDQuerySale.SQL.Add('SELECT id_clients,billed FROM sales  WHERE billed = ''N''  ');
 FDQuerySale.Open();
 while not (FDQuerySale.Eof) do
 begin
   matriz[i] := FDQuerySale.FieldByName('id_clients').AsInteger;
   FDQuerySale.Next;
   inc(i);
 end;
 FDQuerySale.Close;

 FDQueryClient.Close;
 FDQueryClient.SQL.Clear;
 FDQueryClient.SQL.Add('SELECT id_clients, email FROM clients WHERE id_clients = :vId_client');
 FDQueryClient.ParamByName('vId_client').Value := matriz[i];
 FDQueryClient.Open();
 mailBill[i] := FDQueryClientEMAIL.AsString;
 FDQueryClient.Close;

How can I take all this data and fire all the emails at once?

Answer 1

You have a logic problem here:

FDQueryClient.ParamByName('vId_client').Value := matriz[i];

matrix[i] has a result only because you are accessing a specific position.

The ideal would be to use WHERE id_clients IN(1, 2, 3, ...). But it is worth remembering that in the Firebird IN (passing values) has a limitation of 1500 items.

You also do not need to order sales and customers separately from the database.

We can improve the query for something like:

SELECT C.email
FROM sales S
JOIN clientes C on C.id_clients = S.id_clients
WHERE S.billed = ''N''
  AND C.id_clients > 0 -- aqui é para acionar algum indice

Now a new improved repetition structure:

FDQuerySale.First;    
while not FDQuerySale.Eof do
begin
  SetLength(matriz, Succ(Length(matriz)); // dimensiona a matriz conforme necessário
  matriz[High(matriz)] := FDQuerySale.FieldByName('email').AsString
  FDQuerySale.Next;
end;

To fire all emails simultaneously we can use thread, however, it needs a lot of control for executions like this.

Basically we can do it this way:

TParallel.For(0, Pred(Length(matriz)),
  procedure (i: integer)
  begin
    TThread.Queue(TThread.CurrentThread,
      procedure
      begin
        EnviarEmail(matriz[i]);
      end)
  end);

With a little bit of analysis on top of this answer we can notice that we don’t even need this "matrix" array, it could all be done in the Fdquerysale repetition structure.