embedding cfswitch in cfquery

Question

Ok so we're trying to be clever by having database tables with defined fields like:

(1) id, name, title, datemodified, dateadded

and then extending them for various "objects" like

Contact table (2) id, name, title, datemodified, dateadded, sitecode, contactid

or:

Article table (3) id, name, title, datemodified, dateadded, sitecode, articleid, votes

so you'll notice that (1) becomes is a base, and (2) and (3) extend.

we have a base object that does a database query on those base fields and then we try and magically extend it with the following code:

<cfquery name="local.qReturnQuery" datasource="#variables.sDSN#">   
     SELECT id, name, title, datemodified, dateadded, sitecode, contactid
       FROM tbl_#arguments.sPrefix# cb
      WHERE 1
        <cfif arrayLen(arguments.aExtendedParams) gt 0>
            <cfloop from="1" to="#arrayLen(arguments.aExtendedParams)#" index="local.x">
                 <cfscript>
                      local.sParamField = arguments.aExtendedParams[local.x][1];
                      local.sParamValue = arguments.aExtendedParams[local.x][2];
                      local.sParamType = arguments.aExtendedParams[local.x][3];
                      local.bParamIsList = arguments.aExtendedParams[local.x][4];
                      local.sParamCondition = arguments.aExtendedParams[local.x][5];            
                      local.bIsPositive = arguments.aExtendedParams[local.x][6];
                </cfscript>
                <cfswitch expression="#local.sParamType#">
                      <cfcase value="integer,boolean" delimiters="true">
                            #local.sParamCondition#
                           <cfif local.bParamIsList>
                                #local.sParamField# <cfif not local.bIsPositive>NOT </cfif>IN (<cfqueryparam cfsqltype="cf_sql_integer" value="#local.sParamValue#" list="true">)
                           <cfelse>
                                #local.sParamField# <cfif not local.bIsPositive>!</cfif>= <cfqueryparam cfsqltype="cf_sql_integer" value="#local.sParamValue#"> 
                           </cfif>
                      </cfcase>
                      <cfcase value="string">
                            #local.sParamCondition#
                            <cfif local.bParamIsList>
                                  #local.sParamField# <cfif not local.bIsPositive>NOT </cfif>IN (<cfqueryparam cfsqltype="cf_sql_longvarchar" value="#local.sParamValue#" list="true">)
                            <cfelse>
                                  #local.sParamField# <cfif not local.bIsPositive>!</cfif>= <cfqueryparam cfsqltype="cf_sql_longvarchar" value="#local.sParamValue#">
                            </cfif>                     
                      </cfcase>
                  </cfswitch>
              </cfloop>
          </cfif>
 </cfquery>

Unfortunately my incredible code seems to ignore my cfswitch and outputs a query like:

SELECT id, name, title, datemodified, dateadded, sitecode, contactid
  FROM tbl_contact_thing cb
 WHERE 1

my Array looks like:

arguments.aExtendedParams = [{1="contactid",2="44",3="integer",4="false",5="AND",6="true"}];

so should look like:

SELECT id, name, title, datemodified, dateadded, sitecode, contactid
  FROM tbl_contact_thing cb
 WHERE 1
   AND contactid = 44

What might I be doing wrong (in terms of this code)

Answer 1

I didn't pore over the code, but I spotted this which is wrong:

<cfcase value="integer,boolean" delimiters="true">

Your delimiters are not the letters t, r, u, e; it's just a comma.

That might not be all the problem, but it's part of the problem.

Docs for <cfcase>