QB tries to automatically figure out what ColdFusion SQL type to use for its updates and inserts based on the actual value that is passed in for each column. Although that works for most of the cases, as I am using more of this awesome module I ran into an issue where my value type was being wrongly inferred, so I needed to figure out how to override it.
I ran into this issue while working on a project where my database column type was a VARCHAR but needed to store numbers, but I imagine this can happen with other value types as well, like booleans being stored as 1 rather than true. There are many ways to issue updates and inserts through QB, but I using what I like most, which is to pass a struct of columns-values to update. To show the issue in code, here is a ColdBox component that acts as a Data Access Object (DAO) for my card entity, using the QB update() function (which accepts a struct with key-value pairs of database column names and values).
In my case I am trying to save the value 000000000000020005060720116005061, so here is my ColdBox Service component calling the DAO and trying to pass the weird looking string.
Based on the string with the leasing zeros, QB was inferring the type as CF_SQL_NUMERIC, truncating the leading zeros and storing the value 20005060720116005061 in my database column instead. If I add an interceptor to QB and look at what it passes to SQL I can clearly see the issue.
So, it thinks the type is a CF_SQL_NUMERIC instead of a CF_SQL_VARCHAR. This reminds me of ACF serialization issues, and who wants to deal with that, right?
After some chatting on Slack and John Whish‘s wisdom put to work, I managed to fix this issue by passing a struct to QB, containing all the keys that it uses internally (which are really the same as cfqueryparam‘s):
- cfsqltype
- list
- null
- value
By explicitly setting the cfsqltype = “CF_SQL_VARCHAR” then QB accepts that as the source of truth and overrides the inferred type.
My query then worked as expected and saved the value properly in the DB. I tried to make my code simpler by passing just the cfsqltype key for the binding, but QB needed all four of the binding property keys to work.
The conclusion to take home here is that we can override the inferred QB types by passing in any of the cfqueryparam cfsqltype values.