MySQL performing math operations on strings?

Question

I am trying to store latitude and longitude information in MySQL. I am using Ruby to parse the latitude and longitude data. They are parsed separately and then combined into a string variable. So if I have:

latitude = 43.78219.to_s
longitude = -75.00326.to_s
location = latitude + " " + longitude  # => "43.78219 -75.00326"

I have a table in MySQL to store this data, and the data type is set to String varchar. The problem is that when I insert the location string into the table, MySQL performs a math operation and subtracts the two numbers in the string. Then I'm like Whaaaaaaaaa??????? So it ends up storing -31.22107 as the location. Am I doing something wrong?

Here is the query I'm using

db.query("insert into StationCapacityStatus
        (city_id, station_id, location, empty_docks, nonempty_docks, timestamp) 
        values(
            23,
            #{$station_number},
            #{location},
            #{$empty_docks},
            #{$nonempty_docks},
            #{$datetime}
        )"
)

Add the query you are using to save it to the database. Chances are if it isn't in quotes, then it is picking it up as an equation. — judda
– judda, Commented Mar 7, 2012 at 3:41
I agree with peterbond here. It makes more logical sense to split them up. I don't know ruby enough to know the syntax on how to change your query, so I'll leave that to someone else. — judda
– judda, Commented Mar 7, 2012 at 3:49
I guess I'll have to go the 2 columns route to reduce the headache. Thanks. — icanc
– icanc, Commented Mar 7, 2012 at 3:57

mu is too short · Accepted Answer · 2012-03-07 04:05:37Z

2

You're not quoting the location so MySQL's sees a plain 43.78219 -75.00326 inside the VALUES and interprets it as a floating point expression. You should use quote method to properly escape the strings as well:

db.query("insert into StationCapacityStatus
    (city_id, station_id, location, empty_docks, nonempty_docks, timestamp) 
    values(
        23,
        #{$station_number},
        '#{db.quote(location)}',
        #{$empty_docks},
        #{$nonempty_docks},
        #{$datetime}
    )"
)

You should also switch to prepared statements with bound values if possible:

sth = db.prepare('insert into StationCapacityStatus (city_id, station_id, location, empty_docks, nonempty_docks, timestamp) values (?, ?, ?, ?, ?, ?)')
sth.execute(23, $station_number, location, $empty_docks, $nonempty_docks, $datetime)

answered Mar 7, 2012 at 4:05

mu is too short

436k71 gold badges863 silver badges822 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

mu is too short Over a year ago

@icanc: You're welcome, separate columns (and db.prepare) is a good plan.

Collectives™ on Stack Overflow

MySQL performing math operations on strings?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related