Debugging a Single Query

toSQL

Returns the SQL that would be executed for the current query.

var q = query.from( "users" )
    .where( "active", "=", 1 );

writeOutput( q.toSQL() );

SELECT * FROM "users" WHERE "active" = ?

The bindings for the query are represented by question marks (?) just as when using queryExecute. qb can replace each question mark with the corresponding cfqueryparam-compatible struct by passing showBindings = true to the method.

var q = query.from( "users" )
    .where( "active", "=", 1 );

writeOutput( q.toSQL( showBindings = true ) );

SELECT * FROM "users" WHERE "active" = {"value":1,"cfsqltype":"CF_SQL_NUMERIC","null":false}

If you want to show the SQL that would be executed for the update, insert, updateOrInsert, or delete methods, you can pass a toSQL = true flag to those methods. Please see those individual methods for more information.

To get back a SQL string that can be copied and pasted into a SQL client to run can be retrieved by passing showBindings = "inline".

var q = query.from( "users" )
    .where( "active", "=", 1 );

writeOutput( q.toSQL( showBindings = "inline" ) );

SELECT * FROM "users" WHERE "active" = 1

tap

Executes a callback with a clone of the current query passed to it. Any changes to the passed query is ignored and the original query returned.

While not strictly a debugging method, tap makes it easy to see the changes to a query after each call without introducing temporary variables.

query.from( "users" )
    .tap( function( q ) {
        writeOutput( q.toSQL() & "<br>" );
    } )
    .where( "active", "=", 1 )
    .tap( function( q ) {
        writeOutput( q.toSQL() & "<br>" );
    } );

SELECT * FROM "users"
SELECT * FROM "users" WHERE "active" = ?

dump

A shortcut for the most common use case of tap. This forwards on the SQL for the current query to writeDump. You can pass along any writeDump argument to dump and it will be forward on. Additionally, the showBindings argument will be forwarded on to the toSQL call.

query.from( "users" )
    .dump()
    .where( "active", "=", 1 )
    .dump( label = "after where", showBindings = true, abort = true )
    .get();

SELECT * FROM "users"
SELECT * FROM "users" WHERE "active" = ?

pretend

A QueryBuilder instance can be put into pretend mode by calling the pretend method. In this mode, the QueryBuilder will turn all query operations into no-ops. A log of the SQL that would have been executed can be retrieved from the query log.

Once a QueryBuilder instance has been set to pretend mode, it cannot be unset. Instead, you will need to obtain a new query.

queryLog

Each instance of a QueryBuilder maintains a log of queries it executed. This can be accessed by calling getQueryLog. This will return an array of structs like so:

[
  {
    "sql": "SELECT * FROM `users` WHERE `active` = ?",
    "bindings": [ { "value": 1, "sqltype": "bit" } ],
    "options": { "datasource": "main" },
    "returnObject": "array",
    "pretend": false,
    "result": {},
    "executionTime": 21
  }
]

This can be very useful in combination with the pretend feature to see what SQL will be executed before actually executing it.

Debugging All Queries

sqlCommenter

You can add contextual information as a comment to all executed queries using sqlCommenter, a specification from Google.

cbDebugger

Starting in cbDebugger 2.0.0 you can view all your qb queries for a request. This is enabled by default if you have qb installed. Make sure your debug output is configured correctly and scroll to the bottom of the page to find the debug output.

LogBox Appender

qb is set to log all queries to a debug log out of the box. To enable this behavior, configure LogBox to allow debug logging from qb's grammar classes.

logbox = {
    debug = [ "qb.models.Grammars" ]
};

ColdBox Interception Points

ColdBox Interception Points can also be used for logging, though you may find it easier to use LogBox. See the documentation for qb's Interception Points for more information.

qb supports the sqlCommenter specification by Google for appending contextual information to executed queries.

sqlCommenter support is off by default, but can be activated by setting the sqlCommenter.enabled setting.

moduleSettings = {
    "qb": {
        "sqlCommenter": {
            "enabled": true
        }
    }
};

Once enabled, qb will append a comment on to every non-commented query. This happens as the query is ran, so you will not see this output when calling toSQL() or dump().

An example query with a sqlCommenter comment looks like this:

SELECT * FROM foo /*action='index',dbDriver='mysql-connector-java-8.0.25%20%28Revision%3A%2008be9e9b4cba6aa115f9b27b215887af40b159e0%29',event='Main.index',framework='coldbox-6.0.0',handler='Main',route='%2F'*/

