ZSQL - multiple inserts to multiple tables in one query.
Hi All, I am developing my first Zope/SQL application. I'm not a coder by profession (more a sysadmin) so I may be missing something. 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? Thanks, Dan
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
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
On Wed, Sep 25, 2002 at 01:39:16AM +0100, Dan Jacobs wrote:
This looks really useful, next question. How do I call it from a DTML method?
As long as REQUEST contains the relevant information, you just call the ZSQL method like this: <dtml-call expr="qry_insertEvent()"> I use the Formulator product to build my add form, but that's the line that does the actual call to the insert query. -Tim -- 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
participants (2)
-
Dan Jacobs -
Tim Wilson