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
Popular Articles by Our Team
Our expert team of designers and developers love what the do and enjoy sharing their knowledge with the world.
-
The Benefits of Ruby on Rails
-
Heroku Forcing Password Resets as GitHub Investigation Continues
-
How to Maximise your ROI with a Custom Application Built in Sa...
We Hire Only the Best
reinteractive is Australia’s largest dedicated Ruby on Rails development company. We don’t cut corners and we know what we are doing.
We are an organisation made up of amazing individuals and we take pride in our team. We are 100% remote work enabling us to choose the best talent no matter which part of the country they live in. reinteractive is dedicated to making it a great place for any developer to work.
Free Community Workshops
We created the Ruby on Rails InstallFest and Ruby on Rails Development Hub to help introduce new people to software development and to help existing developers hone their skills. These workshops provide invaluable mentorship to train developers, addressing key skills shortages in the industry. Software development is a great career choice for all ages and these events help you get started and skilled up.