Dear Zope Community,

 

I am a zope newbie and enjoying it!  I am helping someone build a database for storing musical scores.

 

I am using ZSQL and trying to figure out how to do the following:  I want to do some inserts then a SELECT and then run some DTML on the result to wrap the result with html.  This all needs to happen from one html form.

 

The inserts and the select is on metadata pertaining to some an external file, which needs to get created during this process.  My ZSQL inserts the metadata into the database and creates an ID. I want to use this ID as the file name.  So far I have this:

 

INSERT INTO titles (
id, title) VALUES (
   nextval('titleseq'),
  <dtml-sqlvar title type=nb>
)

 

<dtml-var sql_delimiter>

 

INSERT INTO pieces(
id, primarytitle, category, subcategory, notes, lyrics, editor, lastupdated
) VALUES (
 nextval('pieceseq'),
 currval('titleseq'),
 <dtml-sqlvar category type=int>,
 <dtml-sqlvar subcategory type=int optional>,
 <dtml-sqlvar notes type=nb optional>,
 <dtml-sqlvar lyrics type=nb optional>,
 <dtml-sqlvar editor type=int optional>,
 CURRENT_TIMESTAMP
)

 

<dtml-var sql_delimiter>

 

INSERT INTO piecetitles (
piece, title) VALUES (
 currval('pieceseq'),
 currval('titleseq')
)

 

<dtml-var sql_delimiter>

 

INSERT INTO scorefiles (
id, piece, filetype, filesize, lastupdated) VALUES (
 nextval('scorefileseq'),
 currval('pieceseq'),
 <dtml-sqlvar filetype type=int optional>,
 <dtml-sqlvar category type=int optional>,
 CURRENT_TIMESTAMP
)
<dtml-var sql_delimiter>

 

SELECT currval('pieceseq')

 

<dtml-var sql_delimiter>

 

So next I want to take the ID - which is retrieved from the SELECT at the end - and use it to create an External File (with the External file product).

 

I am stuck on writing the DTML that first inserts the metadata and then creates the External File finally returning the user a confirmation displaying the record they have entered.

  

Kind Regards,

 

Robert Bunker

www.ljmc.org.uk