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 fbtaylor@gmail.com 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, < mbsoftwaresolutions@mbsoftwaresolutions.com
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 asiNewKey',"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]