Connecting Tech Pros Worldwide Forums | Help | Site Map
bytes > topic > coldfusion > answers

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

Member
  
Join Date: Jan 2008
Posts: 113
#1: Nov 21 '08
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:

Expand|Select|Wrap|Line Numbers
  1. 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:

Expand|Select|Wrap|Line Numbers
  1.     <cffunction name="create" access="public" output="false" returntype="struct" hint="CRUD Method">
  2.         <!--- arguments for the constructor, all of which are optional (no-arg constructor) --->
  3.         <cfargument name="dsn" displayName="dsn" type="string" hint="Datasource name" 
  4.             required="true" default="" />        
  5.         <cfargument name="id" displayName="id" type="string" hint="The project ID (UUID)" 
  6.             required="true" default="0" />    
  7.         <cfargument name="priority" displayName="priority" type="string" hint="The project priority (Sort order)" 
  8.             required="false" default="1" />
  9.         <cfargument name="type" displayName="type" type="string" hint="The project type" 
  10.             required="false" default="" />            
  11.         <cfargument name="title" displayName="title" type="string" hint="The project title" 
  12.             required="false" default="" />
  13.         <cfargument name="location" displayName="location" type="string" hint="The project location"
  14.             required="false" default="" />
  15.         <cfargument name="description" displayName="description" type="string" hint="The project description"
  16.             required="false" default="" />             
  17.         <cfargument name="body" displayName="body" type="string" hint="The project body description"
  18.             required="false" default="" />                   
  19.         <cfargument name="image" displayName="image" type="string" hint="The project image name" 
  20.             required="false" default="" />
  21.         <cfargument name="image_alt" displayName="image_alt" type="string" hint="The project image alternative text"
  22.             required="false" default="" />
  23.         <cfargument name="movie" displayName="movie" type="string" hint="The project movie"
  24.             required="false" default="" />            
  25.         <cfargument name="pdf_doc" displayName="pdf_doc" type="string" hint="The project pdf reference" 
  26.             required="false" default="" />            
  27.         <cfargument name="word_doc" displayName="word_doc" type="string" hint="The project work doc reference" 
  28.             required="false" default="" />                   
  29.         <cfargument name="date" displayName="date" type="string" hint="The date the project was added"
  30.             required="false" default="" /> 
  31.         <cfargument name="time" displayName="time" type="string" hint="The time the project was added"
  32.             required="false" default="" />             
  33.         <cfargument name="archive" displayName="archive" type="string" hint="The project should be archived"
  34.             required="false" default="" />       
  35.         <cfargument name="display" displayName="display" type="string" hint="The project should be shown"
  36.             required="false" default="" />  
  37.  
  38.         <!--- initialize variables --->
  39.         <cfset var results = StructNew() />
  40.         <cfset var qInsert = 0 />
  41.  
  42.         <!--- defaults --->
  43.         <cfset results.success = true />
  44.         <cfset results.message = "The project was inserted successfully." />
  45.  
  46.         <!--- <cftry> --->
  47.             <cftransaction>
  48.                 <cfquery name="qInsert" datasource="#arguments.dsn#">
  49.                     INSERT INTO
  50.                     projects
  51.                     (
  52.                         id,
  53.                         priority,
  54.                         type,
  55.                         title,
  56.                         location,
  57.                         description,
  58.                         body,
  59.                         image,
  60.                         image_alt,
  61.                         movie,
  62.                         pdf_doc,
  63.                         word_doc,                        
  64.                         date,
  65.                         time,
  66.                         archive,
  67.                         display
  68.                     )
  69.                     VALUES
  70.                     (
  71.                         <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.id#" null="yes" />,
  72.                         <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.priority#" />,
  73.                         <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.type#" />,                        
  74.                         <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.title#" />,
  75.                         <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.location#" />,
  76.                         <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.description#" />,                        
  77.                         <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.body#" />,
  78.                         <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.image#" />,
  79.                         <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.image_alt#" />,
  80.                         <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.movie#" />,                        
  81.                         <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.pdf_doc#" />,       
  82.                         <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.word_doc#" />,                                           
  83.                         <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.date#" />,
  84.                         <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.time#" />,                        
  85.                         <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.archive#" />,
  86.                         <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.display#" />                                                            
  87.                     )
  88.                 </cfquery>
  89.  
  90.                 <!--- Select insert id --->
  91.                 <cfquery name="qMaxID" datasource="#arguments.dsn#">
  92.                     SELECT max( id ) AS maxID FROM projects
  93.                 </cfquery>
  94.  
  95.                 <!--- Add last insert id to results --->   
  96.                 <cfset results.lastInsertID = StructFind(qMaxID,"maxID") />       
  97.  
  98.             </cftransaction>  
  99.         <!--- 
  100.             <cfcatch type="database">
  101.                 <cfset results.success = false />
  102.                 <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." />
  103.                 <cfif cfcatch.detail NEQ "">
  104.                     <cfset results.message = results.message & "Error details: <br />" & cfcatch.detail />
  105.                 </cfif>
  106.             </cfcatch>
  107.         </cftry> --->
  108.  
  109.         <!--- return the struct --->
  110.         <cfreturn StructCopy(results) />
  111.       </cffunction>   
  112.  
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:

