Rails - Apr 2007 - How to connect and retrieve data from a MS SQL SERVER 2000 database?

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


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Hey Jeremy,
Yes can easily connect rails(+ active record of course.... :D 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:
> 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...

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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.
On 4月18日, 下午8时33分, cammo <mvpaustra...@gmail.com>
wrote:
> Hey Jeremy,
> Yes can easily connect rails(+ active record of course.... :D 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:
> > 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...

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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

On 4月18日, 下午8时33分, cammo <mvpaustra...@gmail.com>
wrote:
> Hey Jeremy,
> Yes can easily connect rails(+ active record of course.... :D 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:
> > 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...

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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

On Apr 19, 12:52 am, Jeremy
<yangyi1...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> BTW, where and how can I get this "Ruby''s DBI library"?
I can''t "gem
> install dbi". thanks.
>
> On 4月18日, 下午8时33分, cammo <mvpaustra...@gmail.com> wrote:
>
> > Hey Jeremy,
> > Yes can easilyconnectrails(+ active record of course.... :D we like
> > active record!!!) up toSQLServer, you just need to install
Ruby''s
> > DBI library, along with its support for either ADO or ODBCdatabase
> > drivers depending on how you want toconnect.
>
> > >From there you just set it up like most otherdatabaseconnections
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
> > yourMSSQLdatabase.
> > If you really want to writeSQLstatements, 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 yourdatabaseinto another system if you havesql
> > 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 yourdatabase.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 orSQLdatabasefirst, 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:
> > > Good evening~~HO HO~~It''s 19:51 in China now. :)
I''m new to Ruby
> > > on Rails. I wanna know how toconnectto aMSSQLSERVER2000
> > >database. Can I use a T-SQLquery in Ruby on Rails directly? If I
can
> > > do it directly, how to do it? For example, I wannaretrievealldata
> > > from aSQLSERVER2000 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...

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---