Perl Guru Forums: Perl Programming Help: Beginner: mysql-perl : Float value out of range and duplicate key error

CGI/Perl Guide | Learning Center | Forums | Advertise | Login

Site Search: in


	MAIN INDEX	SEARCH POSTS	WHO'S ONLINE	LOG IN

Home: Perl Programming Help: Beginner:
mysql-perl : Float value out of range and duplicate key error

zak100
User

Aug 14, 2014, 11:39 PM

Post #1 of 10 (3012 views)

mysql-perl : Float value out of range and duplicate key error	Can't Post

Hi,
I am creating a table using a float field. I am able to create table but at insertion it is generating error.
My code is:

Code

#!C:/Perl64/bin/perl.exe 
use DBI; 
use strict; 
use warnings; 
my $dbh; 
my $sql; 
my $sth; 
my @row; 
 
 
 
$dbh=DBI->connect('dbi:mysql:testdb','root','zulfi') or die "connection error: $DBI::errstr\n"; 
$sql = "Create table if not exists cars(Id int(10) not null auto_increment , Name varchar(128) , cost float   , Primary key(id))"; 
$sth = $dbh->prepare($sql); 
$sth->execute () or die("Cannot create table: " . $dbh->errstr); $sql = "insert into cars (Id, Name, cost) values (?, ?, ?) " or die("Cannot insert: " . $dbh->errstr); 
 
# prepare your statement for connecting to the database 
$sth = $dbh->prepare($sql); 
 
# execute your SQL statement 
$sth->execute(3, 'Suzuki1', 10.23) or die "SQL error $DBI::errstr \n"; 
 
 
#inserting 2nd record 
 
$sth->execute(4, 'Suzuki2', 10000.23) or die "SQL error $DBI::errstr \n"; 
 
 
$sql="select * from cars"; 
$sth=$dbh->prepare($sql); 
$sth->execute or die "SQL error $DBI::errstr \n"; 
 
while (@row=$sth->fetchrow_array) 
{ 
	print "@row\n"; 
}

I am getting following error:

Code

 
D:\Zulfi\PERL MAIN FOLDER\mysql LabProg\Lab9>perl creat_insert_selectdb.pl 
Found = in conditional, should be == at creat_insert_selectdb.pl line 29. 
DBD::mysql::st execute failed: Out of range value for column 'cost' at row 1 at 
creat_insert_selectdb.pl line 40. 
SQL error Out of range value for column 'cost' at row 1 
 
D:\Zulfi\PERL MAIN FOLDER\mysql LabProg\Lab9>

It is also giving duplicate key error.

Somebody plz guide me.

Zulfi.

(This post was edited by zak100 on Aug 15, 2014, 10:07 PM)

FishMonger
Veteran / Moderator

Aug 15, 2014, 6:17 AM

Post #2 of 10 (3007 views)

Re: [zak100] mysql-perl : Float value out of range and duplicate key error [In reply to]	Can't Post

Id is an auto_increment field so it shouldn't be included in the insert statements.

zak100
User

Aug 15, 2014, 11:25 AM

Post #3 of 10 (2992 views)

Re: [FishMonger] mysql-perl : Float value out of range and duplicate key error [In reply to]	Can't Post

Hi,
Thanks i am able to solve this duplicate value problem related to Primary Key.
The code after leaving the auto_increment field as you advised is:

Code

#!C:/Perl64/bin/perl.exe 
use DBI; 
use strict; 
use warnings; 
my $dbh; 
my $sql; 
my $sth; 
my @row; 
 
#out of range error 
 
$dbh=DBI->connect('dbi:mysql:testdb','root','zulfi') or die "connection error: $DBI::errstr\n"; 
$sql = "Create table if not exists cars(Id int(10) not null auto_increment , Name varchar(128) , cost int    , Primary key(id))"; 
$sth = $dbh->prepare($sql); 
$sth->execute () or die("Cannot create table: " . $dbh->errstr);  
 
$sql = "insert into cars ( Name, cost) values ( ?, ?) " ; 
  #ignoring the auto_increment field   
 
# prepare your statement for connecting to the database 
$sth = $dbh->prepare($sql); 
 
# execute your SQL statement 
$sth->execute( 'Suzuki1', 10) or die("Cannot create table: " . $dbh->errstr); 
 
 
#inserting 2nd record 
 
$sth->execute( 'Suzuki2', 10000) or die("Cannot create table: " . $dbh->errstr); 
 
 
$sql="select * from cars"; 
$sth=$dbh->prepare($sql); 
$sth->execute or die "SQL error $DBI::errstr \n"; 
 
while (@row=$sth->fetchrow_array) 
{ 
	print "@row\n"; 
} 
 
But i still need guidance to solve the float value out of range problem. Please guide me. 
 
Zulfi. 


	

(This post was edited by zak100 on Aug 15, 2014, 10:08 PM)

