Data Truncation error

[d.acha]

Hello,

I have CF MX 7, developer version
Database is MySQL v. 4.1.12

Every 15 days I receive new ASCII files which contains data which have to
affect database tables. I have CF scripts which formated this ASCII text and
the script's result are files which have SQL code. Next, I upload the files
with SQL code to the server and run another CF scripts which execute this SQL
and I have updated database for the next 15 days.

I have done this several times and everything was OK. Now, I have strange
errors of type "Data Truncation" (SQLSTATE 01004) and I don't know what is it.
The CF code seems correct to me, produced SQL also. But error appears. I must
add that some parts of SQL executed pretty well, but for certain tables errors
appears. I have tried to run this "problematic" SQL code direct into MySQL
query browser application (which affect database without Coidfusion's
participation) and everything works well.

CF DSN connection with database is OK in CF administrator.

In short: my CF scripts are good (in vice versa I will have errors for all
tables, but I have errors only for certain tables), SQL code is good (it was
correcty performed in the MySQL query browser), DSN connection is OK.

I don't know what it can be? Maybe, I have problem with Mysql Connector, I use
"MySQL Connector/J" or maybe I must reinstall CF server. Have You any ideas??

Onceagain, I want to say that everything in this process works fine several
times in past few months, now I have problems.

Thank in advance,
Dacha.

[Brian Hogue]

d.acha -

Sounds like the new rows were added, but one or more columns may have been
truncated. You should check the SQL records against your input file.

Do you remove "bad" characters? Depending on your code commas, quotes and
number signs might yield bad results.

hth

-brian


"d.acha" <webforumsuser@macromedia.com> wrote in message
news:db3duo$mt8$1@forums.macromedia.com...

> Hello,
>
> I have CF MX 7, developer version
> Database is MySQL v. 4.1.12
>
> Every 15 days I receive new ASCII files which contains data which have to
> affect database tables. I have CF scripts which formated this ASCII text

and

> the script's result are files which have SQL code. Next, I upload the

files

> with SQL code to the server and run another CF scripts which execute this

SQL

> and I have updated database for the next 15 days.
>
> I have done this several times and everything was OK. Now, I have strange
> errors of type "Data Truncation" (SQLSTATE 01004) and I don't know what is

it.

> The CF code seems correct to me, produced SQL also. But error appears. I

must

> add that some parts of SQL executed pretty well, but for certain tables

errors

> appears. I have tried to run this "problematic" SQL code direct into MySQL
> query browser application (which affect database without Coidfusion's
> participation) and everything works well.
>
> CF DSN connection with database is OK in CF administrator.
>
> In short: my CF scripts are good (in vice versa I will have errors for

all

> tables, but I have errors only for certain tables), SQL code is good (it

was

> correcty performed in the MySQL query browser), DSN connection is OK.
>
> I don't know what it can be? Maybe, I have problem with Mysql Connector,

I use

> "MySQL Connector/J" or maybe I must reinstall CF server. Have You any

ideas??

>
> Onceagain, I want to say that everything in this process works fine

several

> times in past few months, now I have problems.
>
> Thank in advance,
> Dacha.
>

[d.acha]

Hi Brian,

Yes, the new rows were added. Completely SQL is executed. Everything in tables
is correct inserted, deleted or updated. After SQL executing error appears.

As I can remark this error appears on numeric integer field, to which is
allowed null value, when I update or insert blank value ''. In case that null
allowed is false it is normal that error appears, but here is allowed null
value. Apart from that this is not my first try to populate such values in the
tables. In past everything works fine.
Also, value which I am trying to insert is null '' and here is not misgiving
that value is longer than allowed value.

I don't know whether is important to say or not, I'm using MySQL Connector/J
for DSN connection between CF and MySQL.

I don't know what is happening here :)
D.

[rmorgan]

I have for some reason started getting a data truncation error on a page that
previously worked, the only thing that has changed since was upgrading to the
latest MySQL and cfmx7. Does any one see something wrong with my code? TIA


<cfquery name="getsched" datasource="#ds#">
SELECT *
FROM schedule
where age_id = #session.ageid#
</cfquery>
<cfloop list="#valueList(getsched.sched_id)#" index="sched_id">
<cfparam name="form.gid#sched_id#_homeforfeit" default="">
<cfparam name="form.gid#sched_id#_visitorforfeit" default="">
<cfif IsDefined("form.gid#sched_id#_homescore")
and IsDefined("form.gid#sched_id#_visitorscore")
and IsDefined("form.gid#sched_id#_homeforfeit")
and IsDefined("form.gid#sched_id#_visitorforfeit")>

