How to connect and retrieve data from a MS SQL SERVER 2000 database?
Jeremy
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...
cammo
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
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.
Jeremy
install dbi". thanks.
On 4月18日, 下午8时33分, cammo <mvpaustra...@gmail.com> wrote:
cammo
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...@gmail.com> 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