Jump to content
PenelopeSkye

Query result to dynamic array

Recommended Posts

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;

 

Share this post


Link to post

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 by programmerdelphi2k

Share this post


Link to post

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!!!!!

Share this post


Link to post
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 by programmerdelphi2k

Share this post


Link to post
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.

Share this post


Link to post

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?

Share this post


Link to post
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.

Share this post


Link to post
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;

 

Share this post


Link to post


@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 by programmerdelphi2k

Share this post


Link to post

... 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 by programmerdelphi2k

Share this post


Link to post

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!

Share this post


Link to post

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 by PenelopeSkye

Share this post


Link to post

@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 by programmerdelphi2k

Share this post


Link to post

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

Share this post


Link to post
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.

Share this post


Link to post

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!!!

Share this post


Link to post

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

Share this post


Link to post
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);

Share this post


Link to post
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!

Share this post


Link to post
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 by skyzoframe[hun]

Share this post


Link to post

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×