Search…
8.7.0
Query Builder
Schema Builder
External Links
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
1
query.from( "users" )
2
.where( "id", "=", { value = 18, cfsqltype = "CF_SQL_VARCHAR" } );
Copied!
MySQL
1
SELECT *
2
FROM `users`
3
WHERE `id` = ?
Copied!
This can be used when inserting or updating records as well.
QueryBuilder
1
query.table( "users" )
2
.insert( {
3
"id" = { value 1, cfsqltype = "CF_SQL_VARCHAR" },
4
"age" = 18,
5
"updatedDate" = { value = now(), cfsqltype = "CF_SQL_DATE" }
6
} );
Copied!
MySQL
1
INSERT INTO `users`
2
(`id`, `age`, `updatedDate`)
3
VALUES
4
(?, ?, ?)
Copied!
Strict Date Detection
By default, qb will try to determine if a variable is a date using the built-in
isDate function. This can have some interesting effects with different formatted strings. You can opt in to stricter date detection which will check the underlying Java class of the value to determine if the value is a date. This is more accurate, but does require you to specifically pass date instances instead of strings. For this reason, it is currently opt-in to not break existing applications. It is likely to become the default in the next major version of qb.You can opt in to stricter date detection by setting
strictDateDetection = true in your moduleSettings in config/ColdBox.cfc.1
moduleSettings = {
2
"qb": {
3
"strictDateDetection": true
4
}
5
};
Copied!
Numeric SQL Type
By default, qb will use the
CF_SQL_NUMERIC SQL type when it detects a numeric binding. You can specify your own default SQL type to use with numeric values using the numericSQLType setting in your moduleSettings in config/ColdBox.cfc.1
moduleSettings = {
2
"qb": {
3
"numericSQLType": "CF_SQL_INTEGER"
4
}
5
};
Copied!
Automatic Scale Detection
In some combinations of database grammars and CFML engines, the
scale argument on a cfqueryparam would default to 0. This would cause issues when attempting to insert a floating point number, even when using the correct SQL type (i.e., CF_SQL_DECIMAL) . In 8.5.0, qb now automatically calculates a scale based on the value provided if the value is a floating point number. This can be disabled by setting autoAddScale in your ColdBox config or passing autoAddScale = false when instantiating your QueryBuilder instance.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
1
query.from( "users" )
2
.join( "logins", function( j ) {
3
j.on( "users.id", "logins.user_id" );
4
j.where( "logins.created_date", ">", dateAdd( "m", -1, "01 Jun 2019" ) );
5
} )
6
.where( "active", 1 );
Copied!
Result
1
[
2
{ value = "01 May 2019", cfsqltype = "CF_SQL_TIMESTAMP" },
3
{ value = 1, cfsqltype = "CF_SQL_NUMERIC" }
4
]
Copied!
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
1
query.from( "users" )
2
.join( "logins", function( j ) {
3
j.on( "users.id", "logins.user_id" );
4
j.where( "logins.created_date", ">", dateAdd( "m", -1, "01 Jun 2019" ) );
5
} )
6
.where( "active", 1 );
Copied!
Result
1
{
2
"commonTables" = [],
3
"select" = [],
4
"join" = [
5
{ value = "01 May 2019", cfsqltype = "CF_SQL_TIMESTAMP" },
6
],
7
"where" = [
8
{ value = 1, cfsqltype = "CF_SQL_NUMERIC" }
9
],
10
"union" = [],
11
"insert" = [],
12
"insertRaw" = [],
13
"update" = []
14
};
Copied!
Last modified 11mo ago
Copy link
Edit on GitHub