[Zope] zsqlmethod and inserting count(*) value blues

[Tino Wildenhain]

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.