Bizzare comma bug

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

Dear Monks, I am totally perplexed about a rather bizzare little bug I cannot seem to fix;


  my $price = '23,356.00';
  my $itemref = 'S10099';   

  print "price = $price\n";

  $price =~ s/,//;

  print "new price = $price\n";

  my $query = "UPDATE items SET price='$price' WHERE itemref='$itemref
+';";

   print $query;

   $sth = $dbh->prepare ( $query );
   $sth->execute ();
   $sth->finish ();
[download]

The price column in the database is a decimal(15,2)

Ok so the first print of price gives the expected '23,356.00'

the strip commas regex runs

second print out of the price var shows it has removed the commas:

'23356.00'

the print out of the query reads :

UPDATE items SET price='23501.35' WHERE itemref='S10099';

however when I check the value saved in the database what I find is :

23.00

which is the price with everything after where the comma WAS... removed for no explicable reason.

I tried entering the number raw with commas into the db, same effect.

I tried putting the s/,//; into a sub called removecommas and setting a new var to its return... same effect.

can someone help me please?

-------- UPDATE -----------

I have implemented the changes in structure suggested by the kind chaps below, however it still didn't work.

I then changed the SQL column type to char(15), and had a look at what I got, for some reason the s/,//; is not working, even tho it prints out the var as if it has worked, the SQL was still receiving the same var with the commas in. Thankfully my price sorting routines still work even on a char column, so for now Im going to have to put up with it.

Thanks All.

Si

Comment on Bizzare comma bug Download Code

Replies are listed 'Best First'.
Re: Bizzare comma bug by kennethk (Abbot) on Jan 30, 2009 at 14:42 UTC
If the field is decimal, why are you trying to store a string in it? The usual syntax in that condition is `UPDATE items SET price=23501.35 WHERE itemref='S10099';` Second, rather than directly storing the value in the string, you should use placeholders: perhaps something like this: `my $query = "UPDATE items SET price=? WHERE itemref=?"; $sth = $dbh->prepare ( $query ); $sth->execute ($price, $itemref); $sth->finish ();` [download] It can make your life significantly easier - see DBI for details.	[reply] [d/l] [select]
Re: Bizzare comma bug by ikegami (Patriarch) on Jan 30, 2009 at 14:45 UTC
Putting everything all values quotes is not correct. It's not enough to convert strings into SQL literals, and it's just plain wrong for numbers. You should be using replaceable parameters aka placeholders. See the DBI docs and PerlMonks for more info on these. `my $price = '23,356.00'; my $itemref = 'S10099'; my $query = "UPDATE items SET price=? WHERE itemref=?;"; <--- $sth = $dbh->prepare ( $query ); $sth->execute ( $price, $itemref ); <--- $sth->finish ();` [download] Not saying this will fix the problem you asked about, but it might. It should be done either way.	[reply] [d/l]
Re: Bizzare comma bug by dreadpiratepeter (Priest) on Jan 30, 2009 at 14:46 UTC
This code looks like it should work. here are a few things i would check.: go into the sql tool for whatever DB you are using and try entering the command you think you are sending. The DB could have a trigger that does something to the data. look at your schema, you are treating prices as a string in the sql statement, but it may be types are something else in the db and you are getting somekind of conversion bug. try using placeholders instead of interpolation: `my $query = "UPDATE items SET price=? WHERE itemref=?;"; print $query; $sth = $dbh->prepare ( $query ); $sth->execute ($price,$itemref);` [download] This may solve the datatyping issues above. You are not checking the prepare or execute for errors, there is a good chance your code isn't updating anything in the db and you don't know it. If all the fails, try moving the comma in the string and see if that changes the results. ie '233,56.00'. if it does that's more information. -pete "Worry is like a rocking chair. It gives you something to do, but it doesn't get you anywhere."	[reply] [d/l]
Re: Bizzare comma bug by Rodster001 (Pilgrim) on Jan 30, 2009 at 20:06 UTC
Your code DOES work. I just set up a table and tried it and was unable to reproduce the result you are getting unless I commented out the regex replacement. Are you running the exact code you posted or did you strip something out? I would break this out to another script so you eliminate any other factors. As posted your code works. But, the other responses are correct... use placeholders.	[reply]
Re: Bizzare comma bug by targetsmart (Curate) on Jan 31, 2009 at 07:27 UTC
What database(along with version) you are using, so that we would check locally and give you directions. Vivek -- In accordance with the prarabdha of each, the One whose function it is to ordain makes each to act. What will not happen will never happen, whatever effort one may put forth. And what will happen will not fail to happen, however much one may seek to prevent it. This is certain. The part of wisdom therefore is to stay quiet.	[reply]
Re: Bizzare comma bug by Argel (Prior) on Jan 31, 2009 at 00:45 UTC
What happens if you specify the comma via its octal code? I like to do this when I run into unexpected behavior like you have. Note that you need the backslash. `$price =~ s/\054//;` [download] Elda Taluta; Sarks Sark; Ark Arks	[reply] [d/l]
Re: Bizzare comma bug by Anonymous Monk on Feb 02, 2009 at 01:14 UTC
You probably mean: `$price =~ s/,//g;` [download] The g is important so you also remove the second comma, for instance, in 4,332,653.00 Not that this is the problem, but fix that too.	[reply] [d/l]
Re: Bizzare comma bug by Anonymous Monk on Feb 02, 2009 at 12:05 UTC
Try adding: $price += 0; ... after the regex replacement. I've seen similar problems before with the informix database. The DBD driver seems to treat values differently if the last operation on a variable treated it like a string rather than a number.	[reply]
Re: Bizzare comma bug by Anonymous Monk on Feb 01, 2009 at 00:03 UTC
If "new price = 23356.00" then why "UPDATE items SET price='23501.35' WHERE itemref='S10099';" ? The bug is not in removing the comma. It is elsewhere in your code.	[reply]