Grabbing Data from a Foreign Database From Rails

I love Ruby on Rails.  After I did my first Ruby on Rails tutorial, the famous Rolling with Ruby on Rails at O'Reilly (this tutorial is now out of date), I was hooked.

Rails did so many things for me, like tying code to the data layer.  Validation.  Scaffolding.  Writing my unit tests for me. :)

However, I put a lot of time and effort into my blog.  The software is b2evolution, which I like a lot.  Unfortunately, b2evolution runs on PHP and mySQL.  So integration with my Ruby on Rails web site is a bit tricky.  Of course, I could have attempted to use another blogging package that is built on Rails, like Typo--I just didn't want to go to the work to convert all my content into yet another package.

So I just posted a link to my blog from my default layout so that it showed up on the sidebar.  However, I lost some old functionality from when my website was all written in PHP and I could easily talk to other databases all willy-nilly.  I used to be able to read from the b2evolution database and grab a little tiny bit of my last post, so I could put a teaser link in the side menu.  So what if no one actually used it or cared--I cared!--and I wanted that functionality back in my Rails project.

So here's how I did it.

Edit config/database.yml

First, create an entry in your config/database.yml file for the external database.  Obviously, you must customize it for your own use.

# Find me in config/database.yml
b2evo:
  adapter: mysql
  encoding: utf8
  database: your_database_name 
  pool: 5
  username: your_top_secret_username 
  password: your_top_secret_password 
  host: mysqlhost.yourdomain.com

Second, create a B2evo class that inherits from ActiveRecord::Base

You can actually name the class whatever makes sense for your application.

# Find me in app/models/b2evo.rb
class B2evo < ActiveRecord::Base

  # class created to poll b2evo stuff

  def self.last_blog_post
    # read the connection info from the database.yml file
    b2evo_connection_hash = configurations["b2evo"]

    # connect to the external database
    establish_connection b2evo_connection_hash
    con = connection()

    # read three columns into three variables
    post_title, post_content, post_datecreated =
      con.execute("select post_title
        ,post_content AS post_content
        ,post_datecreated
      from evo_posts
     where post_status = 'published'
     order by post_ID desc limit 1").fetch_row;

    # this is important or future ActiveRecord calls will fail
    # because they'll be talking to the wrong database!
    remove_connection

    # this connects me back to the default rails database
    establish_connection configurations[RAILS_ENV]

    # Clean the HTML to make sure it's safe for human consumption
    sanitized_post_content = Sanitize.clean(post_content)
    datetime_of_post = Time.parse(post_datecreated)

    # build a dynamic link which gives a hint to my latest blog post
    link = "<div class=\"sn\">Last Blog (#{datetime_of_post.strftime("%Y-%m-%d")})
      </div><a href=\"http://www.paultastic.com/blog.php\" title=\"
      Click here to view the full blog entry from #{post_datecreated}\">
      \"#{post_title}: #{sanitized_post_content[0..30]}...\"</a>"

    return link
  end
end

Third, reference the code in your web page

<% cache do %>
<%= B2evo.last_blog_post %>
<% end %>

I hope you found this useful.  I sure did, and now my blog teaser link is back in action!

For those of you who are curious, Sanitize is a gem based on Hpricot--a full-fledged HTML parser.  If you don't want to install the gem, just use a regular expression or something to clean your input.

Last modified about over 1 year ago.