How to get last inserted id after insert

[Gebo]

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]

I would define afterinsert event handler.

[dseligo]

Like this:

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

1. With ZQuery do
2. begin
3.   SQL.Text := 'insert into sale_invoice (field1, field2, ...) values ' +
4.     '(:field1, :field2, ...)';
5.   ParamByName('field1').AsInteger := 12323;
6.   ParamByName('field2').AsString := '12323';
7.   ...
8.   ExecSQL;
9.  
10.   SQL.Text:='select LAST_INSERT_ID()';
11.   Open;
12.   iLastID := Fields[0].AsInteger;
13.   ...
14. end;

P.S.: And if you insert from another table, you can also do it like this, without variable:

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

1. SQL.Text := 'insert into sold_items ' +
2.   '(itemid, field1, field2, ...) ' +
3.   'select last_insert_id(), field1, field2, ... ' +
4.   'from someothertable ' +
5.   'where ...';
6. ParamByName('...').AsString := 'abc';
7. ExecSQL;

[Nimbus]

Another option is INSERT...RETURNING

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

[Gebo]

I tried

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

1. ascal  [Select]
2. [+]
3.  
4.     With ZQuery do
5.     begin
6.       SQL.Text := 'insert into sale_invoice (field1, field2, ...) values ' +
7.         '(:field1, :field2, ...)';
8.       ParamByName('field1').AsInteger := 12323;
9.       ParamByName('field2').AsString := '12323';
10.       ...
11.       ExecSQL;
12.      
13.       SQL.Text:='select LAST_INSERT_ID()';
14.       Open;
15.       iLastID := Fields[0].AsInteger;
16.       ...
17.     end;
18.  

but It returns 0.

[jcmontherock]

Maybe a "commit" after insert was missing.

[dseligo]

Maybe a "commit" after insert was missing.

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

[jcmontherock]

Did you check if your table was updated after insert ?

[egsuh]

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

[swissbob]

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.

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]

Did 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][+][-]

1. ZConnection3.Connect;
2.   ZConnection3.StartTransaction;
3.   //إدخال بيانات الفاتورة وجلب ID الفاتورة
4.   ZQuery3.Close;
5.   ZQuery3.SQL.Clear;
6.   ZQuery3.SQL.Text:='insert into sales_invoices (invoice_date,customer_id,total)values(:indate,:custid, :total)';
7.   ZQuery3.ParamByName('custid').AsInteger:= VarAsType(DBLookupComboBox1.KeyValue, varInteger);
8.   ZQuery3.ParamByName('total').AsFloat:=total;
9.   ZQuery3.ParamByName('indate').AsDate:=Date;
10.   ZQuery3.ExecSQL;
11.  
12.   ZQuery3.SQL.Text:='SELECT LAST_INSERT_ID()';
13.   ZQuery3.Open;
14.  
15.   invoiceid:=ZQuery3.Fields[0].AsInteger;
16.  
17.   ZQuery3.Close;
18.  
19.   if invoiceid=0 then
20.     raise Exception.Create('لم يتم تسجيل الفاتورة');
21.   //تسجيل عناصر الفاتورة
22.   ZMemTable1.First;
23.  
24.   ZQuery3.Close;
25.   ZQuery3.SQL.Clear;
26.   ZQuery3.SQL.Text:='insert into sales_items(invoice_id,item,qty,sale_price,cost_price,total_price)values(:inid,:it,:qty,:saleprice,:costprice,:total)';
27.   while not ZMemTable1.EOF do
28.   begin
29.     ZQuery3.ParamByName('inid').AsInteger:=invoiceid;
30.     ZQuery3.ParamByName('it').AsString:=ZMemTable1.FieldByName('productname').AsString;
31.     ZQuery3.ParamByName('qty').AsFloat:=ZMemTable1.FieldByName('qty').AsFloat;
32.     ZQuery3.ParamByName('saleprice').AsFloat:=ZMemTable1.FieldByName('price').AsFloat;
33.     ZQuery3.ParamByName('costprice').AsFloat:=ZMemTable1.FieldByName('costprice').AsFloat;
34.     ZQuery3.ParamByName('total').AsFloat:=ZMemTable1.FieldByName('total').AsFloat;
35.     ZQuery3.ExecSQL;
36.     ZMemTable1.Next;
37.   end;
38.   ZConnection3.Commit;
39.   ShowMessage('تم حفظ الفاتورة رقم '+IntToStr(invoiceid)+' بنجاح');
40.  
41.  
42.   Except on e:Exception do
43.   begin
44.     if ZConnection3.InTransaction then ZConnection3.Rollback;;
45.     MessageDlg('خطأ أثناء حفظ الفاتورة:' + LineEnding +E.Message,mtError, [mbOK],0);
46.   end;
47.   end;
48.  

[zeljko]

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]

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.

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

[cdbc]

Hi
LMGTFY 

LAST_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]

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