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

[Dan Jacobs]

Tim,

This looks really useful, next question.  How do I call it from a DTML
method?

Thanks,

Dan

On Tue, 24 Sep 2002, Tim Wilson wrote:

> 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
>

 - Dan Jacobs
 - 07956 246 659