Forum OpenACS Q&A: Re: How to retrieve data using quries written in Xql with TCL and ADP

Short answer: read the docs and browse the source.

However, I know that looking at the source might be a little confusing, because you often see sql embedded in the .tcl file, even in the top level index.tcl file. You should leave those queries out if you've already got them in the .xql files.

These are the three steps you need to take to have things properly abstracted:

  1. Define queries in the .xql file:
    <queryset> 
      <fullquery name="total_select"> 
        <querytext> 
            SELECT COUNT(email) AS num_parties 
            FROM parties
        </querytext> 
      </fullquery> 
    
      <fullquery name="emails_select"> 
        <querytext> 
            SELECT email FROM parties
        </querytext> 
      </fullquery> 
    </queryset> 
    
  2. Perform the queries in the .tcl file
    # note how there is no SQL here, just an empty statement {}
    # and we use the query_name from the .xql file.
    
    # single row query for num_parties
    db_1row total_select {}
    
    # multirow query for emails
    db_multirow foobar emails_select {}
    
  3. Refer to the variables in the .adp file
    <p>There are @num_parties@ people in the database:</p>
    
    <ol>
    <multiple name="foobar">
    <li>@foobar.email@</li>
    </multiple>
    </ol>
    
I liked Dave's answer so much I copied it onto my website:

http://rubick.com/openacs/templating

and I've added a few more comments. I'll eventually flesh it out even more, hopefully :-)