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

16 Aug 2003

      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