Forum OpenACS Q&A: Porting of dynamic queries

I have a question on porting dynamic queries. I've read the docs on the db_map command, but it's not clear to me what should I do in cases like this (I abbreviated the query, since the original is huge):

  set query "
    select
      attributes.attribute_id, attribute_name, 
      attributes.table_name,
      attribute_label, type_label, object_type as subtype, datatype, 
      params.is_html, params.is_required,
      ..."
  
  if { ![template::util::is_nil extra_where] } {
    append query "
   and
      $extra_where"
  }

  append query "
    order by
      attributes.tree_level, attributes.sort_order desc, 
      attributes.attribute_id, params.param_id"
  
  if { ![template::util::is_nil extra_orderby] } {
    append query ", $extra_orderby"
  }  

  uplevel "
    template::query $datasource_name $datasource_type {$query}
  "

My question is regarding the two conditional statements that may or may not go in the query. What should I do about them in the .xql files?

Thanks for the tip Dan. I had read the Wimpy presentation and the section on db_map. But, as I said in my original post, the example in the Wimpy presentation did not help me in my question. It wasn't clear as to what should I do in that case.

I'll add some more examples to that slide.

There are a couple of ways to approach this.  One way would be to use a db_map call for the original "set sql" statement and one db_map call for each of the dynamic parts like extra_orderby and extra_where.  Then just let the query string built up and passed to the template::query as it originally was.  Doing it this way, you wouldn't need to have anything for the template::query in the .xql file.

What do I put in the partialquery section for the $extra_where

I believe you can just keep $extra_where in the .xql file. When the interpreter comes along a db_map call, it reaches out to the proper .xql file, grabs the partialquery snippet and evaluates it in the scope of the .tcl script. So $extra_where will be replaced by whatever $extra_where was up until that point in the .tcl script.(Note: that's how I conceptualize it happening - not sure if that's exactly how it really happens)

A brief example:

.tcl

if { ![template::util::is_nil extra_where] } {
  append query "[db_map extra_where_snippet]"
}


.xql

< partialquery name="extra_where_snippet">
< querytext>

 and $extra_where 

< /querytext>
< /partialquery>

Yes, this is right.  By convention we're only using the $tcl_var form for SQL snippets.  For Tcl values use the :tcl_var form, which gets set to a bindvar in the Oracle driver and surrounded by single quotes in the PG driver, both of which provide protection against "smuggled SQL" exploits.