[Zope] ZSQL Methods and SELECT functions

[Joel Burton]

On Sat, 16 Mar 2002, Robert Ln wrote:

> Hi all,
>
> 	I am using Zope 2.5.0 and the latest ZMySQLDA. I have a few
> related questions.
>
> 1) If I insert a new row into the mysql database that has
> an auto_insert column (say 'id'), how do I get the automatically
>  generated id? In mysql you would use the LAST_INSERT_ID()
> function, but this doesnt work in a zsql method
>
> 2) The reason 1) doesnt work is that zsql methods (and
> the underlying python-mysql db interface) doesnt seem
> to allow select functions.  Something like
> SELECT 1+1 doesnt work. Is there any way of getting this to work?
>
> 3) Actually, I see from the python mysql db interface that the cursor
> object has an insert_id() method. Does the ZMySQL DA allow any
> way of getting at this method?
>
> 4) Finally, the mysql docs say the last_insert_id() returns the last
> automatically generated id on a per-connection basis. If in zope
> only a single connection is made and a lot of clients are accessing
> it at once, how do I guarantee I get back the correct id? I thought
> you could concatenate multiple SQL queries in one zsql method, but
> that doesnt seem to work anymore.

SELECT functions work fine; the problem is that 'LIMIT 1000' is appearing
at the end of the SQL statement, and to MySQL,

  SELECT 1 + 1 LIMIT 1000

is an error. Go to the Advanced tab and set "Max rows to retrieve" to 0.

Yes, since several Zope shares one connection against several web users,
it might be possible that, between the insert and the select
last_insert_id(), that they would be different web viewers. So: make it
all part of one ZSQL method:

  insert into test (a) values ('a')

  <dtml-var sql_delimiter>

  select last_insert_id;

You can combine more than one SQL statement in a ZSQLMethod this way.

-- 

Joel BURTON  |  joel@joelburton.com  |  joelburton.com  |  aim: wjoelburton
Independent Knowledge Management Consultant