Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Execute queries that return multiple result sets
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;
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment