I’ve been getting a broken pipe and non existent MySql database entries in a conversion from ACF10 to Lucee 5. I’m making the code work on both systems.
The issue is in the CFQUERY area.
I’ve located the problem but am not sure how to solve it short of querying the server to see if it is lucee or coldfusion which does solve the problem but does not seem very elegant. It only fails on INSERT.
‘#QMovement.DeliveryDateTime#’ works with Coldfusion
No not for this field - I saw that this had been mentioned in a previous post but I’m using an internal variable. Do I need to cfqueryparam everything in a sql statement?
This is still not working, but I’m probably missing something! I’ve tried lots of combinations of the quotes but nada. It is the same database just one system using ACF10 and the other using Lucee.
If I put a ’ each side of the # sign as well as the " I get
ERROR can’t cast [‘{ts ‘2022-08-15 06:30:00’}’] to date value. This does not happen if there are no single quotes
Re-reading the error, it sounds like the data type is date. Try setting cfsqltype=“cf_sql_date” and see if that helps. If not, adding the formatting should do it for you.
Looks like your date isn’t representing a date type. Did you try dumping QMovement.DeliveryDateTime just to see what it tells you?
For security reasons, I’d do it with every variable because your application (as many default cfml apps) might be open for scope injection, making your app vulnerable for SQL injection.
If the data type in the column is correct, there’s no reason in my experience that cfqueryparam should not work (no formatting should be needed either).
Double check the table schema. The issue you report doesn’t make sense.
Last suggestion,
Post the insert query. You know what you have, we’re helping blind and making wrong assumptions.
If you’re worried about giving too much info, swap the table column/variable names - it’s the details in the syntax that matters most which hides frustrating not obvious errors.
I believe you mentioned MySQL. There’s a syntax with backticks to wrap the table name and columns (not required) which is not the same as the single quotes used around strings. Weird.
Maybe there’s a subtle difference that one DB driver handles where the other does not?
This is the suggested syntax hint I’m seeing
INSERT INTO `tbl` (`col_timestamp`) VALUES ( '#ts#' );
Usually I would skip the backticks and write this as:
Despite isDate() works for a timestamp Object AND a string - it doesn’t really help us here.
Because for a timestamp you could extract the “parts” and recreate it to a “known” format.
But you can’t use the same for a string
myDate = {ts '2022-08-25 06:30:00'};
you could use mydate.year() / mydate.month() / etc to get to a known format. myNewDate = createDatetime(year = mydate.year(), ......
Unfortunately you cannot use *.day() / *.month()… on a string.
so if you start with a string not an object; myDate = '2022-08-25 06:30:00';
then myNewDate = createDatetime(year = mydate.year(), ......
fails - telling you cannot use .year() / etc on a string.
I am sure there has got to be a better way to do it - but you could use parseDateTime in a try/catch.
I created a GIST here that shows the same code works whether you start with a string or dateObject.
(it works with ACF and Lucee)
But it seems like a lot of work to place this everywhere you need it.
Perhaps my not-so-perfect idea will spawn something more workable by you or someone else!
Yes - interesting. I think I had a bug in the code while testing the queryparam that I had set up. Using the reverted code with the queryparam now works with both servers. No date formatting is required.
