[Zope] Multiple queries in a ZSQL method?

[Noah]

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  -  [EMAIL PROTECTED]
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 )

Re: [Zope] Multiple queries in a ZSQL method?

[Ender]

multiple non-select statements (max 1 select) are done through the use
of

dtml-var sql_delimiter

kapil

Noah wrote:
 
 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  -  [EMAIL PROTECTED]
 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 )

___
Zope maillist  -  [EMAIL PROTECTED]
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 )