[resolved]Debugging SQLQuery.EOF loop with a strange SQLQuery.Next
code redirect
lazarus / 16-Databases

----------------------------------------------------------------------
indydev · 2024-06-12 17:16

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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TForm1.BtnLoadClick(Sender: TObject);var  SelectedIndex, SessionID, i,
j: Integer;  QA: TQandA;  Role, Content: string;begin  try   
SelectedIndex := Subjects.ItemIndex;     // Retrieve the session ID
from the selected item    SessionID :=
PtrInt(Subjects.Items.Objects[SelectedIndex]);     // Query the
database for the session details    SQLQuery.Close; // Ensure SQLQuery
is closed before setting new SQL text    SQLQuery.SQL.Text := 'SELECT
* FROM sessions WHERE id = :id';   
SQLQuery.Params.ParamByName('id').AsInteger := SessionID;   
SQLQuery.Open;     if SQLQuery.RecordCount = 0 then    begin     
ShowMessage('Session not found.');      SQLQuery.Close;      Exit;   
end;     // Assign the session ID to CurrentSessionID   
CurrentSessionID := SessionID;     // Load session details into
variables    temperature :=
SQLQuery.FieldByName('temperature').AsFloat;    presence_penalty :=
SQLQuery.FieldByName('presence_penalty').AsFloat;    frequency_penalty
:= SQLQuery.FieldByName('frequency_penalty').AsFloat;    max_tokens :=
SQLQuery.FieldByName('max_tokens').AsInteger;    max_Query :=
SQLQuery.FieldByName('max_tokens_query').AsInteger;    top_p :=
SQLQuery.FieldByName('top_p').AsFloat;    SYSTEM_MSG :=
SQLQuery.FieldByName('system_msg').AsString;    image_mode :=
SQLQuery.FieldByName('is_image_mode').AsBoolean;    assistant_id :=
SQLQuery.FieldByName('tool_choice').AsString;      // Load the
messages for the session    SQLQuery.Close;    SQLQuery.SQL.Text :=
'SELECT * FROM messages WHERE session_id = :session_id ORDER BY id'; 
 SQLQuery.Params.ParamByName('session_id').AsInteger := SessionID;   
SQLQuery.Open;     QA := TQandA.Create;     try      while not
SQLQuery.EOF and (SQLQuery.FieldByName('session_id').AsInteger =
SessionID) do      begin        Role :=
SQLQuery.FieldByName('role').AsString;        Content :=
SQLQuery.FieldByName('content').AsString;         if Role = 'user'
then          QA.Question.Add(Content)        else if Role =
'assistant' then          QA.Response.Add(Content);        
SQLQuery.Next;   // <-- This statement jumps back to line 8 for some
reason.      end;    except      on E: Exception do      begin       
ShowMessage('Error processing messages: ' + E.Message);       
QA.Free;        Exit;      end;    end;     // Add the QA object to
the Conversation    Conversation.Add(QA);   // Variable of type
TConversationList = specialize TFPGObjectList<TQandA>;    
SQLQuery.Close;   except    on E: Exception do      ShowMessage('Error
loading session: ' + E.Message);  end;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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TForm1.LoadSubjects;var  SessionID: PtrInt;begin  try   
SQLQuery.SQL.Text := 'SELECT id, title FROM sessions';   
SQLQuery.Open;     Subjects.Items.Clear;     if SQLQuery.RecordCount
<> 0 then      while not SQLQuery.EOF do      begin        SessionID
:= SQLQuery.FieldByName('id').AsInteger;       
Subjects.Items.AddObject(SQLQuery.FieldByName('title').AsString,     
                          TObject(SessionID));        SQLQuery.Next; 
   end;     SQLQuery.Close;  except    on E: Exception do     
ShowMessage('Error loading sessions: ' + E.Message);  end;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 · 2024-06-12 18:19

First of all.

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 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 · 2024-06-12 19:51

Quote from: rvk on June 12, 2024, 08:19:08 pmThe 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.

QuoteSecond, 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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i 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 · 2024-06-12 19:54

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 · 2024-06-12 21:16

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 · 2024-06-12 21:24

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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i  {---------
--------------------------------------------------------------------- 
    procedure RaiseGDBException(const Msg: string);          Raises an
exception.      Normally gdb does not catch fpc Exception objects,
therefore this procedure      raises a standard "division by zero"
exception which is catched by gdb.      This allows one to stop a
program, without extra gdb configuration.     ------------------------
------------------------------------------------------}    procedure
RaiseGDBException(const Msg: string);    begin     
DebugLn(lrsERRORInCode, Msg);      // creates an exception, that gdb
catches:      DebugLn(lrsCreatingGdbCatchableError);      DumpStack; 
   {$ifndef HASAMIGA} // On Amiga Division by 0 is not catchable, just
crash      if (length(Msg) div (length(Msg) div 10000))=0 then ;     
{$endif}    end;

----------------------------------------------------------------------
indydev · 2024-06-12 21:43

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 · 2024-06-12 21:46

Quote from: indydev on June 12, 2024, 11:43:50 pmMy 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 · 2024-06-12 22:21

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 · 2024-06-13 02:42

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 · 2024-06-13 03:39

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 · 2024-06-13 07:47

Quote from: indydev on June 13, 2024, 05:39:37 amThe 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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TForm1.LoadSubjects;var  SessionID: Integer;Change the line in
BtnLoadClick with a cast to Integer and add the check for
SelectedIndex.
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
SelectedIndex < 0 then exit;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 · 2024-06-13 09:24

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 · 2024-06-13 09:34

Quote from: cdbc on June 13, 2024, 11:24:44 amPtrInt & 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 · 2024-06-13 10:18

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

----------------------------------------------------------------------
indydev · 2024-06-14 23:04

I finally got a chance to look at this more.

By removing the TDBListBox and replacing it with a TListBox. The
problem disappears! No other changes.

I will note that the TDBListBox does not have a published
OnSelectionChange event, so I had to assign it in the FormCreate
method when using TDBListBox.  With TListBox I just assigned it in the
Events panel. That is the only difference.

I don't know why. I appreciate your help with this nonetheless.

----------------------------------------------------------------------
indydev · 2024-06-15 01:01

After reading an article about casting pointers as TObject not being a
good idea, I have created a class with an integer property.
`TSessionData.Index` where index is an integer.

I have to create and destroy the object on my own, but this appears to
be the better way.