[resolved]Debugging SQLQuery.EOF loop with a strange SQLQuery.Next code redirect

[indydev]

I will preface this post with the acknowledgement that this problem may not have anything to do with SQLQuery, but I can't track down the problem; hence the post for help.

I am attempting to retrieve data from an SQLite3 database and loading the data into some other List variables for display massaging before the actual display on the form.

For some reason my looping through the messages table causes a jump out of the loop to the beginning of the procedure.

Here is my procedure:

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

1. procedure TForm1.BtnLoadClick(Sender: TObject);
2. var
3.   SelectedIndex, SessionID, i, j: Integer;
4.   QA: TQandA;
5.   Role, Content: string;
6. begin
7.   try
8.     SelectedIndex := Subjects.ItemIndex;
9.  
10.     // Retrieve the session ID from the selected item
11.     SessionID := PtrInt(Subjects.Items.Objects[SelectedIndex]);
12.  
13.     // Query the database for the session details
14.     SQLQuery.Close; // Ensure SQLQuery is closed before setting new SQL text
15.     SQLQuery.SQL.Text := 'SELECT * FROM sessions WHERE id = :id';
16.     SQLQuery.Params.ParamByName('id').AsInteger := SessionID;
17.     SQLQuery.Open;
18.  
19.     if SQLQuery.RecordCount = 0 then
20.     begin
21.       ShowMessage('Session not found.');
22.       SQLQuery.Close;
23.       Exit;
24.     end;
25.  
26.     // Assign the session ID to CurrentSessionID
27.     CurrentSessionID := SessionID;
28.  
29.     // Load session details into variables
30.     temperature := SQLQuery.FieldByName('temperature').AsFloat;
31.     presence_penalty := SQLQuery.FieldByName('presence_penalty').AsFloat;
32.     frequency_penalty := SQLQuery.FieldByName('frequency_penalty').AsFloat;
33.     max_tokens := SQLQuery.FieldByName('max_tokens').AsInteger;
34.     max_Query := SQLQuery.FieldByName('max_tokens_query').AsInteger;
35.     top_p := SQLQuery.FieldByName('top_p').AsFloat;
36.     SYSTEM_MSG := SQLQuery.FieldByName('system_msg').AsString;
37.     image_mode := SQLQuery.FieldByName('is_image_mode').AsBoolean;
38.     assistant_id := SQLQuery.FieldByName('tool_choice').AsString;
39.  
40.     // Load the messages for the session
41.     SQLQuery.Close;
42.     SQLQuery.SQL.Text := 'SELECT * FROM messages WHERE session_id = :session_id ORDER BY id';
43.     SQLQuery.Params.ParamByName('session_id').AsInteger := SessionID;
44.     SQLQuery.Open;
45.  
46.     QA := TQandA.Create;
47.  
48.     try
49.       while not SQLQuery.EOF and (SQLQuery.FieldByName('session_id').AsInteger = SessionID) do
50.       begin
51.         Role := SQLQuery.FieldByName('role').AsString;
52.         Content := SQLQuery.FieldByName('content').AsString;
53.  
54.         if Role = 'user' then
55.           QA.Question.Add(Content)
56.         else if Role = 'assistant' then
57.           QA.Response.Add(Content);
58.  
59.         SQLQuery.Next;   // <-- This statement jumps back to line 8 for some reason.
60.       end;
61.     except
62.       on E: Exception do
63.       begin
64.         ShowMessage('Error processing messages: ' + E.Message);
65.         QA.Free;
66.         Exit;
67.       end;
68.     end;
69.  
70.     // Add the QA object to the Conversation
71.     Conversation.Add(QA);   // Variable of type TConversationList = specialize TFPGObjectList<TQandA>;
72.  
73.     SQLQuery.Close;
74.  
75.   except
76.     on E: Exception do
77.       ShowMessage('Error loading session: ' + E.Message);
78.   end;
79. end;  

The important line is 59 where SQLQuery.Next is called.  I have stepped through the code and when this line executes it jumps to line 8 and I cannot figure out why.

