I think there is confusion here. @@Identity returns the tables identity setting I thought, and in SQL Server it gives you your insert's PKey value.
I don't do mySQL but still think that @@Identity gives you how your table is set for PKey incrementation. It could be int or guid. On Thu, Mar 10, 2016 at 10:22 AM, < [email protected]> wrote: > It's *almost* 15 years, and I always used this function in my framework: > > FUNCTION GetNewID(tiHandle as Integer) as Integer > * Retrieves newly created ID from MYSQL backend. > *** mjb 11/09/2011 - added here from Scheduler > LOCAL liKey as Integer > IF SQLEXEC(tiHandle,'SELECT @@IDENTITY as > iNewKey',"curKey") = 1 THEN && retrieve PK from resulting cursor > liKey = curKey.iNewKey > IF VARTYPE(liKey) = "C" THEN > liKey = VAL(liKey) > ENDIF > ELSE > liKey = -1 > ENDIF > RETURN liKey > ENDFUNC && GetNewID(tiHandle as Integer) as Integer > > Wondering about the difference between last_insert_id() and SELECT > @@IDENTITY? Researching later... > > Never saw this site: http://www.mysqltutorial.org/ HUGE THANKS FOR > THAT! The MYSQL help online is usually decent but this might supplement > that nicely. > > --Mike > > > > > On 2016-03-10 11:05, Stephen Russell wrote: > >> You have been using mySQL for 15 years now and you don't know about this? >> hahahahha >> >> Well the documentation says that when you append it to your insert >> statement you get THAT PKEY value even in multi insert conditions to the >> same table by others. Think of EBAY and last seconds of an auction with a >> few to a few hundred bids coming in. >> >> If you are inserting and then doing a separate statement with the same >> spid >> you should get your insert pkey but if you have done multiple inserts >> which >> one are you getting? >> >> You should experiment with some of these methods. >> http://www.mysqltutorial.org/mysql-stored-procedure-tutorial.aspx >> >> I had to laugh at the causal warnings about getting too fancy in your >> sprocs. Glad the don't like at mine. hahahahaha. >> >> >> On Thu, Mar 10, 2016 at 9:59 AM, < >> [email protected]> wrote: >> >> On 2016-03-10 10:51, Stephen Russell wrote: >>> >>> You may get the incorrect value back buy doing this: >>>> So I'm thinking "how do I get something like VFP's old NextKey >>>> generation" >>>> akin to this: >>>> >>>> * this was pseudo-code, off the top of my head trying to remember my >>>> coding >>>> from 17+ years ago >>>> FUNCTION GetNextKey(tcTable as String) as Integer >>>> <snip> >>>> >>>> In the same insert statement you append ; select last_insert_id() ; and >>>> it >>>> returns YOUR value no matter what type of data column the key is. If >>>> you >>>> do some super fancy secondary select someone else may have gotten an >>>> insert >>>> in as well and your code returns their key, not yours. >>>> >>>> >>> >>> Ah, I see it now. Sorry, I thought you were in SQL Server land with your >>> answer. Yes, that's MySQL. So that should work, right? Or can this >>> fail >>> somehow if others are inserting simultaneously? >>> [excessive quoting removed by server] _______________________________________________ Post Messages to: [email protected] Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/cajidmyjpcbpry5_yopa7w5ehnyma7jth2px9bcxqvw5fko7...@mail.gmail.com ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
