Re: [Zope] passing a parameter retrieved from a sql call

6 Jul 2004

      Kate Legere wrote:
...
I have a dtml-method that calls a bunch of sql inserts to update tables in a
relational database. The last thing I need to do is retrieve the
auto-increment ID from one table and use an update command to put it into a
joining table. I'm using MySQL 3.23.52 and it won't let me write the call as
:
UPDATE linksProjects
SET linksProjects.categoryId = categories.categoryId
(SELECT categories.categoryId
   FROM categories 
   WHERE categories.catTitle = <dtml-sqlvar catTitle type=string>)
Apparently it won't let me reference two tables in a nested query at all.
So if I just make a call to a select statement:
SELECT categories.categoryId
       FROM categories 
       WHERE categories.catTitle = <dtml-sqlvar catTitle type=string>
How do I pass the value retrieved back to a variable ... say 'ct' to use in 
The update statement?
Kate
Try this example ZSQL method (named insert_stuff):

INSERT INTO some_table (stuff) VALUES (<dtml-sqlvar stuff type=string>)
<dtml-var sql_delimiter>
SELECT LAST_INSERT_ID() as last_insert_id

And retrieve the last_insert_id from a python script:

results = context.insert_stuff(stuff='more stuff')
if results:
     return results[0].last_insert_id

I don't do DTML but the concept will be similar.

Jim

Re: [Zope] passing a parameter retrieved from a sql call

Jim Gallacher