[solved] endless loop - I feel stupid

[Nicole]

This is a question I feel very stupid to put it.
I have an endless loop. Since 2 days I simplify it and cannot see, why:

Code: Pascal  [Select][+][-]

1.   ShowMessage('before');
2.  
3.   IBSQLChart.Active:=true; // I tried both IBSQL and IBquery, so before here was ExecQuery with the same effect
4.   while not IBSQLChart.Eof do begin
5.    {  
6. *simplified = set into brackets*
7.               }
8.      IBSQLChart.Next;
9.   end;
10.  
11. ShowMessage('after');

[domasz]

Code: Pascal  [Select][+][-]

1.   ShowMessage('before');;

Ah, Grandma's debugger, classic!

[Joanna from IRC]

I’m not familiar with ibsqlchart but I’m curious what happens if you insert

ibsqlchart.First;  After line 3?

Also is there any way to trace into the code in loop to find out what it is doing?

[rvk]

WHAT is IBSQLChart at the moment??
If it's a TIBQuery or TIBSQL then there is something in your code which you commented out.

Did you try to put that code in comments and see if it's still loops endlessly???

TDataset.First after setting TDataset.Active to true shouldn't make a difference because it's always at the first record after opening. That is... IF the TDataset wasn't already active before that line !!!
And that's a big if because we don't see any other code (as usual).

Just try the code exactly as you put it here in your post and report back.
If it's still happening... show us more code from before this code.

[Nicole]

Code: Text  [Select][+][-]

1. WHAT is IBSQLChart at the moment??
2. If it's a TIBQuery or TIBSQL then there is something in your code which you commented out.

TIBQuery

Code: Text  [Select][+][-]

1. Did you try to put that code in comments and see if it's still loops endlessly???

yes!!

Code: Text  [Select][+][-]

1. TDataset.First after setting TDataset.Active to true shouldn't make a difference because it's always at the first record after opening. That is... IF the TDataset wasn't already active before that line !!!
2. And that's a big if because we don't see any other code (as usual).
3.  
4. Just try the code exactly as you put it here in your post and report back.
5. If it's still happening... show us more code from before this code.

To make sure, I had added the line

Code: Text  [Select][+][-]

1. IBSQLChart.Active:=false;

already.
Is it good style to set the query to active? Or shall I switch something else?

Where the trouble occurs is at the end of looping. I watched the first datasets passing through and watched the "order by" criteria. It runs about to the end.
So to my mind the problem is the
.EOF, which is not triggered.

[rvk]

Where the trouble occurs is at the end of looping. I watched the first datasets passing through and watched the "order by" criteria. It runs about to the end.
So to my mind the problem is the
.EOF, which is not triggered.

If you don't do anything other than .Next inside the loop then the EOF should trigger.
That's why I asked if you commented everything out like you showed.

If it still doesn't work then it must be a bug and you need to report it.
(But I still find it very strange that this can happen.)

Does it happen with all your tables?
BTW. It could also be a corruption on your database/index.
What happens with the same query in Flamerobin and you choose fetch all?

.Active := true should be the same as .Open.
https://www.freepascal.org/docs-html/fcl/db/tdataset.active.html

[Nicole]

yes and yes and yes and it works in Flamrobin,.................. BUT it took 16 seconds for listing 58 data with 3 fields each
So there shall be something wrong with the table.
This is what the statisics of FlameRobin say.


and 27726511 fetches?!
should be about 100

Code: Text  [Select][+][-]

