Raw SQL from a Rake Task

rubyonrails-talk
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

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

3

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...

4

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

ActiveRecord::Base.connection.execute(...)

Fred

5

Frederick Cheung 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?

6

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

7

Roy Pardee 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','<html><body>Hello</body></html>','\r\nborder:1px; \r\nborder-color:#333333; \r\nborder-style:solid;\r\n}\r\n','','0');

8

Joe Peck wrote:

Roy Pardee 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','<html><body>Hello</body></html>','\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.

9

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