Mysql smallint out of range. How to fetch warning?

bannaN has asked for the wisdom of the Perl Monks concerning the following question:

Hello Monks,

If you insert a integer that is out of range in mysql it automaticly converts the integer to the nearest valid value.

If you have a mysql column with smallint and try to insert -32769, it automaticly inserts -32768 and throws a warning(according to the mysql documentation). How do i fetch this warnings with DBD::mysql?

In DBI its mentioned that a driver can write warnings to $DBI::errstr, but even though $sth->{mysql_warning_count} > 0 both $DBI::errstr and $DBI::err is empty. How to i fetch the warnings?

Thanks for any input!

Best regards

Comment on Mysql smallint out of range. How to fetch warning?

Replies are listed 'Best First'.
Re: Mysql smallint out of range. How to fetch warning? by samtregar (Abbot) on Oct 16, 2010 at 03:39 UTC
Just use the SHOW WARNINGS statement: `$dbh->do("CREATE TEMPORARY TABLE foo (bar SMALLINT)") $dbh->do("INSERT INTO foo (bar) VALUES (100000000)"); use Data::Dumper; print Dumper($dbh->selectall_arrayref("SHOW WARNINGS"));` [download] Output: `$VAR1 = [ [ 'Warning', '1264', 'Out of range value adjusted for column \'bar\' at row 1' ] ];` [download] -sam	[reply] [d/l] [select]
Re: Mysql smallint out of range. How to fetch warning? by tinita (Parson) on Oct 16, 2010 at 22:30 UTC
IMHO it might be even better to forbid mysql to do that insert at all. if you turn to strict mode, the insert will fail and output a warning. with the "RaiseError" option on, it will die: `my $dbh = DBI->connect(..., { RaiseError => 1 }); $dbh->do(q{SET sql_mode="STRICT_TRANS_TABLES,STRICT_ALL_TABLES"}); # insert` [download] Then you can be sure to not get wrong data inserted, but you have to put an eval block around it to catch the error.	[reply] [d/l]
Re^2: Mysql smallint out of range. How to fetch warning? by bannaN (Acolyte) on Oct 17, 2010 at 18:44 UTC
Thanks, this sorted out the problem. Wasnt aware of the strict options	[reply]
Re: Mysql smallint out of range. How to fetch warning? by locked_user sundialsvc4 (Abbot) on Oct 16, 2010 at 00:21 UTC
My considered reaction to this inquiry ... is one of the two following responses: If the data could possibly (legitimately...) exceed the domain of a `smallint` column, then you are “quite in error” to have chosen that data-type for that column. If, on the other hand, the data could not possibly (legitimately...) exceed the domain of a `smallint` column, then you are, once again, “quite in error” to expect or to rely upon mySQL to do the dirty-work for you! If the data that you have been presented does not conform to the rules that you know ought to apply to it ... then it is your responsibility to ensure that such data is not presented to the database-engine in the first place. And, please, do understand that this reaction is absolutely not “off-the-cuff.” It is carefully-considered and very serious. Behaviors such as the one that you describe are, if you will, “an attempt (by the database engine in question ...) to make a dollar out of ~~ninety-nine cents~~ a sack of pure garbage (that ... ahem... you just forced upon it). The implementors are basically telling you what their code will do in a fundamentally erroneous situation. But you, as an application designer, have (what is to me...) an undeniable duty to make certain that their database engine never encounters that (fundamentally erroneous...) situation. Although my stance on this matter is, “ahem... quite forceful,” I trust that you take it in proper context, and that you perceive the (I think...) very-necessary reason for it...