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

[Dr. Hawkins]

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.
> LAST_INSERT_ID() for MySQL
> LAST_INSERT_ROWID() for SQLite
>
> 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