Jason Steinshouer

about blog projects

Return Multiple Record Sets from a Query in Coldfusion

In Adobe Coldfusion you can only return one record set from using cfquery or queryExecute. If you need to return multiple record sets then you can write a stored procedure and use cfstoredproc.

Why is this needed?

Recently my team has started using Table-Valued Prameters which is a pre-defined table type in SQL Server that you can pass as a parameter. Here is an example where we use dynamic sql to populate a table-valued prameter and pass it to the stored procedure.

var sql = createObject("java","java.lang.StringBuffer").init("
	DECLARE @employee_list udt_list_integer;
");

for (var employeeID in arguments.employees) {
	sql.append("INSERT INTO @employee_list VALUES(#employeeID#);");
}

sql.append("
	EXEC spu_my_stored_proc
		@start_date = :startDate,
		@end_date = :endDate,
		@employee_list = @employee_list
");

var results = queryExecute(sql,{
	startDate = {cfsqltype="cf_sql_timestamp",value=arguments.startDate},
	endDate = {cfsqltype="cf_sql_timestamp",value=arguments.endDate}
}, datasource="MyDSN");

This works great if your stored procedures only return one record set. However recently we built one that needed to return mutiple result sets.

I do realize I could also do the dynamic sql inside the stored procedure but I perfer using CFML for this.

Solution

After spending some time playing with the underlying Coldusion java libraries I was able to come up with a component that would allow me to execute a query and have it return mutiple result sets. I thought I would throw this out there in case this is useful for someone else.

Disclaimer: This has only been testing using Adobe Coldfusion 11 and MS SQL Server 2008. Also, currently it does not support all the same features as queryExecute. For example, it doesn’t support lists at the moment but that could be added.

MutliRecordSetQuery.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;
}
}

The previous example would now look like this.

var multiRecordSetQuery = new MultiRecordSetQuery();

var sql = createObject("java","java.lang.StringBuffer").init("
	DECLARE @employee_list udt_list_integer;
");

for (var employeeID in arguments.employees) {
	sql.append("INSERT INTO @employee_list VALUES(#employeeID#);");
}

sql.append("
	EXEC spu_my_stored_proc
		@start_date = :startDate,
		@end_date = :endDate,
		@employee_list = @employee_list
");

var results = multiRecordSetQuery.execute(sql,{
	startDate = {cfsqltype="cf_sql_timestamp",value=arguments.startDate},
	endDate = {cfsqltype="cf_sql_timestamp",value=arguments.endDate}
}, "MyDSN");