| Bug #37315 | bind_param incorrectly interprets scientific notation | ||
|---|---|---|---|
| Submitted: | 10 Jun 2008 18:17 | Modified: | 20 Feb 2013 22:14 |
| Reporter: | Andrew Feren | Email Updates: | |
| Status: | Unsupported | Impact on me: | |
| Category: | Connectors: DBD::mysql ( Perl ) | Severity: | S3 (Non-critical) |
| Version: | 4.005, 4.007 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | bind_param | ||
[10 Jun 2008 19:52]
Valeriy Kravchuk
Thank you for a problem report. What exact version of DBD::mysql do you use and what exact version of MySQL server you are working with?
[10 Jun 2008 20:22]
Andrew Feren
Tested on several Servers and DBI versions. I first ran across this several months ago and I no longer have a complete list, but I have most recently seen this on. MySQL 5.0.45-community-nt 5.0.51a-3ubuntu5.1 DBD::mysql 4.005 (on ubuntu) 4.005 (on windows) 4.006 (on windows)
[10 Jun 2008 21:00]
Andrew Feren
Upgraded DBD on Ubuntu to 4.007 and I still see the same issue.
[11 Jul 2008 20:17]
Sveta Smirnova
Thank you for the report. Verified as described.
[20 Feb 2013 22:14]
Sveta Smirnova
Thank you for the report. We don't work on DBD::mysql bugs anymore. All its bugs should go to CPAN: https://rt.cpan.org/Public/Dist/Display.html?Name=DBD-mysql I re-submitted your report to https://rt.cpan.org/Public/Bug/Display.html?id=83489 Please subscribe to the new report on CPAN and work with DBD::mysql developers in case if they need additional details.
Description: I have a DB with a DECIMAL column. If I use bind_param to insert a value formated using scientific notation. The value inserted is off by several orders of magnitude (the exponent specified in the scientific notation). example 7.1e-03 will get inserted as 7.1 not as .007 If I insert the same value ("7.1e-03") using execute with an arg list I get the expected result. How to repeat: #!perl -w use strict; # DB modules use DBI qw(:sql_types); my $dbH = DBI->connect("DBI:mysql:database=test;host=localhost;port=3306", 'root',''); $dbH->do(qq { CREATE TABLE IF NOT EXISTS test.float_test ( input VARCHAR(15) NOT NULL, result DECIMAL(15,3) NOT NULL, expected VARCHAR(15) NOT NULL, ins_type VARCHAR(15) NOT NULL ) ENGINE = MyISAM }); my $sci_v = '7.1e-03'; #reformat as 0.xxx my $float_v = sprintf('%06f', $sci_v); # This is my current workaround my $sth_execute = $dbH->prepare( qq{ INSERT INTO test.float_test (input, result, expected, ins_type) VALUES ($float_v,?,'0.007','execute'), ($sci_v, ?,'0.007','execute') }); $sth_execute->execute($float_v, $sci_v); my $sth_bind = $dbH->prepare( qq{ INSERT INTO test.float_test (input, result, expected, ins_type) VALUES ($float_v,?,'0.007','bind_param'), ($sci_v, ?,'0.007','bind_param') }); $sth_bind->bind_param(1, $float_v, SQL_VARCHAR); $sth_bind->bind_param(2, $sci_v, SQL_DECIMAL); $sth_bind->execute(); # to see the row that failed. # SELECT * FROM test.float_test WHERE result != expected;