Wrapper cffunction for query

[cadol_CF]

Hello,

I am trying to write a wrapper function to be able to work with queries in cfscript (I deal with CF MX7). The SQL string can be assembled and passed to the function as an argument. Here is the function:

<cffunction name="QUERY" access="public" returntype="query">

  <cfargument name="SQLString" type="string" required="yes">

  <cfargument name="Datasource" type="string" required="no" default="#this.dsn#">

  <cfargument name="dbType" type="string" required="no" default="">

  <CFQUERY NAME="QryToReturn" Datasource="#arguments.Datasource#" dbtype="#arguments.dbType#">

    #preserveSingleQuotes(arguments.SQLString)#

  </CFQUERY>

  <cfreturn QryToReturn>

</cffunction>

When it's as simple as this it works:

<cfscript>

Variables.iMyValue = 10;

sSQL = "SELECT * FROM t_test WHERE field1 = #Variables.iMyValue#";

Variables.qry1 = QUERY(SQLString: sSQL, DATASOURCE: Application.sDataSrc, DBTYPE: Application.sDBType);

</cfscript>

However I want to add cfqueryparam stuff to the queries and here is where I get errors.

I tried two ways to assemple the SQL:

<cfscript>

Variables.iMyValue = 10;

//this way the function gets value 10

sSQL1 = "SELECT * FROM t_test WHERE field1 = < cfqueryparam value=#Variables.iMyValue# cfsqltype='cf_sql_integer'>";

//this way the function gets a variable

sSQL2 = "SELECT * FROM t_test WHERE field1 = < cfqueryparam value=##Variables.iMyValue## cfsqltype='cf_sql_integer'>";

Variables.qry1 = QUERY(SQLString: sSQL1, DATASOURCE: Application.sDataSrc, DBTYPE: Application.sDBType);

Variables.qry2 = QUERY(SQLString: sSQL2, DATASOURCE: Application.sDataSrc, DBTYPE: Application.sDBType);

</cfscript>

Either way results in the same server error:

Error Executing Database Query.

[Macromedia][SequeLink JDBC Driver][ODBC Socket][Oracle][ODBC][Ora]ORA-00936: missing expression

Could anybody tell me what I am doing wrong?

Thanks,

Alex

[Dan_Bracuk]

You might be trying something that can't be done.  Whenever I tried to put a query param tag into a variable I would get a error saying that the tag can only be used inside a query.

[BKBK]

Passing the cfqueryparam tag within a string neutralizes it. As a general rule, ColdFusion wont evaluate tags that are enclosed within a string.

It is better to place the tag within the query in the function. Then pass the rest of the SQL script as a string. Something like this:

<cffunction name="QUERY" access="public" returntype="query">

  <cfargument name="SQLString" type="string" required="yes">

  <cfargument name="fieldValue" type="numeric" required="yes">

  <cfargument name="Datasource" type="string" required="no" default="#this.dsn#">

  <cfargument name="dbType" type="string" required="no" default="">

  <CFQUERY NAME="QryToReturn" Datasource="#arguments.Datasource#" dbtype="#arguments.dbType#">

    #arguments.SQLString#

WHERE field1 = <cfqueryparam value="#arguments.fieldValue#" cfsqltype="cf_sql_integer">

  </CFQUERY>

  <cfreturn QryToReturn>

</cffunction>

<cfscript>

variables.iMyValue = 10;

sSQL = "SELECT * FROM t_test";

Variables.qry = QUERY(SQLString: sSQL, variables.iMyValue, DATASOURCE: Application.sDataSrc, DBTYPE: Application.sDBType);

</cfscript>

[cadol_CF]

Thanks Dan and BKBK!

I didn't know this "ColdFusion wont evaluate tags that are enclosed within a string". It seems that the function cannot be as generic as I wanted it to be )))

[BKBK]

cadol_CF wrote:

It seems that the function cannot be as generic as I wanted it to be )))

You are right. There is a limit to what one function can do. However, there is more to this.

Doing one thing, and doing it well, is one of the principles of good function and program design. You can get as generic as you want, but then, using more than one function!