I've played around with MariaDB a bit and this stored procedure works (newids table must have primary key index):
CREATE DEFINER=`root`@`localhost` PROCEDURE `newidX`(IN `TableName` CHAR(20)) LANGUAGE SQL NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT '' BEGIN select nextid from newids where newids.table=TableName for update; update newids set nextid=nextid+1 where newids.`table`=TableName; END Fred On Wed, Mar 9, 2016 at 4:17 PM, <[email protected] > wrote: > Tech used: VFP9SP2 against MySQL (MariaDB) backend > > I created the new incarnation of FabMate a year ago with 16-byte character > keys. Works fine, but I wanted to entertain the idea of using integer keys > again instead (because I love the idea of 4 bytes instead of 16) and I did > NOT want to use auto-increment keys in the tables because I wanted to have > more control over them during data-entry, so basically, I can create all > kinds of records and then at save time AND ONLY THEN do I want to store the > actual data (and that's why AUTOINC keys wouldn't work). Oh...and they > wouldn't work because I have several child-parent-grandparent relationships > being created all during data-entry. > > 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 > LOCAL llGotIt as Logical, liNewKey as Integer > IF SEEK(tcTable,"MyKeysTable","TableName") THEN > llGotIt = .F. > DO WHILE NOT llGotIt > IF RLOCK("MyKeysTable") THEN > REPLACE NextVal with NextVal + 1 in MyKeysTable > liNewKey = MyKeysTable.NextVal > UNLOCK IN MyKeysTable > llGotIt = .T. > ELSE > WAIT WIND NOWAIT "Trying to get record lock on table > counter...please wait" > ENDIF > ENDDO > ELSE > liNewKey = -1 > ENDIF > RETURN liNewKey > ENDFUNC > > > My idea at this point was to simply have ONE table who's primary key *IS* > an Auto-Increment integer field, where I insert something (probably the > unique 16-byte key I'm generating now) and just grab the resulting new > primary (integer) key created via this old routine: > > 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 > > Can anyone think of a better way? > > tia, > --Mike > > [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/CAJCBksrRk3WBvvHLmCEt0Cxnt6tG_3Bd-Ow222dyymE=yfc...@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.
