Forum OpenACS Development: Question on Query Extractor

Posted by Charles Mok on 09/21/01 09:23 AM

Below is a query wrapped inside a Tcl variable. When we ran the Query Extractor, this query had not been picked up. Should Query Extractor pick up this query?

set sql "
select   ...
         nvl(e.name, a.name) as name,
         ...
"

To make it run on postgreSQL, I commented it out and used


set sql "
select   ...
         coalesce(e.name, a.name) as name,
         ...
"

After replacing the original query by this, PostgreSQL works fine. But I guess this will not run on Oracle database. So, what should I do?

2: Response to Question on Query Extractor (response to 1)

Posted by Don Baccus on 09/21/01 03:49 PM

The query extractor has no way of knowing that the variable will subsequently be used as part of a query, so it can't process these. It's very simple and doesn't "understand" Tcl programs.

You can't just replace the string as you've done because, as you've noted it will no longer run on Oracle.

There's a "db_map" procedure used to map pieces of queries that are dynamic - check out packages/acs-admin/www/users/complex-search*

In the case of the "day_view" query, you can get rid of the "set sql" entirely. Just define the two queries in your *-oracle.xql and *-postgresql.xql files with a name, and reference the name in the "db_foreach" calls that follow. You can just pass the empty string ""
as the query to the db_foreach since the query dispatcher will fetch the proper PG or Oracle query for you ... you can get rid of the "set sql" entirely because this isn't being built as a dynamic query but rather just to avoid duplicating the query in the two calls. The query dispatcher already allows the sharing of queries in a file...