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 the error message I get: ------------------ Error ------------------ Error, _mysql_exceptions.ProgrammingError: (1064, "You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near ';\ninsert into tbl2 values ( @n + 1, 'data)' ; ________________________________________________________________________ -SQL used: select @n:=count(*) from tbl; insert into tbl2 values (@n+1, 'data'); -------------------------------------------- I've achieved the functionality I want with next code: ---------------- ZSQLMETHOD ---------------- insert into tbl values ( <dtml-var "_.len(getMessages()) + 1">, 'data' ) -------------------------------------------- where getMessages is a simple zsqlmethod containing "select * from tbl", but I suppose there is a better way to do it. Any ideas? Thanks in advance, Iñigo Serna PS: I'm using next versions: Linux 2.4.20, Zope 2.6, MySQL-4.0.14, MySQL-python 0.9.2
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.
participants (2)
-
Iñigo Serna -
Tino Wildenhain