By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,652 Members | 989 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,652 IT Pros & Developers. It's quick & easy.

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

100+
P: 113
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
Nov 21 '08 #1

✓ answered by acoder

Then use CF_SQL_DATE for the cfsqltype.

Share this Question
Share on Google+
13 Replies


acoder
Expert Mod 15k+
P: 16,027
What's the format/data type of the date field?
Nov 21 '08 #2

100+
P: 113
What's the format/data type of the date field?
The date field in the db is date.
Nov 21 '08 #3

acoder
Expert Mod 15k+
P: 16,027
Then use CF_SQL_DATE for the cfsqltype.
Nov 21 '08 #4

100+
P: 113
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?
Nov 21 '08 #5

acoder
Expert Mod 15k+
P: 16,027
Check the docs for the cfqueryparam tag (link for CF8) and match the cfsqltype with the ones in your database.
Nov 21 '08 #6

100+
P: 113
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.
Nov 21 '08 #7

acoder
Expert Mod 15k+
P: 16,027
Where you have blank values and default ones and not strings, e.g. integers, date, time, etc., use NULL, i.e. null="yes".
Nov 21 '08 #8

100+
P: 113
Ah great that's working now, thanks again acoder!
Nov 21 '08 #9

acoder
Expert Mod 15k+
P: 16,027
No problem at all. You're welcome :)
Nov 21 '08 #10

100+
P: 113
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.
Nov 26 '08 #11

acoder
Expert Mod 15k+
P: 16,027
Are you passing a blank string? For non-string fields, set a default value instead, e.g. 0.
Nov 26 '08 #12

100+
P: 113
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.
Nov 26 '08 #13

acoder
Expert Mod 15k+
P: 16,027
Yes, that's correct. I forgot about Val. The defaults are strings, so using val should solve the problem.
Nov 26 '08 #14

Post your reply

Sign in to post your reply or Sign up for a free account.