How can run multiple query in an Z sql method
Hi, How can run multiple query in an Z sql method.. I am using Mysql.. Also is there any method to return a value from insert query? Thanks Remil
--On 9. März 2007 14:42:57 +0530 Remil Mathew <remil.dm@gmail.com> wrote:
Hi, How can run multiple query in an Z sql method..
Sure. SQL Statement just have to separated by <dtml-sql-delimiter>. I am using Mysql..
Also is there any method to return a value from insert query?
An insert statement does not return a value! If you are working with sequences for generating the primary key you might perform a select on the current value of the related sequence. -aj
Andreas Jung schrieb:
--On 9. März 2007 14:42:57 +0530 Remil Mathew <remil.dm@gmail.com> wrote:
Hi, How can run multiple query in an Z sql method..
Sure. SQL Statement just have to separated by <dtml-sql-delimiter>.
I am using Mysql..
Also is there any method to return a value from insert query?
An insert statement does not return a value! If you are working with sequences for generating the primary key you might perform a select on the current value of the related sequence.
Well, it can. See RETURNING clause: http://www.postgresql.org/docs/8.2/static/sql-insert.html but I doubt mysql supports this ... checking ... no, it doesnt. Regards Tino
On Friday 09 March 2007 10:12, Remil Mathew wrote:
Hi, How can run multiple query in an Z sql method.. I am using Mysql.. Also is there any method to return a value from insert query?
Thanks Remil
could you possibly be thinking of? insert foo into bar <dtml-var sql_delimiter> SELECT LAST_INSERT_ID() gaute
--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
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. Andreas Jung ha scritto:
--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
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 -- Marco Bizzarri http://iliveinpisa.blogspot.com/
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
Jary Busato schrieb:
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? Regards Tino
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
On 3/9/07, Jary Busato <jbusato@libero.it> wrote:
If I understood you're telling me that begin and commit statements are "redundant".
Yes, exactly: they are issued by Zope at the boundaries of a transaction.
Sometimes conflicts are not correctly managed by ZTM (no rollbacks in db)
Are you using a non-transactional database (like old MySQL version)? -- Marco Bizzarri http://iliveinpisa.blogspot.com/
participants (7)
-
Andreas Jung -
Gaute Amundsen -
Jary Busato -
Maciej Wisniowski -
Marco Bizzarri -
Remil Mathew -
Tino Wildenhain