In ADO mode, this adapter will ONLY work on Windows systems, since it relies on Win32OLE, which, to my knowledge, is only available on Windows.

This mode also relies on the ADO support in the DBI module. If you are using the one-click installer of Ruby, then you already have DBI installed, but the ADO module is NOT installed. You will need to get the latest source distribution of Ruby-DBI from ruby-dbi.sourceforge.net/ unzip it, and copy the file src/lib/dbd_ado/ADO.rb to X:/Ruby/lib/ruby/site_ruby/1.8/DBD/ADO/ADO.rb (you will more than likely need to create the ADO directory). Once you‘ve installed that file, you are ready to go.

In ODBC mode, the adapter requires the ODBC support in the DBI module which requires the Ruby ODBC module. Ruby ODBC 0.996 was used in development and testing, and it is available at www.ch-werner.de/rubyodbc/

Options:

  • :mode — ADO or ODBC. Defaults to ADO.
  • :username — Defaults to sa.
  • :password — Defaults to empty string.

ADO specific options:

  • :host — Defaults to localhost.
  • :database — The name of the database. No default, must be provided.

ODBC specific options:

  • :dsn — Defaults to nothing.

ADO code tested on Windows 2000 and higher systems, running ruby 1.8.2 (2004-07-29) [i386-mswin32], and SQL Server 2000 SP3.

ODBC code tested on a Fedora Core 4 system, running FreeTDS 0.63, unixODBC 2.2.11, Ruby ODBC 0.996, Ruby DBI 0.0.23 and Ruby 1.8.2. [Linux strongmad 2.6.11-1.1369_FC4 1 Thu Jun 2 22:55:56 EDT 2005 i686 i686 i386 GNU/Linux]

Methods
Public Class methods
new(connection, logger, connection_options=nil)
     # File vendor/rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 189
189:       def initialize(connection, logger, connection_options=nil)
190:         super(connection, logger)
191:         @connection_options = connection_options
192:       end
Public Instance methods
active?()

Returns true if the connection is active.

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 234
234:       def active?
235:         @connection.execute("SELECT 1").finish
236:         true
237:       rescue DBI::DatabaseError, DBI::InterfaceError
238:         false
239:       end
adapter_name()
     # File vendor/rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 211
211:       def adapter_name
212:         'SQLServer'
213:       end
add_column(table_name, column_name, type, options = {})

Adds a new column to the named table. See TableDefinition#column for details of the options you can use.

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 448
448:       def add_column(table_name, column_name, type, options = {})
449:         add_column_sql = "ALTER TABLE #{table_name} ADD #{quote_column_name(column_name)} #{type_to_sql(type, options[:limit], options[:precision], options[:scale])}"
450:         add_column_options!(add_column_sql, options)
451:         # TODO: Add support to mimic date columns, using constraints to mark them as such in the database
452:         # add_column_sql << " CONSTRAINT ck__#{table_name}__#{column_name}__date_only CHECK ( CONVERT(CHAR(12), #{quote_column_name(column_name)}, 14)='00:00:00:000' )" if type == :date       
453:         execute(add_column_sql)
454:       end
add_limit_offset!(sql, options)
     # File vendor/rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 364
