[Zope-DB] Compound SQL Statements

[Charlie Clark]

On 2004-06-30 at 01:23:20 [+0200], Roy S. Rapoport 
<zope-db at ols.inorganic.org> wrote:
> I'm using Zope 2.7.0 with ZMySQLDA 2.0.9 and Python-MySQL 1.1.1.
> 
> Trying to use this bit of SQL:
> ---
> INSERT INTO person
> (Login, Password, FirstName, LastName, Email, Organization, Title, Street1,
> Street2, City, State, Postal, Country)
> VALUES(
>     'roy2',
>     'as',
>     'asdf',
>     'asd',
>     'sd',
>     'da',
>     'a',
>     'd',
>     'd',
>     'j',
>     'j',
>     'j',
>     'j'
> );
> 
> SELECT LAST_INSERT_ID()
> --
> Within a ZSQL statement nets me:
> 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 ';\n\nSELECT LAST_INSERT_ID()' at
> line 18")
> 
> Even though this seems like perfectly legit SQL code (and it works within a
> mysql interpreter).

Code that works within an interpreter doesn't necessarily mean legitimate 
SQL and SELECT LAST_INSERT_ID() isn't ANSI SQL but MySQL SQL! This isn't the 
problem however. The problem is that you are trying to issue two SQL 
commands in one ZSQL Method. For safety reasons the ";" is escaped by Zope 
to stop someone hijacking your code and running all kinds of other calls. 
While it is possible to use <dtml-var sqldelimiter> to separate such 
commands I wouldn't advise it. Use two ZSQL Methods called from a single 
PythonScript instead. They will be wrapped within the same transaction and 
will be reliable.

Charlie
-- 
Charlie Clark
eGenix.com

Professional Python Services directly from the Source
 >>> Python/Zope Consulting and Support ...        http://www.egenix.com/
 >>> mxODBC.Zope.Database.Adapter ...             http://zope.egenix.com/
 >>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
________________________________________________________________________

::: Try mxODBC.Zope.DA for Windows,Linux,Solaris,FreeBSD for free ! ::::