The above procedure (BtnLoadClick) is called after this procedure:

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

1. procedure TForm1.LoadSubjects;
2. var
3.   SessionID: PtrInt;
4. begin
5.   try
6.     SQLQuery.SQL.Text := 'SELECT id, title FROM sessions';
7.     SQLQuery.Open;
8.  
9.     Subjects.Items.Clear;
10.  
11.     if SQLQuery.RecordCount <> 0 then
12.       while not SQLQuery.EOF do
13.       begin
14.         SessionID := SQLQuery.FieldByName('id').AsInteger;
15.         Subjects.Items.AddObject(SQLQuery.FieldByName('title').AsString,
16.                                  TObject(SessionID));
17.         SQLQuery.Next;
18.       end;
19.  
20.     SQLQuery.Close;
21.   except
22.     on E: Exception do
23.       ShowMessage('Error loading sessions: ' + E.Message);
24.   end;
25. end;      

Which executes properly.  There are some variable assignments in between the two calls, but nothing relating to these two procedures.

Debugging tips welcome.

[rvk]

First of all.

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

1. while not SQLQuery.EOF and (SQLQuery.FieldByName('session_id').AsInteger = SessionID) do

The check for session_id isn't needed because you have a SQL where you only ask for records with that session_id. So you shouldn't get any other. That check is useless.

Second, sometimes the debugger can jump back to the beginning of the procedure when it is at the end. I think it has something to do with the cleanup. If you press f8 again (step through) you should jump to the end again. The procedure won't repeat itself (unless the calling functions calls it again).

[indydev]

The check for session_id isn't needed because you have a SQL where you only ask for records with that session_id.

Good catch. I didn't even notice it.

Second, sometimes the debugger can jump back to the beginning of the procedure when it is at the end. I think it has something to do with the cleanup. If you press f8 again (step through) you should jump to the end again. The procedure won't repeat itself (unless the calling functions calls it again).

Unfortunately, that isn't the case here. After the jump back (and the jump occurs on the first execution of line 59), It tries to reload SessionID again on line 11. This then produces a memory related error. In the debugger it raises an exception that eventually gets displayed in LazTracer.pas 

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

1. if (length(Msg) div (length(Msg) div 10000))=0 then ;

The division by 0 error is what led me to step through the code to see if I could figure out what was happening.

Thanks for looking it over. Removing unnecessary code is always a good thing.

[rvk]

Did it jump at the first call to next?

What happens if you comment out that if/then/else block?
Does it iterate through all the records then?

BTW. The double try/except is not needed either. And there should be a try/finally in there.
But that should not be the issue.

[indydev]

If I remove the If else block the error occurs right at the SQL.Next; statement. It is the same message.

I only added the second try except block in the hope I could narrow the error, but stepping is what caught it.

using: Lazarus 2.2.6 (rev lazarus_2_2_6) FPC 3.2.2 x86_64-linux-gtk2

[rvk]

The division by 0 is just an artifact for the benefit of GDB.

What happens if you press continue or run the program outside of the ide (or without debugger)?

It should display the correct exception.

   

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

1.  {------------------------------------------------------------------------------
2.       procedure RaiseGDBException(const Msg: string);
3.    
4.       Raises an exception.
5.       Normally gdb does not catch fpc Exception objects, therefore this procedure
6.       raises a standard "division by zero" exception which is catched by gdb.
7.       This allows one to stop a program, without extra gdb configuration.
8.      ------------------------------------------------------------------------------}
9.     procedure RaiseGDBException(const Msg: string);
10.     begin
11.       DebugLn(lrsERRORInCode, Msg);
12.       // creates an exception, that gdb catches:
13.       DebugLn(lrsCreatingGdbCatchableError);
14.       DumpStack;
15.       {$ifndef HASAMIGA} // On Amiga Division by 0 is not catchable, just crash
16.       if (length(Msg) div (length(Msg) div 10000))=0 then ;
17.       {$endif}
18.     end;

[indydev]

My Exception "Error loading session: " gets triggered (which makes sense). If I push through that I get a range check error in another unit, but that makes sense since nothing was loaded into the List.

