SQL: using the newly inserted index as a value for insertion

Dr. Hawkins 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
> wrote:

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

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.
> Keeping in mind the danger inherent in those last two (and LASTVAL()), of
> course.

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.
(702) 508-8462

More information about the Use-livecode mailing list