Coldfusion query function to run and convert any SQL select query to json format, a struct, an array, or return as a query object. includes error checking, comments, supports cfqueryparams, and setting the Content-Type header for returning application/json.

getQuery.cfc

component output="false" {

	public getQuery function init(required string dsn){
		variables.dsn = arguments.dsn;
		return this;
	}
	
	// arg.sql String (required) - A SQL select statement to query
	// arg.contentType Array (optional) - Array of obj with sqp query params having the keys name, value, and type.
	// arg.label String (optional) - The label for the return (only applies if returnType is 'json'). Default is 'queryData'
	// arg.returnType String (optional) - This is the type for the return. Can be 'json', 'query', or 'array'. Default is 'query'.
	// arg.contentType String (optional) - Sets the Content-Type header for the return. Default is 'text/html'
	public any function returnQuery(required struct params) 
	description="Takes a struct with {sql,[label,returnType,contentType]}, runs the query, and then returns the results in the specified return type" 
	output="false" {
		// array to serialize with our query if anything exists
        var returnArray = []; 
        var returnJson = {};
        // set defaults and validate input
		arguments.params = {
			'sql' = (structkeyexists(arguments.params,"sql") && len(arguments.params.label)) ? trim(arguments.params.sql) : "",
			'sqlParams' = (structkeyexists(arguments.params,"sqlParams") && isArray(arguments.params.sqlParams) && !ArrayIsEmpty(arguments.params.sqlParams)) ? arguments.params.sqlParams : [],
			'label' = (structkeyexists(arguments.params,"label") && len(arguments.params.label)) ? trim(arguments.params.label) : "queryData",
			'returnType' = (structkeyexists(arguments.params,"returnType") && len(arguments.params.returnType)) ? trim(arguments.params.returnType) : "query",
			'contentType' = (structkeyexists(arguments.params,"contentType") && len(arguments.params.contentType)) ? trim(arguments.params.contentType) : "text/html;charset=UTF-8"
		};
		// sets the Content-Type header
		getPageContext().getResponse().setcontenttype(arguments.params.contentType);
		// return error if no sql is passed
		if(!len(arguments.params.sql)){
			var errorStruct = {
				'error':'Struct key ''sql'' is required.'
			};
			arrayappend(returnArray,errorStruct);
			// return error as json
			if(arguments.params.returnType == 'json'){
				return serializejson(returnArray);
			}
			// return error as array
			else if (arguments.params.returnType == 'array'){
				return returnArray;
			}
			// return error as empty query
			else{
				return query();
			}
		}
		
        // we'll try so we can gracefully handle any errors
		try{
			// initialize query obj
			var q = new query(); 
			// initalize query name var
            var qName = 0; 
            // set datasource
            q.setDatasource(variables.dsn); 
            // set query name
            q.setName(qName); 
            if(!ArrayIsEmpty(arguments.params.sqlParams)){
            	for(var p=1;p<=arraylen(arguments.params.sqlParams);p++){
            		q.addParam(name="#arguments.params.sqlParams[p].name#",value="#arguments.params.sqlParams[p].value#",cfsqltype="cf_sql_#arguments.params.sqlParams[p].type#");  
            	}
            }
            // execute SQL that was passed as arg[0]
            var qResult = q.execute(sql=arguments.params.sql); 
            // this holds our query results
            qName = qResult.getResult(); 
            // this is a list of column names which we'll convert to an array
            var columnArray = listtoarray(qResult.getPrefix().columnList); 
	        // initialize struct to hold column-value pair
            var queryStruct = {};
            // if returnType is not query, let's process them into an array
            if(arguments.params.returnType != 'query'){ 
            	// loop through query rows
	            for(var i = 1;i<=qName.recordcount;i++){ 
	            	var queryStruct = {}; 
	            	// loop through query columns
	            	for(var ii=1;ii<=arraylen(columnArray);ii++){ 
	            		// insert column-value pair into temp struct
	            		structInsert(queryStruct,columnArray[ii],qName[columnArray[ii]][i]); 
	            	}
	            	// add struct for this row as an item indexed in our return array
					arrayAppend(returnArray,queryStruct); 
	            }
			}
			else{
				// returnType is query, so let's put it into our array for now (we pull it out in the return)
				queryStruct = {
					'query' = qName
				};
				arrayAppend(returnArray,queryStruct); 
			}
		}
		// catch any errors that may occur
		catch (any e){
			// if we have an error, add some userful error details our return array
			var templateArray = [];
			var templateStruct = {};
			for(var item=1;item<=arraylen(e.tagContext);item++){
				templateStruct = {
					"line #e.tagContext[item].line#" = e.tagContext[item].template
				};
				arrayAppend(templateArray,templateStruct);
			}
			var errorStruct = {
				'detail':e.detail,
				'error':e.message,
				'diagnostics':templateArray
			};
			arrayappend(returnArray,errorStruct); 
		}
		// finally always runs, whether there is an error or not. This is where we return
		finally{
			// assign the return array to the resultLabel (arg[1])
			var returnJson[arguments.params.label] = returnArray;
			// return serialized array. It's either empty, has query results, or holds the error info 
			
			switch(arguments.params.returnType){
				case 'json':
					return serializejson(returnJson);
					break;
				case 'array':
					return returnArray;
					break;
				case 'struct':
					return returnJson;
					break;
				case 'query':
					return (structkeyexists(returnArray[1],'query')) ? returnArray[1].query : returnArray;
					break;
			}
		}    
	}
}

queryTest.cfm

<cfscript>
	// uncomment and try each of these to see the return
	
	variables.showMe = 'json';
	//variables.showMe = 'query';
	//variables.showMe = 'struct';
	//variables.showMe = 'array';
	
	
	/**************************************************/
	// The function returnQuery({required string sql,[array sqlParams, string label, string returnType, string contentType]}) 
	// takes a single structure as a parameter. It expects the following key value pairs:
	//
	// arg.sql String (required) - A SQL select statement to query
	// arg.sqlParams Array (optional) - This is key-value pairings for queryparams. The 'type' argument only needs the obj type and 
	// 		assumes the 'cf_sql_' part of the value.
	// arg.label String (optional) - The label for the return (only applies if returnType is 'json' or 'struct'). Default is 'queryData'
	// arg.returnType String (optional) - This is the type for the return. Can be 'json', 'query', 'struct', or 'array'. Default is 'query'.
	// arg.contentType String (optional) - Sets the Content-Type header for the return. Default is 'text/html'
	
	// create object
	variables.queryFormatter = createobject('component', getQuery).init(dsn);
	// call function and set a variable to the return
	variables.output = variables.queryFormatter.returnQuery({
		sql = '
			SELECT 
			  * 
			FROM
			  tbl_users 
			WHERE uid = :userid 
			  AND isactive = :isActive 
			',
		sqlParams = [
			{
				'name' = 'userid',
				'value' = '1',
				'type' = 'integer' // this will fill in the cfsqltype="cf_sql_#arg#" string in the queryparam
			},
			{
				'name' = 'isActive',
				'value' = '1',
				'type' = 'tinyint'
			}
		],
		label = 'getUsers',
		returnType = '#variables.showMe#',
		contentType = (variables.showMe == 'json') ? 'application/json' : '' // conditional contentType if json is returnType
	});
	
	// output the results (write if json else dump)
	(variables.showMe == 'json') ? writeoutput(variables.output) : writedump(variables.output);
</cfscript>