Expand|Select|Wrap|Line Numbers
  1.                     INSERT INTO
  2.                     projects
  3.                     (
  4.                         id,
  5.                         priority,
  6.                         type,
  7.                         title,
  8.                         location,
  9.                         description,
  10.                         body,
  11.                         image,
  12.                         image_alt,
  13.                         movie,
  14.                         pdf_doc,
  15.                         word_doc,                        
  16.                         date,
  17.                         time,
  18.                         archive,
  19.                         display
  20.                     )
  21.                     VALUES
  22.                     (
  23.                         '',
  24.                         '',
  25.                         '',                        
  26.                         '',
  27.                         '',
  28.                         '',                        
  29.                         '',
  30.                         '',
  31.                         '',
  32.                         '',                        
  33.                         '',       
  34.                         '',                                           
  35.                         '',
  36.                         '',                        
  37.                         '',
  38.                         ''                                                            
  39.                     )
  40.  
The cfqueryparam function must be doing something and I don't know what.

Does anyone know what the problem is?

Thanks,

chromis

acoder's Avatar
Site Moderator
  
Join Date: Nov 2006
Location: UK
Posts: 14,581
#2: Nov 21 '08

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

What's the format/data type of the date field?
Member
  
Join Date: Jan 2008
Posts: 113
#3: Nov 21 '08

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

Quote:

Originally Posted by acoder

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

The date field in the db is date.
acoder's Avatar
Site Moderator
  
Join Date: Nov 2006
Location: UK
Posts: 14,581
#4: Nov 21 '08

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

Then use CF_SQL_DATE for the cfsqltype.
Member
  
Join Date: Jan 2008
Posts: 113
#5: Nov 21 '08

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

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's Avatar
Site Moderator
  
Join Date: Nov 2006
Location: UK
Posts: 14,581
#6: Nov 21 '08

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

Check the docs for the cfqueryparam tag (link for CF8) and match the cfsqltype with the ones in your database.
Member
  
Join Date: Jan 2008
Posts: 113
#7: Nov 21 '08

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

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

