Wrapper cffunction for query

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>

Code (markup):

When it's a simple thing it works:

<cfscript>
 Variables.iPatient = 1000;
 Variables.iBlock = 2;


 sSQL = "SELECT * FROM table1 WHERE i_ptid = #Variables.iPatient#";
 sSQL = sSQL & " AND i_radical_trt_block = #Variables.iBlock#";


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

Code (markup):

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.iPatient = 1000;
 Variables.iBlock = 2;


 //this way the function gets values 1000 and 2
 sSQL1 = "SELECT * FROM table1 WHERE i_ptid = #Variables.iPatient#";
 sSQL1 = sSQL1 & " AND i_radical_trt_block = #Variables.iBlock#";


 //this way the function gets variables
 sSQL2 = "SELECT * FROM table1 WHERE i_ptid = ##Variables.iPatient##";
 sSQL2 = sSQL2 & " AND i_radical_trt_block = ##Variables.iBlock##";


 Variables.qry1 = QUERY(SQLString: sSQL1, DATASOURCE: Application.sDataSrc, DBTYPE: Application.sDBType);
 Variables.qry2 = QUERY(SQLString: sSQL2, DATASOURCE: Application.sDataSrc, DBTYPE: Application.sDBType);
</cfscript>

Code (markup):

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

 

I've just noticed to my embarrassment that I have pasted a wrong code block. The code that assembles the SQL (and does not work) is:

<cfscript>
 Variables.iPatient = 1000;
 Variables.iBlock = 2;


 //this way the function gets values 1000 and 2
 sSQL1 = "SELECT * FROM table1 WHERE i_ptid = < cfqueryparam value=#Variables.iPatient# cfsqltype='cf_sql_integer'>";
 sSQL1 = sSQL1 & " AND i_radical_trt_block = < cfqueryparam value=#Variables.iBlock# cfsqltype='cf_sql_integer'>";


 //this way the function gets variables
 sSQL1 = "SELECT * FROM table1 WHERE i_ptid = < cfqueryparam value=##Variables.iPatient## cfsqltype='cf_sql_integer'>";
 sSQL1 = sSQL1 & " AND i_radical_trt_block = < cfqueryparam value=##Variables.iBlock## 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>

Code (markup):