Last Inserted ID

This is the place to post technical queries about SQL Yoga

Moderators: Klaus, FourthWorld, heatherlaine, robinmiller, trevordevore

Post Reply
nrprioleau
Posts: 24
Joined: Mon May 09, 2016 10:53 am

Last Inserted ID

Post by nrprioleau » Mon Oct 10, 2016 11:01 am

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

AxWald
Posts: 288
Joined: Thu Mar 06, 2014 2:57 pm

Re: Last Inserted ID

Post by AxWald » Tue Oct 11, 2016 6:36 pm

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!
Livecode programming until the cat hits the fan ...

trevordevore
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 850
Joined: Sat Apr 08, 2006 3:06 pm
Location: Overland Park, Kansas
Contact:

Re: Last Inserted ID

Post by trevordevore » Thu Mar 02, 2017 4:14 pm

@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.
Trevor DeVore
ScreenSteps - http://www.screensteps.com

Levure Application Framework: https://github.com/trevordevore/levure
LiveCode Resources for Developers: http://livecode.bluemangolearning.com

Post Reply

Return to “SQL Yoga”