Header My Outline
Clear Back

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.




# Comment from

over 1 year ago.   

You can now leave comments! Enjoy.
Omaha Weather
Click to Enlarge 73 Sml_a33

Clear, 55

55
51
More Weather
What's New
Best Encryption Software Ban Water! It is Dangerous! A Visual Guide to Inflation Avatar to be Released Tomorrow My Favorite Two Line Phone Pranks
Last Blog (2009-03-28)
"Finally! I'm on Twitter!: It took me long enough. :) ..."
Popular Pages
What's New Deep Thoughts by Jack Handey Invoke Updates the Parent Form From Child Thread Get Rid of Cable With TiVo and a Converter Box How to Win An Argument
Deep Thought
In my next life, I hope I come back as a parrot, because I already know quite a few words.
Sponsored Links

All content © 1997-2010 . All Rights Reserved. Privacy Policy