String<->float conversion headaches

[pmak]

I'm programming an application that saves its data in a MySQL
database. I frequently run into headaches where I get errors like:

undefined method `-' for "9000.00":String (NameError)

because I typed

source['salary'] -= encumbered['salary']

where source[] and encumbered[] were instantiated from
MysqlResult#fetch_hash, and are thus strings instead of integers or
floats.

I write this sort of thing all the time in Perl and don't have trouble
because of its automatic type conversion. What would be the best way
to do this in Ruby? Right now I'm writing:

source['salary'] = source['salary'].to_f -
encumbered['salary'].to_f

everywhere and it feels quite tedious.

 

[Malte Milatz]

pmak:

I write this sort of thing all the time in Perl and don't have trouble
because of its automatic type conversion. What would be the best way to do
this in Ruby? Right now I'm writing:

source['salary'] = source['salary'].to_f -
encumbered['salary'].to_f

everywhere and it feels quite tedious.

Write a simple class around your MySql table doing the conversion behind
the scenes.

If you like something like this automatically, then ActiveRecord (a
library which is part of RubyOnRails) is what you're looking for. If, on
the other hand, you'd like a class which creates an SQL table from your
class definition, then you might have a look at Og, for which a tutorial
can be found at rubygarden.org.

Or maybe I understood your question the wrong way - is the salary in the
example above saved as in the database as a float or as a string?

Malte

 

[peufeu]

I was under the assumption that any decent database adapter would convert
the column into the relevant native language types for you (ie. integer
column => ruby integer).
Is this not the case ?

I had problems in Python because the postgresql adapter only managed the
default types (int, float, string, bool) and not the fancy types (arrays,
trees, geometric coordinates...) but in your case it might be worthy to
use an object to database mapper to automate the process ?

 

[Robert Klemme]

I'm programming an application that saves its data in a MySQL
database. I frequently run into headaches where I get errors like:

undefined method `-' for "9000.00":String (NameError)

because I typed

source['salary'] -= encumbered['salary']

where source[] and encumbered[] were instantiated from
MysqlResult#fetch_hash, and are thus strings instead of integers or
floats.

I write this sort of thing all the time in Perl and don't have trouble
because of its automatic type conversion. What would be the best way
to do this in Ruby? Right now I'm writing:

source['salary'] = source['salary'].to_f -
encumbered['salary'].to_f

everywhere and it feels quite tedious.

One option is to change table data types to Float. DBI should then return
proper Ruby instances (i.e. Floats) for them. This is also more efficient
from a db storage point of view than having those numbers as strings in
there.

Kind regards

robert

 

[Andreas Schwarz]

source['salary'] = source['salary'].to_f -
encumbered['salary'].to_f

Don't use Float for monetary values. Here's why:
http://www-106.ibm.com/developerworks/java/library/j-jtp0114/
The BigDecimal class is what you need.

 

[pmak]

The corresponding column in the MySQL database is of type DECIMAL(7,2),
but the corresponding Ruby structure came out as a String instead of a
decimal type. I haven't updated my database driver in a few years,
though; perhaps I'll look into that. Thanks.

 

[Brian Schröder]

The corresponding column in the MySQL database is of type DECIMAL(7,2),
but the corresponding Ruby structure came out as a String instead of a
decimal type. I haven't updated my database driver in a few years,
though; perhaps I'll look into that. Thanks.

DECIMAL(7.2) should not be converted to a float value, as floats can't
represent all instances of DECIMAL(7.2). For example 0.1 can not be
represented as a float.

irb(main):010:0> "%.18f" % 0.1
=> "0.100000000000000006"

regards,

Brian

 

[Michael Campbell]

DECIMAL(7.2) should not be converted to a float value, as floats can't
represent all instances of DECIMAL(7.2). For example 0.1 can not be
represented as a float.

irb(main):010:0> "%.18f" % 0.1
=> "0.100000000000000006"

In general, using non BCD type number encoding systems, *No* real
number that isn't a summation of powers of 2 can be represented by a
float.

By your logic then, does this mean that they just shouldn't be used
for any "DECIMAL(x, y)" (y > 0) field in a database?

 

[Brian Schröder]

In general, using non BCD type number encoding systems, *No* real
number that isn't a summation of powers of 2 can be represented by a
float.

By your logic then, does this mean that they just shouldn't be used
for any "DECIMAL(x, y)" (y > 0) field in a database?

That depends on what you are using the numbers for. If it is for
simulation of something the float resolutions are good, but in this
special example decimal(7,2) hinted a monetary value to me, and there
it may be bad if you sum up rounding errors.

best regards,

Brian