Last id via RETURNING with TSQLQuery.InsertSQL and TDBGrid
lazarus / 16-Databases

----------------------------------------------------------------------
quantumfoam · 2022-02-23 17:47

I want to implement editable TDBGrid but I can not get id of new row.
Here is example. With new application I drop
2x TButton, TDataSource, TDBGrid, TSQLConnector, TSQLQuery,
TSQLTransaction. Button1 creates new db. Button2 opens it. When I
enter new value of abc column into grid and press arrow down, TDBGrid
performs Post, which is processed in SQLQuery1AfterPost. I see 2
problems.

1. Commit in SQLQuery1AfterPost closes Query1, so it needs to be
reopened. Why? How to prevent it?
2. I can not find newly created id. How to do it? It could be probably
done by putting insert command from TSQLQuery.InsertSQL to
TSQLQuery.SQL and opening. But TDBGrid just uses TSQLQuery.InsertSQL.

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 unit
Unit1; {$mode objfpc}{$H+} interface uses  Classes, SysUtils, SQLDB,
SQLite3Conn, Forms, Controls, Graphics, Dialogs,  StdCtrls, DBGrids,
DB; type   { TForm1 }   TForm1 = class(TForm)    Button1: TButton;   
Button2: TButton;    DataSource1: TDataSource;    DBGrid1: TDBGrid;  
SQLConnector1: TSQLConnector;    SQLQuery1: TSQLQuery;   
SQLTransaction1: TSQLTransaction;    procedure Button1Click(Sender:
TObject);    procedure Button2Click(Sender: TObject);    procedure
FormCreate(Sender: TObject);    procedure SQLQuery1AfterPost(DataSet:
TDataSet);  private   public   end; var  Form1:
TForm1; implementation {$R *.lfm} { TForm1 } procedure
TForm1.Button1Click(Sender: TObject);  // create new dbbegin  //
cleanup from previous run and create database 
SQLConnector1.Connected:=false;  DeleteFile('abc'); 
SQLConnector1.Connected:=true;  SQLConnector1.ExecuteDirect( 'CREATE
TABLE abc ( id integer not null primary key autoincrement, abc
varchar(20) ) ' );  SQLTransaction1.Commit; 
SQLConnector1.Connected:=false;end; procedure
TForm1.Button2Click(Sender: TObject); // open existing dbbegin 
SQLQuery1.Close;  if not SQLConnector1.connected then
SQLConnector1.Connected:=true;  SQLQuery1.Open;end; procedure
TForm1.FormCreate(Sender: TObject);begin  // setup 
SQLTransaction1.DataBase:=SQLConnector1; 
SQLConnector1.ConnectorType:='SQLite3'; // sqlite3.dll and others
needed  SQLConnector1.DatabaseName:='abc'; 
SQLConnector1.Transaction:=SQLTransaction1; 
SQLQuery1.DataBase:=SQLConnector1; 
SQLQuery1.Transaction:=SQLTransaction1;  SQLQuery1.SQL.text:='select
id, abc from abc';  SQLQuery1.InsertSQL.text:= 'insert into abc (abc)
values (:abc) returning id'; 
SQLQuery1.AfterPost:=@SQLQuery1AfterPost; 
DataSource1.DataSet:=SQLQuery1; 
DBGrid1.DataSource:=DataSource1;end; procedure
TForm1.SQLQuery1AfterPost(DataSet: TDataSet);begin 
SQLQuery1.ApplyUpdates; 
showmessage(DataSet.FieldByName('id').asstring); // how to get
returning id here?  SQLTransaction1.Commit; // why this commit closes
SQLQuery1?  SQLQuery1.open;  // with returning id, I can locate new
record hereend; end.

----------------------------------------------------------------------
balazsszekely · 2022-02-23 18:36

QuoteHow to get returning id here?Your code looks OK to me(not
tested), except procedure SQLQuery1AfterPost is not called.
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   //... 
SQLQuery1.AfterPost:=@SQLQuery1AfterPost; 
DataSource1.DataSet:=SQLQuery1;  DBGrid1.DataSource:=DataSource1; 
SQLQuery1.Post; //add this lineend;
QuoteWhy this commit closes SQLQuery1?Use CommitRetaining instead of
Commit.

----------------------------------------------------------------------
quantumfoam · 2022-02-23 18:59

QuoteYour code looks OK to me(not tested), except procedure
SQLQuery1AfterPost is not called.
I put setup in formCreate so the property values are clear. But still,
you need to click button's onclick and form's onCreate to link pas
with form events.
When  formCreate runs it assigns afterPost, so it should run.

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
SQLQuery1.AfterPost:=@SQLQuery1AfterPost; 
Adding Post to form create is useless, since I need post every time
user somehow saves new row (e.g. by arrow down on edited row). But
this is already handled internally by TDbGrid.
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  
SQLQuery1.Post; //add this line 
I just need to address Post performed by TDbGrid and get new id.
------

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  
SQLTransaction1.CommitRetaining; 
causes error:
QuoteEDatabaseError: Cannot commit transactions - SQL Statements in
progress

----------------------------------------------------------------------
Sieben · 2022-02-23 19:19

While using SQLite you can use TSQLiteConnection instead of
TSQLConnector and always get the last inserted ID with

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 NewID :=
SQLiteConnection1.GetInsertID;
As for prevent closing of the DataSets on Commit include
sqoKeepOpenOnCommit in TSQLQuery.Options.

However, for SQLIte and the standard settings of the SQLdb components
you don't really need a Commit, ApplyUpdates actually writes to the
database on implicit transactions, cf here 2.3.

----------------------------------------------------------------------
balazsszekely · 2022-02-23 19:32

@quantumfoam
QuoteAdding Post to form create is useless, since I need post every
time user somehow saves new rowMy bad. Post on form create indeed
makes no sense. I thought it was a button click or something.

----------------------------------------------------------------------
quantumfoam · 2022-02-23 19:38

Quote from: Sieben on February 23, 2022, 08:19:06 pmWhile using SQLite
you can use TSQLiteConnection instead of TSQLConnector and always get
the last inserted ID with

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 NewID :=
SQLiteConnection1.GetInsertID;
As for prevent closing of the DataSets closing on Commit include
sqoKeepOpenOnCommit in TSQLQuery.Options.

However, for SQLIte and the standard settings of the SQLdb components
you don't really need a Commit, ApplyUpdates actually writes to the
database on implicit transactions, cf here 2.3.

Thanks Sieben and GetMem.  SqoKeepOpenOnCommit actually solves my
problem, since no wild cursor movement happens on query reopen.
Still, some TSQLQuery "ResultFields"  would be handy, along with
TSQLQuery.Fields, TSQLQuery.FieldByName etc.