I think you guys are missing the point of the original post. I just told him I didn't want to commit to disk immediately until I was absolutely sure I wanted to save the entire dataset.
The original post title/question was basically "how do I replicate the same key generation we used to do in VFP 15+ years ago?"
On 2016-03-10 09:23, Stephen Russell wrote:
Yeah what he said.
http://www.mysqltutorial.org/mysql-stored-procedure-tutorial.aspx
Unlock the power of the database and make your life easier.
On Thu, Mar 10, 2016 at 4:12 AM, AndyHC jarndice@gmail.com wrote:
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]