andreas rippel schrieb:
Hi all,
I'm looking for the SQL grammar, which is allowed/supported by ZSQL. E.g. the point is, how does ZSQL support sql-transaction expressions.
You shouldn't involve TA expressions on your own, Zope is able to handle that for you. See the archives of the Zope-DB mailinglist of this January, I recently answered this question there.
E.g. "select NOW()" is a legal expression for MySQL, but causes a parse-error in ZSQL.
I think I see an explanation for that - select NOW() returns a string, not a valid SQL result (a table). The Z SQL method can't handle that, I presume... One possibility might be to give the value a name: select now() as now but I'm not sure about that.
There seems to be a "standard" inclusion of a limit clause within ZSQL for security and/or overflow reasons.
That's right, and sometimes this causes problems with specific SQL statements, e. g. when setting locks (explanation: LIMIT as inserted where the MySQL parsed doesn't expect it, so it's a syntax error). Go to the "Advanced" tab ot the Z SQL method and set the Limit to 0.
Does anybody know the grammar? Are there considerations to suit or extend the grammar?
Basically, Z SQL methods support every valid SQL grammar - AFAIK there's no syntax check before deploying them. Problems are caused by special statements as above - invalid return values, LIMIT wrongly added... HTH. Martin