[ColdBox] QueryBuilder Module (qb), possible bug, peculiar problem.

Communities ColdBox HMVC
1

Hi, I am having some issue with the querybuilder plugin here: https://www.forgebox.io/view/qb

It seems it might be a bug.

When I try to run the following in a handler:

// create date
rc.date = dateFormat(Now(),“yyyy-mm-dd”);

// update table
query.from(‘users’)
.whereUserID(rc.userID)
.update({
‘availableOn’ = ‘#rc.date#’

});

The field ‘availableOn’ in the database is a varchar, 12 characters long, despite this I keep getting a data truncation error, saying the value is too long. However, when I change the format to “yyyy,mm,dd” - that is, use commas instead of dashes, I have no problem at all. I do not want to store this in a date field, just a plain string.

Further, all of the following will work and will be committed to the database:

2019-da-da
2019-10-da
2019-10-3a
2019-10-26b

But as soon as I use this format:

2019-10-12

It throws this error:

Data truncation: Data too long for column ‘availableOn’ at row 1

This has nothing to do with the data being too long and must be some other issue to do with how querybuilder is executing the query.

Unless I have missed something.

Any assistance would be appreciated.

2

Further to the above, when I use the following, the normal query execution in cfscript:

rc.date = dateFormat(Now(),“yyyy-mm-dd”);

qryResult = queryExecute("
UPDATE users
SET availableOn = ‘#rc.availableOn#’
WHERE userID = #rc.userID#
");

The query executes as normal, no errors thrown.

3

qb is trying to guess your sqltype. You can override this by passing a query param struct. Try `{ value = rc.date, sqltype = “varchar” }` instead of just `rc.date`.

Cheers,
Eric

4

Thanks Eric.

I tried the following:

query.from(‘users’)

.whereUserID(rc.userID)
.update({
‘availableOn’ = ‘{ value = “#rc.availableOn#”, sqltype = “varchar” }’
});

But it threw the same data truncation error.

5

Dee,

You’re quoting the struct, which is making it a string and causing the truncation errors again.

Try it without the single quotes around the struct - so that it’s an actual object.

6

Thanks,

I already tried that first, but it gave the same error:

7

Did you format the date correctly? You can see the queries and bindings being executed by adding qb.models.Grammars to your logbox.debug array in config/ColdBox.cfc.

8

Eric, the query being executed is:

UPDATE users SET availableOn = {ts ‘2019-09-30 00:00:00’} WHERE UserID = 38008

So I think qb is taking the format “2019-09-30” and converting it into datetime field, even though the sql type is being sent as “varchar”. I need to store this just in plain text, and do not require a datetime format.

9

Sorry, use `cfsqltype`. Then it should respect your override.

Cheers,
Eric