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

Hi,
  I had written all my select quries in .Xql file. It contains quries for retrieving onerow and multirows. Now i want to use .Tcl files to retrieve them and want display them using .Adp,
if any body can provide me sample for this i would be very thankful.

  .Tcl should have commands or statements to fetch single row i.e onerow and multrow and to display them through .ADP files.

  Plz give me a sample or example source code for this implementation.

With Regards

VenuMadhav Deevi

There are examples throughout the toolkit in every package.  I would also read the documentation located in:
    https://openacs.org/doc/openacs-4-6-2/
(in particular the docs and demos in the "ACS Templating" section), plus Joel's excellent docs (yikes, where's the link to that again?).

And kindly post these questions to just this forum.  No need to cross-post.  Thx!

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

Hi Jade Rubick / Dave Hwang,
  Thank u very much for giving a proper guidence with regard to this question.
  The Template given by Jade Rubick is very much help ful if this is going to be placed in the openacs templating for the new people like me.

Cheers

Venu Madhav Deevi

Hi,
  I am new at this, but I tried something like this and it didnt work, the first error i noticed was that multiple should be multirow in the tcl file. I also had problems with the naming of the query in my xql file. How does this work? e.g. I have noticed that in the default /web/{service}/www/index.xql file the queries are named dbqd.www.index.user_name_select etc. when i put a new query in this file it wasnt found. What is the naming convention for queries? Is there a document I should have read to find this out?

Thanks

David Bell

David,

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.

Oh, and regarding your multiple problem: the error message sounds quite self-explanatory. If you want to present multiple rows of information, you have to have multiple rows of data. You achieve that by using db_multirow database proc in your tcl file (or just multirow, if you're not retrieving data from db). Please see the link to documentation in my previous post.