I've been very fortunate to lead an ETL project, using Ruby on Rails as the core engine. With a few weeks devoted to "Pure R&D Effort", I have come up with a mini-recipe of tweaks and connectivity layers necessary to connect a Rails Application (on Linux) to a SQL2005 Database. I hope my published instructions here are eloquent and sufficient enough to guide you through your own efforts along such a path.
While I'd love to use Rails-ODBC in the true "Bleeding-Edge" spirit of Fedora Core, Rails-ODBC isn't quite usable with SQL2005 at the time of this writing. If you need to use SQL2005 right now then you should try using the Ruby DBI layer until the Rails-ODBC solution matures a little more. This article documents both approaches, in anticipation of an updated Rails-ODBC that will be usable with SQL2005.
Specifically, in my experience, most SELECT queries work, but most UPDATE queries fail. Trace Logs show a properly formed query -- i.e. the query works fine if cut/pasted and run from SQL2005 Studio -- but FreeTDS reports a syntax error. When I have time, I'll dig deeper and see if I can fix it myself (or submit a detailed bug-report).. but for now, my boxes have to be up ASAP, so I'm moving forward with the Ruby-DBI solution as it seems to work better.
Fedora Core has a package out-of-box, so we'll use it:
Place variables similar to these in an /etc/profile.d global-settings file, or in your ~/.bashrc file
FreeTDS isn't included out-of-box in Fedora because of potential Intellectual Property issues. But Dries and others have an RPM that can be used:
FYI, I didn't take this approach, so you're on your on if you take this route. :-7
[SQL2005Host1] host = 192.168.1.101 port = 1433 tds version = 8.0
You should be able to type in your password and get a ">" prompt. At this point, try fetching something from a database table, to make sure your connection and database access controls are how you expect them.
use SomeDatabaseName go select * from SomeTable go quit
[SQL2005DSN1] Driver = FreeTDS Description = ODBC Connection via FreeTDS Trace = No Server = 192.168.1.101 Port = 1433 TDS Version = 8.0 Database = SomeDatabaseName
[FreeTDS] Description = ODBC Connection via FreeTDS Driver = /usr/lib/libtdsodbc.so.0 FileUsage = 1
+---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL>
checking for sql.h... yes checking for sqlext.h... yes checking for SQLTCHAR... yes checking for SQLLEN... yes checking for SQLULEN... yes checking for odbcinst.h... yes checking for dlopen()... yes checking for dlopen() in -ldl... yes creating Makefile
You should just get back to the command prompt. if you get a LoadError, something is amiss within this step.
Caution: At this writing, the Rails-ODBC Layer is not quite usable with SQL2005. Hopefully an updated version will be, though.
Make modifications as you see fit (I didn't change anything on my own boxes), and then type "i" to proceed with the installation.
Modify your database.yml to something similar to this:
development: adapter: odbc dsn: SQL2005DSN1 username: MyUserID password: MyPassword trace: false convert_numeric_literals: true
The Rails-ODBC connection-adapter doesn't yet implement current_database. The following patch will implement a current_database function that gives the ODBC DSN in place of the current_database. For my needs (My application tells users what database they're working against), this is sufficient enough -- I can name my DSNs to reflect a meaningful name.
If you also have a need for current_database, add the following hunk of code to the tail-end of your Rails Application's environment.rb file:
# LALEE's Quick-Fix for ODBCAdapter
class ActiveRecord::ConnectionAdapters::ODBCAdapter
def current_database
@connection_options[0] # 0 == dsn name, 1 == username, 2 == password
end
end
I've noticed that using SQL2005 Databases under this solution is a bit more strict than using the Ruby-DBI/ADO solution under Windows. Specifically, I'm noticing type-mismatch complaints when using strings to search for text-fields. The following document may be helpful in identifying these issues:
Since this still a brand-new setup for me, I'm figuring out the best way to handle this for my application. In the end, I may just decide to patch Rails-ODBC to test field-types and maybe figure out how to rework things at that layer. We'll see.
Modify your database.yml to something similar to this:
development: adapter: sqlserver mode: odbc dsn: SQL2005DSN1 username: MyUserID password: MyPassword
(More to Come)
This article could not have been put together without referring to older documentation:
If you need additional Ruby on Fedora support, or are looking to hire an experienced Ruby On Rails Developer, feel free to contact me (Laurence Lee) as username lalee_net, at yahoo.com.
HI.. i need to do this connection but I wanna ask U if U want to send me a copy of one of your programs who reallly works...TKS
Hi juanvidal2,
I wish I could provide you with the source code that uses SQL2005 as the back-end, but it was done as a work-for-hire. All I can really say is that it's an ETL application that uses two databases concurrently, and that the company I wrote it for is reselling it in the Insurance/Finance sector.
The above instructions work OK -- about the only gotchas I remember are in making sure the SQL2005 databases have ANSI-Nulls enabled. (Found in the properties tab when you right-click the database in SQL2005 Manager.)
The goal of this article is to create a "Production-Quality" Rails Server. Thus, the best possible technologies (strictly my opinion) have been selected at the time of this writing to achieve this goal.
Unlike other developers, I prefer to install my Rails Applications under Web Subdirectories, such as http://www.not404.com/MyRailsApp, instead of running it as a Root Application of a Web Root, such as http://MyRailsApp.not404.com/. These instructions are geared for how I lay things out, but will let you know what to adjust in order to run your Rails Apps as traditional Web-Root Applications.
You may also notice that these instructions are SQLite3-oriented. This is intentional. IMHO, it's better to use the simplest-case database to prove that everything else is properly stitched together. Then, once you're satisfied that everything is properly locked down and performance-tuned, you can focus your attention on tying your Rails Application to a real database.
The Ruby on Rails support in Fedora 8 is superb, and there are RPMs that handle everything about this configuration. Note that the most common Ruby Gems are available as rubygem-* in the Yum Repositories. (You can do a yum search rubygem for a complete list of Fedora-Supplied Ruby Gems).
Thus, through Fedora 8's Yum support, you can install Rails, Lighttpd, and Mongrel in one command:
yum install ruby ruby-devel rubygem-rails rubygem-mongrel lighttpd lighttpd-fastcgi
The following instructions are specific to Fedora Core 6. If you're using Fedora 8, you can skim through most of the Installation Procedures, but pay attention to the Configuration Procedures -- particularly for the Lighttpd Proxy and the Mongrel Cluster as a Startup Service.
If you missed the "Development Tools" checkbox, do a yum -y groupinstall "Development Tools" to install it now. You'll need the GCC compiler to build some Ruby Gems, it makes sense to have it ready here. After everything's configured, you may decide to do a yum -y groupremove "Development Tools" to remove the C compiler and other tools from your production box.
As I've mentioned before, I prefer to install my Rails Applications under Web Subdirectories, such as http://www.not404.com/MyRailsApp. Most of the other deployment tutorials and strategies advocate installing the Rails Application under the Web Root directory: http://MyRailsApp.not404.com/.
To correctly handle Rails Applications running under Web Subdirectories , we need to use Mongrel's --prefix support, recently added in Mongrel Cluster 0.2.1. At this writing though, Mongrel Cluster 0.2.1 is still a pre-release version, so it needs to be installed from their "trunk" repository, instead of from the standard Gem Repositories.
To get the Pre-Release version of Mongrel Cluster to let us run Rails Applications under Web Subdirectories, run this command:
Launch firefox, and go to http://localhost:3000 -- you should get the "Welcome Aboard" web page. You can now stop Mongrel, so we can configure it as a Service.
Because everyone lays out their Production Directories differently, I'll just call the Production Root Directory $PRODUCTION in this article, and I'll assume that Application Instances are in subdirectories, which I'll call $APP_ROOT. For my Production Servers, I like to lay things out this way -- it makes things easier to create master startup scripts that can iterate over all $APP_ROOT instances in $PRODUCTION. I also configure $APP_PORT to a unique service port for each instance on my Production Server.
Please substitute my variables with your own directory structures as appropriate.
If you're following along with my layout, now's a good time to copy ~/testapp to your Production Area:
(I've not found a use for this, but since others think it's important enough to document, I've put it in . . .)
(NOTE: The following hunk is a pseudo-script, just to give you an idea of what I do on my machines. It's not a complete bash script)
Note that my instructions above include the new --prefix $APP_ROOT command, which allows Mongrel Applications to properly "ignore" the prefix. Prior to this Mongrel Enhancement, we needed to configure Apache or Lighttpd to strip out this prefix.
If you're running your Rails Apps as the web server root application, remove the --prefix /$APP_ROOT additions.
At this point, you now have your Mongrel-Rails Applications properly configured to run as Startup Services. (They'll automatically startup when your machine reboots). Now it's time to stitch together the Lighttpd Web Server as our front-end.
Uncomment the mod_proxy module, as we'll need that to dispatch requests to our Mongrel Serves.
Out of the Box, Fedora's Lighttpd configuration is rather light. You may want to uncomment these additional modules to get more functionality.
Add a hunk of code similar to the following to the tail end of /etc/lighttpd/lighttpd.conf:
proxy.balance = "fair"
proxy.server = ( "/testapp" =>
( ( "host" => "127.0.0.1", "port" => 8001 ),
( "host" => "127.0.0.1", "port" => 8002 ),
( "host" => "127.0.0.1", "port" => 8003 ) ) )
You will need to change the /testapp prefix to the name of your Rails Application. Remember that $APP_ROOT variable that we passed to Mongrel as --prefix? Yes, the value you input here must match that --prefix value. Obviously, you will also need to change the Mongrel Server-Ports to match your $APP_PORT, up to $APP_NODES instances for this server pool.
If you are running your apps in the root-directory, change "/testapp" to "/"
That should be it! Now you can fire up the whole shebang and cross your fingers:
(more to come)
This article couldn't have been put together without referring to older documentation:
If you need additional Ruby on Fedora support, or are looking to hire an experienced Ruby On Rails Developer, feel free to contact me (Laurence Lee) as username lalee_net, at yahoo.com.
I actually wrote the BryanThompsons blog article, you have a great howto here, good work!
Thanks for the HOWTO... worked like a charm for my very first Rails installation :-)
In section: Install Mongrel_Cluster a Startup Service I think you mean:
Sam,
It's a matter of personal preference, I suppose. I create /etc/mongrel_cluster as a directory that stores all of the Mongrel Configuration Files deployed across all applications. For me, it makes more logical sense to keep it as a subdirectory of /etc (the system-wide configuration directory), rather than in /etc/init.d (the system-wide configuration-scripts repository).
As far as I can tell in Fedora's layout, most of the services have configuration directories directly under /etc, too, so at least I'm being consistent with what's already in place. :-)
The biggest problem I have with FC6 is that rails for the x64 version is still stuck at 1.8.5. This is inadequate if you use certain methods ( Time.to_datetime for example). I'm trying to manually build Ruby 1.8.6 and rubygems, but the rubygems installer complains about missing zlib.. any thoughts?
previous should have read "ruby for the x64 version".
You might want to upgrade to Fedora7, or wait to grab Fedora8 since it's scheduled for release on November 8. Fedora7-x64 has Ruby 1.8.6 available.
This was quite good, but for us Ruby first timers, it might be nice to give somewhat more: 1) Folders to change to before executing commands. 2) For optional packages/utilities - what are they/why or when would you need them. 3) Some servers - Mongrel in particular - would go on a second server computer. It would be nice to have some sense of how those are configured to work with Ruby and Rails on a development computer.
Thanks for the feedback, Lou. I have been considering an overhaul of this article for Fedora8 for quite some time, and those suggestions will help take it into a new direction.
Best Regards,
Laurence Lee