Using variations of prepare to help with messy sql inserts

Background

One project I've been working on has a bunch of remote MySQL databases that we need to attach to (but don't control).

The user specifies columns and we run jobs that insert data into these remote tables. This, of course, generates some really ugly sql, especially when the data you're inserting contains scraped websites.

One Solution

Most DB drivers have something along the lines of prepare. Of course, it's different for each driver. This is a "just as much as I needed" class that handles inserts for MySQL and PostgreSQL.

ps = PreparedSql.new(ActiveRecord::Base.connection)
ps.execute_prepared_sql('scrape_results', 
      ['id', 'url', 'data'], 
      [1, 'http://site.com', '<h1>Site.com</h1>'])

Adding to your Rails project:

git clone git@github.com:wiseleyb/rails_preprared_sql.git 
cp rails_prepared_sql/prepared_sql.rb [yourproject]/lib/

More info

• Sample code
• Discussion for PostgreSQL on StackOverflow
• Doc for PostgreSQL connection
• Blog post for MySQL
• Doc for MySQL connection
• Discussion for SQLite3 on StackOverflow * note - I didn't implement this in this project
• Doc for SQLite3