Query Listeners

Query Listeners in Lucee

A Query Listener is a hook which can be configured to run before and after a query is executed.

This is available as an experimental feature in Lucee 5.3 and is officially supported in Lucee 6.0.

This allows you to change the query arguments, including the SQL before it executes and then also modify the result before it's returned.

Remember, when working with the after() method, that a query can return a struct or array, not just a query object

They can be configured:

A Query Listener is a component with two methods, before() and after(). You can have other methods in your listener component, but Lucee will only call these two.

The Query Listener can be a struct(that has a key before/after with closure as value)/ closure (that calls after the query execution is completed)

this.query.listener = {
	before = function (caller,args) {
		systemOutput(arguments,1,1);
		dump(arguments);
		return args;
	}
	,after = function () {
		systemOutput(arguments,1,1);
		dump(arguments);
	}
};
OR
this.query.listener = function () {
	systemOutput(arguments, 1, 1);
	dump(arguments);
};

Examples

i.e. QueryListener.cfc

<cfscript>
component {
	function before( caller, args ) {
		dump( var=#arguments#, label="queryListener.before()" );
		args.sql &= " where TABLE_NAME like 'SCH%'"; // modify the sql statement before it executes
		// args.maxrows=2;
		return arguments;
	}
	function after( caller, args, result, meta ) { 
        // remember, result maybe a query, array or struct, check the args!
		dump( var=#arguments#, label="queryListener.after()" );
		// var row=queryAddRow(result);
		// result.setCell("abc","123",row);
		return arguments;
	}
}
</cfscript>

To add an Application wide query listener, add the following to your Application.cfc

this.query.listener = new QueryListener();

To add a query listener to an individual <cfquery>

<cfset listener = new QueryListener()>
<cfquery name="qry" datasource="local_mysql" listener=#listener#>
    SELECT  COLUMN_NAME, TABLE_NAME F
    FROM    INFORMATION_SCHEMA.COLUMNS
</cfquery>

Or an individual QueryExecute()

<cfscript>
    qry = queryExecute(
        sql="SELECT COLUMN_NAME, TABLE_NAME
        FROM INFORMATION_SCHEMA.COLUMNS",
        options={
            datasource : "local_mysql",
            listener=new QueryListener()
        }
    );
</cfscript>

`

Dump of arguments to the query Listener before() method

Query Listener Before()

Dump of arguments to the query Listener after() method (cfquery)

Query Listener After()

Dump of arguments to the query Listener after() method (queryExecute)

Query Listener After()

Sample query result

Query Listener Result

See also