Use autoinc but wrap your child-parent-grandparent inserts in a transaction - or better still an SP.
On 10/03/2016 04:47, 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]