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.)