Data truncation: Data truncated for column 'date' at row 1

[chromis]

Hi,

I have a query which updates the projects table of my database, however when I try to run my query with blank values i get the following error:

Data truncation: Data truncated for column 'date' at row 1

I have done some researching and I believe the problem is to do with the data that is being sent by my cfqueryparam function.

Here is my create function:

    <cffunction name="create" access="public" output="false" returntype="struct" hint="CRUD Method">
        <!--- arguments for the constructor, all of which are optional (no-arg constructor) --->
        <cfargument name="dsn" displayName="dsn" type="string" hint="Datasource name" 
            required="true" default="" />        
        <cfargument name="id" displayName="id" type="string" hint="The project ID (UUID)" 
            required="true" default="0" />    
        <cfargument name="priority" displayName="priority" type="string" hint="The project priority (Sort order)" 
            required="false" default="1" />
        <cfargument name="type" displayName="type" type="string" hint="The project type" 
            required="false" default="" />            
        <cfargument name="title" displayName="title" type="string" hint="The project title" 
            required="false" default="" />
        <cfargument name="location" displayName="location" type="string" hint="The project location"
            required="false" default="" />
        <cfargument name="description" displayName="description" type="string" hint="The project description"
            required="false" default="" />             
        <cfargument name="body" displayName="body" type="string" hint="The project body description"
            required="false" default="" />                   
        <cfargument name="image" displayName="image" type="string" hint="The project image name" 
            required="false" default="" />
        <cfargument name="image_alt" displayName="image_alt" type="string" hint="The project image alternative text"
            required="false" default="" />
        <cfargument name="movie" displayName="movie" type="string" hint="The project movie"
            required="false" default="" />            
        <cfargument name="pdf_doc" displayName="pdf_doc" type="string" hint="The project pdf reference" 
            required="false" default="" />            
        <cfargument name="word_doc" displayName="word_doc" type="string" hint="The project work doc reference" 
            required="false" default="" />                   
        <cfargument name="date" displayName="date" type="string" hint="The date the project was added"
            required="false" default="" /> 
        <cfargument name="time" displayName="time" type="string" hint="The time the project was added"
            required="false" default="" />             
        <cfargument name="archive" displayName="archive" type="string" hint="The project should be archived"
            required="false" default="" />       
        <cfargument name="display" displayName="display" type="string" hint="The project should be shown"
            required="false" default="" />  
 
        <!--- initialize variables --->
        <cfset var results = StructNew() />
        <cfset var qInsert = 0 />
 
        <!--- defaults --->
        <cfset results.success = true />
        <cfset results.message = "The project was inserted successfully." />
 
        <!--- <cftry> --->
            <cftransaction>
                <cfquery name="qInsert" datasource="#arguments.dsn#">
                    INSERT INTO
                    projects
                    (
                        id,
                        priority,
                        type,
                        title,
                        location,
                        description,
                        body,
                        image,
                        image_alt,
                        movie,
                        pdf_doc,
                        word_doc,                        
                        date,
                        time,
                        archive,
                        display
                    )
                    VALUES
                    (
                        <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.id#" null="yes" />,
                        <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.priority#" />,
                        <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.type#" />,                        
                        <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.title#" />,
                        <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.location#" />,
                        <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.description#" />,                        
                        <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.body#" />,
                        <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.image#" />,
                        <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.image_alt#" />,
                        <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.movie#" />,                        
                        <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.pdf_doc#" />,       
                        <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.word_doc#" />,                                           
                        <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.date#" />,
                        <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.time#" />,                        
                        <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.archive#" />,
                        <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.display#" />                                                            
                    )
                </cfquery>
 
                <!--- Select insert id --->
                <cfquery name="qMaxID" datasource="#arguments.dsn#">
                    SELECT max( id ) AS maxID FROM projects
                </cfquery>
 
                <!--- Add last insert id to results --->   
                <cfset results.lastInsertID = StructFind(qMaxID,"maxID") />       
 
            </cftransaction>  
        <!--- 
            <cfcatch type="database">
                <cfset results.success = false />
                <cfset results.message = "An error occured whilst attempting to update the database, please check you have filled in all the fields correctly and try again." />
                <cfif cfcatch.detail NEQ "">
                    <cfset results.message = results.message & "Error details: <br />" & cfcatch.detail />
                </cfif>
            </cfcatch>
        </cftry> --->
 
        <!--- return the struct --->
        <cfreturn StructCopy(results) />
      </cffunction>   
 

As you can see i am just using the cf_sql_varchar cfsqltype, as I'm not sure which is best suited.

