Query result to dynamic array

[PenelopeSkye 1]

I need to loop through a dynamic array which is populated by the result of a query.  I have gotten as far as what you see below, but I keep getting an error 

           Incompatible types: 'Char' and 'Variant'

at the end of the following line

           Result := q_InsValuePackLinks2.Fields[i].Value;

 

I have to admit that I changed a lot of this messing around to the point where I don't know what other parts are wrong, so I thought I'd ask for help!

How do I create a dynamic array from q result set?

Thanks!
 

procedure TfDesignMaster.btnVP_PostClick(Sender: TObject);
 
 var b: array of string;
Begin
         var i,j,numberOfFields: integer;
        var PresentationID,designid,master_pres,result,presentations:string;
        with dm do

            begin
              master_pres :=q_GetStockNumFromValuePackLinks.FieldByName('master_presentation').AsString;
              q_InsValuePackLinks2.SQL.Add('select presentation from presentation_master where master_presentation = '+master_pres);
              q_InsValuePackLinks2.Open;

              presentations := q_InsValuePackLinks2.Fields.ToString;
              SetLength(Result,i);
              for i := 0 to q_InsValuePackLinks2.Fields.Count - 1 do
                Result[i] := q_InsValuePackLinks2.Fields[i].Value;
            end;
end;

 

[programmerdelphi2k 237]

For simpler code (without using complex business rules, classes, rtti, etc...), you could create a "record TYPE" with all values "type" that you need to store in your Array, then, you need verify "where store this, where store that"...

type

 

type
  TMyRecToStoreSomeValues = record
    ValInt: integer;
    ValString: string;
    ValDate: TDate;
    // etc...
  end;

procedure TForm1.Button1Click(Sender: TObject);
var
  LMyValue : TMyRecToStoreSomeValues;
  LMyValues: TArray<TMyRecToStoreSomeValues>;
begin
  myTable.First; // after your filters...
  //
  while not myTable.EOF do
    begin
      for var F in myTable.Fields do
        begin
          if F.DisplayName = 'hello' then
            LMyValue.ValInt := F.AsInteger;
          if F.DisplayName = 'world' then
            LMyValue.ValString := F.AsString;
          if F.DisplayName = 'Delphi' then
            LMyValue.ValDate := F.AsDateTime;
        end;
      //
      LMyValues := LMyValues + [LMyValue];
      //
      myTable.NEXT;
    end;
  //
  // reading... for example
  for var V in LMyValues do
    Memo1.Lines.Add(                                                     { }
      V.ValInt.ToString + ' ' + V.ValString + ' ' + DateToStr(V.ValDate) { }
      );
end;

 

Edited April 11, 2023 by programmerdelphi2k

[PenelopeSkye 1]

Thanks James!!!  Unfortunately I haven't successfully gotten the query results into the dynamic array.

 

Did you explain how above and I'm so ignorant I didn't see it?  That is entirely possible!!!!!

[programmerdelphi2k 237]

type
  TMyRecToStoreSomeValues = record
    ValInt: integer;
    ValString: string;
    ValDate: TDate;
    // etc...
  end;

  TMyValuesInArray = TArray<TMyRecToStoreSomeValues>;

function HelloWorld: TMyValuesInArray;
var
  LMyValue : TMyRecToStoreSomeValues;
  LMyValues: TMyValuesInArray;
begin
  LMyValues := [];
  //
  // ...
  //
  result := LMyValues;  // of course, dont abuse this... like result = 1.000.000.000.000 records :)   when all end, free it on "target"
end;

 

Edited April 11, 2023 by programmerdelphi2k

[Uwe Raabe 2064]

3 hours ago, PenelopeSkye said:

I need to loop through a dynamic array which is populated by the result of a query. 

Why the array? What hinders you to iterate through the dataset directly? Besides being easier it also avoids the memory problem when copying the complete query result into an array.

[PenelopeSkye 1]

Thank you both.

 

I get the master presentation from one table using FieldByName.

