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 178
178:       def initialize(connection, logger, connection_options=nil)
179:         super(connection, logger)
180:         @connection_options = connection_options
181:       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 210
210:       def active?
211:         @connection.execute("SELECT 1") { }
212:         true
213:       rescue DBI::DatabaseError, DBI::InterfaceError
214:         false
215:       end

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 199
199:       def adapter_name
200:         'SQLServer'
201:       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\b/i, "SELECT 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/i, "SELECT * FROM (SELECT TOP #{options[:limit]} * FROM (SELECT 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:               end
383:               parts.join(' ')
384:             end.join(', ')
385:             sql << " ORDER BY #{change_order_direction(options[:order])}) AS tmp2 ORDER BY #{options[:order]}"
386:           else
387:             sql << " ) AS tmp2"
388:           end
389:         elsif sql !~ /^\s*SELECT (@@|COUNT\()/i
390:           sql.sub!(/^\s*SELECT([\s]*distinct)?/i) do
391:             "SELECT#{$1} TOP #{options[:limit]}"
392:           end unless options[:limit].nil?
393:         end
394:       end

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 312
312:       def begin_db_transaction
313:         @connection["AutoCommit"] = false
314:       rescue Exception => e
315:         @connection["AutoCommit"] = true
316:       end

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 242
242:       def columns(table_name, name = nil)
243:         return [] if table_name.blank?
244:         table_name = table_name.to_s if table_name.is_a?(Symbol)
245:         table_name = table_name.split('.')[-1] unless table_name.nil?
246:         sql = "SELECT COLUMN_NAME as ColName, COLUMN_DEFAULT as DefaultValue, DATA_TYPE as ColType, IS_NULLABLE As IsNullable, COL_LENGTH('#{table_name}', COLUMN_NAME) as Length, COLUMNPROPERTY(OBJECT_ID('#{table_name}'), COLUMN_NAME, 'IsIdentity') as IsIdentity, NUMERIC_SCALE as Scale FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '#{table_name}'"
247:         # Comment out if you want to have the Columns select statment logged.
248:         # Personally, I think it adds unnecessary bloat to the log. 
249:         # If you do comment it out, make sure to un-comment the "result" line that follows
250:         result = log(sql, name) { @connection.select_all(sql) }
251:         #result = @connection.select_all(sql)
252:         columns = []
253:         result.each do |field|
254:           default = field[:DefaultValue].to_s.gsub!(/[()\']/,"") =~ /null/ ? nil : field[:DefaultValue]
255:           type = "#{field[:ColType]}(#{field[:Length]})"
256:           is_identity = field[:IsIdentity] == 1
257:           is_nullable = field[:IsNullable] == 'YES'
258:           columns << ColumnWithIdentity.new(field[:ColName], default, type, is_identity, is_nullable, field[:Scale])
259:         end
260:         columns
261:       end

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 318
318:       def commit_db_transaction
319:         @connection.commit
320:       ensure
321:         @connection["AutoCommit"] = true
322:       end

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 405
405:       def create_database(name)
406:         execute "CREATE DATABASE #{name}"
407:       end

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 409
409:       def current_database
410:         @connection.select_one("select DB_NAME()")[0]
411:       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 228
228:       def disconnect!
229:         @connection.disconnect rescue nil
230:       end

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 401
401:       def drop_database(name)
402:         execute "DROP DATABASE #{name}"
403:       end

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 294
294:       def execute(sql, name = nil)
295:         if sql =~ /^\s*INSERT/i
296:           insert(sql, name)
297:         elsif sql =~ /^\s*UPDATE|^\s*DELETE/i
298:           log(sql, name) do
299:             @connection.execute(sql)
300:             retVal = select_one("SELECT @@ROWCOUNT AS AffectedRows")["AffectedRows"]
301:           end
302:         else
303:           log(sql, name) { @connection.execute(sql) }
304:         end
305:       end

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 421
421:       def indexes(table_name, name = nil)
422:         indexes = []
423:         execute("EXEC sp_helpindex #{table_name}", name).each do |index| 
424:           unique = index[1] =~ /unique/
425:           primary = index[1] =~ /primary key/
426:           if !primary
427:             indexes << IndexDefinition.new(table_name, index[0], unique, index[2].split(", "))
428:           end
429:         end
430:         indexes
431:       end

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 263
263:       def insert(sql, name = nil, pk = nil, id_value = nil, sequence_name = nil)
264:         begin
265:           table_name = get_table_name(sql)
266:           col = get_identity_column(table_name)
267:           ii_enabled = false
268: 
269:           if col != nil
270:             if query_contains_identity_column(sql, col)
271:               begin
272:                 execute enable_identity_insert(table_name, true)
273:                 ii_enabled = true
274:               rescue Exception => e
275:                 raise ActiveRecordError, "IDENTITY_INSERT could not be turned ON"
276:               end
277:             end
278:           end
279:           log(sql, name) do
280:             @connection.execute(sql)
281:             id_value || select_one("SELECT @@IDENTITY AS Ident")["Ident"]
282:           end
283:         ensure
284:           if ii_enabled
285:             begin
286:               execute enable_identity_insert(table_name, false)
287:             rescue Exception => e
288:               raise ActiveRecordError, "IDENTITY_INSERT could not be turned OFF"
289:             end
290:           end
291:         end
292:       end

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 183
183:       def native_database_types
184:         {
185:           :primary_key => "int NOT NULL IDENTITY(1, 1) PRIMARY KEY",
186:           :string      => { :name => "varchar", :limit => 255  },
187:           :text        => { :name => "text" },
188:           :integer     => { :name => "int" },
189:           :float       => { :name => "float", :limit => 8 },
190:           :datetime    => { :name => "datetime" },
191:           :timestamp   => { :name => "datetime" },
192:           :time        => { :name => "datetime" },
193:           :date        => { :name => "datetime" },
194:           :binary      => { :name => "image"},
195:           :boolean     => { :name => "bit"}
196:         }
197:       end

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 330
330:       def quote(value, column = nil)
331:         case value
332:           when String                
333:             if column && column.type == :binary && column.class.respond_to?(:string_to_binary)
334:               "'#{quote_string(column.class.string_to_binary(value))}'"
335:             else
336:               "'#{quote_string(value)}'"
337:             end
338:           when NilClass              then "NULL"
339:           when TrueClass             then '1'
340:           when FalseClass            then '0'
341:           when Float, Fixnum, Bignum then value.to_s
342:           when Date                  then "'#{value.to_s}'" 
343:           when Time, DateTime        then "'#{value.strftime("%Y-%m-%d %H:%M:%S")}'"
344:           else                            "'#{quote_string(value.to_yaml)}'"
345:         end
346:       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 348
348:       def quote_string(string)
349:         string.gsub(/\'/, "''")
350:       end

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 356
356:       def quoted_false
357:         "0"
358:       end

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 352
352:       def quoted_true
353:         "1"
354:       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 218
218:       def reconnect!
219:         disconnect!
220:         @connection = DBI.connect(*@connection_options)
221:       rescue DBI::DatabaseError => e
222:         @logger.warn "#{adapter_name} reconnection failed: #{e.message}" if @logger
223:         false
224:       end

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 396
396:       def recreate_database(name)
397:         drop_database(name)
398:         create_database(name)
399:       end

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 456
456:       def remove_column(table_name, column_name)
457:         remove_default_constraint(table_name, column_name)
458:         execute "ALTER TABLE #{table_name} DROP COLUMN #{column_name}"
459:       end

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 437
437:       def remove_column(table_name, column_name)
438:         execute "ALTER TABLE #{table_name} DROP COLUMN #{column_name}"
439:       end

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 461
461:       def remove_default_constraint(table_name, column_name)
462:         defaults = 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"
463:         defaults.each {|constraint|
464:           execute "ALTER TABLE #{table_name} DROP CONSTRAINT #{constraint["name"]}"
465:         }
466:       end

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 468
468:       def remove_index(table_name, options = {})
469:         execute "DROP INDEX #{table_name}.#{index_name(table_name, options)}"
470:       end

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 441
441:       def rename_column(table, column, new_column_name)
442:         execute "EXEC sp_rename '#{table}.#{column}', '#{new_column_name}'"
443:       end

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 433
433:       def rename_table(name, new_name)
434:         execute "EXEC sp_rename '#{name}', '#{new_name}'"
435:       end

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 324
324:       def rollback_db_transaction
325:         @connection.rollback
326:       ensure
327:         @connection["AutoCommit"] = true
328:       end

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 232
232:       def select_all(sql, name = nil)
233:         select(sql, name)
234:       end

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 236
236:       def select_one(sql, name = nil)
237:         add_limit!(sql, :limit => 1)
238:         result = select(sql, name)
239:         result.nil? ? nil : result.first
240:       end

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 413
413:       def tables(name = nil)
414:         execute("SELECT table_name from information_schema.tables WHERE table_type = 'BASE TABLE'", name).inject([]) do |tables, field|
415:           table_name = field[0]
416:           tables << table_name unless table_name == 'dtproperties'
417:           tables
418:         end
419:       end

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 307
307:       def update(sql, name = nil)
308:         execute(sql, name)
309:       end

[Validate]