Expand|Select|Wrap|Line Numbers
  1. CREATE TABLE IF NOT EXISTS `projects` (
  2.   `id` int(11) NOT NULL auto_increment,
  3.   `priority` tinyint(4) NOT NULL default '0',
  4.   `type` varchar(12) NOT NULL default '',
  5.   `title` text NOT NULL,
  6.   `location` varchar(64) NOT NULL default '',
  7.   `description` text NOT NULL,
  8.   `body` longtext NOT NULL,
  9.   `image` varchar(128) NOT NULL default '',
  10.   `image_alt` varchar(128) NOT NULL default '',
  11.   `movie` varchar(128) NOT NULL default '',
  12.   `pdf_doc` varchar(128) NOT NULL default '',
  13.   `word_doc` varchar(128) NOT NULL default '',
  14.   `date` date default '0000-00-00',
  15.   `time` time default '00:00:00',
  16.   `archive` tinyint(1) NOT NULL default '0',
  17.   `display` tinyint(1) NOT NULL default '1',
  18.   PRIMARY KEY  (`id`)
  19. ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=24 ;
  20.  
Expand|Select|Wrap|Line Numbers
  1.                         <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.id#" null="yes" />,
  2.                         <cfqueryparam cfsqltype="cf_sql_tinyint" value="#arguments.priority#" />,
  3.                         <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.type#" />,                        
  4.                         <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.title#" />,
  5.                         <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.location#" />,
  6.                         <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.description#" />,                        
  7.                         <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.body#" />,
  8.                         <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.image#" />,
  9.                         <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.image_alt#" />,
  10.                         <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.movie#" />,                        
  11.                         <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.pdf_doc#" />,       
  12.                         <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.word_doc#" />,                                           
  13.                         <cfqueryparam cfsqltype="cf_sql_date" value="#arguments.date#" />,
  14.                         <cfqueryparam cfsqltype="cf_sql_time" value="#arguments.time#" />,                        
  15.                         <cfqueryparam cfsqltype="cf_sql_tinyint" value="#arguments.archive#" />,
  16.                         <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's Avatar
Site Moderator
  
Join Date: Nov 2006
Location: UK
Posts: 14,581
#8: Nov 21 '08

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

Where you have blank values and default ones and not strings, e.g. integers, date, time, etc., use NULL, i.e. null="yes".
Member
  
Join Date: Jan 2008
Posts: 113
#9: Nov 21 '08

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

Ah great that's working now, thanks again acoder!
acoder's Avatar
Site Moderator
  
Join Date: Nov 2006
Location: UK
Posts: 14,581
#10: Nov 21 '08

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

No problem at all. You're welcome :)
Member
  
Join Date: Jan 2008
Posts: 113
#11: Nov 26 '08

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

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's Avatar
Site Moderator
  
Join Date: Nov 2006
Location: UK
Posts: 14,581
#12: Nov 26 '08

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

Are you passing a blank string? For non-string fields, set a default value instead, e.g. 0.
Member
  
Join Date: Jan 2008
Posts: 113
#13: Nov 26 '08

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

Yes I am already doing that which is strange.

Expand|Select|Wrap|Line Numbers
  1.         <cfargument name="archive" displayName="archive" type="string" hint="The project should be archived"
  2.             required="false" default="0" />       
  3.         <cfargument name="display" displayName="display" type="string" hint="The project should be shown"
  4.             required="false" default="1" />
  5.  
I've done some reading and come across a post which talks about using the Val function like so:

Expand|Select|Wrap|Line Numbers
  1. <cfqueryparam cfsqltype="cf_sql_tinyint" value="#Val(arguments.display)#" /> 
  2.  
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's Avatar
Site Moderator
  
Join Date: Nov 2006
Location: UK
Posts: 14,581
#14: Nov 26 '08

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

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

« previous question | next question »

Similar ColdFusion bytes

/bytes/development

/bytes/it

Forums
Visit our community forums for general discussions and latest on Bytes

Our staff and community areas have moved to bytes.com/forums/

/bytes/about

We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights. Get the best answers to your questions from over 226,501 network members.

dev questions:
algorithms | asp | asp.net | c/c++ | coldfusion | c# | flash | html/css | java | javascript |
mobile dev | .net | perl | php | python | ruby | software dev | vb.net | visual basic | xml
it questions:
access | apache | careers | db2 | iis | macosx | mysql | networking | oracle | postgresql | sql server | unix | windows

About Bytes | Copyright 1995-2009 BYTES. All rights Reserved