Last Inserted ID

This is the place to post technical queries about SQL Yoga

Moderators: FourthWorld, heatherlaine, Klaus, robinmiller, trevordevore

Post Reply
nrprioleau
Posts: 27
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: 578
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!
All code published by me here was created with Community Editions of LC (thus is GPLv3).
If you use it in closed source projects, or for the Apple AppStore, or with XCode
you'll violate some license terms - read your relevant EULAs & Licenses!

trevordevore
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 1005
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 - https://www.screensteps.com

LiveCode Repos - https://github.com/search?q=user%3Atrevordevore+topic:livecode
LiveCode Builder Repos - https://github.com/search?q=user%3Atrevordevore+topic:livecode-builder

Post Reply

Return to “SQL Yoga”