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 <[email protected]> wrote: > Use autoinc but wrap your child-parent-grandparent inserts in a > transaction - or better still an SP. > > > On 10/03/2016 04:47, [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/CAJidMYL=uok_oq+bmbn_ofbvt7e4uxk_snwan0f5qrhdkpp...@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.
