[Zope] passing a parameter retrieved from a sql call

[Jim Gallacher]

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