Incorrect Integer Value for MySQL Insert

Rails
1

I am trying to insert a line of data into a table in MySQL 5, the data I
am using is:

StartTime: 20090413 13:41:53.000 TestName:changehistory.pl Pass:20
Fail:0 Total:20

Using this function I strip the data down and am going to insert it
using the ActiveRecord insert function (I’m not using Rails as this is a
Ruby app) so I am loading the ActiveRecord library and using it
directly.

class AutoHarnesses < ActiveRecord::Base
end

def test_add(line,name)
    @machine = name[2]
    begin
        # Test Results will have data in the line
        if line =~ /data/
            # Scan the line and pull out the pairs we want
            # for eventual sql insert, thank you ruby-forums!
            s = line.scan(/(\w+):\s*([\s\d:.]+|\S+)/)
            s.shift
            # Assign values
            s.each { |key,value|
                if key == "TestName"
                    then @testname = value
                elsif key == "Pass"
                    then @passes = value.to_i
                elsif key == "Fail"
                    then @failures = value.to_i
                elsif key == "Total"
                    then @totals = value.to_i
                else
                    LOGGER.warn "Did not find a recognizable test

result."
end
}
agNew = AutoHarnesses.connection.insert(“INSERT into
auto_harnesses
(machinename,testname,test_pass,test_fail,
test_total) VALUES (”{@machine}","{@testname}",
“{@passes}”, “{@failures}”, “{@totals}”)")
end
rescue
# Print out errors
LOGGER.warn $!
end
end

If I run some debugging statements after assinging the values I get:
Test changehistory.pl on testbox20 had 20 passes and 0 fails from a
total of 20. So I know the values are being set properly.

The data looks ok, but the insert returns the following error:
#<ActiveRecord::StatementInvalid: Mysql::Error: Incorrect integer value:
‘{@passes}’ for column ‘test_pass’ at row 1: INSERT into
auto_harnesses
(machinename,testname,test_pass,test_fail,
test_total) VALUES ("{@machine}","{@testname}",
“{@passes}”, “{@failures}”, “{@totals}”)>

I thought converting the Pass, Fail and Total values to_i would be best,
since those database columns are int, but that does not seem to resolve
anything. I thought maybe the double quotes was somehow converting the
integer values to strings again but I got a SQL error instead so I don’t
think that is it either. Google and a search of the forums has not
shown me anything that is helping me figure out what is going on.

Anyone encountered something similar they can share some insights with?

Thanks.

2

Michael Furmaniuk wrote:
[…]

I thought maybe the double quotes was somehow converting the
integer values to strings again

That is part of the problem, but not the whole problem. Remove the
double quotes.

but I got a SQL error instead so I don’t
think that is it either.

Did you read what the SQL error is telling you? ‘{@passes}’ is not a
legitimate integer, and the DB doesn’t know what to do with it. Ruby is
not Perl, and so the “{@passes}” construct in your query string will not
do any variable interpolation, but will rather send ‘{@passes}’
literally to the DB. If you want variable interpolation in Ruby, you
must put a # before the braces.

Anyway, why are you building a query string like that? That’s
ActiveRecord’s job – just pass it a hash of attributes. As it stands
right now, you’re essentially not using ActiveRecord. Either use it
properly or remove it from your app.

Best,

Marnen Laibow-Koser
http://www.marnen.org
[email protected]

3

Marnen Laibow-Koser wrote:

Anyway, why are you building a query string like that? That’s
ActiveRecord’s job – just pass it a hash of attributes. As it stands
right now, you’re essentially not using ActiveRecord.

I’m still getting used to ActiveRecord outside of Rails and having a
hard time coming up with the right hash, for now making my own query
seemed the best option since I can understand it. When I can find a
good tutorial that describes how to build custom hashes I’ll make the
switch.

Thanks for the help, I didn’t realize that about the variable
interpolation.

4

Michael Furmaniuk wrote:
[…]

I’m still getting used to ActiveRecord outside of Rails and having a
hard time coming up with the right hash,

I’ve never used AR outside Rails, but from the information at

it looks like you’d use the same options as in Rails – you just have to
explicitly connect to the DB.

Note: it took about 30 seconds of Web searching to find that article. :slight_smile:

for now making my own query
seemed the best option since I can understand it.

If you’re going to use a library, take the time to learn how to use
it.

When I can find a
good tutorial that describes how to build custom hashes I’ll make the
switch.

See above.

Thanks for the help, I didn’t realize that about the variable
interpolation.

You’re welcome! Perhaps you should read through the pickaxe book and
make sure you’re not missing other Ruby basics.

Best,

Marnen Laibow-Koser
http://www.marnen.org
[email protected]