Raw SQL from a Rake Task

Rails
1

How do I execute a raw SQL file from a rake task? Right now I have
this:

require ‘active_record’
desc “Get all the pico HTML mess into a Ruby model”
task :muck => :environment do
ActiveRecord::Base.establish_connection(RAILS_ENV.to_sym)
puts “About to process 'uge SQL file…\n”
execute “#{RAILS_ROOT}/db/html_template.sql”
puts “File executed\n”
end

But when I try it, I get this error:
undefined method `execute’ for main:Object

Anyone know how to use a raw sql file in a rake task?

2

import foo.sql

No, that’s not quite it, because it’s a ruby command on a .sql file.
It’s not a rake task, but accomplishes what you’re trying to do.

I believe that rake can be used to import CVS data, which might be
more convenient…

-Thufir
For some reason, running “import foo.sql” opens up X11, then hangs.
There must be some way to execute SQL in a rake task…

3

I also would be interested to know how to do this!

On pg 38 (?) of “ruby on rails, up and running” by oreilly, they
describe something along the lines of:

import foo.sql

No, that’s not quite it, because it’s a ruby command on a .sql file.
It’s not a rake task, but accomplishes what you’re trying to do.

I believe that rake can be used to import CVS data, which might be
more convenient…

-Thufir

4

On 13 Feb 2008, at 20:48, Joe P. wrote:

puts “File executed\n”
end

But when I try it, I get this error:
undefined method `execute’ for main:Object

ActiveRecord::Base.connection.execute(…)

Fred

5

Can you just shell out to whatever command-line tool your database
supports for executing large sql files?

6

Frederick C. wrote:

ActiveRecord::Base.connection.execute(…)

Fred

That seems to work if actual sql code is put in the parenthesis, but how
can I run SQL out of a sql file in the rake task?

Doing this in the rake task:
ActiveRecord::Base.connection.execute("#{RAILS_ROOT}/db/html_template.sql")

gives me this error:
rake aborted!
Mysql::Error: You have an error in your SQL syntax; check the manual
that corresponds to your MySQL server version for the right syntax to
use near ‘/projects/trieste2/db/html_template.sql’ at line 1:
/projects/trieste2/db/html_template.sql

How can I run the sql that is in the sql file?

7

Joe P. wrote:

Roy P. wrote:

Can you just shell out to whatever command-line tool your database
supports for executing large sql files?

Yeah, I can. Now I ran into good ol’ Error 1136: Column count doesn’t
match value count at row 1

It really looks to me like it should work, but I’m sure there’s some
tiny thing that is wrong.

insert into html_templates (id, name, htmlcode, style, js, count)
values(‘1000’,‘Mega
Template’,‘Hello’,‘\r\nborder:1px;
\r\nborder-color:#333333; \r\nborder-style:solid;\r\n}\r\n’,‘’,‘0’);

Okay, figured it out. I’ll post this in case someone else stumbles
around looking for how to fix this error.

http://htmlfixit.com/cgi-tutes/tutorial_MySQL_Error_Invalid_Query_Column_Count_Does_Not_Match_Value_Count.php

8

you need to load the .sql file into ruby,
and then call .execute on it.

sql = File.open(“some.sql”).read
sql.split(‘;’).each do |sql_statement|
ActiveRecord::Base.connection.execute(sql_statement)
end

We split it by “;” because a .sql file may contain multiple
statgements,
and you’ll need to execute those seperately.

But that should work fine.

Yours,
MatthewRudy
http://workingwithrails.com/person/12394-matthew-rudy-jacobs

9

Roy P. wrote:

Can you just shell out to whatever command-line tool your database
supports for executing large sql files?

Yeah, I can. Now I ran into good ol’ Error 1136: Column count doesn’t
match value count at row 1

It really looks to me like it should work, but I’m sure there’s some
tiny thing that is wrong.

insert into html_templates (id, name, htmlcode, style, js, count)
values(‘1000’,‘Mega
Template’,‘Hello’,’\r\nborder:1px;
\r\nborder-color:#333333; \r\nborder-style:solid;\r\n}\r\n’,’’,‘0’);