[Zope] Multiple queries in a ZSQL method?

[Phil Harris]

Noah,

Try something like:

insert into author
    (id, name, sort_name, email, bio, initials)
values
    (null,
    <dtml-sqlvar name type=nb>,
    <dtml-sqlvar sort_name type=nb>,
    <dtml-sqlvar email type=nb>,
    <dtml-sqlvar bio type=nb>,
    <dtml-sqlvar initials type=nb>)

<dtml-var sql_delimiter>

select LAST_INSERT_ID()

hth

Phil
Phil.harris@zope.co.uk


----- Original Message -----
From: "Noah" <Noah@noah.org>
To: <zope@zope.org>
Sent: Sunday, October 22, 2000 9:29 PM
Subject: [Zope] Multiple queries in a ZSQL method?


| Hi,
|
| I'm using MySQL.  I need to have a ZSQL Method that does an insert and a
select in one call.
| After I do an insert I need to check the LAST_INSERT_ID to get the primary
key of the
| record that I just inserted. Something like this:
|
| insert into author
|     (id, name, sort_name, email, bio, initials)
| values
|     (null,
|     <dtml-sqlvar name type=nb>,
|     <dtml-sqlvar sort_name type=nb>,
|     <dtml-sqlvar email type=nb>,
|     <dtml-sqlvar bio type=nb>,
|     <dtml-sqlvar initials type=nb>);
|
| select LAST_INSERT_ID();
|
| If I put this into two different methods then I'm afraid that I will have
some
| threading problems due to the fact that another client might come in and
| do an insert using the database connection I was using. LAST_INSERT_ID
| is safe as long as you have an unshared connection. Zope does
| connection pooling, so I worry that using two separate ZSQL Methods
| may not always work. And WILL NOT work if Zope gives me a different
connection
| for the second ZSQL call.
|
| Is there a better way to do this?
|
| Yours,
| Noah
|
|
|
|
|
|
| _______________________________________________
| Zope maillist  -  Zope@zope.org
| http://lists.zope.org/mailman/listinfo/zope
| **   No cross posts or HTML encoding!  **
| (Related lists -
|  http://lists.zope.org/mailman/listinfo/zope-announce
|  http://lists.zope.org/mailman/listinfo/zope-dev )