binding variable that might be NULL in Wt::Dbo::backend::MySQL
Added by John Davidson almost 11 years ago
I am having trouble in Wt::Dbo with binding variables that might be NULL.
A statement something like
queryModel->setQuery(dB->getSession()->query<FileData>
("SELECT b FROM bogans AS b")
.where("bogan_date = ?")
.bind(boganDate);
generates the MySQL query ".... where file_job_id = NULL" if boganDate happens to be NULL.
Now, MySQL wants the the query to be "IS NULL", not "= NULL". "= NULL" won't work.
Short of writing separate setQuery statements for every combination and permutation where a variable might be NULL (some queries have four or five bound variables, any or all of which could be NULL), using "IS" instead of "=", how do I fix this?
thanks
John
PS I'd can't replace NULLS with 0's in the database, because I'm mainly dealing with dates and I need to have NULL dates rather than zeroed dates.
Replies (2)
RE: binding variable that might be NULL in Wt::Dbo::backend::MySQL - Added by John Davidson almost 11 years ago
rather it would generate the MySQL query ".... where bogan_date = NULL". But same diff.
RE: binding variable that might be NULL in Wt::Dbo::backend::MySQL - Added by John Davidson almost 11 years ago
The solution was simple: I used the nullsafe operator "<=>" rather than "=" and it operated as hoped.
thus:
queryModel->setQuery(dB->getSession()->query<FileData>
("SELECT b FROM bogans AS b")
.where("bogan_date <=> ?")
.bind(boganDate);