[Zope-DB] Compound SQL Statements
charlie at egenix.com
Wed Jun 30 06:18:36 EDT 2004
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)
> 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.
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 ! ::::
More information about the Zope-DB