CFLib.org – Common Function Library Project

queryExecute(sql_statement[, queryParams][, queryOptions])

Last updated September 22, 2014

author

Henry Ho

Version: 1 | Requires: CF9 | Library: CFMLLib

Description:
Include a cfm with this function if server.coldfusion.productVersion is 9 or 10.

Return Values:
Returns a query.

Example:

QueryExecute ("select from Employees where empid=1");
  
QueryExecute("select from Employee where country=:country and citizenship=:country", {country='USA'});
  
More example: https://wikidocs.adobe.com/wiki/display/coldfusionen/QueryExecute

Parameters:

Name Description Required
sql_statement SQL. Yes
queryParams Struct of query param values. No
queryOptions Query options. No

Full UDF Source:

<!---
 Backport of QueryExecute in CF11 to CF9 &amp; CF10
 
 @param sql_statement 	 SQL. (Required)
 @param queryParams 	 Struct of query param values. (Optional)
 @param queryOptions 	 Query options. (Optional)
 @return Returns a query. 
 @author Henry Ho (henryho167@gmail.com) 
 @version 1, September 22, 2014 
--->
<cffunction name="QueryExecute" output="false"
			hint="
				* result struct is returned to the caller by utilizing URL scope (no prefix needed) * 
				https://wikidocs.adobe.com/wiki/display/coldfusionen/QueryExecute">
	<cfargument name="sql_statement" required="true">
	<cfargument name="queryParams"  default="#structNew()#">
	<cfargument name="queryOptions" default="#structNew()#">
	
	<cfset var parameters = []>
	
	<cfif isArray(queryParams)>
		<cfloop array="#queryParams#" index="local.param">
			<cfif isSimpleValue(param)>
				<cfset arrayAppend(parameters, {value=param})>
			<cfelse>
				<cfset arrayAppend(parameters, param)>
			</cfif>
		</cfloop>
	<cfelseif isStruct(queryParams)>
		<cfloop collection="#queryParams#" item="local.key">
			<cfif isSimpleValue(queryParams[key])>
				<cfset arrayAppend(parameters, {name=local.key, value=queryParams[key]})>
			<cfelse>
				<cfset var parameter = {name=key}>
				<cfset structAppend(parameter, queryParams[key])>
				<cfset arrayAppend(parameters, parameter)>
			</cfif>
		</cfloop>
	<cfelse>
		<cfthrow message="unexpected type for queryParams">
	</cfif>
	
	<cfif structKeyExists(queryOptions, "result")>
		<!--- strip scope, not supported --->
		<cfset queryOptions.result = listLast(queryOptions.result, '.')>
	</cfif>
	
	<cfset var executeResult = new Query(sql=sql_statement, parameters=parameters, argumentCollection=queryOptions).execute()>
	
	<cfif structKeyExists(queryOptions, "result")>
		<!--- workaround for passing result struct value out to the caller by utilizing URL scope (no prefix needed) --->
		<cfset URL[queryOptions.result] = executeResult.getPrefix()>
	</cfif>
	
	<cfreturn executeResult.getResult()>
</cffunction>
blog comments powered by Disqus

Search CFLib.org


Latest Additions

Kevin Cotton added
date2ExcelDate
May 5, 2016

Raymond Camden added
CapFirst
April 25, 2016

Chris Wigginton added
loremIpsum
January 18, 2016

Gary Stanton added
calculateArrival...
November 19, 2015

Sebastiaan Naafs - van Dijk added
getDaysInQuarter
November 13, 2015

Created by Raymond Camden / Design by Justin Johnson