<cfquery name="updatesched#sched_id#" datasource="#ds#">
update schedule
SET homescore = <cfif Len(Trim(form["gid" & sched_id &
"_homescore"]))>'#form["gid" & sched_id & "_homescore"]#'
<cfelse>Null</cfif>,
visitorscore = <cfif Len(Trim(form["gid" & sched_id &
"_visitorscore"]))>'#form["gid" & sched_id & "_visitorscore"]#'
<cfelse>Null</cfif>,
homeforfeit = '#form["gid" & sched_id & "_homeforfeit"]#',
visitorforfeit = '#form["gid" & sched_id & "_visitorforfeit"]#'
where sched_id = '#sched_id#'
and age_id = '#session.ageid#'
</cfquery>
</cfif>
</cfloop>

[rmorgan]

It returns the correct value corresponding to all the teams within this particular age group.

[mxstu]

1) Can you post the error message including the actual sql statement passed to the database?
2) What is the data type and size of the "age_id" field?

[rmorgan]

datatype is int, length 5.

THE QUERY:
<cfquery name="updatesched#sched_id#" datasource="#ds#">
update schedule
SET homescore = <cfif Len(Trim(form["gid" & sched_id &
"_homescore"]))>'#form["gid" & sched_id & "_homescore"]#'
<cfelse>Null</cfif>,
visitorscore = <cfif Len(Trim(form["gid" & sched_id &
"_visitorscore"]))>'#form["gid" & sched_id & "_visitorscore"]#'
<cfelse>Null</cfif>,
homeforfeit = '#form["gid" & sched_id & "_homeforfeit"]#',
visitorforfeit = '#form["gid" & sched_id & "_visitorforfeit"]#'
where sched_id = '#sched_id#'
and age_id = '#session.ageid#'
</cfquery>


THE ERROR:
Error Executing Database Query.
Data truncation: Data truncated for column 'visitorforfeit' at row 1

The error occurred in C:\Program Files\Apache
Group\Apache2\htdocs\LeagueTracking\results\enterr esultsaction.cfm: line 37

35 : visitorforfeit = '#form["gid" & sched_id & "_visitorforfeit"]#'
36 : where sched_id = '#sched_id#'
37 : and age_id = '#session.ageid#'
38 : </cfquery>
39 : </cfif>

Visitorforfeit, is just a checkbox with the value of 1 if it is checked,
otherwise it is NULL.

[mxstu]

No, I meant the sql statement sent to the database (with the values). It is usually part of the error message if full debugging is turned on.

[rmorgan]

Gotcha.

SQL update schedule SET homescore = '0' , visitorscore = '1' , homeforfeit
= '', visitorforfeit = '' where sched_id = '1' and age_id = '1'
DATASOURCE leaguetracking
SQLSTATE 01004

[mxstu]

What are the data types /sizes of the table columns?

homescore, visitorscore, homeforfeit, visitorforfeit, sched_id, age_id (you
said is int)

I ask because I'm wondering why you are using single quotes around values that
look like they are numeric...

[rmorgan]

The quotes around what is numeric, was just the last thing I had tried when I
pasted the code into this, when I could not think of anything else. Below is my
table structure.




Field Type Null Key Default

-------------- ----------- ------ ------ ---------
--------
sched_id int(10) PRI (NULL)

age_id int(5) YES
(NULL)
gamedate varchar(10) YES (NULL)
gametime time YES (NULL)

home varchar(25) YES (NULL)
homescore int(5) YES (NULL)

homeforfeit int(5) YES (NULL)

visitor varchar(25) YES (NULL)

visitorscore int(5) YES (NULL)

visitorforfeit int(5) YES
(NULL)
field varchar(10) YES (NULL)

change timestamp YES
CURRENT_TIMESTAMP

[mxstu]

Since you mentioned you're using a new version of mySql it sounds like the
"jdbcCompliantTruncation" setting for you database may be set to "true".
According to the mySQL documentation

"Starting with Connector/J 3.1.0, the JDBC driver will issue warnings or throw
DataTruncation exceptions as is required by the JDBC specification unless the
connection was configured not to do so by using the property
"jdbcCompliantTruncation" and setting it to "false"...."

[url]http://dev.mysql.com/doc/connector/j/en/cj-type-conversions.html[/url]

[rmorgan]

Thanks, looks like that fixed it

All that appears to be needed is to append: ?jdbcCompliantTruncation=false to the jdbc url in cfadmin