Coldbox and VueJS untangled

Tag: qb

qb: Autodetecting your bind variables in SQL

In an ideal world, everyone is using qb or quick, and you really don’t know what a bind variable is. Before you discovered this ideal world, maybe you were using queryExecute and were executing queries like this one.

var q =queryExecute("Select * from users where userId = #url.UserId#");

This kind of code ( don’t do this in production ! ) is wide open for sql injection attacks. This post is not about sql injection, so we assume you were already so smart to use queryparams, so something similar to this.

var q = queryExecute(
  sql="Select * from users where userId = :myId",
  params =  { myId: { value = rc.UserId, cfsqlType = "integer" }
)
Continue reading

Order of elements in a qb array of structs

Recently someone asked in the coldbox Slack channel if the keys of a struct are always accessed in the same order. The answer is usually no , but if you know which keys are present it is quite simple to access them in the right order. But if you really want the keys in a fixed order you can create your structs this way:

MyOrderedStruct= structNew( "ordered"); 

Although the key order is seldom important we encountered such a case. We were building a control panel in VueJS where we displayed a list of DNS records based on a JSON array of structs.
These records can be exported to a comma delimited file, but since we were doing the export in some standard Vue component we were limited in the output format of the file. Keys showed up in the same order as in the JSON structs, which was undesirable for our clients.

Our output was based on a qb (QueryBuilder) query were the results were returned as an array of structs. Let’s say we were querying for id and name in our query, e.g:

return qb.from("records")
  .select("id,name")
  .get()
//wen serializing to json the result might look like this
//but we want id,name order instead of name,id
"data": [
      {
          "name": "SomeName",
          "id": "297A83FD-1715-416F-801B-44BE1743443A",
      },
      {
          "name": "Another item",
          "id": "892FECBC-84FA-4C5E-B494-525C3712F6A2"
      }
] 

If we want to change the order of the struct keys we could do some mapping on the array, e.q.

return items.map(function(item){
  var newitem = structNew( "ordered" );
  newitem.id = item.id;
  newitem.name = item.name;
  return newitem;
});

but this is quite inefficient for larger datasets. Qb is converting all queries to an array of structs, and we are creating structs again based on the qb results. Fortunately qb has a handy feature where you can define your own output format. In this case the queries in qb are directly converted to an array of ordered structs. The following code snippet was suggested by qb author Eric Peterson. Thanks Eric!

// CF2016+ and/or Lucee 5+
moduleSettings = {
    qb = {
        returnFormat = function( q ) {
            return queryReduce( arguments.q, function( acc, row ) {
                var rowStruct = structNew( "ordered" );
                for ( var columnName in q.columnList ) {
                    rowStruct[ columnName ] = row[ columnName ];
                }
                acc.append( rowStruct );
                return acc;
            }, [] );
        }
    }
}

Inserting NULL values in qb

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 },
		} )

© 2020 ShiftInsert.nl

Theme by Anders NorenUp ↑