1. Starting transaction...
2. Preparing statement: select datum, betrag, fk_einkommen from tbkupons order by fk_einkommen
3. Statement prepared (elapsed time: 0.001s).
4. Field #01: TBKUPONS.DATUM Alias:DATUM Type:DATE
5. Field #02: TBKUPONS.BETRAG Alias:BETRAG Type:NUMERIC(18,2)
6. Field #03: TBKUPONS.FK_EINKOMMEN Alias:FK_EINKOMMEN Type:INTEGER
7. PLAN (TBKUPONS ORDER FK_TBKUPONS_0)
8. Parameters: 0
9.  
10.  
11. Executing statement...
12. Statement executed (elapsed time: 0.000s).
13. 27726511 fetches, 2 marks, 911 reads, 0 writes.
14. 0 inserts, 0 updates, 0 deletes, 103 index, 25072676 seq.
15. Delta memory: 101168 bytes.
16. RDB$PAGES: 25072675 reads sequence.
17. RDB$DATABASE: 1 reads sequence.
18. RDB$FIELDS: 4 reads index.
19. RDB$INDICES: 6 reads index.
20. RDB$RELATION_FIELDS: 8 reads index.
21. RDB$RELATIONS: 4 reads index.
22. RDB$FORMATS: 1 reads index.
23. RDB$SECURITY_CLASSES: 2 reads index.
24. RDB$TRIGGERS: 1 reads index.
25. TBKUPONS: 77 reads index.
26. Total execution time: 16.781s
27. Script execution finished.
28.  

I tend to delete all data and re-write them including reset the index.
The more I think about it, the more strange things come up to my mind. E.g. that the index was expected to be about 30 and it was 18.000. Perhaps I made a mistake in writing the id's generator. I did not do do this too often in my life.


If anybody guesses better than me what went wrong, I would be grateful, if he shares it.


PS:
I bet, you want to see it:

generator

CREATE SEQUENCE GEN_TBKUPONS_ID START WITH 1 INCREMENT BY 1;

trigger

SET TERM ^ ;
CREATE TRIGGER GEN_TBKUPONS_TR FOR TBKUPONS ACTIVE
BEFORE insert POSITION 0
as
begin
  if (new.id_kupon is null) then
    new.id_kupon = gen_id(GEN_TBKUPONS_ID,1);
end
^
SET TERM ; ^

[rvk]

If anybody guesses better than me what went wrong, I would be grateful, if he shares it.

PS:
I bet, you want to see it:

No, we need to see your SQL and the database DDL (with indexes etc).

[egsuh]

try something like following:

Code: Pascal  [Select][+][-]

1.   IBSQLChart.Active:=true;
2.   ibsqlchart.first;
3.   while not IBSQLChart.Eof do begin
4.      memo1.lines.add (ibsqlchart.fields[0].asstring);
5.    {  
6. *simplified = set into brackets*
7.               }
8.      IBSQLChart.Next;
9.   end;
10.   Memo1.lines.add('Loop finished');

First, simply run this codes. If you see the finial text, then there are no problem with next--- eof.  If the application is down after showing the last record then there should be some problem with next---eof logic. Else, there would be a problem in your codes which have been commented out.

[Nicole]

Thank you for all answers.
Probably the problem never had been an endless loop.
May be the query led to an extreme high traffic in answers, which lasted that long, that it felt like an endless loop.

WHY this answers were that long / verbose / plenty, - no idea.
It was a new table with a handful of records. It should not feel any delay.

However I found the whole database suddenly damaged: a few triggers and constraints were gone.

So I saved the DDL and just dropped the whole table and wrote it new as table and its content.
Now the looping query works without noticeable delay.

To sum up: The problem was the database.
The solution brought the idea, to try the query in FlameRobin, there I discovered the db-problem.

[rvk]

WHY this answers were that long / verbose / plenty, - no idea.
It was a new table with a handful of records. It should not feel any delay.

However I found the whole database suddenly damaged: a few triggers and constraints were gone.

Yeah, and that can definitely lead to slowness of your queries.

So I saved the DDL and just dropped the whole table and wrote it new as table and its content.
Now the looping query works without noticeable delay.

Which will not fix your original problem as to WHY the database got corrupted.

You should really keep an eye on that.
(check backups and S.M.A.R.T. of your harddisk regularly.)

[dseligo]

So I saved the DDL and just dropped the whole table and wrote it new as table and its content.
Now the looping query works without noticeable delay.

Which will not fix your original problem as to WHY the database got corrupted.

I agree. There is no point of correcting data and DDL if root cause is not identified and solved.

[Nicole]

In the meanwhile I suspect, that a tool may have damage something, which cleaned up the virtual disk. It is from VMWare and says "clean up no more needed but reserved space of your virtual HD" or similar.

This would explain, why elements of the database were gone, which I was sure, to have been there.