Skip to main content
Participant
June 3, 2019
Question

cfupdate/cfinsert invalid date string error with mysql varchar field

  • June 3, 2019
  • 1 reply
  • 265 views

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!

    This topic has been closed for replies.

    1 reply

    Community Expert
    June 3, 2019

    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

    Dave Watts, Eidolon LLC
    WolfShade
    Legend
    June 3, 2019

    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,

    ^ _ ^

    Community Expert
    June 4, 2019

    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

    Dave Watts, Eidolon LLC