Page 1 of 1

Last Inserted ID

Posted: Mon Oct 10, 2016 11:01 am
by nrprioleau
Please can I have the right syntax to use for retrieving the last inserted id after creating a record?

I have this which returns 0

Code: Select all

command mlteSales_CreateSales pRecordA
   local tSqlQuery, tSqlParamsA, tRowid
   --
   sqlquery_create "sales", pRecordA
   put the result into theError
   put it into theAffectedRows
   -- now get the brand new id
  dbconn_lastInsertedID
   return tRowid
end mlteSales_CreateSales

Re: Last Inserted ID

Posted: Tue Oct 11, 2016 6:36 pm
by AxWald
Hi,

"lastInsertedID" might not be what you want, read the documentation about. It'a a highly situational function ...

To get the last value of a auto-increment ID, usually:

Code: Select all

SELECT MAX(ID) FROM `MyTable`;
does the job - as long as the last insert didn't re-use a "ID hole" from a previously deleted record. But since such can only occur due to "manual" overrides of the auto-increment, you should know about ;-)

Assuming SQLYogas "lastInsertedID" does what the equally named MySQL function does, and that you can translate my SQL to SQLYoga, hope I could help ;-)

Have fun!

Re: Last Inserted ID

Posted: Thu Mar 02, 2017 4:14 pm
by trevordevore
@nrprioleau - in your function tRowId is never set to anything. dbconn_lastInsertedID puts the id into the it variable. You would want to return it rather than tRowId. Or put it into tRowId and then return tRowId.