Class ActiveRecord::ConnectionAdapters::SQLServerAdapter
In: vendor/rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb
Parent: AbstractAdapter

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

[Source]

     # 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

Returns true if the connection is active.

[Source]

     # 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

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 211
211:       def adapter_name
212:         'SQLServer'
213:       end

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

[Source]

     # 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

[Source]

     # 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

[Source]

     # 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

[Source]

     # 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

[Source]

     # 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

[Source]

     # 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

[Source]

     # 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

Disconnects from the database

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 252
252:       def disconnect!
253:         @connection.disconnect rescue nil
254:       end

[Source]

     # 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

[Source]

     # 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

[Source]

     # 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

[Source]

     # 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

[Source]

     # 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

[Source]

     # 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

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 360
360:       def quote_column_name(name)
361:         "[#{name}]"
362:       end

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 356
356:       def quote_string(string)
357:         string.gsub(/\'/, "''")
358:       end

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

[Source]

     # 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

[Source]

     # 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

[Source]

     # 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

[Source]

     # 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

[Source]

     # 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

[Source]

     # 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

[Source]

     # 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

[Source]

     # 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

[Source]

     # 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

[Source]

     # 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

[Source]

     # 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

[Validate]