CODEWITHSUNDEEP
mysqlsqlcoldfusioncfc
Saul
Saul

Reputation: 1397

Date time exception in coldfusion query in cfc and mySQL

I'm moving from an MS Access backend to mySQL. This used to work but now doesn't and I can't figure the problem. 

<cfargument required="false" name="expiry" type="any" default="" />


        <cfquery datasource='#arguments.dsn#'>      
            INSERT INTO users(expiry)
            VALUES (<cfqueryparam value="#arguments.expiry#" cfsqltype="CF_SQL_TIMESTAMP"/>)
        </cfquery>

The database field is set to datetime and default NULL

The argument is populated from a form field which is either empty, or a javascript validated date. It chokes on empty formfield.

Upvotes: 5

Views: 1292

Answers (2)

Adam Tuttle
Adam Tuttle

Reputation: 19804

Before you mess with the DSN settings, I would also try changing your <cfqueryparam> to the following:

<cfqueryparam value="#arguments.expiry#" cfsqltype="CF_SQL_TIMESTAMP" null="#len(arguments.expiry) eq 0#" />

This will pass a true null in the event that the argument value is an empty string.

Upvotes: 6

Rob Dudley
Rob Dudley

Reputation: 110

CF's implementation of the JDBC driver for MySQL doesn't handle NULL dates very well.

You need to add a config flag to your DSN connection string settings (under advanced) in the CF admin

&zeroDateTimeBehavior=convertToNull

Should set you right.

Rob

Upvotes: 1

Related Questions