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:
- application wide, via Application.cfc / <cfapplication> i.e.
Application.cfc - per <cfquery> or QueryExecute()
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 {
<span class="nv">function</span> <span class="nf">before</span><span class="p">(</span> <span class="nv">caller</span><span class="p">,</span> <span class="nv">args</span> <span class="p">)</span> <span class="p">{</span>
<span class="nf">dump</span><span class="p">(</span> <span class="k">var</span><span class="o">=</span><span class="err">#</span><span class="nv">arguments</span><span class="err">#</span><span class="p">,</span> <span class="nv">label</span><span class="o">=</span><span class="s2">"queryListener.before()"</span> <span class="p">);</span>
<span class="nv">args.sql</span> <span class="o">&=</span> <span class="s2">" where TABLE_NAME like 'SCH%'"</span><span class="p">;</span> <span class="c">// modify the sql statement before it executes</span>
<span class="c">// args.maxrows=2;</span>
<span class="nv">return</span> <span class="nv">arguments</span><span class="p">;</span>
<span class="p">}</span>
<span class="nv">function</span> <span class="nf">after</span><span class="p">(</span> <span class="nv">caller</span><span class="p">,</span> <span class="nv">args</span><span class="p">,</span> <span class="nv">result</span><span class="p">,</span> <span class="nv">meta</span> <span class="p">)</span> <span class="p">{</span>
<span class="c">// remember, result maybe a query, array or struct, check the args!</span>
<span class="nf">dump</span><span class="p">(</span> <span class="k">var</span><span class="o">=</span><span class="err">#</span><span class="nv">arguments</span><span class="err">#</span><span class="p">,</span> <span class="nv">label</span><span class="o">=</span><span class="s2">"queryListener.after()"</span> <span class="p">);</span>
<span class="c">// var row=queryAddRow(result);</span>
<span class="c">// result.setCell("abc","123",row);</span>
<span class="nv">return</span> <span class="nv">arguments</span><span class="p">;</span>
<span class="p">}</span>
}
</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
Dump of arguments to the query Listener after() method (cfquery)
Dump of arguments to the query Listener after() method (queryExecute)
Sample query result
