jamtronix

I'm porting my MSSQL Schema Explorer app to Ruby

In the process, I discovered there's not much documentation around on how to use the ADO provider in Ruby DBI to connect to a SQL Server. here's a little example that lists the names of all the databases on the server 'localhost'. It wil work on SQL Server 2005 - if you are using SQL Server 2000 you will need to change the text 'Provider=SQLNCLI' to 'Provider=SQLOLEDB'

####

require 'dbi'

class Server
 attr_reader :name
 def initialize(name)
  @server_name=name
  @dbh=DBI.connect("DBI:ADO:Provider=SQLNCLI;Data Source=#{name};Integrated Security=SSPI")
 end
 
 def databases
  db=Array.new
  @dbh.select_all('SELECT name FROM master.sys.databases ORDER BY 1') do | row |
   db.<< Database.new(@dbh,row[0])
  end  
  db
 end
end

class Database
 attr_reader :name
 def initialize(dbh,name)
  @dbh=dbh
  @name=name
 end
end

server=Server.new("localhost")
server.databases.each {|x| p x.name}