| 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: - 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
| |
Then use CF_SQL_DATE for the cfsqltype.
Share this Question
| Expert Mod 15k+
P: 16,027
|
What's the format/data type of the date field?
| |
| 100+
P: 113
|
What's the format/data type of the date field?
The date field in the db is date.
| |
| Expert Mod 15k+
P: 16,027
|
Then use CF_SQL_DATE for the cfsqltype.
| |
| 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?
| |
| 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.
| |
| 100+
P: 113
|
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.
| |
| 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".
| |
| 100+
P: 113
|
Ah great that's working now, thanks again acoder!
| |
| Expert Mod 15k+
P: 16,027
|
No problem at all. You're welcome :)
| |
| 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.
| |
| Expert Mod 15k+
P: 16,027
|
Are you passing a blank string? For non-string fields, set a default value instead, e.g. 0.
| |
| 100+
P: 113
|
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.
| |
| 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.
| | | |  Question stats - viewed: 30067
- replies: 13
- date asked: Nov 21 '08
|
