# sqlCommenter qb supports the [sqlCommenter specification by Google](https://google.github.io/sqlcommenter/) for appending contextual information to executed queries. sqlCommenter support is **off** by default, but can be activated by setting the `sqlCommenter.enabled` setting. ```cfscript 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()`](#tosql) or [`dump()`](#dump). {% hint style="warning" %} sqlCommenter will only add a comment to **non-commented** queries. If you query contains a comment anywhere in it, sqlCommenter will ignore it. {% endhint %} An example query with a sqlCommenter comment looks like this: {% code overflow="wrap" %} ```sql 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'*/ ``` {% endcode %} ### Configuring sqlCommenter The default configuration structure for sqlCommenter is as follows: ```cfscript 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`: ```cfscript 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](https://forgebox.io) 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. ```cfscript 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 | Name | Type | Required | Default | Description | | ---- | ------ | -------- | ------- | ---------------------------------- | | sql | string | `true` | ​ | The commented SQL string to parse. | Parses a commented SQL string into the SQL and a struct of the key/value pair comments. {% code overflow="wrap" %} ```cfscript 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'*/" ); ``` {% endcode %} {% code title="Result" %} ```json { "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": "/" } } ``` {% endcode %} #### parseCommentString | Name | Type | Required | Default | Description | | ------------- | ------ | -------- | ------- | ------------------------------------------ | | commentString | string | `true` | ​ | The comment string to parse into a struct. | Parses a comment string into a struct. {% code overflow="wrap" %} ```cfscript 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'*/" ); ``` {% endcode %} {% code title="Result" %} ```json { "action": "index", "dbDriver": "mysql-connector-java-8.0.25 (Revision: 08be9e9b4cba6aa115f9b27b215887af40b159e0)", "event": "Main.index", "framework": "coldbox-6.0.0", "handler": "Main", "route": "/" } ``` {% endcode %} ### 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](https://forgebox.io). --- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://qb.ortusbooks.com/query-builder/debugging/sqlcommenter.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.