scientific notation problem

We're developing a system that's used for tracking company IPO data and we're running into a few number storage issues. For large numbers, i.e. numbers greater than 100 million, MySQL insists on storing these as scientific notation. I don't really have a problem with that but when we want to edit these fields using something like:

<% form_for(@company) do |f| %>   <p>     <%= f.text_field :some_large_number_field , :size => 20 %>     <%= f.submit "Update" %>   </p> <% end %>

the field is presented to the user with the number in scientific notation form, not what we want unfortunatly, since the users won't understand this. We've looked at a bunch of documentation but can only find info on getting numbers into scientific notation, not the other way around. Is there a way to either force MySQL to store the numbers normally or have them formatted on the way out?

Dale

Why not just store the number as a String and convert it to a number in Ruby?

Chris

We're developing a system that's used for tracking company IPO data and we're running into a few number storage issues. For large numbers, i.e. numbers greater than 100 million, MySQL insists on storing these as scientific notation.

What's wrong with DECIMAL date type?

create table t(n decimal(12,2));

Query OK, 0 rows affected (0.04 sec)

insert into t values(1234567890.11);

Query OK, 1 row affected (0.01 sec)

100 Million isn't that large, certainly not for a float. There are so many issues with doing it this way I don't know where to start. Thanks for the suggestion though.

Because decimals have fixed decimal places, which just doesn't work with this kind of financial data - not all numbers are going to be dollar and cent amounts.

Let me clarify this question. I'm not looking for other storage types, I'm looking for an answer as to

1. Why MySQL would be forcing some of the large numbers into scientific notation and if there's a way to stop it from doing that. 2. Baring that, is there a simple way to force the formatting back into standard notation for editing?

Thanks Dale

To clarify this even further. Can you let us know what kind of object you are using to store the value in Ruby and what type you are using for the row in MySQL?

Because decimals have fixed decimal places, which just doesn't work with this kind of financial data - not all numbers are going to be dollar and cent amounts.

Well it shouldn't be dollar and cents, you can define how many decimal places you want. Other types will give you more rounding errors, is that good for financial data?

Regards, Rimantas

If it's money you are talking about, use the Money gem. It takes care of lots of the syntax and conversion and accuracy for you. It actually stores the values as very large integers (i.e. cents).

http://dist.leetsoft.com/api/money/

-Danimal

I don't know how to attack MySQL. Sorry.

When you pull the info from the database, does @number.to_f give you scientific still?

-Kyle