Execute queries that return multiple result sets

MultiRecordSetQuery.cfc

component output="false" singleton="true" {

	/**
	*
	* Constructor
	*
	*/
	public any function init() {

		var serviceFactory = createObject("java", "coldfusion.server.ServiceFactory");
		variables.datasourceService = serviceFactory.getDataSourceService();
		
		return this;
	}
	

	/**
	*
	* Execute a multiple record set query
	*
	* @sql.hint string containing sql to execute
	* @params.hint structure of sql parameters
	* @datasource.hint datasource name
	*
	*/
	public array function execute(
		required string sql,
		required struct params,
		required string datasource
	) {

		var results = [];

		var conn = datasourceService.getDatasource(arguments.datasource).getConnection();
		
		var newSQL = reReplaceNoCase(arguments.sql,"\s\:([a-zA-Z])+"," ?","all");
		/* This is needed in TSQL when using insert statements otherwise we get no results */
		newSQL = "SET NOCOUNT ON; " & newSQL;
		var stmt = conn.prepareStatement(newSQL);

		parseNamedParameters(
			sql = arguments.sql,
			params = arguments.params,
			stmt = stmt
		);

		var hasNext = stmt.execute();

		/* Loop while we still have results */
		while (hasNext) {
			/* Get the result set */
			var rs = stmt.getResultSet();
			/* get metadata for column names */
			var meta = rs.getMetaData();
			/* create a column list for the query result set */
			var columnCount = meta.getColumnCount();
			var columns = "";
			for (i = 1; i <= columnCount; i++) {
				columns = listAppend(columns,meta.getColumnLabel(i));
			}
			/* Create query */
			var q = queryNew(columns);
			/* Populate rows */
			while (rs.next()) {
				queryAddRow(q);
				for (col in columns) {
					querySetCell(q, col, rs.getString(col));
				}
			}
			results.append(q);
			hasNext = stmt.getMoreResults();
		}
		
		stmt.close();

		return results;
	}

	/**
	*
	* Parses parameters
	*
	* argument hints @arg.hint my hint
	*
	*/
	private void function parseNamedParameters(
		required string sql,
		required struct params,
		required any stmt
	) {
		
		var parameters = reMatchNoCase("\s\:([a-zA-Z])+",arguments.sql);

		parameters.each(function(item,index) {
			var paramName = mid(trim(item),2,item.len());
			if (structKeyExists(params,paramName)) {
				var param = params[paramName];
				if (isSimpleValue(param)) {
					setParam(stmt,index,getType(param),param);
				}
				else if (isStruct(param)) {
					/* Get rid of cf_sql_ */
					var type = replaceNoCase(param.cfsqltype,"cf_sql_","");
					setParam(stmt,index,type,param.value);
				}
			}
		});
	}

	/**
	*
	* Set a sql parameter
	*
	* @stmt.hint Java PreparedStatement
	* @index.hint Parameter index in sql statement
	* @type.hint SQL data type
	* @value.hint value to set the parameter to
	*
	*/
	private void function setParam(
		required any stmt,
		required numeric index,
		required string type,
		required any value
	) {
		
		switch(arguments.type) {
		case "integer":
			arguments.stmt.setInt(arguments.index,javacast("int",arguments.value));
		break; 
		case "varchar": case "longvarchar":
			arguments.stmt.setString(arguments.index,javacast("string",arguments.value));
		break; 
		case "nvarchar":
			arguments.stmt.setNString(arguments.index,javacast("string",arguments.value));
		break; 
		case "timestamp":
			var ts = createObject("java","java.sql.Timestamp").init(parseDateTime(arguments.value).getTime());
			arguments.stmt.setTimestamp(arguments.index,ts);
		break;
		case "date":
			arguments.stmt.setDate(arguments.index,arguments.value);
		break;
		case "bit":
			arguments.stmt.setBoolean(arguments.index,javacast("boolean",arguments.value));
		break;
		case "float":
			arguments.stmt.setFloat(arguments.index,javacast("float",arguments.value));
		break;
		case "double":
			arguments.stmt.setDouble(arguments.index,javacast("double",arguments.value));
		break;
		case "long":
			arguments.stmt.setLong(arguments.index,javacast("long",arguments.value));
		break;
		case "decimal":
			arguments.stmt.setBigDecimal(arguments.index,javacast("bigdecimal",arguments.value));
		break;
		default:
			arguments.stmt.setString(arguments.index,javacast("string",arguments.value));
		}		
	}

	/**
	*
	* Get the match sql type
	*
	* @value.hint Value to get the type for
	*
	*/
	private string function getType(required any value) {
		var type = "varchar";

		if (isValid("integer",arguments.value)) {
			type = "integer";
		}
		else if (isDate(arguments.value)) {
			type = "timestamp";
		}
		else if (isNumeric(arguments.value)) {
			type = "float";
		}
		/* This needs to go after the numerical check because they can be boolean as well */
		else if (isBoolean(arguments.value)) {
			type = "bit";
		}

		return type;
	}
	
		
}