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/[email protected]
** 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.