I use that master presentation to get anywhere from 1 to 8 presentations from a second table.

Then I need to loop through the dynamic list of presentations and update the row in the second table.

 

How would I get a dataset with this information without using a query?

[Uwe Raabe 2064]

45 minutes ago, PenelopeSkye said:

How would I get a dataset with this information without using a query?

I didn't say you need no query - you don't need the array.

[PenelopeSkye 1]

Ah!  Got it!  Thanks!

[PenelopeSkye 1]

I am able to loop through the result set from the query!  Thank you both so much!!!

[PenelopeSkye 1]

On 4/11/2023 at 3:22 PM, programmerdelphi2k said:

Hi James, I went back to your code as I needed exactly what you wrote and I still don't get it. I'm finally past embarrassment and so am asking how to populate that array using a query because I can't get it to work.

type
  TMyRecToStoreSomeValues2 = record
    ValInt: integer;
    ValString: string;
    ValDate: TDate;
    // etc...
  end;

  TMyValuesInArray = TArray<TMyRecToStoreSomeValues2>;

function HelloWorld: TMyValuesInArray;
var
  LMyValue : TMyRecToStoreSomeValues2;
  LMyValues: TMyValuesInArray;
begin
  LMyValues := [ dm.q_GetStockNumFromValuePackLinks.SQL.Text := 'SELECT art_status  FROM [AdiMS2].[dbo].[art_status]';
  //
  // ...
  //
  result := LMyValues;  
end;

 

[programmerdelphi2k 237]

@PenelopeSkye

 

try some like this:

• just note that my function it's not really generic ok? it's just a simple sample!

type
  TMyRecordWithValues = record
    FEmpNo: integer;
    FEmpFirstName: string;
    FEmpBirthday: TDate;
  end;

function MyListOfValuesInMyTableByFields(const ATable: TFDQuery; const ASQL: string; out AError: string): TArray<TMyRecordWithValues>;
var
  LTableIsActive     : boolean;
  LOldSQL            : string;
  LMyRecordWithValues: TMyRecordWithValues;
begin
  result := [];
  //
  if (ATable = nil) or (ASQL.IsEmpty) then
    exit;
  //
  LTableIsActive := ATable.Active; // if need use old statement...  else, remove this 2 lines and vars definitions!
  LOldSQL        := ATable.SQL.Text;
  //
  if LTableIsActive then
    ATable.Close; // free any resource... if necessary!
  //
  try
    try
      ATable.Open(ASQL);
      ATable.First;      
      //
      while not ATable.Eof do
        begin
          LMyRecordWithValues.FEmpNo        := ATable.FieldByName('EMP_NO').AsInteger;
          LMyRecordWithValues.FEmpFirstName := ATable.FieldByName('FIRST_NAME').AsString;
          LMyRecordWithValues.FEmpBirthday  := ATable.FieldByName('HIRE_DATE').AsDateTime;
          //
          result := result + [LMyRecordWithValues];
          //
          ATable.Next;
        end;
    except
      on E: Exception do
        begin
          result := [];
          AError := E.Message;
        end;
    end;
  finally
    ATable.Close;
    //
    if LTableIsActive then
      ATable.Open(LOldSQL);
  end;
end;

procedure TForm1.Button1Click(Sender: TObject);
var
  LText : string;
  LError: string;
begin
  for var R in MyListOfValuesInMyTableByFields(EmployeeTable, 'select * from employee', LError) do
    LText := LText + slinebreak + R.FEmpNo.ToString + ', ' + R.FEmpFirstName + ', ' + DateToStr(R.FEmpBirthday);
  //
  if not LError.IsEmpty then
    Memo1.Text := LError
  else
    if not LText.IsEmpty then
      Memo1.Text := LText.Remove(0, 2);
end;

 

Edited May 2, 2023 by programmerdelphi2k

[programmerdelphi2k 237]

... or directly in your code.

procedure TForm1.Button1Click(Sender: TObject);
var
  LText              : string;
begin
  EmployeeTable.Close;
  EmployeeTable.Open('Select * from employee');
  EmployeeTable.First;
  //
  LText := '';  
  //
  while not EmployeeTable.Eof do
    begin
      LText := LText + slinebreak + EmployeeTable.FieldByName('FIRST_NAME').AsString { + ... other fields values };
      //
      EmployeeTable.Next;
    end;
  //
  if not LText.IsEmpty then
    Memo1.Text := LText.Remove(0, 2);
end;

Edited May 2, 2023 by programmerdelphi2k

[PenelopeSkye 1]

I am not using it for the memo box. I am clicking on a field with some text. I need to compare the value from that field to a list of values from the database.

 

Wouldn't that call for a dynamic array?

 

Thank you!

[PenelopeSkye 1]

I tried looping through the dataset and comparing it to the value but the UI behaved very oddly, Sorry, I am not making it easy for you to help, I should probably go home and come back when I feel better!

Edited May 2, 2023 by PenelopeSkye

[programmerdelphi2k 237]

@PenelopeSkye

• if you are using the Dataset (ATable or your dataset xxx) in your Form/Datamodule that is " the same" dataset using in "while not ATable.EOF....", then, you will have a "side effect" in your screen:  record rolling your dbgrid or any other component or memory (if you dont use any other component binded in your datasource!

how to solve it?

• use a "new" dataset (TEMPORARY to any other actions, like: catch the values, print, etc...) to your SQL actions!
  • aNewTable := TFDQuery.Create; .....  ANewTable.Open( '..... sql.....' );
  • when end, free it:  FreeAndNil(ANewTable);

summary:

• dont use the same dataset to "scroll your records"!
• that way, you dont need "close" and "open" your "read dataset = used in your forms, for example"!

Edited May 2, 2023 by programmerdelphi2k

[PenelopeSkye 1]

Duuuude!  You are a genius!!! Thank you!!!

[programmerdelphi2k 237]

no! no! no! Eistein who knows?

[Lajos Juhász 295]

What's wrong with the good old DisableControls/EnableControls using bookmark? (https://docwiki.embarcadero.com/Libraries/Sydney/en/Data.DB.TDataSet.DisableControls)

[programmerdelphi2k 237]

look, same that you "disable it or hide it" or use "ATable.Datasource := nil" etc... , the record still scrolling in background (memory)

[Lajos Juhász 295]

2 minutes ago, programmerdelphi2k said:

look, same that you "disable it or hide it" or use "ATable.Datasource := nil" etc... , the record still scrolling in background (memory)

it's cheaper to query a database and move the data through the network.

[PenelopeSkye 1]

I just got back!!  Since I don't understand what you are talking about I can't weigh in, but I am very very happy to say that the code works as expected! 🙂

 

Thank you both for weighing in!!!

[skyzoframe[hun] 4]

The best solution for converting the query into an array was developed by Pawel Glowacki in Chapter 9 of the Expert Delphi book.

I use the same technique all the time, except I do everything at runtime.

ToDoListSQLite.7z

[skyzoframe[hun] 4]

On 5/2/2023 at 7:11 PM, programmerdelphi2k said:

var LTableIsActive : boolean; LOldSQL : string; LMyRecordWithValues: TMyRecordWithValues

In these case, maybe you need one more row.

LMyRecordWithValues := default (TMyRecordWithValues);

[programmerdelphi2k 237]

41 minutes ago, skyzoframe[hun] said:

maybe you need one more row.

Definitely not!
because the expected target (function return) is an "Array", so if the array is empty, there is nothing to expect from its "possible" content (the records), and if there is "content", there will be "records" with values!

[skyzoframe[hun] 4]

26 minutes ago, programmerdelphi2k said:

because the expected target (function return) is an "Array", so if the array is empty, there is nothing to expect from its "possible" content (the records), and if there is "content", there will be "records" with values! 

I mean, before you put anything into the array, you have to clear the record, then fill the record. If you don't do so, then there are the opportunity to create redundancy.

 

Edited May 19, 2023 by skyzoframe[hun]