[Zope] ZSQL Method Question

[Cliff Ford]

Comment on Peter's suggestion: I am no expert on these things, but it is 
my understanding that for MySQL LAST_INSERT_ID() fetches the last 
autoincrement value made by the current insert, so the outcome is not 
affected by virtually simultaneous requests. And I don't think MySQL 
accepts a value for the autoincrement key. I am a bit surprised that the 
code you quote is reliable - surely there is a possibility of an insert 
after calling GetNextID and before calling SQLInsert in the python code?

Comment on Tino's suggestion: I think that LAST_INSERT_ID() is MySQL 
specific and I guess CURRVAL() is Postgres specific. But the principle 
is the same: calling within the same Z SQL Method ought to be safe.

Anyway, I hope John has had is question answered.

Cliff

Tino Wildenhain wrote:
> Peter Bengtsson wrote:
> 
>>What if you have 1,000,000 requests/sec?
>>What if between the INSERT and the LAST_INSERT_ID() another INSERT is made?
>>
>>I use PostgreSQL and with postgres you can always ask the sequence what
>>the next id is going to be. It goes something like this::
>>
>>next_id = context.GetNextId()[0].next_id
>>context.SQLInsertUser(uid=next_id, name='Peter')
>>
>>where 'GetNextId' is a ZSQL method that looks like this::
>>
>><params></params>
>>SELECT NEXTVAL('users_uid_seq') AS next_id
> 
> 
> Its even easier:
> one ZSQL Method:
> 
> INSERT INTO foo (foo_id,blah,bar) VALUES
> (nextval('foo_foo_id_seq'),<dtml-sqlvar blah> ... );
> SELECT CURRVAL('foo_foo_id_seq') as foo_id;
> 
> But your above solution is valid too.
> 
> Regards
> Tino
> _______________________________________________
> Zope maillist  -  Zope at zope.org
> http://mail.zope.org/mailman/listinfo/zope
> **   No cross posts or HTML encoding!  **
> (Related lists - 
>  http://mail.zope.org/mailman/listinfo/zope-announce
>  http://mail.zope.org/mailman/listinfo/zope-dev )