Highlighted

cfupdate/cfinsert invalid date string error with mysql varchar field

New Here ,
Jun 03, 2019

Copy link to clipboard

Copied

I recently had up set up a new development laptop on which I use CF and mySQL.  I have had this application running for almost 10 years on various versions without a problem, but this is the first install on a Windows 10 machine (don't know how Windows 10 would affect this, but that is the only difference).

After getting everything set up and running I came across a weird error and am wondering if anyone else has seen it.

I have information I collect in which I need to encrypt a date of birth and store it.  I have this field in the database as a VARCHAR, not a date.  However, on the new machine, when trying to update or insert with cfupdate/cfinsert, I receive an error that the encrypted value is not a valid date or time string.  This happens even if I remove any validation of a valid date format and just leave it strictly as a text value.

I am able to manually code an SQL update/insert which is accepted, so that would make me believe it is a CF issue, however, I am also able to create a new table with a single VARCHAR field and use the same exact CF code to insert the same encrypted date values without error, which makes me think it's mySQL table related.

Having a work around I'm not in a bind, however I am curious if anyone else has seen it and knows why it's happening.  I am absolutely stumped as to why the system is seeming to read this VARCHAR field as a DATE field when trying to insert or update.

Any thoughts would be greatly appreciated!

Views

112

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

cfupdate/cfinsert invalid date string error with mysql varchar field

New Here ,
Jun 03, 2019

Copy link to clipboard

Copied

I recently had up set up a new development laptop on which I use CF and mySQL.  I have had this application running for almost 10 years on various versions without a problem, but this is the first install on a Windows 10 machine (don't know how Windows 10 would affect this, but that is the only difference).

After getting everything set up and running I came across a weird error and am wondering if anyone else has seen it.

I have information I collect in which I need to encrypt a date of birth and store it.  I have this field in the database as a VARCHAR, not a date.  However, on the new machine, when trying to update or insert with cfupdate/cfinsert, I receive an error that the encrypted value is not a valid date or time string.  This happens even if I remove any validation of a valid date format and just leave it strictly as a text value.

I am able to manually code an SQL update/insert which is accepted, so that would make me believe it is a CF issue, however, I am also able to create a new table with a single VARCHAR field and use the same exact CF code to insert the same encrypted date values without error, which makes me think it's mySQL table related.

Having a work around I'm not in a bind, however I am curious if anyone else has seen it and knows why it's happening.  I am absolutely stumped as to why the system is seeming to read this VARCHAR field as a DATE field when trying to insert or update.

Any thoughts would be greatly appreciated!

Views

113

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Jun 03, 2019 0
Adobe Community Professional ,
Jun 03, 2019

Copy link to clipboard

Copied

Why are you using those tags? Writing SQL inserts and updates is pretty simple, and I don't think Adobe spends much time testing CFINSERT and CFUPDATE for newer versions.

Dave Watts, Eidolon LLC

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jun 03, 2019 0
LEGEND ,
Jun 03, 2019

Copy link to clipboard

Copied

I've seen instances where some people pooh-poohed the idea of using cfinsert or cfupdate, and I must admit that I haven't used them much, myself.  Primarily because (I think?) the field names have to be the same as the column names in the database.  Plus, I'm so pedantic, I'd much rather write my own SQL.

But for those who prefer to use cfinsert and cfupdate, if there are many (I imagine not) who use it, then maybe Adobe should examine the tags in Windows 10, since Win10 itself is fairly widely distributed.  Just my two cents.

V/r,

^ _ ^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jun 03, 2019 0
Adobe Community Professional ,
Jun 04, 2019

Copy link to clipboard

Copied

I'm going to go full "pooh-pooh" here. They were added to CFML (at the time it was DBML if I recall correctly) as a way for people with no development experience to insert and update records. But modern developers really shouldn't be using them, because modern developers should know how to insert and update records using SQL. Any maintenance time spent keeping these working is being taken away from other, potentially useful things.

Dave Watts, Eidolon LLC

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jun 04, 2019 0