views:

487

answers:

1

I have a bunch of SQL statements to execute on a database. (I'm doing things that Rails doesn't provide methods for, as far as I know: creating views, adding foreign keys, etc. It's mostly for non-Rails interaction with the data.) In essence, I'm doing the following:

sql = "statement_1; statement_2; statement_3; etc;"
ActiveRecord::Base.connection.execute(sql)

Or with newlines, like so:

sql = <<EOF
statement_1;
statement_2;
statement_3;
etc;
EOF
ActiveRecord::Base.connection.execute(sql)

(Obviously, these statements are just place holders, but I don't think their content matters, according to my tests.)

In either case, only the first statement is executed and the others seem to be ignored. Is that what's going on? I'm only seeing the effects of the first statement whenever I try more than one at a time. Do I need to execute each one separately? One set of statements is coming from a file, so it'd be nice to just load the contents of the file and execute. If there are better strategies I could adopt, I'd be interested in them.

I was hoping the documentation on execute would shed some light, but besides using the singular ("statement"), it doesn't. Perhaps it's because of the database engine I'm using? (For reference, I'm using SQLite at the moment.)

UPDATE: I ended up writing a method that does the following:

def extract_sql_statements(sql)
  statements = []

  sql.split(';').each do |statement|
    statement.strip!

    unless statement.empty?
      statement += ';'
      statements << statement
    end
  end

  return statements
end

...and then looping over statements. It's fixed the problem, but if there are more elegant solutions, I would be interested in hearing about them.

+1  A: 

If you look at the rails code then you will find that execute method runs the passed sql, so it should essentially run all your queries as long as they are ';' separated and valid.

EDIT: Sorry! No it won't because it will add ';' in between your query string and complain about wrong syntax

nas
Hrm... that's interesting. I can spit out the SQL statements and do `sqlite3 db.sqlite3 < my_code.sql` and it all works as expected. If what you're saying is true, I would think that it should work.
Benjamin Oakes
It doesn't complain at all though... that's the weird part. Just seems to ignore everything past the first statement and fail silently.
Benjamin Oakes
May be due to different dbs because I tried on mysql and you are on sqlite
nas
Yeah, SQLite is often *too* forgiving. You're saying it fails on MySQL though? Does it work if you run each statement separately?
Benjamin Oakes
by separately you mean running execute method separately for each sql query, then principally it should work.
nas
Well, good to know. I'll just go that route, I suppose. Thank you for your help.
Benjamin Oakes