How to get last inserted id after insert
lazarus / 16-Databases

----------------------------------------------------------------------
Gebo · 2025-12-28 11:34

I insert invoice data in sale_invoice table, then I need the id value
to insert the sold product to sold_items with invoiceid.
How to get last inserted id after insert the invoice data?

I use zeosdb and mariadb.

----------------------------------------------------------------------
egsuh · 2025-12-28 13:08

I would define afterinsert event handler.

----------------------------------------------------------------------
dseligo · 2025-12-28 13:42

Like this:
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 With ZQuery
dobegin  SQL.Text := 'insert into sale_invoice (field1, field2, ...)
values ' +    '(:field1, :field2, ...)'; 
ParamByName('field1').AsInteger := 12323; 
ParamByName('field2').AsString := '12323';  ...  ExecSQL;  
SQL.Text:='select LAST_INSERT_ID()';  Open;  iLastID :=
Fields[0].AsInteger;  ...end;
P.S.: And if you insert from another table, you can also do it like
this, without variable:
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 SQL.Text :=
'insert into sold_items ' +  '(itemid, field1, field2, ...) ' + 
'select last_insert_id(), field1, field2, ... ' +  'from
someothertable ' +  'where ...';ParamByName('...').AsString :=
'abc';ExecSQL;

----------------------------------------------------------------------
olatov · 2025-12-28 14:02

Another option is INSERT...RETURNING

https://mariadb.com/docs/server/reference/sql-statements/data-
manipulation/inserting-loading-data/insertreturning

----------------------------------------------------------------------
Gebo · 2025-12-28 16:28

I tried
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 ascal
 [Select][+]     With ZQuery do    begin      SQL.Text := 'insert into
sale_invoice (field1, field2, ...) values ' +        '(:field1,
:field2, ...)';      ParamByName('field1').AsInteger := 12323;     
ParamByName('field2').AsString := '12323';      ...      ExecSQL;   
     SQL.Text:='select LAST_INSERT_ID()';      Open;      iLastID :=
Fields[0].AsInteger;      ...    end; but It returns 0.

----------------------------------------------------------------------
jcmontherock · 2025-12-28 16:48

Maybe a "commit" after insert was missing.

----------------------------------------------------------------------
dseligo · 2025-12-28 17:41

Quote from: jcmontherock on December 28, 2025, 05:48:56 pmMaybe a
"commit" after insert was missing.

Either that or his table doesn't have AUTO_INCREMENT column.

----------------------------------------------------------------------
jcmontherock · 2025-12-29 17:14

Did you check if your table was updated after insert ?

----------------------------------------------------------------------
egsuh · 2025-12-30 08:30

Commit update shouldn’t matter as within the same transaction.

----------------------------------------------------------------------
swissbob · 2025-12-30 16:50

Quote from: Gebo on December 28, 2025, 12:34:06 pmI insert invoice
data in sale_invoice table, then I need the id value to insert the
sold product to sold_items with invoiceid.
How to get last inserted id after insert the invoice data?

I use zeosdb and mariadb.

Well in a properly designed database, the auto inc field should be a
candidate key used for fast loading of records, so you should use that
actual primary key to return it.

Failing that would be best to use an insert/return statement...

And the least preferred option is to query the last used id.  But in
environments with a high degree of concurrency managed by
professionals,  that may occasionally fail I have seen it in banking
and the travel industry.  But it is probably good enough in most
cases.

----------------------------------------------------------------------
Gebo · 2025-12-31 10:03

Quote from: jcmontherock on December 29, 2025, 06:14:37 pmDid you
check if your table was updated after insert ?
yes the invoice is saved in the table.
I can't make the code in one transaction and I don't know why.

my code is
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
ZConnection3.Connect;  ZConnection3.StartTransaction;  //إدخال بيانات
الفاتورة وجلب ID الفاتورة  ZQuery3.Close;  ZQuery3.SQL.Clear; 
ZQuery3.SQL.Text:='insert into sales_invoices
(invoice_date,customer_id,total)values(:indate,:custid, :total)'; 
ZQuery3.ParamByName('custid').AsInteger:=
VarAsType(DBLookupComboBox1.KeyValue, varInteger); 
ZQuery3.ParamByName('total').AsFloat:=total; 
ZQuery3.ParamByName('indate').AsDate:=Date;  ZQuery3.ExecSQL;  
ZQuery3.SQL.Text:='SELECT LAST_INSERT_ID()';  ZQuery3.Open;  
invoiceid:=ZQuery3.Fields[0].AsInteger;   ZQuery3.Close;   if
invoiceid=0 then    raise Exception.Create('لم يتم تسجيل الفاتورة'); 
//تسجيل عناصر الفاتورة  ZMemTable1.First;   ZQuery3.Close; 
ZQuery3.SQL.Clear;  ZQuery3.SQL.Text:='insert into sales_items(invoice
_id,item,qty,sale_price,cost_price,total_price)values(:inid,:it,:qty,:
saleprice,:costprice,:total)';  while not ZMemTable1.EOF do  begin   
ZQuery3.ParamByName('inid').AsInteger:=invoiceid;    ZQuery3.ParamByNa
me('it').AsString:=ZMemTable1.FieldByName('productname').AsString;   
ZQuery3.ParamByName('qty').AsFloat:=ZMemTable1.FieldByName('qty').AsFl
oat;    ZQuery3.ParamByName('saleprice').AsFloat:=ZMemTable1.FieldByNa
me('price').AsFloat;    ZQuery3.ParamByName('costprice').AsFloat:=ZMem
Table1.FieldByName('costprice').AsFloat;    ZQuery3.ParamByName('total
').AsFloat:=ZMemTable1.FieldByName('total').AsFloat;   
ZQuery3.ExecSQL;    ZMemTable1.Next;  end;  ZConnection3.Commit; 
ShowMessage('تم حفظ الفاتورة رقم '+IntToStr(invoiceid)+' بنجاح');   
Except on e:Exception do  begin    if ZConnection3.InTransaction then
ZConnection3.Rollback;;    MessageDlg('خطأ أثناء حفظ الفاتورة:' +
LineEnding +E.Message,mtError, [mbOK],0);  end;  end;

----------------------------------------------------------------------
zeljko · 2025-12-31 11:37

mariadb/mysql does not have RETURNING ? eg insert into mytable
.....etc RETURNING recno; (eg recno is serial) and then after Open
just read FieldByName('recno').AsInteger ? I'm using it in postgresql.

----------------------------------------------------------------------
Gebo · 2025-12-31 12:56

Quote from: zeljko on December 31, 2025, 12:37:03 pmmariadb/mysql does
not have RETURNING ? eg insert into mytable .....etc RETURNING recno;
(eg recno is serial) and then after Open just read
FieldByName('recno').AsInteger ? I'm using it in postgresql.

I want the invoice to be cancelled if a problem has happened with
sales items. I want it to be one transaction.

----------------------------------------------------------------------
cdbc · 2025-12-31 13:25

Hi
LMGTFY 
QuoteLAST_INSERT_ID() (no arguments) returns the first automatically
generated value successfully inserted for an AUTO_INCREMENT column as
a result of the most recently executed INSERT statement. The value of
LAST_INSERT_ID() remains unchanged if no rows are successfully
inserted found HERE
Regards Benny

----------------------------------------------------------------------
jcmontherock · 2025-12-31 16:00

Yes, I forgot that in Mariadb and Mysql an AUTO-INCREMENT field is not
implicit and defined by default. It is with SQLite. Sorry.