FishMonger
Veteran / Moderator

Aug 15, 2014, 12:09 PM

Post #4 of 10 (2985 views)

Re: [zak100] mysql-perl : Float value out of range and duplicate key error [In reply to]	Can't Post

Your latest example doesn't have any fields defined as float and neither do your inserts.

Please post the complete test script that demonstrates the "Out of range value for column 'cost'" problem.

FishMonger
Veteran / Moderator

Aug 15, 2014, 12:16 PM

Post #5 of 10 (2982 views)

Re: [zak100] mysql-perl : Float value out of range and duplicate key error [In reply to]	Can't Post

BTW, if you're dealing with monetary numbers, then you should be defining the field as a DECIMAL(m,n)

(This post was edited by FishMonger on Aug 15, 2014, 12:16 PM)

zak100
User

Aug 15, 2014, 10:07 PM

Post #6 of 10 (2966 views)

Re: [FishMonger] mysql-perl : Float value out of range and duplicate key error [In reply to]	Can't Post

Hi,
Thanks for your guidance. I did not make any changes but its working now. I have only changed the name of table and the type of cost is float. I am showing my code and output:

Code

#!C:/Perl64/bin/perl.exe 
use DBI; 
use strict; 
use warnings; 
my $dbh; 
my $sql; 
my $sth; 
my @row; 
 
#out of range error 
 
$dbh=DBI->connect('dbi:mysql:testdb','root','zulfi') or die "connection error: $DBI::errstr\n"; 
$sql = "Create table if not exists cars2(Id int(10) not null auto_increment , Name varchar(128) , cost float    , Primary key(id))"; 
$sth = $dbh->prepare($sql); 
$sth->execute () or die("Cannot create table: " . $dbh->errstr);  
$sql = "insert into cars2 ( Name, cost) values ( ?, ?) " ; 
 
# prepare your statement for connecting to the database 
$sth = $dbh->prepare($sql); 
 
# execute your SQL statement 
$sth->execute( 'Suzuki1', 10.23) or die("Cannot create table: " . $dbh->errstr); 
 
 
#inserting 2nd record 
 
$sth->execute( 'Suzuki2', 10000.23) or die("Cannot create table: " . $dbh->errstr); 
 
 
$sql="select * from cars2"; 
$sth=$dbh->prepare($sql); 
$sth->execute or die "SQL error $DBI::errstr \n"; 
 
while (@row=$sth->fetchrow_array) 
{ 
	print "@row\n"; 
}

and the out is:

Code

D:\Zulfi\PERL MAIN FOLDER\mysql LabProg\Lab9>perl creat_insert_selectdb3.pl 
1 Suzuki1 10.23 
2 Suzuki2 10000.2 
 
D:\Zulfi\PERL MAIN FOLDER\mysql LabProg\Lab9>

and mysql window output is also same:

Code

 
mysql> select * from cars2; 
+----+---------+---------+ 
| Id | Name    | cost    | 
+----+---------+---------+ 
|  1 | Suzuki1 |   10.23 | 
|  2 | Suzuki2 | 10000.2 | 
+----+---------+---------+ 
2 rows in set (0.00 sec) 
 
mysql>

Thanks again for solving my problems.

Zulfi.

(This post was edited by zak100 on Aug 15, 2014, 10:09 PM)

FishMonger
Veteran / Moderator

Aug 16, 2014, 6:09 AM

Post #7 of 10 (2937 views)

Re: [zak100] mysql-perl : Float value out of range and duplicate key error [In reply to]	Can't Post

Quote

# execute your SQL statement
$sth->execute( 'Suzuki1', 10.23) or die("Cannot create table: " . $dbh->errstr);

#inserting 2nd record

$sth->execute( 'Suzuki2', 10000.23) or die("Cannot create table: " . $dbh->errstr);

Code

mysql> select * from cars2;  
+----+---------+---------+  
| Id | Name    | cost    |  
+----+---------+---------+  
|  1 | Suzuki1 |   10.23 |  
|  2 | Suzuki2 | 10000.2 |  
+----+---------+---------+  
2 rows in set (0.00 sec)

Do you see the problem that I see?

zak100
User

Aug 16, 2014, 10:17 AM

Post #8 of 10 (2928 views)

Re: [FishMonger] mysql-perl : Float value out of range and duplicate key error [In reply to]	Can't Post

Thanks for your comments. What i see is

Quote

The two float values are not consistent with digits after decimal point.

I would try to correct it with your approach i.e.

Code

DECIMAL(m,n)

Zulfi.

zak100
User

Aug 16, 2014, 10:49 AM

Post #9 of 10 (2923 views)

Re: [zak100] mysql-perl : Float value out of range and duplicate key error [In reply to]	Can't Post

Hi,
I have modified the code so that number of digits are consistent after decimal points. The new code is:

