Page 1 of 1
Data too long for a column? Help?
Posted: Thu Sep 04, 2008 8:26 am
by kaisellgren
Hi,
I'm coding a project, it uses MySQL(i), PostgreSQL, Oracle and MSSQL (SQL Server through ODBC).
Everything seems fine, except for a few errors.
It seems that MySQL 5+ throws an error if you insert let's say 500 characters long string into a varchar(255), but MySQL 4 will automatically truncate it? How can I force MySQL to do this autotruncating?
Does this have something to do with SQL modes?
http://dev.mysql.com/doc/refman/5.0/en/ ... -mode.html
Does setting it to Strict mode provide any help?
Thanks for your answers

Re: Data too long for a column? Help?
Posted: Thu Sep 04, 2008 8:30 am
by onion2k
Switch off strict mode prior to doing the query:
What on Earth are you writing that needs 5 different databases?
Re: Data too long for a column? Help?
Posted: Thu Sep 04, 2008 1:05 pm
by kaisellgren
onion2k wrote:Switch off strict mode prior to doing the query:
What on Earth are you writing that needs 5 different databases?
It's for several different servers, some support some

(ok ok and I admit also for the education of mine

)
But, can't I do that myself too? I mean the truncation.
The best way is to 'force sql into strict mode and then do checkings&changes myself'? There's no point of doing the checks/changes in both PHP & SQL side, so I just force the SQL mode to strict and do the checkings myself?
Or... do you think that it's always possible to set an SQL server into nonstrict mode? Are there servers that won't do this, or is any of those dbms I mentioned limited to use strict only?
How can I know what kind of things to look at. The varchar too long is just one kind of problem, what other problems there could possibly be (caused by this strict mode)?
Re: Data too long for a column? Help?
Posted: Thu Sep 04, 2008 1:47 pm
by onion2k
If validating the data in the code first is an option then do that. Always. Maybe you could use some sort of data dictionary approach?
Re: Data too long for a column? Help?
Posted: Thu Sep 04, 2008 3:35 pm
by kaisellgren
onion2k wrote:If validating the data in the code first is an option then do that. Always. Maybe you could use some sort of data dictionary approach?
Can you light me more about that data dictionary approach?
Re: Data too long for a column? Help?
Posted: Thu Sep 04, 2008 3:59 pm
by onion2k
A data dictionary is basically a model of your database schema that contains meta data you can use to generate validation rules, queries, etc.
http://dev.mysql.com/tech-resources/art ... onary.html