Good evening~~HO HO~~It's 19:51 in China now. 
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.... 
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
rails :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.
Cam
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 - http://rubyforge.org/projects/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 accordingly.
Cam