--On 9. März 2007 15:30:23 +0100 Jary Busato <jbusato@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

Sorry, but you must be crazy to perform you own transaction handling within Zope. Dealing with transactions is the task of Zope and your database but it is *not recommended* to perform your own transaction hand here...at least not in this way. If you want o integration a third-party system the transaction handling of Zope: look at the DataManager API of Zope's transaction module. -aj

--On 9. März 2007 16:45:56 +0100 Jary Busato <jbusato@libero.it> wrote:

My only need is to be sure to keep the right id; I did'nt use this method for other operations. You sayd that it's not recommended but I don't understand why in this simple context.

Simple or complex context..that does not matter. It is just bad-style. -aj

If I understood you're telling me that begin and commit statements are "redundant". Sometimes conflicts are not correctly managed by ZTM (no rollbacks in db) Marco Bizzarri ha scritto:

On 3/9/07, Jary Busato <jbusato@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

...

If I understood you're telling me that begin and commit statements are "redundant".

No in this case they would be an error.

...

Sometimes conflicts are not correctly managed by ZTM (no rollbacks in db)

Examples? Which bug reports? Stored procedures implementation in DCOracle2 (original implementation) under some conditins may cause that behaviour (no rollback). I've written about this on Zope-db (http://permalink.gmane.org/gmane.comp.web.zope.general/56435).

But this is very specific thing possibly connected with a bit strange (at last for me) Stored procedures implementation in DCOracle2 (they're resource managers itself) and AFAIR they're using other resource managers. I've solved this by changing ZOracleDA Stored Procedures to behave similarly to ZSQLMethods. Another thing is that Zope database adapters usually use volatile attributes and as Dieter Maurer told me (on zope-db) under some (very specific) conditions (AFAIR subtransaction commit or something like that) this may cause connection object stored in volatile attribute to disappear during request. -- Maciej Wisniowski