Getting FeedTools to work with SQL Server

Posted by Chris on October 03, 2006

I have been trying for a few days (hey, I’m on vacation and have been spending time with my girlfriend, son and dog so I have not spent a lot of time with this) to get “FeedTools “:http://rubyforge.org/projects/feedtools/ to work with a Rails application I am working on. I had a couple of problems getting up and running, despite following “tutorials”:http://sporkmonger.com/articles/2005/08/11/tutorial/ that I found online and starting with a “really simple example”:http://sporkmonger.com/articles/2005/08/11/tutorial/. First I encountered some problems with example code that did not work with the current version of FeedTools, but that was easy enough to fix. However, even with that fixed I could not seem to get the database cache to function, which meant I could not use FeedTools at all (I want to cache of course).

Whatever I tried I always got stuck at FeedTools saying that the cache was not initialized properly, even though I knew it was there in my database. I was puzzled for a while until I finally took a peek in the log file. There seemed to be a SQL query failing with an error message saying @”Incorrect syntax near ’1′”@. At first I thought it was strange since I had not yet tried to retrieve a feed. But looking through the code I found the problem. When FeedTools initializes the database cache it calls @DatabaseFeedCache.table_exists?@. What this method does is try to retrieve the first row from the table where FeedTools stores the cached feeds. However, it does this by calling the execute method and sending it a specific SQL query to execute. This query uses the MySQL syntax @LIMIT 1@ to restrict the result set to one row. But I am using SQL Server 2005 as my DBMS, and the @LIMIT@ keyword is not included in T-SQL (SQL Server’s SQL dialect). So I get the incorrect syntax error.

To fix this I just needed to make a small change in the @DatabaseFeedCache.table_exists?@ method. This is what the old code was:

def DatabaseFeedCache.table_exists?
begin
  ActiveRecord::Base.connection.execute("select id, href, title, " +
    "link, feed_data, feed_data_type, http_headers, last_retrieved " +
    "from #{self.table_name()} LIMIT 1")
  rescue ActiveRecord::StatementInvalid
    return false
  rescue
    return false
  end
  return true
end

And my modified version is this:

def DatabaseFeedCache.table_exists?
begin
  ActiveRecord::Base.connection.select_one("select id, href, title, " +
    "link, feed_data, feed_data_type, http_headers, last_retrieved " +
    "from #{self.table_name()}")
  rescue ActiveRecord::StatementInvalid
    return false
  rescue
    return false
  end
  return true
end

I have created a patch and have -been trying to submit- submitted it to the FeedTools project at RubyForge , -but I keep getting timeouts-. You can get “the patch here”:http://rubyforge.org/tracker/index.php?func=detail&aid=4871&group_id=775&atid=3063 (or from my site), or simply update your DatabaseFeedCache.rb file manually with the code above.

Trackbacks

Trackbacks are closed.

blog comments powered by Disqus