You control freak. Let the backend do what it does so well. https://mariadb.com/kb/en/mariadb/auto_increment-faq/
I gave up on that single table of last PKs 15 + years ago when I found that the backend already did it for us, all we had to was ask for it. On Thu, Mar 24, 2016 at 12:51 PM, Fred Taylor <[email protected]> wrote: > 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/cajidmy+torux8aacz_7ryfjf-ppho-zud+vhqn9g6c+zayt...@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.
