[solved] beginner's question: IBX - "fetch all" lazarus / 16-Databases ---------------------------------------------------------------------- Nicole · 2024-03-08 12:57 I have a range check error. Not sure, if I can solve it this way, but I would have tried something with "Fetch all" in Firedac. Firedac is uninstalled long ago and IBX replaced it, what is a great thing. now: How to solve it in IBX? This is behind. Nobody knows, how many data there will be found. My problem-query is about 200 records. These record shall be written into an array. Here is my first line, which may be wise or not wise: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i  i:=IBQuery_Kontrakt_1.RecordCount; followed by Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i  SetLength(Kurse1,i); Then my query starts reading data. The range check error is at the 10th record. I want it to "fetch all", but cannot find this in the object instpector. What I found is: BufferChunc = 1000 BufferFirstBlock = 50. Both values are different from 10?! Pls give me a hint, how to solve the task: "Find out, how many result-rows there will be in the query". Or do you think, it is the best way just to send a select count before? ---------------------------------------------------------------------- rvk · 2024-03-08 13:24 You can solve this a couple of ways. If you really want to do one SetLength with RecordCount you can do Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i TIBQuery.Last;TIBQuery.Last;// your TIBQuery.RecordCount should now be accurate for use with SetLength Another option is to do Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i SetLength(Arr, 0);while not TIBQuery.Eof do  SetLengt(Arr, Length(Arr) + 1);  // set Arr[Length(Arr) - 1]  TIBQuery.Next;end;The downside of this method is that you do a lot of SetLength and you could get memory fragmentation and a lot of moving of memory to accommodate the new array length. Last option could be to keep a counter and only increase by 100 or 200 each time you hit the limit Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i Cnt := 0;SetLength(Arr, Cnt);while not TIBQuery.Eof do  if Cnt + 1 > Length(Arr) then SetLength(Arr, Length(Arr) + 100);  // set Arr[Cnt]  Inc(Cnt);  TIBQuery.Next;end; Something along those lines... ---------------------------------------------------------------------- Zvoni · 2024-03-08 13:28 No idea about IBX, but the standard sqldb-components have PacketRecords of 10 as default. At a guess: Nicole thinks, she's fetching all records, but the Component actually fetches only the first 10, which is reflected in the RecordCount ---------------------------------------------------------------------- Nicole · 2024-03-08 13:33 There exists a ".last"    Thank you so much, I was not aware of this. This IS useful and I will need it often. Zvoni, thank you for the explanation. Now I know, why the bang comes at 10. ---------------------------------------------------------------------- rvk · 2024-03-08 13:41 Quote from: rvk on March 08, 2024, 02:24:16 pmIf you really want to do one SetLength with RecordCount you can do Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i TIBQuery.Last;TIBQuery.Last;// your TIBQuery.RecordCount should now be accurate for use with SetLengthOops. That second last should have been first so that you begin at the beginning again. Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i TIBQuery.Last;TIBQuery.First;// your TIBQuery.RecordCount should now be accurate for use with SetLength For a few hundred to a few thousand records this is no problem. If your dataset goes into the millions then it might be better to do a count(*) via SQL ---------------------------------------------------------------------- Zvoni · 2024-03-08 13:42 Quote from: Nicole on March 08, 2024, 02:33:41 pmThere exists a ".last"    Thank you so much, I was not aware of this. This IS useful and I will need it often. Zvoni, thank you for the explanation. Now I know, why the bang comes at 10. You do know, that you can change those "10" (DefaultValue) for PacketRecords? QuotePls give me a hint, how to solve the task: "Find out, how many result-rows there will be in the query". Well.... Code: SQL  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i SELECT COUNT(*) AS RC FROM FromHereAsInYourOtherQueryAndDontForgetTheJoinsWhe reGroupByClausesAndWhateverYouHaveInThere... That said: I'd probably not use "Last/First", but SetLength of the destination Array to some Arbitrary (Chunk)-Length, say 1000. Then loop through the resultset using "Next" within a While-Loop until resultSet.EOF is True, only checking that current recNo is smaller or equal than Array-Length. If RecNo is greater than Array-Length, add another Chunk to the array, and continue processing. After the Loop exits, i can adjust the array back with the then real RecordCount Basically, what rvk wrote in his answer as the third option, just not with a counter but with RecNo, provided RecNo is reliable. I seem to remember reading something it might not be reliable (for whatever reason?) ---------------------------------------------------------------------- Nicole · 2024-03-08 13:58 by the way, what do you think of this loop below? Is it good style? To me it looks weired, but I am not sure about an alternative. The idea is, that every 2 records consist of a pair which has the same date = "datum". This very loop shall write two sister-arrrays, where every day at the same "i" holds the same date: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i  i:= -1; While not IBQuery_Kontrakt_1.EOF do begin   fk:=IBQuery_Kontrakt_1.FieldByName('fk').AsInteger;   i:= i + 1;  // dabei bleibt jedes zweite Feld im array frei!   if fk = fk_id1 then begin     Kurse1[i].Datum:=IBQuery_Kontrakt_1.FieldByName('Datum').AsDateTime;     Kurse1[i].close:=IBQuery_Kontrakt_1.FieldByName('c').AsFloat;   end;   if fk = fk_id2 then begin      Kurse2[i].Datum:=IBQuery_Kontrakt_1.FieldByName('Datum').AsDateTime;    Kurse2[i].close:=IBQuery_Kontrakt_1.FieldByName('c').AsFloat;      i:= i - 1; // wenn der 2. Kontrakt geschrieben ist, muss das i wieder zurückgesetzt werden, weil es JE kontrakt erhöht.   end;   IBQuery_Kontrakt_1.Next;end; (initialisation -1 each) ---------------------------------------------------------------------- Nicole · 2024-03-08 14:11 @rvk, thank you for your answer. luckily no millions. The text - complete function found the meanwhile an old friend: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i  IBQuery_Kontrakt_1.FetchAll; Is there a reason to prefer last or fetch all? ---------------------------------------------------------------------- rvk · 2024-03-08 14:19 Quote from: Nicole on March 08, 2024, 02:58:20 pmby the way, what do you think of this loop below? Is it good style? To me it looks weired, but I am not sure about an alternative. The idea is, that every 2 records consist of a pair which has the same date = "datum". This very loop shall write two sister-arrrays, where every day at the same "i" holds the same date: Yeah, that loop kinda looks funny to me too Especially the i := i - 1; I would create a help date and only change the i when the date changes. Like this: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i   i := -1;  prev := 0;  while not IBQuery_Kontrakt_1.EOF do  begin    fk := IBQuery_Kontrakt_1.FieldByName('fk').AsInteger;     if prev <> IBQuery_Kontrakt_1.FieldByName('Datum').AsDateTime then    begin      inc(i);    end;     if fk = fk_id1 then    begin      Kurse1[i].Datum := IBQuery_Kontrakt_1.FieldByName('Datum').AsDateTime;      Kurse1[i].Close := IBQuery_Kontrakt_1.FieldByName('c').AsFloat;    end;    if fk = fk_id2 then    begin      Kurse2[i].Datum := IBQuery_Kontrakt_1.FieldByName('Datum').AsDateTime;      Kurse2[i].Close := IBQuery_Kontrakt_1.FieldByName('c').AsFloat;    end;     IBQuery_Kontrakt_1.Next;   end; But how do you set the length of Kurse1 and Kurse2 now? Because RecordCount now consists of both fk_id1 and fk_id2 values. In this case you probably want to expand both arrays when they reach the limit. And you can do that when you do inc(i); Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i     if prev <> IBQuery_Kontrakt_1.FieldByName('Datum').AsDateTime then    begin    inc(i);      if i > length(Kurse1) then      begin        SetLength(Kurse1, Length(Kurse1) + 100);        SetLength(Kurse2, Length(Kurse2) + 100);      end;    end; Another option would be to adjust your SQL and make a separate column for both Kurse1 and Kurse2 and return both in one record. You could do this with iif and putting both in their own column in a join or with. It also depends on what you want to do with the array and if and why the I need to be at the same place for all datum. ---------------------------------------------------------------------- Nicole · 2024-03-08 14:35 thank you! Without "i - 1" it looks much better. I will change this. How I get the length? Like this. The query looks for pairs, so we know, the record number must be even. I write: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i  IBQuery_Ko ntrakt_1.FetchAll; i:=IBQuery_Kontrakt_1.RecordCount; i:=trunc (i / 2); // das ich Datumspaare abfrage, muss die Zahl immer gerade sein SetLength(Kurse1,i); ---------------------------------------------------------------------- rvk · 2024-03-08 14:46 Quote from: Nicole on March 08, 2024, 03:35:58 pmHow I get the length? Like this. The query looks for pairs, so we know, the record number must be even. I write: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i  i:=trunc (i / 2); // das ich Datumspaare abfrage, muss die Zahl immer gerade seinOk. Yes, that would be possible. About the query itself. If you mentioned a bit earlier that you wanted to fill just two arrays we could have spared us a lot of trouble.. Doesn't this SQL fit the bill? https://dbfiddle.uk/JrDXPjr4 Or am I missing something??? And now you just have the records besides each other per DATUM. Code: SQL  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i SELECT   A.DATUM AS A_DATUM, A.ID_KURSZEILE AS A_ID, A.FK_KONTRAKT AS A_FK,   X.DATUM AS B_DATUM, X.ID_KURSZEILE AS B_ID, X.FK_KONTRAKT AS B_FKFROM TBKURSZEILEN A LEFT JOIN TBKURSZEILEN X ON A.DATUM=X.DATUMWHERE A.FK_KONTRAKT='5' AND X.FK_KONTRAKT='6'ORDER BY A.DATUM, A.ID_KURSZEILE Wouldn't that be much easier? QuoteA_DATUM   A_ID   A_FK   B_DATUM   B_ID   B_FK 2024-05-01   5   5   2024-05-01   12   6 2024-06-01   7   5   2024-06-01   13   6 2024-07-01   8   5   2024-07-01   14   6 2024-08-01   9   5   2024-08-01   15   6 ---------------------------------------------------------------------- Nicole · 2024-03-08 15:50 Indeed! Thank you. This works! This is the third solution for a problem I was not able to solve. I will take time to compare the solutions and give all the helpful people the results of performance in the next days. ---------------------------------------------------------------------- Zvoni · 2024-03-08 19:39 Quote from: rvk on March 08, 2024, 03:46:24 pmQuote from: Nicole on March 08, 2024, 03:35:58 pmHow I get the length? Like this. The query looks for pairs, so we know, the record number must be even. I write: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i  i:=trunc (i / 2); // das ich Datumspaare abfrage, muss die Zahl immer gerade seinOk. Yes, that would be possible. About the query itself. If you mentioned a bit earlier that you wanted to fill just two arrays we could have spared us a lot of trouble.. Doesn't this SQL fit the bill? https://dbfiddle.uk/JrDXPjr4 Or am I missing something??? And now you just have the records besides each other per DATUM. Code: SQL  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i SELECT   A.DATUM AS A_DATUM, A.ID_KURSZEILE AS A_ID, A.FK_KONTRAKT AS A_FK,   X.DATUM AS B_DATUM, X.ID_KURSZEILE AS B_ID, X.FK_KONTRAKT AS B_FKFROM TBKURSZEILEN A LEFT JOIN TBKURSZEILEN X ON A.DATUM=X.DATUMWHERE A.FK_KONTRAKT='5' AND X.FK_KONTRAKT='6'ORDER BY A.DATUM, A.ID_KURSZEILE Wouldn't that be much easier? QuoteA_DATUM   A_ID   A_FK   B_DATUM   B_ID   B_FK 2024-05-01   5   5   2024-05-01   12   6 2024-06-01   7   5   2024-06-01   13   6 2024-07-01   8   5   2024-07-01   14   6 2024-08-01   9   5   2024-08-01   15   6 Not good. You’re assuming to every FK=6 there exists a date for FK=5 (and probably vice versa). Like FK=6 has dates that don’t appear in FK=5, and vice versa I‘d go for a full outer join with the dates being the connector. That way you catch them all ---------------------------------------------------------------------- rvk · 2024-03-08 19:45 Quote from: Zvoni on March 08, 2024, 08:39:00 pmNot good. You’re assuming to every FK=6 there exists a date for FK=5 (and probably vice versa). Like FK=6 has dates that don’t appear in FK=5, and vice versa That shouldn't happen. It was one of the rules that a date has both 5 and 6. If there is only 5 or 6 the line shouldn't be there. Although that was discussed in another topic. That's why it is sometimes not convenient to split topics. So Trunc(i /2) would do just fine. And my join which returns only records where there is 5 AND 6 is also correct. ---------------------------------------------------------------------- Zvoni · 2024-03-08 19:48 Quote from: rvk on March 08, 2024, 08:45:55 pmQuote from: Zvoni on March 08, 2024, 08:39:00 pmNot good. You’re assuming to every FK=6 there exists a date for FK=5 (and probably vice versa). Like FK=6 has dates that don’t appear in FK=5, and vice versa That shouldn't happen. It was one of the rules that a date has both 5 and 6. If there is only 5 or 6 the line shouldn't be there. So Trunc(i /2) would do just fine. I div 2 too. I understand you, don’t get me wrong. But with a full outer join you definitely get them all, and would immediately see, if there is a gap (for whatever reason, holiday in one country not a holiday in another country, etc.) ---------------------------------------------------------------------- rvk · 2024-03-08 19:52 Quote from: Zvoni on March 08, 2024, 08:48:32 pmI understand you, don’t get me wrong. But with a full outer join you definitely get them all, and would immediately see, if there is a gap (for whatever reason, holiday in one country not a holiday in another country, etc.) I agree with you. But this was the question. Quote from: Nicole on March 07, 2024, 04:01:04 pmIn other words: There shall be counted in Count nothing but the datum itself, not the whole record. What I want to do: Find in my queries pairs of datum. And skip what has not a pair of datum. So there is always a pair otherwise the record should be discarded. If the use-case is different then the question was wrong. Yeah... Split topics are the worst