Code

 
#!C:/Perl64/bin/perl.exe 
use DBI; 
use strict; 
use warnings; 
my $dbh; 
my $sql; 
my $sth; 
my @row; 
 
#out of range error 
 
$dbh=DBI->connect('dbi:mysql:testdb','root','zulfi') or die "connection error: $DBI::errstr\n"; 
$sql = "Create table if not exists cars2(Id int(10) not null auto_increment , Name varchar(128) ,   cost Decimal (8, 2)    , Primary key(id))"; 
$sth = $dbh->prepare($sql);        
$sth->execute () or die("Cannot create table: " . $dbh->errstr);  
 
 
 
 
$sql = "insert into cars2 ( Name, cost) values ( ?, ?) " ; 
 
# prepare your statement for connecting to the database 
$sth = $dbh->prepare($sql); 
 
# execute your SQL statement 
$sth->execute( 'Suzuki1', 10.23) or die("Cannot create table: " . $dbh->errstr); 
 
 
#inserting 2nd record 
 
$sth->execute( 'Suzuki2', 10000.23) or die("Cannot create table: " . $dbh->errstr); 
 
 
$sql="select * from cars2"; 
$sth=$dbh->prepare($sql); 
$sth->execute or die "SQL error $DBI::errstr \n"; 
 
while (@row=$sth->fetchrow_array) 
{ 
	print "@row\n"; 
}

The output is now:

Code

D:\Zulfi\PERL MAIN FOLDER\mysql LabProg\Lab9>perl creat_insert_selectdb3.pl 
1 Suzuki1 10.23 
2 Suzuki2 10000.23 
3 Suzuki1 10.23 
4 Suzuki2 10000.23 
 
D:\Zulfi\PERL MAIN FOLDER\mysql LabProg\Lab9>

Thanks for guidance.

Zulfi.

(This post was edited by zak100 on Aug 16, 2014, 10:50 AM)

Laurent_R
Veteran / Moderator

Aug 16, 2014, 3:14 PM

Post #10 of 10 (2914 views)

Re: [zak100] mysql-perl : Float value out of range and duplicate key error [In reply to]	Can't Post

Hi zak100,

OK, using DECIMAL(m,n) is solving your decimal display issue.

But there is a far far more important reason not to use floats for monetary amounts. Floating point arithmetics gives wrong results because of the way floating point numbers are stored within computers (a binary mantissa and a binary exponent). Because they are stored in binary format, they cannot be accurately representing decimal numbers that we are using in the daily life, especially for representing monetary amounts, just as 1/3 is 0.3333333333... and cannot be accurately represented in our decimal system.

As an imaginary example, and depending on your particular computer, you may have an invoice just adding two floating point numbers, say 4.12 and 3.13, and not end up with 7,25, but possibly 7.24 or 7.26. And I can tell you for a fact, if you produce dozens of millions of invoices per month like the company I am working for (as a freelance cunsultant, but that does not change anything, except that you will see the point later), even if that error is not happening too often, you'll have thousands of clients (probably more) complaining every month and that's gonna cost your company a fortune in Customer Service Representatives' work. And since CSRs don't know what to answer, the incidents will escalate to level 1 support, level 2 support, usually all the way up, and even the highest level support will often not know what to say. And that's again a lot of money wasted, with no result. So, they will call the software editor, the hardware manufacturer and some senior consultants, and that's where I am starting to make a lot of money. Wink

Perhaps I should not reveal that secret to avoid drying my sources of income, but on this one, the cat has been out of the bag for so many decades that people should know better. And, believe it or not, although this problem has been known for more than half a century, I have been asked to solve this kind of problems at least 5 or 6 times in the last 10 years. And contrary to what I said jokingly above, I haven't been making so much money on these issues, because it usually did not take me very long to find the origin of the problem (even though it is sometimes obscured by other things), but solving these problems has contributed to establish my expert reputation and my capability to demand a higher daily rate.

The way to solve the problem in (I guess almost) all databases and accounting software packages, and in programs using them, is to use internally integers, for which calculations are accurate. So the database is really using integers to store, for example, not dollars, but cents (that's if the second "n" number in the DECIMAL(m,n) is 2, there are other reasons for which you may prefer to do the calculations in tenths of cents or less). And anytime you do a select, insert, update or other SQL operation on such a number, the database engine is making the conversion between dollars and cents without telling you, so that you don't have to worry about that.

There are a number of other gotchas in this field. For example, even using integer representations of monetary amount, OK, you solve the machine internal representation of decimal numbers, but there are a number of other reasons for which you might end up with very difficult to solve rounding problems. Date proratas can also be a serious headache. etc.

May be you want to read this important document:
http://docs.oracle.com/cd/E19957-01/806-3568/ncg_goldberg.html to get an idea of the problems involved.

Search for (options)

Web Applications & Managed Hosting Powered by Gossamer Threads
Visit our Mailing List Archives