Re: [NF] Getting MySQL integer primary keys (NOT AUTOINC) idea...similar to VFP's old IF SEEK("TheTable","Keys","TheTable") AND RLOCK('Keys') idea

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 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]