[Zope] ZSQL - multiple inserts to multiple tables in one query.

[Tim Wilson]

On Wed, Sep 25, 2002 at 01:21:11AM +0100, Dan Jacobs wrote:
> 
> I am developing my first Zope/SQL application.

Good luck, I'm just finishing one. I think you'll find it enjoyable.

> I have a database which stores information on musical scores.  I am trying
> to write a zope interface to add new musical scores.  My schema has
> several tables and in each table 2/3 fields need values inserted into them
> each time a new musical score is added.
> 
> So my question is...  How do I do this within one ZSQL method?

Here's some code I use in my SQL application. This code adds a record to
an 'event' table and one or more associated records to an
'event_participant' table. Notice the <dtml-var sql_delimiter>. This is
used to separate the two queries.

-Tim

INSERT INTO event (event_name, event_type, location_id, 
                   start_time, end_time, description, 
                   username)
  VALUES (
    <dtml-sqlvar event_name type="nb">,
    <dtml-sqlvar event_type type="nb">,
    <dtml-sqlvar location_id type="int">,
    <dtml-sqlvar start_time type="nb">,
    <dtml-sqlvar end_time type="nb" optional="true">,
    <dtml-sqlvar description type="nb" optional="true">,
    <dtml-sqlvar username type="nb">
          )

<dtml-var sql_delimiter>

<dtml-in participant_ids>
INSERT INTO event_participant (participant_id, event_id)
  VALUES (
          <dtml-sqlvar sequence-item type="int">,
          (SELECT max(event_id) FROM event)
         )
<dtml-var sql_delimiter>
</dtml-in>

-- 
Tim Wilson      |   Visit Sibley online:   | Check out:
Henry Sibley HS |  http://www.isd197.org   | http://www.zope.com
W. St. Paul, MN |                          | http://slashdot.org
wilson@visi.com |  <dtml-var pithy_quote>  | http://linux.com