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

1: How to retrieve data using quries written in Xql with TCL and ADP

Posted by VenuMadhav Deevi on 05/16/03 07:35 AM

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

2: Re: How to retrieve data using quries written in Xql with TCL and ADP (response to 1)

Posted by Robert Locke on 05/16/03 08:48 AM

There are examples throughout the toolkit in every package. I would also read the documentation located in:
http://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!

3: Re: How to retrieve data using quries written in Xql with TCL and ADP (response to 1)

Posted by Dave Hwang on 05/16/03 11:21 AM

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:

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>

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 {}

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>

4: Re: How to retrieve data using quries written in Xql with TCL and ADP (response to 3)

Posted by Jade Rubick on 05/16/03 10:55 PM

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

5: Re: How to retrieve data using quries written in Xql with TCL and ADP (response to 1)

Posted by VenuMadhav Deevi on 05/17/03 08:10 AM

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

6: Re: How to retrieve data using quries written in Xql with TCL and ADP (response to 1)

Posted by David Bell on 05/23/03 04:06 AM

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

7: Re: How to retrieve data using quries written in Xql with TCL and ADP (response to 1)

Posted by Jarkko Laine on 05/23/03 10:39 AM

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 http://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.

8: Re: How to retrieve data using quries written in Xql with TCL and ADP (response to 6)

Posted by Jarkko Laine on 05/23/03 10:49 AM

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.