Forum OpenACS Development: xql files don't work

i am trying to use .xql files to write queries called in the tCL files.

However for some reason when i use the queries in the xql files they just don't work.

Then I tried to put the queries directly in the tcl file and they worked great.

i wonder why is that

here it is and example
Scenario 1: Everything in the TCL file
#####
db_foreach select_states {
select c.category as state, count(c.category_id) as num_items
from im_categories c,im_conf_items i
where c.category_id = i.conf_item_status_id
group by c.category

} {
lappend item_status_options [list $state $state $num_items]
}
###

Scenario 2: if i use

In the tcl file
####
db_foreach select_states { } {
lappend item_status_options [list $state $state $num_items]
}
####

and in the xql file:

###
queryset
fullquery name="select_item_states"
querytext

select c.category as state, count(c.category_id) as num_items
from im_categories c,im_conf_items i
where c.category_id = i.conf_item_status_id
group by c.category

/querytext
/fullquery
/queryset
########

Your .tcl file shows "select_states" so your fullquery name should be select_states as well.

Here are some things to check:

1) .xql file and .tcl file exactly similar names.

db_foreach select_states { } {
lappend item_status_options [list $state $state $num_items]
}

2) A complete set of xml tags in xql file:

<?xml version="1.0"?>

<queryset>

<fullquery name="select_states">
      <querytext>
        select c.category as state, count(c.category_id) as num_items
        from im_categories c,im_conf_items i
        where c.category_id = i.conf_item_status_id
        group by c.category
      </querytext>
</fullquery>

</queryset>

3) Check that the unix permissions are correctly set on the files.

4) Retart the server:

kill -9 `cat /usr/local/aolserver/log/nspid.yourserver`

or use developer support to re-load the changed file. This is beause the xql files are ONLY sourced at server startup time.

5) If it still won't play, copy another query/tcl pair and modify it to avoid errors creeping in. For what it is worth, most code has the query in quotes in the tcl as well as in the xql:

db_foreach "query_name" {code per row}

and I always put it in both places. You can still verify which query is running by making a change to one of them that is obvious from the results.

6) Weep with frustration at the knowledge that if it still doesn't work, its your fault! :-|

Been there.

Regards
Richard

I have checked, tcl and xql pages are not part of a tcl library function.

i am working on /www directory, in the index page of an application i installed.

PLus, i have changed tcl and xql code a little

set item_status_options [list]
db_foreach select_state { *SQL* } { lappend item_status_options [list $state $state $num_items] }

fullquery name="select_state.select_states"
querytext
select c.category as state, count(c.category_id) as num_items
from im_categories c,im_conf_items i
where c.category_id = i.conf_item_status_id
group by c.category
/querytext
/fullquery

The error remains the same

based on what you are saying i don't see how the pages "/acs-subiste/www/members/index" works then.
I have based the code i wrote code on it.

Verify that the file permissions are consistent with other xql files that are working.

Sometimes an xql file will not load, for example if there is an error in the form of the file.

Remove any tabs in the xql file. xql files do not like tabs. Results can seem unpredictable. Also check for other problematic invisible characters.

Check for the existence of the query name used elsewhere in the xql file or another xql file in the same directory.

I've been frustrated with nonpredictable xql problems. Most have been caused by one of these.

Hope this helps,

Torben