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 <[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/[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.