If I run it without debugging, I just get my exceptions and the range check error. The program does not crash. I can keep cycling through these errors, The program performs its other operations without error.

[rvk]

My Exception "Error loading session: " gets triggered (which makes sense).

I'm after what's behind that message.
The E.Message.
That's telling you the exact exception.

[indydev]

Putting everything back and running without debugging the exception gives me the "Error loading session: division by zero" message. However, the content does get displayed briefly behind that message, but then disappears. I get second exception "Error loading session: division by zero" message and then a range check error message, and then the program hangs. So the E.Message is "division by zero".

If I leave the loading part out I just get a couple of "Error loading session: division by zero" messages and then I can use the rest of the program (can't load or save content though).

[rvk]

You get the exception part for Error loading session:
So that's the last/outer exception.

So then the exception isn't really the SQLQuery.Next but in the Conversation.Add(QA) part.

What happens if you comment out that line and run without debugger?

[indydev]

I removed the inner exception block.

The problem is line 11 (or at least one problem).

SessionID := PtrInt(Subjects.Items.Objects[SelectedIndex]);

If I change it to a value in the table directly

SessionID := 4;

Then the errors go away.

I tried changing PtrInt to PtrUInt but that did not make a difference.

I changed SessionID to PtrInt type, and that didn't make a difference

Unfortunately I am traveling soon, so I might be able to read a response, but I won't be able to implement anything for approximately 24 hours.

Thank you so much for helping.

[rvk]

The problem is line 11 (or at least one problem).

SessionID := PtrInt(Subjects.Items.Objects[SelectedIndex]);

Ha, yes, that makes more sense.

First (again) a small comment about ItemIndex ( -> SelectedIndex). It can be -1. So you might want to check if there really is something selected.
https://lazarus-ccr.sourceforge.io/docs/lcl/stdctrls/tcustomcombobox.itemindex.html

Then... You cast the Object from Items with PtrInt. PtrInt is a Int64 (I assume you are on 64 bit). But you declared SessionID in BtnLoadClick as an Integer !!

I assume SessionId is something that's always an integer in the database (because you do SessionID := SQLQuery.FieldByName('id').AsInteger ).
Then you shouldn't use PtrInt at all. It's for pointers, not for integers.

Change the PtrInt in your calling LoadSubjects to Integer.

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

1. procedure TForm1.LoadSubjects;
2. var
3.   SessionID: Integer;

Change the line in BtnLoadClick with a cast to Integer and add the check for SelectedIndex.

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

1. if SelectedIndex < 0 then exit;
2. SessionID := Integer(Subjects.Items.Objects[SelectedIndex]);

Always save and restore items from .Objects with the same type (which wasn't done here)

Reference for types: https://saco-evaluator.org.za/docs/fpc/ref/refsu4.html

(If sessionid is something bigger than an Integer, i.e. signed 32 bit, then you might need to change the database type and assignments too)

[cdbc]

Hi
PtrInt & PtrUInt are aliases to the 'pointer-sized-integer' for the platform, signed or unsigned, they are 2=16bit, 4=32bit & 8=64bit bytes in size.
'SizeInt' is another alias... they fit in a register, I think...
Regards Benny

[rvk]

PtrInt & PtrUInt are aliases to the 'pointer-sized-integer' for the platform, signed or unsigned, they are 2=16bit, 4=32bit & 8=64bit bytes in size.
'SizeInt' is another alias... they fit in a register, I think...

Yes, and seeing that SessionID from the database is taken as .AsInteger, you shouldn't stuff that into a PtrInt 
(and later take it out as cast with PtrInt into a Integer type, as is done here)

If you are on 32 bit platform, you might get away with it because then PtrInt is a LongInt (which you can interchange with that Integer).
But an Integer is still 4 bytes on 64 bit, while PtrInt points to a Int64.
Mixing those while CASTING, you'll get in a whole heap of trouble

[cdbc]

Hi
You are absolutely right.
I wasn't arguing that, but there are still people out there, who are not sure about this ptrint business...
Regards Benny