I can highly recommend the qb library for all kind of database manipulations, for querying and data definitions. Qb hides a lot of complexities for database actions, and abstracts away differences between database engines.
Although very powerful, some options are really well hidden in the documentation or the source code. Recently I wanted to update a database table with null values. The normal syntax for updating values in a database table is:
query.from( "users" )
.update( {
"email" = "foo",
"name" = "bar"
} );
If you have more complex requirements like inserting date values in the correct formats, you can specify query param options, e.g
query.from( "users" )
.update( {
"email" = "foo",
"name" = "bar",
"updatedDate" = { value = now(), cfsqltype = "CF_SQL_TIMESTAMP" }
} );
This query param syntax comes in handy when inserting NULL values. As we all know older versions of Adobe Coldfusion and Lucee can’t handle null values very well:
query.from("user")
.whereId( 10 )
.update( {
manager_FK = { value = "", null=true },
} )
If you are using Lucee with full Null support you can leave out the null parameter, e.g
query.from("user")
.whereId( 10 )
.update( {
manager_FK = { value = null },
} )
Recent Comments