Configuring sqlCommenter

The default configuration structure for sqlCommenter is as follows:

settings = {
    "sqlCommenter": {
        "enabled": false,
        "commenters": [
            { "class": "FrameworkCommenter@qb", "properties": {} },
            { "class": "RouteInfoCommenter@qb", "properties": {} },
            { "class": "DBInfoCommenter@qb", "properties": {} }
        ]
    }
};

When the enabled flag is false, no comments will be appended.

The commenters array are the different components that will add contextual information to each query. You define them by defining a struct with a class key pointing to a WireBox mapping and a properties key containing a struct of any necessary properties.

Commenters

Each Commenter must implement the ICommenter interface. (The implements keyword is not required.). They will be called with the sql being commented and the current datasource. It should return a struct of key/value pairs that will become comments.

Here is an example of the FrameworkCommenter@qb:

component singleton accessors="true" {

    property name="coldboxVersion" inject="coldbox:coldboxSetting:version";

    property name="properties";

    /**
     * Returns a struct of key/value comment pairs to append to the SQL.
     *
     * @sql         The SQL to append the comments to. This is provided if you need to
     *              inspect the SQL to make any decisions about what comments to return.
     * @datasource  The datasource that will execute the query. If null, the default datasource will be used.
     *              This can be used to make decisions about what comments to return.
     */
    public struct function getComments( required string sql, string datasource ) {
        return { "version": "coldbox-#variables.coldboxVersion#" };
    }

}

You may use any. all, or none of the commenters provided by qb. You may also create your own for your application. You may even see commenters pop up on ForgeBox for popular use cases.

For example, if you use cbauth (or cbsecurity using cbauth), this commenter will add the current user ID to each query.

component singleton accessors="true" {

    property name="auth" inject="AuthenticationService@cbauth";

    property name="properties";

    /**
     * Returns a struct of key/value comment pairs to append to the SQL.
     *
     * @sql         The SQL to append the comments to. This is provided if you need to
     *              inspect the SQL to make any decisions about what comments to return.
     * @datasource  The datasource that will execute the query. If null, the default datasource will be used.
     *              This can be used to make decisions about what comments to return.
     */
    public struct function getComments( required string sql, string datasource ) {
        return { "userId": variables.auth.getUserId() };
    }

}

Parsing Commented SQL

The comment generated by sqlCommenter is escaped and url-encoded. It can be reversed by calling the SQLCommenter.parseCommentedSQL method with the full query or the SQLCommenter.parseCommentString method with just the comment.

parseCommentedSQL

Parses a commented SQL string into the SQL and a struct of the key/value pair comments.

getInstance( "ColdBoxSQLCommenter@qb" )
    .parseCommentedSQL( "SELECT * FROM foo /*action='index',dbDriver='mysql-connector-java-8.0.25%20%28Revision%3A%2008be9e9b4cba6aa115f9b27b215887af40b159e0%29',event='Main.index',framework='coldbox-6.0.0',handler='Main',route='%2F'*/" );

{
    "sql": "SELECT * FROM foo",
    "comments": {
        "action": "index",
        "dbDriver": "mysql-connector-java-8.0.25 (Revision: 08be9e9b4cba6aa115f9b27b215887af40b159e0)",
        "event": "Main.index",
        "framework": "coldbox-6.0.0",
        "handler": "Main",
        "route": "/"
    }
}

parseCommentString

Parses a comment string into a struct.

getInstance( "ColdBoxSQLCommenter@qb" )
    .parseCommentString(
        "/*action='index',dbDriver='mysql-connector-java-8.0.25%20%28Revision%3A%2008be9e9b4cba6aa115f9b27b215887af40b159e0%29',event='Main.index',framework='coldbox-6.0.0',handler='Main',route='%2F'*/"
    );

{
    "action": "index",
    "dbDriver": "mysql-connector-java-8.0.25 (Revision: 08be9e9b4cba6aa115f9b27b215887af40b159e0)",
    "event": "Main.index",
    "framework": "coldbox-6.0.0",
    "handler": "Main",
    "route": "/"
}

Integration with non-ColdBox applications

Out of the box, qb includes a ColdBoxSQLCommenter. Since sqlCommenter adds contextual information, some level of framework or application integration is necessary. You can create your own sqlCommenter instance by extending the qb.models.SQLCommenter.SQLCommenter abstract component. If you create a SQLCommenter for a specific framework, consider sharing it with others on ForgeBox.