How to set the database to accept currency values?

Question

I'm writing a Sinatra application with Sequel. For the first time, I'm trying to add a currency. After looking on-line I used Numeric as a value, of course goes without saying that I need a precision of decimal points.

I know that in MySQL, Numeric and Decimal is the same. So I wonder what I'm doing wrong here. I have the feeling that I need to define the decimal points somehow. Here my model:

# Contracts table: Here the users can define their contracs
DB.create_table?(:contracts, engine: 'InnoDB') do
    primary_key :id
    Integer :user_id,   null: false
    String  :name,      null: false
    String  :description
    Numeric :cost,      null: false
end

Every time I try to submit a value with decimal points I get the following error, which I think it's a validation error. I don't use validations explicitly yet, so I think it's either Sequel or MySQL specific.

enter image description here

How should I change my models in order to allow me to add decimal values?

EDIT: As requested I add my controller (routes) file:

class Metro < Sinatra::Base
    get "/user_add_contract" do
        protected!
        haml :user_add_contract
    end

    post "/user_add_contract" do
        protected!
        user = session['name']
        begin
            uid = User.first(username: user)
            Contract.create(user_id: uid[:id], name: params['name'], description: params['description'], cost: params['cost'].to_f)
            redirect '/user_list_contract'
        rescue Exception => e
            @error = e
            haml :details_error
        end


    end

end

and the HAML (views):

%form(action="/user_add_contract" method="post")
  %fieldset
    %legend Φόρμα Νέου Συμβολαίου
    %div{class: 'column1of2'}
      %ul
        %li
          %label(for="name")Ονομασία:
          %input#name(name="text" name="name")
        %li
          %label(for="description")Περιγραφή:
          %textarea.email#description(type="text" name="description")
        %li
          %label(foor="cost")Κόστος:
          %input#cost(type="number" name="cost")
        %li
          %input(type="submit" value="Εγγραφη") ή <a href="/panel">Ακύρωση</a>

thanks

Can you post the code showing how you handle the input values? Also, is the error appearing immediately or when you hit submit? — acsmith, Jan 21 '14 at 01:19
@acsmith Sure, here are the other two files involved. I'm not sure if in the view (HAML) the line `%input#cost(type="number" name="cost")` can accept a float or decimal instead of type 'number'. — patm, Jan 21 '14 at 09:13
Yes that was, however @acsmith since your insight led me to the right answer, if you want write the answer so I can upvote it later today. :-) — patm, Jan 21 '14 at 09:19
Hmmm apparently another problem came up. Now it saves the value in the DB but not the decimals, so when I issue 80.34 I can see the value '80' in the DB instead of 80.34 — patm, Jan 21 '14 at 09:28

score 1 · Accepted Answer · edited May 23 '17 at 12:19

Related answer: How to handle floats and decimal separators with html5 input type number

The step="" attribute defaults to 1 here, meaning that it will truncate everything after the decimal point (or kick back an error, depending on how it's set up and what browser you're using).

Try setting step="0.01", on the input element, assuming that you just want to get to the nearest cent, and see if that works. Everything else in your code looks fine.*

Except that you have "foor" instead of "for" in your cost <label>

How to set the database to accept currency values?

1 Answers1