Query Parameters and Bindings

Custom Parameter Types
When passing a parameter to qb, it will infer the sql type to be used.  If you pass a number, CF_SQL_NUMERIC will be used. If it is a date, CF_SQL_TIMESTAMP, and so forth. If you need more control, you can pass a struct with the parameters you would pass to cfqueryparam.
You can pass include any parameters you would use with cfqueryparam including null, list, etc.  This applies anywhere parameters are used including where, update, and insert methods.
QueryBuilder
query.from( "users" )
    .where( "id", "=", { value = 18, cfsqltype = "CF_SQL_VARCHAR" } );
MySQL
SELECT *
FROM `users`
WHERE `id` = ?
This can be used when inserting or updating records as well.
QueryBuilder
query.table( "users" )
    .insert( {
        "id" = { value 1, cfsqltype = "CF_SQL_VARCHAR" },
        "age" = 18,
        "updatedDate" = { value = now(), cfsqltype = "CF_SQL_DATE" }
    } );
MySQL
INSERT INTO `users`
    (`id`, `age`, `updatedDate`)
VALUES
    (?, ?, ?)
Bindings
Bindings are the values that will be sent as parameters to a prepared SQL statement.  This protects you from SQL injection.  In CFML, this uses cfqueryparam to parameterize the values.
If you need to inspect the bindings for the current query you can retrieve them in order using the getBindings method.
You can view the current SQL for the query with bindings inline for debugging purposes using the toSQL method.
 Use these methods only for debugging. Modifying the bindings directly will likely cause issues when executing your query.  Adding or removing bindings should be done using the public API.
getBindings
Name
Type
Required
Default
Description
No arguments
​
​
​
​
This method returns the current bindings in order to be used for the query.
QueryBuilder
query.from( "users" )
    .join( "logins", function( j ) {
        j.on( "users.id", "logins.user_id" );
        j.where( "logins.created_date", ">", dateAdd( "m", -1, "01 Jun 2019" ) );
    } )
    .where( "active", 1 );
Result
[
    { value = "01 May 2019", cfsqltype = "CF_SQL_TIMESTAMP"  },
    { value = 1, cfsqltype = "CF_SQL_NUMERIC" }
]
You can also retrieve the bindings associated to their corresponding types.
getRawBindings
Name
Type
Required
Default
Description
No arguments
​
​
​
​
This method returns the current bindings  to be used for the query associated to their corresponding types.
QueryBuilder
query.from( "users" )
    .join( "logins", function( j ) {
        j.on( "users.id", "logins.user_id" );
        j.where( "logins.created_date", ">", dateAdd( "m", -1, "01 Jun 2019" ) );
    } )
    .where( "active", 1 );
Result
{
    "commonTables" = [],
    "select" = [],
    "join" = [
        { value = "01 May 2019", cfsqltype = "CF_SQL_TIMESTAMP"  },
    ],
    "where" = [
        { value = 1, cfsqltype = "CF_SQL_NUMERIC" }
    ],
    "union" = [],
    "insert" = [],
    "insertRaw" = [],
    "update" = []
};
When / Conditionals
Next - Query Builder
Executing Queries
Last updated 10 months ago