Database Date Quotes - ACF10 -> Lucee 5

Hi

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

#QMovement.DeliveryDateTime# works with Lucee

I’ve tried various combinations but none work!

Any thoughts?

OS: MacOS - Monterey 12.4
Java Version: Express
Tomcat Version: Express
Lucee Version: Latest

Are you using cfqueryparam?

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 my value for INSERT

<cfqueryparam value="#QMovement.DeliveryDateTime#" cfsqltype="cf_sql_timestamp">,

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

What’s the data type in the table?
Date or timestamp?
Here’s a cheat sheet: CFSqlType Cheatsheet CFML Documentation

I believe formatting is what you’re after for the code to work - based on my understanding of the error.

Using queryparam is good practice for preventing SQL Injection and reducing the number of query plans.

  • The only time I skip this is for a hard coded value where deleted = 0 for example.

A couple ideas without knowing the data type:

<cfqueryparam cfsqltype="cf_sql_timestamp" value="#dateTimeFormat(now(), "yyyy-mm-dd HH:nn:ss")#" />
or
<cfqueryparam cfsqltype="cf_sql_date" value="#dateFormat(now(), "yyyy-mm-dd")#" />

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.

The data type is DATETIME which the cheatsheet says is TIMESTAMP. I’m going to try the formatting to see if that works.

But using the choose depending on type of server the only difference is the ’ ’ around the variable! Therefore the formating must be correct!

I used this -

<cfqueryparam value="#dateTimeFormat(QMovement.DeliveryDateTime, "yyyy-mm-dd HH:nn:ss" )#" cfsqltype="cf_sql_timestamp" />,

Nothing - no record written from either server.

The only thing that works is -

<cfif server.coldfusion.productname EQ "Coldfusion Server">
                '#QMovement.DeliveryDateTime#',
<cfelse>
                #QMovement.DeliveryDateTime#, 
</cfif>

I’m using CF11 and Lucee Os 5.3.9.141

Just for my own curiosity,
Can you just dump : QMovement.DeliveryDateTime
please?

Hi Gavin

I did a file variable dump of both servers for #QMovement.DeliveryDateTime#

Cold Fusion - 2022-08-25 06:30:00.0

Lucee - Date Time (UTC) {ts ‘2022-08-25 06:30:00’}

The differences are obvious but I’m not sure what they mean…

Thanks

Tony

Looking at this it would appear that the following holds true.

  1. the format is correct.
  2. Cold Fusion does not supply the needed ’ to surround the variable and requires it in the SQL script.
  3. The Lucee variable already has the ’ so it does not need it in the SQL script.

Maybe just a difference in the way the servers ouput and this causes a mismatch when both servers are used at the same time?

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:

INSERT INTO tbl (col_timestamp) VALUES ( <cfqueryparam cfsqltype="cf_sql_timestamp" value="#ts#" /> );

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 :frowning:

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!

Good luck!

1 Like

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.

<cfqueryparam value="#QMovement.DeliveryDateTime#" cfsqltype="cf_sql_timestamp" />,

Moral of the story - use queryparam!

Thanks for everybody’s help!

2 Likes

Awesome - glad you got it figured!