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
_______________________________________________
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.
