How to connect and retrieve data from a MS SQL SERVER 2000 database?

Good evening~~HO HO~~It's 19:51 in China now. :slight_smile: I'm new to Ruby
on Rails. I wanna know how to connect to a MS SQL SERVER 2000
database. Can I use a T-SQL query in Ruby on Rails directly? If I can
do it directly, how to do it? For example, I wanna retrieve all data
from a SQL SERVER 2000 table and display it on a page on my site, I
use a T-SQL "select * from table", how to add it into a ROR-based web
application, and I wanna see a table-view result. Thanks...

Hey Jeremy,
Yes can easily connect rails(+ active record of course.... :smiley: we like
active record!!!) up to SQL Server, you just need to install Ruby's
DBI library, along with its support for either ADO or ODBC database
drivers depending on how you want to connect.

From there you just set it up like most other database connections in

:adapter => "sqlserver",
:mode => "ado", # or "odbc" depending on your connection.
:database => "required for ado",
:host => "localhost",
:dsn => "required for odbc"

From there all you need to do is use a model find, so in your case if

your table was called people you'd create a model called Person and
the a find like this
@people = Person.find(:all) will create an array of all the record in
your MS SQL database.
If you really want to write SQL statements, you could also do
@people = Person.find_by_sql('select * from 'people')
and this would return the same result. The problem with that is that
if you ever move your database into another system if you have sql
statement dependencies it'll kill your app, where as if you use
find(:all) and then later the db got moved to say oracle you could
just change your database.yml file and voila it'll all work.

Bear in mind that Rails tends to work better with convention over
configuration, so if your new to rails you might want to play with it
and a MySQL or SQL database first, just to get used to it. For
instance, rails will expect that your table name is the plural of your
model name.... Now if your intergrating rails into an existing schema,
this can be a little tricky, of course it's all configurable, but it's
not the simplest way to learn rails.

Good luck with it.


Jeremy wrote:

Thanks, cammo! I'll try your method. And there comes another question,
my SQL SERVER database has some different users, for example, a user
named "administrator", he can retrieve or modify all data from Person
table; another user named "guest", he only can see all data from
Person, but he can't modify the date in Person table. How to design a
login form? Different users can login via their different user names.

BTW, where and how can I get this "Ruby's DBI library"? I can't "gem
install dbi". thanks.

heres a link to the ruby dbi -

If your users are pure database users, then this is quite a strange
setup, normally I'd imagine you'd give permissions to users with LDAP/
AD and you can access and athenticate these users with ruby ActiveLDAP
library, however if they are pure database users(are u sure they are?)
then you'll need to use something like acts_as_authenticated and
replicate those users in a new users table and give them permissions