[Zope] How can run multiple query in an Z sql method

[Marco Bizzarri]

On 3/9/07, Jary Busato <jbusato at libero.it> wrote:
>
>  Yes, I must do it when I need to know an autonumber PK after an insert.
>  when DB support transaction i write:
>
>  begin transaction
>  insert foo into bar
> <dtml-var sql_delimiter>
> SELECT LAST_INSERT_ID() as lastid
>  commit
>
>  Then, a DTML method:
>
>  <dtml-in myZSQLinsertandselect()>
>   <dtml-var lastid>
>  </dtml-in>
>
>  Make the insert and write the id returned by the select.
>
>  Illorca
>

This is not good. Keep in mind that Zope transaction management can
cause your full transaction to be aborted and reissued if there is a
conflict during it; this will normally result in a transaction being
aborted on the database, and reissued.

In other words, you could end with something like:

--> your request
begin (issued by Zope)
sql statemnets
<conflict detected>
abort (issued by Zope)
begin (issued by Zope)
(again) sql statements
commit (issued by Zope)
<-- your response

Issuing an explicit commit inside this can cause very strange
behaviour in your application.

Regards
Marco

-- 
Marco Bizzarri
http://iliveinpisa.blogspot.com/