I have tryed running the following test query which works fine:

                    INSERT INTO
                    projects
                    (
                        id,
                        priority,
                        type,
                        title,
                        location,
                        description,
                        body,
                        image,
                        image_alt,
                        movie,
                        pdf_doc,
                        word_doc,                        
                        date,
                        time,
                        archive,
                        display
                    )
                    VALUES
                    (
                        '',
                        '',
                        '',                        
                        '',
                        '',
                        '',                        
                        '',
                        '',
                        '',
                        '',                        
                        '',       
                        '',                                           
                        '',
                        '',                        
                        '',
                        ''                                                            
                    )
 

The cfqueryparam function must be doing something and I don't know what.

Does anyone know what the problem is?

Thanks,

chromis

[acoder]

What's the format/data type of the date field?

[chromis]

What's the format/data type of the date field?

The date field in the db is date.

[acoder]

Then use CF_SQL_DATE for the cfsqltype.

[chromis]

Ok thanks that sorted the date error, but I'm getting a new error with my time argument, the cfqueryparam for that is <cfqueryparam cfsqltype="cf_sql_time" value="#arguments.time#" /> and the data type is time in the db.

Error:

The cause of this output exception was that: java.lang.NullPointerException.

So the database is getting a null value? I've enabled null for the time field in the db, but still the same problem. Any ideas?

[acoder]

Check the docs for the cfqueryparam tag (link for CF8) and match the cfsqltype with the ones in your database.

[chromis]

Hmm, I've read through those, but still struggling, here are my mappings:

CREATE TABLE IF NOT EXISTS `projects` (
  `id` int(11) NOT NULL auto_increment,
  `priority` tinyint(4) NOT NULL default '0',
  `type` varchar(12) NOT NULL default '',
  `title` text NOT NULL,
  `location` varchar(64) NOT NULL default '',
  `description` text NOT NULL,
  `body` longtext NOT NULL,
  `image` varchar(128) NOT NULL default '',
  `image_alt` varchar(128) NOT NULL default '',
  `movie` varchar(128) NOT NULL default '',
  `pdf_doc` varchar(128) NOT NULL default '',
  `word_doc` varchar(128) NOT NULL default '',
  `date` date default '0000-00-00',
  `time` time default '00:00:00',
  `archive` tinyint(1) NOT NULL default '0',
  `display` tinyint(1) NOT NULL default '1',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=24 ;
 

                        <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.id#" null="yes" />,
                        <cfqueryparam cfsqltype="cf_sql_tinyint" value="#arguments.priority#" />,
                        <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.type#" />,                        
                        <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.title#" />,
                        <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.location#" />,
                        <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.description#" />,                        
                        <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.body#" />,
                        <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.image#" />,
                        <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.image_alt#" />,
                        <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.movie#" />,                        
                        <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.pdf_doc#" />,       
                        <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.word_doc#" />,                                           
                        <cfqueryparam cfsqltype="cf_sql_date" value="#arguments.date#" />,
                        <cfqueryparam cfsqltype="cf_sql_time" value="#arguments.time#" />,                        
                        <cfqueryparam cfsqltype="cf_sql_tinyint" value="#arguments.archive#" />,
                        <cfqueryparam cfsqltype="cf_sql_tinyint" value="#arguments.display#" />    

Is there anything wrong in the above?

I'm getting this error now: Invalid data for CFSQLTYPE CF_SQL_TINYINT.

[acoder]

Where you have blank values and default ones and not strings, e.g. integers, date, time, etc., use NULL, i.e. null="yes".

[chromis]

Ah great that's working now, thanks again acoder!

[acoder]

No problem at all. You're welcome :)

[chromis]

Hi,

<cfqueryparam cfsqltype="cf_sql_tinyint" value="#arguments.display#" />

Is producing the following error:

Invalid data for CFSQLTYPE CF_SQL_INTEGER.

It's moaning because I have removed the null="yes" setting in the cfqueryparam but unfortunately it enters NULL into the database when the setting is true, regardless of the contents of the variable. So having removed it I am left with this problem, how do I keep MySQL happy?

Thanks.

[acoder]

Are you passing a blank string? For non-string fields, set a default value instead, e.g. 0.

[chromis]

Yes I am already doing that which is strange.

        <cfargument name="archive" displayName="archive" type="string" hint="The project should be archived"
            required="false" default="0" />       
        <cfargument name="display" displayName="display" type="string" hint="The project should be shown"
            required="false" default="1" />
 

I've done some reading and come across a post which talks about using the Val function like so:

<cfqueryparam cfsqltype="cf_sql_tinyint" value="#Val(arguments.display)#" /> 
 

This seems to keep MySQL happy as it ensures the value is an integer, although I was passing it an integer anyway...

It's fixed now at least, thanks again for your help.

[acoder]

Yes, that's correct. I forgot about Val. The defaults are strings, so using val should solve the problem.