[Zope] Allowed SQL statements for ZSQL

[Martin Gebert]

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