Forum: Ruby on Rails Incorrect Integer Value for MySQL Insert

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Michael F. (Guest)
on 2009-04-17 19:40
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.
Marnen L. (Guest)
on 2009-04-17 20:25
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
removed_email_address@domain.invalid
Michael F. (Guest)
on 2009-04-17 21:39
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.
Marnen L. (Guest)
on 2009-04-18 00:21
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
http://www.juixe.com/techknow/index.php/2009/01/14...
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. :)

> 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
removed_email_address@domain.invalid
This topic is locked and can not be replied to.