Forum OpenACS Q&A: How to trace xql query problems

Collapse
Posted by Tom Jackson on

Cronjob appears to be broken on Oracle. A function uses a query which is in an .xql file, but apparently the query isn't being found correctly. Does anyone have advice on tracing the problem, where is the query processor looking?

Here are some relevant code chunks:

cronjob-procs.tcl (in proc cronjob_check ):

db_foreach cronjob_sched_foreach {} {
  ad_schedule_proc -once t -thread t 1 cronjob_run $cronjob_id
}
cronjob-procs.xql:

  <fullquery name="cronjob_check.cronjob_sched_foreach">
    <querytext>
      select cronjob_id
      from cronjobs
      where disabled_p = 'f'
      and approved_p = 't'
      and ((minute = :minute) or (minute = '*'))
      and ((hr = :hr ) or (hr = '*'))
      and ((mon = :mon ) or (mon = '*'))
      and ((day = :day ) or (day = '*'))
      and ((dayofweek = :dayofweek ) or (dayofweek = '*'))
    </querytext>
  </fullquery>

Collapse
Posted by Michael Hinds on
Haven't delved into the RP for a while, but as a quicky...is your proc in a namespace? (if not, why not!). I usually include the namespace in the name. e.g. if your namespace is cronjob do:
<fullquery name="cronjob::cronjob_check.cronjob_sched_foreach">
Not sure why this would only affect Oracle though.
Collapse
Posted by Tom Jackson on

Cronjob is in need of a major overhaul,(for one thing it should understand the unix crontab format) at that time, maybe namespacing the proc will happen. I'm just wondering how to trace this type of problem, maybe it isn't in cronjob at all, but Oracle doesn't seem to find the query, the actual error was that the query length was zero, or too large. I assume zero was the actual problem.

Collapse
Posted by Janine Ohmer on
If your code excerpt is in a proc, you'll need to use "procname.queryname" in the xql file.  Dunno why this would be Oracle-only, though.
Collapse
Posted by Tom Jackson on

Janine, yes, it appears to be named correctly. I wonder if maybe the package isn't getting registered properly? Maybe the .xql file is never found for some reason? I had to do some work to get it to load since there is a new format for the .info file. There are more file types and the new package doesn't load with older versions of the apm.

Collapse
Posted by Janine Ohmer on
Oh - yea, reading too fast.  Ok, then try taking the proc name off.  Seriously!  I've had to fiddle with this a couple of times, but nothing I could reproduce elsewhere.  Every once in a while the query processor just seems to want it one way or the other.

Also, I'm sure you already know this but just in case - you'll need to restart to see the change.  xql files don't seem to "watch" properly, at least not the few times I've tried it.

Collapse
Posted by Randy O'Meara on
Just a stab in the dark...

Does the xql file contain an <rdbms> element with all the right values? If something is not right in that element, it probably will not be recognized and loaded properly.

If I recall, with debug on, you should see (in the error log) that the QP does or does not find the query. It should also give the exact name it's looking for.