QueryLazy()

edit

Executes a SQL query without loading the data entirely to memory. Instead it calls the given Function with every single row


	QueryLazy( sql=string, listener=function, params=any, options=struct );

Returns: void

Argument	Description
sql _{string, required}	edit SQL to execute
listener _{function, required}	edit listener function that will be called with the data of every single row. if returns false, execution is stopped.
params _{any, optional}	edit Array or Struct of parameter values. When passing an array use ? as place holders. When passing a struct use :keyName where keyName is the name of the key in the structure corresponding to the parameter. The struct or array can be a struct with keys such as the following: cfsqltype, list _{Alias: param}
options _{struct, optional}	edit Struct containing query options, all cfquery tag attributes are supported except the name attribute. _{Alias: option, queryOptions}

Examples

edit

Row at a time

The simplest form: the listener is called once per row, with a struct of column values.

queryLazy(
	sql: "select id, name from users order by id"
	,listener: function( row ){
		systemOutput( row.id & " " & row.name, true );
	}
);

Block processing with blockfactor

When blockfactor is greater than 1, the listener receives a query (or array/struct, see returnType) of up to blockfactor rows at a time. This is much faster than row-at-a-time when streaming output or batching writes.

queryLazy(
	sql: "select id, name from users order by id"
	,listener: function( rows ){
		// rows is a query containing up to 500 rows
		loop query=rows {
			// process each row
		}
	}
	,options: { blockfactor: 500 }
);

Aborting iteration

The listener can return false to stop iteration. The connection and cursor are closed cleanly.

var found = "";
queryLazy(
	sql: "select id, name from users order by id"
	,listener: function( row ){
		if ( row.name == "lucee" ) {
			found = row.id;
			return false;
		}
	}
);

returnType options

options.returnType can be query (default), array, or struct. With struct, you must also pass columnKey to specify which column is used as the struct key.

queryLazy(
	sql: "select id, name from users order by id"
	,listener: function( rows ){
		// rows is a struct keyed by id, e.g. { "1": { id: 1, name: "..." }, ... }
	}
	,options: {
		blockfactor: 100
		,returnType: "struct"
		,columnKey: "id"
	}
);

Bound parameters

Pass parameters as an array (with ? placeholders) or a struct (with :name placeholders), same as queryExecute().

queryLazy(
	sql: "select id, name from users where status = :status order by id"
	,listener: function( row ){
		systemOutput( row.name, true );
	}
	,params: { status: { value: "active", type: "varchar" } }
);