", "all") /> buf.append("SELECT "); // distinct if (arguments.Query.getDistinct()) { buf.append("DISTINCT "); } // columns buf.append(arguments.Query.getSelectAsString(Convention)); buf.append(" "); buf.append("FROM "); buf.append(arguments.Query.getFromAsString(Convention)); buf.append(" "); buf.append(renderWhere(arguments.Query, arguments.Convention)); buf.append(" "); if (ArrayLen(order)) { buf.append("ORDER BY "); // loop over all of the order-bys and render them out for(x = 1; x lte ArrayLen(order); x = x+1) { // get the arguments for this expression orderNode = order[x]; buf.append(getFieldExpression(orderNode, arguments.Convention)); buf.append(" "); buf.append(UCASE(orderNode.direction)); buf.append(" "); if (x IS NOT ArrayLen(order)) { buf.append(", "); } } } ", "all") /> if (ArrayLen(where)) { buf.append('WHERE '); // loop over all of the expressions and render them out for (x = 1; x lte ArrayLen(where); x = x+1) { // get the arguments for this expression whereNode = where[x]; // if the node is a structure output it accordingly. otherwise, just output it. if (IsStruct(whereNode)) { // render the expression // scale is an optional parameter to cfqueryparam. So set scale to the entire parameter clause here and // reuse throughout this function // i.e. scale="2" if ( arguments.Query.findObject(whereNode.objectAlias).getField(whereNode.fieldAlias).scale gt "0" ) { scale = "scale=""#arguments.Query.findObject(whereNode.objectAlias).getField(whereNode.fieldAlias).scale#""" ; } else { scale = ""; } // maxlength is an optional parameter to cfqueryparam. So set maxlength to the entire parameter clause here if ( arguments.Query.findObject(whereNode.objectAlias).getField(whereNode.fieldAlias).length gt "0" ) { maxlength = "maxlength=""#arguments.Query.findObject(whereNode.objectAlias).getField(whereNode.fieldAlias).length#""" ; // add room for wildcards maxlengthwithwildcard = min( getMaxIntegerLength(), arguments.Query.findObject(whereNode.objectAlias).getField(whereNode.fieldAlias).length+2 ); maxlengthwithwildcard = "maxlength=""#maxlengthwithwildcard#""" ; } else { maxlength = ""; maxlengthwithwildcard = ""; } // isBetween if (compareNocase(whereNode.comparison,"isBetween") is 0) { buf.append(getFieldExpression(whereNode, arguments.Convention)).append(" "); buf.append('BETWEEN [[cfqueryparam cfsqltype="#arguments.Query.findObject(whereNode.objectAlias).getField(whereNode.fieldAlias).cfSqlType#" #maxlength# #scale# value="##arguments.Query.getValue(#whereNode.value1#)##" /]] '); buf.append('AND [[cfqueryparam cfsqltype="#arguments.Query.findObject(whereNode.objectAlias).getField(whereNode.fieldAlias).cfSqlType#" #maxlength# #scale# value="##arguments.Query.getValue(#whereNode.value2#)##" /]] '); // isBetweenFields } else if (compareNocase(whereNode.comparison,"isBetweenFields") is 0) { buf.append('#getFieldExpression(whereNode, arguments.Convention)# '); buf.append('BETWEEN #getFieldExpression(whereNode, arguments.Convention, 1)# '); buf.append('AND #getFieldExpression(whereNode, arguments.Convention, 2)# '); // isEqual } else if (compareNocase(whereNode.comparison,"isEqual") is 0) { buf.append('#getFieldExpression(whereNode, arguments.Convention)# = '); buf.append('[[cfqueryparam cfsqltype="#arguments.Query.findObject(whereNode.objectAlias).getField(whereNode.fieldAlias).cfSqlType#" #maxlength# #scale# value="##arguments.Query.getValue(#whereNode.value#)##" /]] '); // isEqualField } else if (compareNocase(whereNode.comparison,"isEqualField") is 0) { buf.append('#getFieldExpression(whereNode, arguments.Convention)# = #getFieldExpression(whereNode, arguments.Convention, 1)# '); // isNotEqual } else if (compareNocase(whereNode.comparison,"isNotEqual") is 0) { buf.append('#getFieldExpression(whereNode, arguments.Convention)# != '); buf.append('[[cfqueryparam cfsqltype="#arguments.Query.findObject(whereNode.objectAlias).getField(whereNode.fieldAlias).cfSqlType#" #maxlength# #scale# value="##arguments.Query.getValue(#whereNode.value#)##" /]] '); // isNotEqualField } else if (compareNocase(whereNode.comparison,"isNotEqualField") is 0) { buf.append('#getFieldExpression(whereNode, arguments.Convention)# != #getFieldExpression(whereNode, arguments.Convention, 1)# '); // isGte } else if (compareNocase(whereNode.comparison,"isGte") is 0) { buf.append('#getFieldExpression(whereNode, arguments.Convention)# >= '); buf.append('[[cfqueryparam cfsqltype="#arguments.Query.findObject(whereNode.objectAlias).getField(whereNode.fieldAlias).cfSqlType#" #maxlength# #scale# value="##arguments.Query.getValue(#whereNode.value#)##" /]] '); // isGteField } else if (compareNocase(whereNode.comparison,"isGteField") is 0) { buf.append('#getFieldExpression(whereNode, arguments.Convention)# >= #getFieldExpression(whereNode, arguments.Convention, 1)# '); // isGt } else if (compareNocase(whereNode.comparison,"isGt") is 0) { buf.append('#getFieldExpression(whereNode, arguments.Convention)# > '); buf.append('[[cfqueryparam cfsqltype="#arguments.Query.findObject(whereNode.objectAlias).getField(whereNode.fieldAlias).cfSqlType#" #maxlength# #scale# value="##arguments.Query.getValue(#whereNode.value#)##" /]] '); // isGtField } else if (compareNocase(whereNode.comparison,"isGtField") is 0) { buf.append('#getFieldExpression(whereNode, arguments.Convention)# > #getFieldExpression(whereNode, arguments.Convention, 1)# '); // isLte } else if (compareNocase(whereNode.comparison,"isLte") is 0) { buf.append('#getFieldExpression(whereNode, arguments.Convention)# <= '); buf.append('[[cfqueryparam cfsqltype="#arguments.Query.findObject(whereNode.objectAlias).getField(whereNode.fieldAlias).cfSqlType#" #maxlength# #scale# value="##arguments.Query.getValue(#whereNode.value#)##" /]] '); // isLteField } else if (compareNocase(whereNode.comparison,"isLteField") is 0) { buf.append('#getFieldExpression(whereNode, arguments.Convention)# <= #getFieldExpression(whereNode, arguments.Convention, 1)# '); // isLt } else if (compareNocase(whereNode.comparison,"isLt") is 0) { buf.append('#getFieldExpression(whereNode, arguments.Convention)# < '); buf.append('[[cfqueryparam cfsqltype="#arguments.Query.findObject(whereNode.objectAlias).getField(whereNode.fieldAlias).cfSqlType#" #maxlength# #scale# value="##arguments.Query.getValue(#whereNode.value#)##" /]] '); // isLtField } else if (compareNocase(whereNode.comparison,"isLtField") is 0) { buf.append('#getFieldExpression(whereNode, arguments.Convention)# < #getFieldExpression(whereNode, arguments.Convention, 1)# '); // isLike } else if (compareNocase(whereNode.comparison,"isLike") is 0) { buf.append('#getFieldExpression(whereNode, arguments.Convention)# LIKE '); if (compareNocase(whereNode.mode,"Anywhere") is 0) { buf.append('[[cfqueryparam cfsqltype="#arguments.Query.findObject(whereNode.objectAlias).getField(whereNode.fieldAlias).cfSqlType#" #maxlengthwithwildcard# #scale# value="%##arguments.Query.getValue(#whereNode.value#)##%" /]] '); } else if (compareNocase(whereNode.mode,"Left") is 0) { buf.append('[[cfqueryparam cfsqltype="#arguments.Query.findObject(whereNode.objectAlias).getField(whereNode.fieldAlias).cfSqlType#" #maxlengthwithwildcard# #scale# value="##arguments.Query.getValue(#whereNode.value#)##%" /]] '); } else if (compareNocase(whereNode.mode,"Right") is 0) { buf.append('[[cfqueryparam cfsqltype="#arguments.Query.findObject(whereNode.objectAlias).getField(whereNode.fieldAlias).cfSqlType#" #maxlengthwithwildcard# #scale# value="%##arguments.Query.getValue(#whereNode.value#)##" /]] '); } else if (compareNocase(whereNode.mode,"All") is 0) { buf.append('[[cfqueryparam cfsqltype="#arguments.Query.findObject(whereNode.objectAlias).getField(whereNode.fieldAlias).cfSqlType#" #maxlength# #scale# value="##arguments.Query.getValue(#whereNode.value#)##" /]] '); } // isNotLike } else if (compareNocase(whereNode.comparison,"isNotLike") is 0) { buf.append('#getFieldExpression(whereNode, arguments.Convention)# NOT LIKE '); if (compareNocase(whereNode.mode,"Anywhere") is 0) { buf.append('[[cfqueryparam cfsqltype="#arguments.Query.findObject(whereNode.objectAlias).getField(whereNode.fieldAlias).cfSqlType#" #maxlengthwithwildcard# #scale# value="%##arguments.Query.getValue(#whereNode.value#)##%" /]] '); } else if (compareNocase(whereNode.mode,"Left") is 0) { buf.append('[[cfqueryparam cfsqltype="#arguments.Query.findObject(whereNode.objectAlias).getField(whereNode.fieldAlias).cfSqlType#" #maxlengthwithwildcard# #scale# value="##arguments.Query.getValue(#whereNode.value#)##%" /]] '); } else if (compareNocase(whereNode.mode,"Right") is 0) { buf.append('[[cfqueryparam cfsqltype="#arguments.Query.findObject(whereNode.objectAlias).getField(whereNode.fieldAlias).cfSqlType#" #maxlengthwithwildcard# #scale# value="%##arguments.Query.getValue(#whereNode.value#)##" /]] '); } else if (compareNocase(whereNode.mode,"All") is 0) { buf.append('[[cfqueryparam cfsqltype="#arguments.Query.findObject(whereNode.objectAlias).getField(whereNode.fieldAlias).cfSqlType#" #maxlength# #scale# value="##arguments.Query.getValue(#whereNode.value#)##" /]] '); } // isIn } else if (compareNocase(whereNode.comparison,"isIn") is 0) { buf.append('#getFieldExpression(whereNode, arguments.Convention)# IN ( '); buf.append('[[cfif Len(Trim(arguments.Query.getValue(#whereNode.values#)))]] '); buf.append(' [[cfqueryparam cfsqltype="#arguments.Query.findObject(whereNode.objectAlias).getField(whereNode.fieldAlias).cfSqlType#" value="##arguments.Query.getValue(#whereNode.values#)##" list="yes" /]] '); buf.append('[[cfelse]] '); buf.append(' [[cfqueryparam null="yes" /]] '); buf.append('[[/cfif]] '); buf.append(') '); // isNotIn } else if (compareNocase(whereNode.comparison,"isNotIn") is 0) { buf.append('#getFieldExpression(whereNode, arguments.Convention)# NOT IN ( '); buf.append('[[cfif Len(Trim(arguments.Query.getValue(#whereNode.values#)))]] '); buf.append(' [[cfqueryparam cfsqltype="#arguments.Query.findObject(whereNode.objectAlias).getField(whereNode.fieldAlias).cfSqlType#" #maxlength# #scale# value="##arguments.Query.getValue(#whereNode.values#)##" list="yes" /]] '); buf.append('[[cfelse]] '); buf.append(' [[cfqueryparam null="yes" /]] '); buf.append('[[/cfif]] '); buf.append(') '); // isNull } else if (compareNocase(whereNode.comparison,"isNull") is 0) { buf.append('#getFieldExpression(whereNode, arguments.Convention)# IS NULL '); // isNotNull } else if (compareNocase(whereNode.comparison,"isNotNull") is 0) { buf.append('#getFieldExpression(whereNode, arguments.Convention)# IS NOT NULL '); } } else { // just output it buf.append('#UCASE(whereNode)# '); } } } if (ArrayLen(where)) { buf.append('WHERE '); // loop over all of the expressions and render them out for (x = 1; x lte ArrayLen(where); x = x+1) { // get the arguments for this expression whereNode = where[x]; // if the node is a structure output it accordingly. otherwise, just output it. if (IsStruct(whereNode)) { // render the expression // scale is an optional parameter to cfqueryparam. So set scale to the entire parameter clause here and // reuse throughout this function // i.e. scale="2" if ( arguments.Query.findObject(whereNode.objectAlias).getField(whereNode.fieldAlias).scale gt "0" ) { scale = "scale=""#arguments.Query.findObject(whereNode.objectAlias).getField(whereNode.fieldAlias).scale#""" ; } else { scale = ""; } // maxlength is an optional parameter to cfqueryparam. So set maxlength to the entire parameter clause here if ( arguments.Query.findObject(whereNode.objectAlias).getField(whereNode.fieldAlias).length gt "0" ) { maxlength = "maxlength=""#arguments.Query.findObject(whereNode.objectAlias).getField(whereNode.fieldAlias).length#""" ; // add room for wildcards maxlengthwithwildcard = min( getMaxIntegerLength(), arguments.Query.findObject(whereNode.objectAlias).getField(whereNode.fieldAlias).length+2 ); maxlengthwithwildcard = "maxlength=""#maxlengthwithwildcard#""" ; } else { maxlength = ""; maxlengthwithwildcard = ""; } // isBetween if (compareNocase(whereNode.comparison,"isBetween") is 0) { buf.append(getFieldOrExpressionForDelete(whereNode, arguments.Convention)).append(" "); buf.append('BETWEEN [[cfqueryparam cfsqltype="#arguments.Query.findObject(whereNode.objectAlias).getField(whereNode.fieldAlias).cfSqlType#" #maxlength# #scale# value="##arguments.Query.getValue(#whereNode.value1#)##" /]] '); buf.append('AND [[cfqueryparam cfsqltype="#arguments.Query.findObject(whereNode.objectAlias).getField(whereNode.fieldAlias).cfSqlType#" #maxlength# #scale# value="##arguments.Query.getValue(#whereNode.value2#)##" /]] '); // isBetweenFields } else if (compareNocase(whereNode.comparison,"isBetweenFields") is 0) { buf.append('#getFieldOrExpressionForDelete(whereNode, arguments.Convention)# '); buf.append('BETWEEN #getFieldOrExpressionForDelete(whereNode, arguments.Convention, 1)# '); buf.append('AND #getFieldOrExpressionForDelete(whereNode, arguments.Convention, 2)# '); // isEqual } else if (compareNocase(whereNode.comparison,"isEqual") is 0) { buf.append('#getFieldOrExpressionForDelete(whereNode, arguments.Convention)# = '); buf.append('[[cfqueryparam cfsqltype="#arguments.Query.findObject(whereNode.objectAlias).getField(whereNode.fieldAlias).cfSqlType#" #maxlength# #scale# value="##arguments.Query.getValue(#whereNode.value#)##" /]] '); // isEqualField } else if (compareNocase(whereNode.comparison,"isEqualField") is 0) { buf.append('#getFieldOrExpressionForDelete(whereNode, arguments.Convention)# = #getFieldOrExpressionForDelete(whereNode, arguments.Convention, 1)# '); // isNotEqual } else if (compareNocase(whereNode.comparison,"isNotEqual") is 0) { buf.append('#getFieldOrExpressionForDelete(whereNode, arguments.Convention)# != '); buf.append('[[cfqueryparam cfsqltype="#arguments.Query.findObject(whereNode.objectAlias).getField(whereNode.fieldAlias).cfSqlType#" #maxlength# #scale# value="##arguments.Query.getValue(#whereNode.value#)##" /]] '); // isNotEqualField } else if (compareNocase(whereNode.comparison,"isNotEqualField") is 0) { buf.append('#getFieldOrExpressionForDelete(whereNode, arguments.Convention)# != #getFieldOrExpressionForDelete(whereNode, arguments.Convention, 1)# '); // isGte } else if (compareNocase(whereNode.comparison,"isGte") is 0) { buf.append('#getFieldOrExpressionForDelete(whereNode, arguments.Convention)# >= '); buf.append('[[cfqueryparam cfsqltype="#arguments.Query.findObject(whereNode.objectAlias).getField(whereNode.fieldAlias).cfSqlType#" #maxlength# #scale# value="##arguments.Query.getValue(#whereNode.value#)##" /]] '); // isGteField } else if (compareNocase(whereNode.comparison,"isGteField") is 0) { buf.append('#getFieldOrExpressionForDelete(whereNode, arguments.Convention)# >= #getFieldOrExpressionForDelete(whereNode, arguments.Convention, 1)# '); // isGt } else if (compareNocase(whereNode.comparison,"isGt") is 0) { buf.append('#getFieldOrExpressionForDelete(whereNode, arguments.Convention)# > '); buf.append('[[cfqueryparam cfsqltype="#arguments.Query.findObject(whereNode.objectAlias).getField(whereNode.fieldAlias).cfSqlType#" #maxlength# #scale# value="##arguments.Query.getValue(#whereNode.value#)##" /]] '); // isGtField } else if (compareNocase(whereNode.comparison,"isGtField") is 0) { buf.append('#getFieldOrExpressionForDelete(whereNode, arguments.Convention)# > #getFieldOrExpressionForDelete(whereNode, arguments.Convention, 1)# '); // isLte } else if (compareNocase(whereNode.comparison,"isLte") is 0) { buf.append('#getFieldOrExpressionForDelete(whereNode, arguments.Convention)# <= '); buf.append('[[cfqueryparam cfsqltype="#arguments.Query.findObject(whereNode.objectAlias).getField(whereNode.fieldAlias).cfSqlType#" #maxlength# #scale# value="##arguments.Query.getValue(#whereNode.value#)##" /]] '); // isLteField } else if (compareNocase(whereNode.comparison,"isLteField") is 0) { buf.append('#getFieldOrExpressionForDelete(whereNode, arguments.Convention)# <= #getFieldOrExpressionForDelete(whereNode, arguments.Convention, 1)# '); // isLt } else if (compareNocase(whereNode.comparison,"isLt") is 0) { buf.append('#getFieldOrExpressionForDelete(whereNode, arguments.Convention)# < '); buf.append('[[cfqueryparam cfsqltype="#arguments.Query.findObject(whereNode.objectAlias).getField(whereNode.fieldAlias).cfSqlType#" #maxlength# #scale# value="##arguments.Query.getValue(#whereNode.value#)##" /]] '); // isLtField } else if (compareNocase(whereNode.comparison,"isLtField") is 0) { buf.append('#getFieldOrExpressionForDelete(whereNode, arguments.Convention)# < #getFieldOrExpressionForDelete(whereNode, arguments.Convention, 1)# '); // isLike } else if (compareNocase(whereNode.comparison,"isLike") is 0) { buf.append('#getFieldOrExpressionForDelete(whereNode, arguments.Convention)# LIKE '); if (compareNocase(whereNode.mode,"Anywhere") is 0) { buf.append('[[cfqueryparam cfsqltype="#arguments.Query.findObject(whereNode.objectAlias).getField(whereNode.fieldAlias).cfSqlType#" #maxlengthwithwildcard# #scale# value="%##arguments.Query.getValue(#whereNode.value#)##%" /]] '); } else if (compareNocase(whereNode.mode,"Left") is 0) { buf.append('[[cfqueryparam cfsqltype="#arguments.Query.findObject(whereNode.objectAlias).getField(whereNode.fieldAlias).cfSqlType#" #maxlengthwithwildcard# #scale# value="##arguments.Query.getValue(#whereNode.value#)##%" /]] '); } else if (compareNocase(whereNode.mode,"Right") is 0) { buf.append('[[cfqueryparam cfsqltype="#arguments.Query.findObject(whereNode.objectAlias).getField(whereNode.fieldAlias).cfSqlType#" #maxlengthwithwildcard# #scale# value="%##arguments.Query.getValue(#whereNode.value#)##" /]] '); } else if (compareNocase(whereNode.mode,"All") is 0) { buf.append('[[cfqueryparam cfsqltype="#arguments.Query.findObject(whereNode.objectAlias).getField(whereNode.fieldAlias).cfSqlType#" #maxlengthwithwildcard# #scale# value="##arguments.Query.getValue(#whereNode.value#)##" /]] '); } // isNotLike } else if (compareNocase(whereNode.comparison,"isNotLike") is 0) { buf.append('#getFieldOrExpressionForDelete(whereNode, arguments.Convention)# NOT LIKE '); if (compareNocase(whereNode.mode,"Anywhere") is 0) { buf.append('[[cfqueryparam cfsqltype="#arguments.Query.findObject(whereNode.objectAlias).getField(whereNode.fieldAlias).cfSqlType#" #maxlengthwithwildcard# #scale# value="%##arguments.Query.getValue(#whereNode.value#)##%" /]] '); } else if (compareNocase(whereNode.mode,"Left") is 0) { buf.append('[[cfqueryparam cfsqltype="#arguments.Query.findObject(whereNode.objectAlias).getField(whereNode.fieldAlias).cfSqlType#" #maxlengthwithwildcard# #scale# value="##arguments.Query.getValue(#whereNode.value#)##%" /]] '); } else if (compareNocase(whereNode.mode,"Right") is 0) { buf.append('[[cfqueryparam cfsqltype="#arguments.Query.findObject(whereNode.objectAlias).getField(whereNode.fieldAlias).cfSqlType#" #maxlengthwithwildcard# #scale# value="%##arguments.Query.getValue(#whereNode.value#)##" /]] '); } else if (compareNocase(whereNode.mode,"All") is 0) { buf.append('[[cfqueryparam cfsqltype="#arguments.Query.findObject(whereNode.objectAlias).getField(whereNode.fieldAlias).cfSqlType#" #maxlengthwithwildcard# #scale# value="##arguments.Query.getValue(#whereNode.value#)##" /]] '); } // isIn } else if (compareNocase(whereNode.comparison,"isIn") is 0) { buf.append('#getFieldOrExpressionForDelete(whereNode, arguments.Convention)# IN ( '); buf.append('[[cfif Len(Trim(arguments.Query.getValue(#whereNode.values#)))]] '); buf.append(' [[cfqueryparam cfsqltype="#arguments.Query.findObject(whereNode.objectAlias).getField(whereNode.fieldAlias).cfSqlType#" #maxlength# #scale# value="##arguments.Query.getValue(#whereNode.values#)##" list="yes" /]] '); buf.append('[[cfelse]] '); buf.append(' [[cfqueryparam null="yes" /]] '); buf.append('[[/cfif]] '); buf.append(') '); // isNotIn } else if (compareNocase(whereNode.comparison,"isNotIn") is 0) { buf.append('#getFieldOrExpressionForDelete(whereNode, arguments.Convention)# NOT IN ( '); buf.append('[[cfif Len(Trim(arguments.Query.getValue(#whereNode.values#)))]] '); buf.append(' [[cfqueryparam cfsqltype="#arguments.Query.findObject(whereNode.objectAlias).getField(whereNode.fieldAlias).cfSqlType#" #maxlength# #scale# value="##arguments.Query.getValue(#whereNode.values#)##" list="yes" /]] '); buf.append('[[cfelse]] '); buf.append(' [[cfqueryparam null="yes" /]] '); buf.append('[[/cfif]] '); buf.append(') '); // isNull } else if (compareNocase(whereNode.comparison,"isNull") is 0) { buf.append('#getFieldOrExpressionForDelete(whereNode, arguments.Convention)# IS NULL '); // isNotNull } else if (compareNocase(whereNode.comparison,"isNotNull") is 0) { buf.append('#getFieldOrExpressionForDelete(whereNode, arguments.Convention)# IS NOT NULL '); } } else { // just output it buf.append('#UCASE(whereNode)# '); } } }