364:       def add_limit_offset!(sql, options)
365:         if options[:limit] and options[:offset]
366:           total_rows = @connection.select_all("SELECT count(*) as TotalRows from (#{sql.gsub(/\bSELECT(\s+DISTINCT)?\b/i, "SELECT#{$1} TOP 1000000000")}) tally")[0][:TotalRows].to_i
367:           if (options[:limit] + options[:offset]) >= total_rows
368:             options[:limit] = (total_rows - options[:offset] >= 0) ? (total_rows - options[:offset]) : 0
369:           end
370:           sql.sub!(/^\s*SELECT(\s+DISTINCT)?/i, "SELECT * FROM (SELECT TOP #{options[:limit]} * FROM (SELECT#{$1} TOP #{options[:limit] + options[:offset]} ")
371:           sql << ") AS tmp1"
372:           if options[:order]
373:             options[:order] = options[:order].split(',').map do |field|
374:               parts = field.split(" ")
375:               tc = parts[0]
376:               if sql =~ /\.\[/ and tc =~ /\./ # if column quoting used in query
377:                 tc.gsub!(/\./, '\\.\\[')
378:                 tc << '\\]'
379:               end
380:               if sql =~ /#{tc} AS (t\d_r\d\d?)/
381:                 parts[0] = $1
382:               elsif parts[0] =~ /\w+\.(\w+)/
383:                 parts[0] = $1
384:               end
385:               parts.join(' ')
386:             end.join(', ')
387:             sql << " ORDER BY #{change_order_direction(options[:order])}) AS tmp2 ORDER BY #{options[:order]}"
388:           else
389:             sql << " ) AS tmp2"
390:           end
391:         elsif sql !~ /^\s*SELECT (@@|COUNT\()/i
392:           sql.sub!(/^\s*SELECT(\s+DISTINCT)?/i) do
393:             "SELECT#{$1} TOP #{options[:limit]}"
394:           end unless options[:limit].nil?
395:         end
396:       end
begin_db_transaction()
     # File vendor/rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 326
326:       def begin_db_transaction
327:         @connection["AutoCommit"] = false
328:       rescue Exception => e
329:         @connection["AutoCommit"] = true
330:       end
columns(table_name, name = nil)
     # File vendor/rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 256
256:       def columns(table_name, name = nil)
257:         return [] if table_name.blank?
258:         table_name = table_name.to_s if table_name.is_a?(Symbol)
259:         table_name = table_name.split('.')[-1] unless table_name.nil?
260:         table_name = table_name.gsub(/[\[\]]/, '')
261:         sql = %Q{
262:           SELECT 
263:             cols.COLUMN_NAME as ColName,  
264:             cols.COLUMN_DEFAULT as DefaultValue,
265:             cols.NUMERIC_SCALE as numeric_scale,
266:             cols.NUMERIC_PRECISION as numeric_precision, 
267:             cols.DATA_TYPE as ColType, 
268:             cols.IS_NULLABLE As IsNullable,  
269:             COL_LENGTH(cols.TABLE_NAME, cols.COLUMN_NAME) as Length,  
270:             COLUMNPROPERTY(OBJECT_ID(cols.TABLE_NAME), cols.COLUMN_NAME, 'IsIdentity') as IsIdentity,  
271:             cols.NUMERIC_SCALE as Scale 
272:           FROM INFORMATION_SCHEMA.COLUMNS cols 
273:           WHERE cols.TABLE_NAME = '#{table_name}'   
274:         }
275:         # Comment out if you want to have the Columns select statment logged.
276:         # Personally, I think it adds unnecessary bloat to the log. 
277:         # If you do comment it out, make sure to un-comment the "result" line that follows
278:         result = log(sql, name) { @connection.select_all(sql) }
279:         #result = @connection.select_all(sql)
280:         columns = []
281:         result.each do |field|
282:           default = field[:DefaultValue].to_s.gsub!(/[()\']/,"") =~ /null/ ? nil : field[:DefaultValue]
283:           if field[:ColType] =~ /numeric|decimal/i
284:             type = "#{field[:ColType]}(#{field[:numeric_precision]},#{field[:numeric_scale]})"
285:           else
286:             type = "#{field[:ColType]}(#{field[:Length]})"
287:           end
288:           is_identity = field[:IsIdentity] == 1
289:           is_nullable = field[:IsNullable] == 'YES'
290:           columns << SQLServerColumn.new(field[:ColName], default, type, is_identity, is_nullable)
291:         end
292:         columns
293:       end
commit_db_transaction()
     # File vendor/rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 332
332:       def commit_db_transaction
333:         @connection.commit
334:       ensure
335:         @connection["AutoCommit"] = true
336:       end
create_database(name)
     # File vendor/rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 407
407:       def create_database(name)
408:         execute "CREATE DATABASE #{name}"
409:       end
current_database()
     # File vendor/rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 411
411:       def current_database
412:         @connection.select_one("select DB_NAME()")[0]
413:       end
delete(sql, name = nil)

Alias for update

disconnect!()

Disconnects from the database

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 252
252:       def disconnect!
253:         @connection.disconnect rescue nil
254:       end
drop_database(name)
     # File vendor/rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 403
403:       def drop_database(name)
404:         execute "DROP DATABASE #{name}"
405:       end
execute(sql, name = nil) {|handle| ...}
     # File vendor/rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 308
308:       def execute(sql, name = nil)
309:         if sql =~ /^\s*INSERT/i && (table_name = query_requires_identity_insert?(sql))
310:           log(sql, name) do
311:             with_identity_insert_enabled(table_name) do 
312:               @connection.execute(sql) do |handle|
313:                 yield(handle) if block_given?
314:               end
315:             end
316:           end
317:         else
318:           log(sql, name) do
319:             @connection.execute(sql) do |handle|
320:               yield(handle) if block_given?
321:             end
322:           end
323:         end
324:       end
indexes(table_name, name = nil)
     # File vendor/rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 425
425:       def indexes(table_name, name = nil)
426:         ActiveRecord::Base.connection.instance_variable_get("@connection")["AutoCommit"] = false
427:         indexes = []        
428:         execute("EXEC sp_helpindex '#{table_name}'", name) do |sth|
429:           sth.each do |index| 
430:             unique = index[1] =~ /unique/
431:             primary = index[1] =~ /primary key/
432:             if !primary
433:               indexes << IndexDefinition.new(table_name, index[0], unique, index[2].split(", "))
434:             end
435:           end
436:         end
437:         indexes
438:         ensure
439:           ActiveRecord::Base.connection.instance_variable_get("@connection")["AutoCommit"] = true
440:       end
insert(sql, name = nil, pk = nil, id_value = nil, sequence_name = nil)
     # File vendor/rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 295
295:       def insert(sql, name = nil, pk = nil, id_value = nil, sequence_name = nil)
296:         execute(sql, name)
297:         id_value || select_one("SELECT @@IDENTITY AS Ident")["Ident"]
298:       end
native_database_types()
     # File vendor/rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 194
194:       def native_database_types
195:         {
196:           :primary_key => "int NOT NULL IDENTITY(1, 1) PRIMARY KEY",
197:           :string      => { :name => "varchar", :limit => 255  },
198:           :text        => { :name => "text" },
199:           :integer     => { :name => "int" },
200:           :float       => { :name => "float", :limit => 8 },
201:           :decimal     => { :name => "decimal" },
202:           :datetime    => { :name => "datetime" },
203:           :timestamp   => { :name => "datetime" },
204:           :time        => { :name => "datetime" },
205:           :date        => { :name => "datetime" },
206:           :binary      => { :name => "image"},
207:           :boolean     => { :name => "bit"}
208:         }
209:       end
quote(value, column = nil)
     # File vendor/rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 344
344:       def quote(value, column = nil)
345:         return value.quoted_id if value.respond_to?(:quoted_id)
346: 
347:         case value
348:           when TrueClass             then '1'
349:           when FalseClass            then '0'
350:           when Time, DateTime        then "'#{value.strftime("%Y%m%d %H:%M:%S")}'"
351:           when Date                  then "'#{value.strftime("%Y%m%d")}'"
352:           else                       super
353:         end
354:       end
quote_column_name(name)
     # File vendor/rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 360
360:       def quote_column_name(name)
361:         "[#{name}]"
362:       end
quote_string(string)
     # File vendor/rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 356
356:       def quote_string(string)
357:         string.gsub(/\'/, "''")
358:       end
reconnect!()

Reconnects to the database, returns false if no connection could be made.

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 242
242:       def reconnect!
243:         disconnect!
244:         @connection = DBI.connect(*@connection_options)
245:       rescue DBI::DatabaseError => e
246:         @logger.warn "#{adapter_name} reconnection failed: #{e.message}" if @logger
247:         false
248:       end
recreate_database(name)
     # File vendor/rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 398
398:       def recreate_database(name)
399:         drop_database(name)
400:         create_database(name)
401:       end
remove_check_constraints(table_name, column_name)
     # File vendor/rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 485
485:       def remove_check_constraints(table_name, column_name)
486:         # TODO remove all constraints in single method
487:         constraints = select "SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE where TABLE_NAME = '#{table_name}' and COLUMN_NAME = '#{column_name}'"
488:         constraints.each do |constraint|
489:           execute "ALTER TABLE #{table_name} DROP CONSTRAINT #{constraint["CONSTRAINT_NAME"]}"
490:         end
491:       end
remove_column(table_name, column_name)
     # File vendor/rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 471
471:       def remove_column(table_name, column_name)
472:         remove_check_constraints(table_name, column_name)
473:         remove_default_constraint(table_name, column_name)
474:         execute "ALTER TABLE [#{table_name}] DROP COLUMN [#{column_name}]"
475:       end
remove_default_constraint(table_name, column_name)
     # File vendor/rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 477
477:       def remove_default_constraint(table_name, column_name)
478:         constraints = select "select def.name from sysobjects def, syscolumns col, sysobjects tab where col.cdefault = def.id and col.name = '#{column_name}' and tab.name = '#{table_name}' and col.id = tab.id"
479:         
480:         constraints.each do |constraint|
481:           execute "ALTER TABLE #{table_name} DROP CONSTRAINT #{constraint["name"]}"
482:         end
483:       end
remove_index(table_name, options = {})
     # File vendor/rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 493
493:       def remove_index(table_name, options = {})
494:         execute "DROP INDEX #{table_name}.#{quote_column_name(index_name(table_name, options))}"
495:       end
rename_column(table, column, new_column_name)
     # File vendor/rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 456
456:       def rename_column(table, column, new_column_name)
457:         execute "EXEC sp_rename '#{table}.#{column}', '#{new_column_name}'"
458:       end
rename_table(name, new_name)
     # File vendor/rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 442
442:       def rename_table(name, new_name)
443:         execute "EXEC sp_rename '#{name}', '#{new_name}'"
444:       end
rollback_db_transaction()
     # File vendor/rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 338
338:       def rollback_db_transaction
339:         @connection.rollback
340:       ensure
341:         @connection["AutoCommit"] = true
342:       end
tables(name = nil)
     # File vendor/rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 415
415:       def tables(name = nil)
416:         execute("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'", name) do |sth|
417:           sth.inject([]) do |tables, field|
418:             table_name = field[0]
419:             tables << table_name unless table_name == 'dtproperties'
420:             tables
421:           end
422:         end
423:       end
update(sql, name = nil)
This method is also aliased as delete
     # File vendor/rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 300
300:       def update(sql, name = nil)
301:         execute(sql, name) do |handle|
302:           handle.rows
303:         end || select_one("SELECT @@ROWCOUNT AS AffectedRows")["AffectedRows"]        
304:       end