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]
- active?
- adapter_name
- add_limit_offset!
- begin_db_transaction
- columns
- commit_db_transaction
- create_database
- current_database
- delete
- disconnect!
- drop_database
- execute
- indexes
- insert
- native_database_types
- new
- quote
- quote_column_name
- quote_string
- quoted_false
- quoted_true
- reconnect!
- recreate_database
- remove_column
- remove_column
- remove_default_constraint
- remove_index
- rename_column
- rename_table
- rollback_db_transaction
- select_all
- select_one
- tables
- update
[ show 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
Returns true if the connection is active.
[ show 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
[ show source ]
# File vendor/rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 199 199: def adapter_name 200: 'SQLServer' 201: end
[ show 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
[ show 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
[ show 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
[ show 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
[ show 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
[ show 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
Alias for update
Disconnects from the database
[ show 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
[ show 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
[ show 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
[ show 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
[ show 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
[ show 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
[ show 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
[ show 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
[ show 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
[ show source ]
# File vendor/rails/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb, line 356 356: def quoted_false 357: "0" 358: end
[ show 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.
[ show 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
[ show 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
[ show 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
[ show 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
[ show 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
[ show 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
[ show 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
[ show 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
[ show 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
[ show 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
[ show 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
[ show 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
[ show 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