SQL: using the newly inserted index as a value for insertion
dochawk at gmail.com
Sun Nov 24 19:50:49 EST 2013
On Sat, Nov 23, 2013 at 9:52 PM, David Glass <dglass at graymattercomputing.com
> How much control over the databases do you have?
Pretty much complete, but . . .
> If you have some measure of control I'd think the simplest solution would
> be to get away from the auto-incrementing key, and provide your own.
> Then you know exactly what the value needs to be in each of your
That opens bigger cans of worms :)
The presumption is simultaneous users with periodic updates (the lag stops
me from doing them real time, so I store in memory until synchronization
when no keystrokes for a while). If Jane enters a new record on her
machine, and Wendy a new record on hers, local assignment would give them
both the same key, at which point I have to deal with *that* when the
second one trys to sync . . . so they keep a temporary id in memory, and
the same transaction that inserts to the table finds the new record and
brings it back.
My temporary workaround is to do a replace in livecode to fix the returned
string when I read back, but I have to check every record from the remote
for this. Once fixed, on the next update it will write the correct string
back to the remote.
> Barring that, for the three dbs you are dealing with you'll have to
> specialize a bit and use the functions available to each one.
> RETURNING for PostgreSQL is probably best, but SELECT LASTVAL() would work.
> LAST_INSERT_ID() for MySQL
> LAST_INSERT_ROWID() for SQLite
> Keeping in mind the danger inherent in those last two (and LASTVAL()), of
However, I would need to process these after transactions, wouldn't I?
Bringing me back to two transactions, and two lags.
Dr. Richard E. Hawkins, Esq.
More information about the Use-livecode