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

With Regards

VenuMadhav Deevi

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>
   

Cheers

Venu Madhav Deevi

Queries in xql files which are bound to tcl/adp templating pairs are not named with any "namespaces". Their scope is only the corresponding tcl file, so you just name them like this:
<fullquery name="user_name_select">

and then include the name in any db_* procedure (see https://openacs.org/doc/openacs-4/db-api.html), e.g.
db_0or1row user_name_select {}

However, if the queries are used in package procs (in tcl subdir of a package), they should be named with their full namespace, e.g.
<fullquery name="logger::package::variables_multirow.select_variables">

They work with only name="proc_name.query_name" but the use of namespaces is AFAIK highly recommended. As Robert said before, you can find a lot of examples from the existing packages.

Just remember, that the xql files are parsed upon server startup, so you *have* to restart the server if you change them.