Hi, Iñigo Serna wrote:
Hi,
I have a problem with a zsqlmethod.
In mysql client command line next works:
------------------ mysql ------------------- mysql> select @n := count(*) from tbl; mysql> insert into tbl2 values (@n+1, 'data'); --------------------------------------------
But This identical zsqlmethod does not:
---------------- ZSQLMETHOD ---------------- select @n:= count(*) from tbl; insert into tbl2 values (@n+1, 'data'); --------------------------------------------
This is not possible since you are using host variables it seems. To achieve this functionallity with ZOPE, you must use its host language. This means you would have to split your query into 2: one to retrieve count() and one to insert with the new key. Be warned: you seem to use a very very unsave technic to generate keys - you are in a concurrent environment if you work over the web - same inserts can and will happen multiple times at once. So if you calculate count(), another concurrent access comes to the very same result, subsequently inserting with the same key. You can avoid this with locking (See your DB manual) or just use a more advanced database which is capable of naturally do what you want, for example Postgres with the serial datatype.