CODEWITHSUNDEEP
mysqlruby-on-railsruby
gerwin
gerwin

Reputation: 31

Ruby on Rails doesn't store decimals in mysql database: truncates numbers after ,

I'm facing a problem with Rails application to store decimals in my mysql database. I have a form with two fields: "amount" and "currency". When I enter a decimal value in the "amount" field (for example 1,22) Rails just stores the 1 in the database.

My log file looks like this:

Started POST "/cashamounts" for 127.0.0.1 at 2012-02-04 13:23:54 +0100
  Processing by CashamountsController#create as HTML
  Parameters: {"utf8"=>"✓","authenticity_token"=>"QpWGfEtDR1tc7wTFmZZst9gYjKAyXtRypilsxDE9Tzs=", "cashamount"=>{"currency_id"=>"eur", "amount"=>"1,22"}, "commit"=>"Create Cashamount"}
  [1m[36mCurrency Load (2.8ms)[0m  [1mSELECT `currencies`.* FROM `currencies` ORDER BY name[0m
  [1m[35m (0.2ms)[0m  BEGIN
  [1m[36mSQL (0.3ms)[0m  [1mINSERT INTO `cashamounts` (`amount`, `created_at`, `currency_id`, `updated_at`) VALUES (1, '2012-02-04 12:23:54', 'eur', '2012-02-04 12:23:54')[0m
  [1m[35m (0.6ms)[0m  COMMIT
Redirected to http://localhost:3000/cashamounts/8
Completed 302 Found in 94ms

So the amount is stored in the params correctly, but is not inserted correctly into the database. I checked my db/schema.rb file. The line for the column "amount" is:

t.decimal  "amount",               :precision => 10, :scale => 2

Where else can I look to find the problem?

PS. I started off with using the money gem, but that showed the same problem: all digits after the "," are not stored.

Upvotes: 2

Views: 2630

Answers (3)

JP Slavinsky
JP Slavinsky

Reputation: 345

This question is a bit old, but for future visitors I believe the answer can be found in the "Gotcha" section of:

http://torontoprogrammer.ca/2010/05/decimal-numbers-in-rails-and-mysql/.

Basically, when you set up the decimal field you have to specify the scale argument to say how many places should be to the right of the decimal point. Otherwise, mysql assumes that you want all of your digits to the left of the decimal point.

This problem bit me too. Everything worked fine in my dev environment (using sqlite), but in production (using mysql), the fractional part of my "decimal" numbers was getting truncated.

Upvotes: 6

d11wtq
d11wtq

Reputation: 35308

1,22 is the European notation for decimal values, while 1.22 is the British, American and Australian convention. You may need to set your locale.

http://guides.rubyonrails.org/i18n.html#optional-custom-i18n-configuration-setup

In an initializer:

I18n.default_locale = :fr

Or at the end of config/application.rb

config.i18n.default_locale = :fr

Upvotes: 2

dee-see
dee-see

Reputation: 24078

The problem is that Rails expects a decimal point and not a comma. If this is user input try substituting the , for . before saving to the database.

Upvotes: 0

Related Questions