Recent

Author Topic: SQLdb ParseSQL  (Read 2419 times)

valdir.marcos

  • Hero Member
  • *****
  • Posts: 1008
SQLdb ParseSQL
« on: September 04, 2018, 03:02:28 pm »
How can I see the automatically generated commands for SQLQuery1.InsertSQL, SQLQuery1.UpdateSQL and SQLQuery1.DeleteSQL after setting SQLQuery1.ParseSQL?
I have attached a very small sample project.

Code: Pascal  [Select][+][-]
  1. procedure TForm1.Button1Click(Sender: TObject);
  2. begin
  3.   SQLQuery1.Close;
  4.   SQLQuery1.SQL.Text := 'Select ID, Name, Age From People ' + LineEnding +
  5.                         'Order By ID;';
  6.   SQLQuery1.ParseSQL := True;
  7.   SQLQuery1.Open;
  8.  
  9.   Memo1.Text := SQLQuery1.InsertSQL.Text;
  10.   Memo2.Text := SQLQuery1.UpdateSQL.Text;
  11.   Memo3.Text := SQLQuery1.DeleteSQL.Text;
  12.  
  13.   Memo4.Clear;
  14.   SQLQuery1.First;
  15.   while not SQLQuery1.EOF do
  16.   begin
  17.     Memo4.Lines.Add(SQLQuery1.FieldByName('ID'). AsString + ', ' +
  18.                     SQLQuery1.FieldByName('Name'). AsString + ', ' +
  19.                     SQLQuery1.FieldByName('Age'). AsString + '.');
  20.     SQLQuery1.Next;
  21.   end;
  22.   SQLQuery1.Close;
  23.   SQLTransaction1.Commit;
  24. end;

mangakissa

  • Hero Member
  • *****
  • Posts: 1101
Re: SQLdb ParseSQL
« Reply #1 on: September 04, 2018, 05:08:50 pm »
Isn't there a Firebird monitoring component is SQLdb tab?
Lazarus 2.06 (64b) / FPC 3.0.4 / Windows 10
stucked on Delphi 10.3.1

valdir.marcos

  • Hero Member
  • *****
  • Posts: 1008
Re: SQLdb ParseSQL
« Reply #2 on: September 04, 2018, 06:29:58 pm »
Isn't there a Firebird monitoring component is SQLdb tab?
Yes, there is and I can see that information through monitoring tables on Firebird.
The question here is if the component generates and uses that information, why can't I see it?
« Last Edit: September 05, 2018, 02:53:32 pm by valdir.marcos »

rvk

  • Hero Member
  • *****
  • Posts: 4407
Re: SQLdb ParseSQL
« Reply #3 on: September 04, 2018, 11:02:23 pm »
As far as I can see from the sources is that those updatesql statements are generated, used and freed only internally. There is no access to the statements themselves. You can use a monitor component to see them but only at the moment you issue an insert, update or delete on the table. The internal statements are not created during parsesql but dyring the actual .insert/.edit/.delete.

What do you need it for? You could generate those statements yourself (you can even look at the sources how it's done).

valdir.marcos

  • Hero Member
  • *****
  • Posts: 1008
Re: SQLdb ParseSQL
« Reply #4 on: September 05, 2018, 12:49:03 pm »
What do you need it for?
I don't need the commands per se, I am just investiganting a bigger problem.

I am trying to understand why those commands work correctly on an isolated test application, but on an actual application sometimes the same form leads to the error message below:
--------------
Project xxx raised exception class 'EDatabaseError' with message:
IBConnection1 : No update query specified and failed to generate one. (No fields for inclusion in where statement found)
--------------

rvk

  • Hero Member
  • *****
  • Posts: 4407
Re: SQLdb ParseSQL
« Reply #5 on: September 05, 2018, 01:14:18 pm »
What do you need it for?
I don't need the commands per se, I am just investiganting a bigger problem.

I am trying to understand why those commands work correctly on an isolated test application, but on an actual application sometimes the same form leads to the error
That's probably because you are using a complex SQL with JOINs. In that case FPC can't generate an updateSQL and you'll need to provide one manually.

Or you have a SQL where FPC can't determine what fields should be used for updating a record.

valdir.marcos

  • Hero Member
  • *****
  • Posts: 1008
Re: SQLdb ParseSQL
« Reply #6 on: September 05, 2018, 02:13:36 pm »
What do you need it for?
I don't need the commands per se, I am just investiganting a bigger problem.

I am trying to understand why those commands work correctly on an isolated test application, but on an actual application sometimes the same form leads to the error
That's probably because you are using a complex SQL with JOINs. In that case FPC can't generate an updateSQL and you'll need to provide one manually.

Or you have a SQL where FPC can't determine what fields should be used for updating a record.
It's not that obvious.
I am talking about simple tables with less than 10 fields and only one field for primary key and no JOINs. And the worse part is that it happens randomly.
I'll keep investigating.

rvk

  • Hero Member
  • *****
  • Posts: 4407
Re: SQLdb ParseSQL
« Reply #7 on: September 05, 2018, 02:18:48 pm »
I am talking about simple tables with less than 10 fields and only one field for primary key and no JOINs. And the worse part is that it happens randomly.
I'll keep investigating.
If it's happening on a simple SELECT * FROM TABLE then you might want to add a primary key to the table and set UpdateMode to upWhereKeyOnly. If it's set to upWhereAll or upWhereChanged, FPC might not be able to create a correct updatesql. With upWhereKeyOnly and the primary key part of the select it should always work fine.

valdir.marcos

  • Hero Member
  • *****
  • Posts: 1008
Re: SQLdb ParseSQL
« Reply #8 on: September 05, 2018, 02:26:36 pm »
With upWhereKeyOnly and the primary key part of the select it should always work fine.
This is the problem: it should work, but randomly it does not.
So, I am setting everything manually as a workaround.

 

TinyPortal © 2005-2018