Module: ActiveRecord::ConnectionAdapters::SQLServer::DatabaseStatements

Includes:
Savepoints
Included in:
ActiveRecord::ConnectionAdapters::SQLServerAdapter
Defined in:
lib/active_record/connection_adapters/sqlserver/database_statements.rb

Instance Method Summary collapse

Instance Method Details

#begin_db_transaction ⇒ Object 



46
47
48
 
# File 'lib/active_record/connection_adapters/sqlserver/database_statements.rb', line 46

def begin_db_transaction
  do_execute 'BEGIN TRANSACTION'
end

#begin_isolated_db_transaction(isolation) ⇒ Object 



54
55
56
57
 
# File 'lib/active_record/connection_adapters/sqlserver/database_statements.rb', line 54

def begin_isolated_db_transaction(isolation)
  set_transaction_isolation_level transaction_isolation_levels.fetch(isolation)
  begin_db_transaction
end

#case_sensitive_modifier(node, table_attribute) ⇒ Object 



85
86
87
88
 
# File 'lib/active_record/connection_adapters/sqlserver/database_statements.rb', line 85

def case_sensitive_modifier(node, table_attribute)
  node = Arel::Nodes.build_quoted node, table_attribute
  Arel::Nodes::Bin.new(node)
end

#commit_db_transaction ⇒ Object 



64
65
66
 
# File 'lib/active_record/connection_adapters/sqlserver/database_statements.rb', line 64

def commit_db_transaction
  do_execute 'COMMIT TRANSACTION'
end

#create_savepoint(name = current_savepoint_name) ⇒ Object 



74
75
76
 
# File 'lib/active_record/connection_adapters/sqlserver/database_statements.rb', line 74

def create_savepoint(name = current_savepoint_name)
  do_execute "SAVE TRANSACTION #{name}"
end

#exec_delete(sql, name, binds) ⇒ Object 



32
33
34
35
 
# File 'lib/active_record/connection_adapters/sqlserver/database_statements.rb', line 32

def exec_delete(sql, name, binds)
  sql << '; SELECT @@ROWCOUNT AS AffectedRows'
  super.rows.first.first
end

#exec_insert(sql, name, binds, _pk = nil, _sequence_name = nil) ⇒ Object 



22
23
24
25
26
27
28
29
30
 
# File 'lib/active_record/connection_adapters/sqlserver/database_statements.rb', line 22

def exec_insert(sql, name, binds, _pk = nil, _sequence_name = nil)
  id_insert = binds_have_identity_column?(binds)
  id_table  = table_name_from_binds(binds) if id_insert
  if id_insert && id_table
    with_identity_insert_enabled(id_table) { exec_query(sql, name, binds) }
  else
    exec_query(sql, name, binds)
  end
end

#exec_query(sql, name = 'SQL', binds = [], sqlserver_options = {}) ⇒ Object 



18
19
20
 
# File 'lib/active_record/connection_adapters/sqlserver/database_statements.rb', line 18

def exec_query(sql, name = 'SQL', binds = [], sqlserver_options = {})
  sp_executesql(sql, name, binds)
end

#exec_rollback_db_transaction ⇒ Object 



68
69
70
 
# File 'lib/active_record/connection_adapters/sqlserver/database_statements.rb', line 68

def exec_rollback_db_transaction
  do_execute 'IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION'
end

#exec_rollback_to_savepoint(name = current_savepoint_name) ⇒ Object 



78
79
80
 
# File 'lib/active_record/connection_adapters/sqlserver/database_statements.rb', line 78

def exec_rollback_to_savepoint(name = current_savepoint_name)
  do_execute "ROLLBACK TRANSACTION #{name}"
end

#exec_update(sql, name, binds) ⇒ Object 



37
38
39
40
 
# File 'lib/active_record/connection_adapters/sqlserver/database_statements.rb', line 37

def exec_update(sql, name, binds)
  sql << '; SELECT @@ROWCOUNT AS AffectedRows'
  super.rows.first.first
end

#execute(sql, name = nil) ⇒ Object 



10
11
12
13
14
15
16
 
# File 'lib/active_record/connection_adapters/sqlserver/database_statements.rb', line 10

def execute(sql, name = nil)
  if id_insert_table_name = query_requires_identity_insert?(sql)
    with_identity_insert_enabled(id_insert_table_name) { do_execute(sql, name) }
  else
    do_execute(sql, name)
  end
end

#execute_procedure(proc_name, *variables) ⇒ Object

SQLServer Specific ======================================== #



92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
 
# File 'lib/active_record/connection_adapters/sqlserver/database_statements.rb', line 92

def execute_procedure(proc_name, *variables)
  vars = if variables.any? && variables.first.is_a?(Hash)
           variables.first.map { |k, v| "@#{k} = #{quote(v)}" }
         else
           variables.map { |v| quote(v) }
         end.join(', ')
  sql = "EXEC #{proc_name} #{vars}".strip
  name = 'Execute Procedure'
  log(sql, name) do
    case @connection_options[:mode]
    when :dblib
      result = @connection.execute(sql)
      result.each(as: :hash, cache_rows: true) do |row|
        r = row.with_indifferent_access
        yield(r) if block_given?
      end
      result.each.map { |row| row.is_a?(Hash) ? row.with_indifferent_access : row }
    when :odbc
      results = []
      raw_connection_run(sql) do |handle|
        get_rows = lambda do
          rows = handle_to_names_and_values handle, fetch: :all
          rows.each_with_index { |r, i| rows[i] = r.with_indifferent_access }
          results << rows
        end
        get_rows.call
        get_rows.call while handle_more_results?(handle)
      end
      results.many? ? results : results.first
    end
  end
end

#newid_function ⇒ Object 



187
188
189
 
# File 'lib/active_record/connection_adapters/sqlserver/database_statements.rb', line 187

def newid_function
  select_value 'SELECT NEWID()'
end

#newsequentialid_function ⇒ Object 



191
192
193
 
# File 'lib/active_record/connection_adapters/sqlserver/database_statements.rb', line 191

def newsequentialid_function
  select_value 'SELECT NEWSEQUENTIALID()'
end

#release_savepoint(name = current_savepoint_name) ⇒ Object 



82
83
 
# File 'lib/active_record/connection_adapters/sqlserver/database_statements.rb', line 82

def release_savepoint(name = current_savepoint_name)
end

#select_rows(sql, name = nil, binds = []) ⇒ Object 



6
7
8
 
# File 'lib/active_record/connection_adapters/sqlserver/database_statements.rb', line 6

def select_rows(sql, name = nil, binds = [])
  sp_executesql sql, name, binds, fetch: :rows
end

#set_transaction_isolation_level(isolation_level) ⇒ Object 



59
60
61
62
 
# File 'lib/active_record/connection_adapters/sqlserver/database_statements.rb', line 59

def set_transaction_isolation_level(isolation_level)
  do_execute "SET TRANSACTION ISOLATION LEVEL #{isolation_level}"
  begin_db_transaction
end

#supports_statement_cache? ⇒ Boolean 



42
43
44
 
# File 'lib/active_record/connection_adapters/sqlserver/database_statements.rb', line 42

def supports_statement_cache?
  true
end

#transaction_isolation_levels ⇒ Object 



50
51
52
 
# File 'lib/active_record/connection_adapters/sqlserver/database_statements.rb', line 50

def transaction_isolation_levels
  super.merge snapshot: "SNAPSHOT"
end

#use_database(database = nil) ⇒ Object 



133
134
135
136
137
 
# File 'lib/active_record/connection_adapters/sqlserver/database_statements.rb', line 133

def use_database(database = nil)
  return if sqlserver_azure?
  name = SQLServer::Utils.extract_identifiers(database || @connection_options[:database]).quoted
  do_execute "USE #{name}" unless name.blank?
end

#user_options ⇒ Object 



139
140
141
142
143
144
145
146
147
148
149
150
151
152
 
# File 'lib/active_record/connection_adapters/sqlserver/database_statements.rb', line 139

def user_options
  return {} if sqlserver_azure?
  select_rows('dbcc useroptions', 'SCHEMA').reduce(HashWithIndifferentAccess.new) do |values, row|
    if row.instance_of? Hash
      set_option = row.values[0].gsub(/\s+/, '_')
      user_value = row.values[1]
    elsif  row.instance_of? Array
      set_option = row[0].gsub(/\s+/, '_')
      user_value = row[1]
    end
    values[set_option] = user_value
    values
  end
end

#user_options_dateformat ⇒ Object 



154
155
156
157
158
159
160
 
# File 'lib/active_record/connection_adapters/sqlserver/database_statements.rb', line 154

def user_options_dateformat
  if sqlserver_azure?
    select_value 'SELECT [dateformat] FROM [sys].[syslanguages] WHERE [langid] = @@LANGID', 'SCHEMA'
  else
    user_options['dateformat']
  end
end

#user_options_isolation_level ⇒ Object 



162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
 
# File 'lib/active_record/connection_adapters/sqlserver/database_statements.rb', line 162

def user_options_isolation_level
  if sqlserver_azure?
    sql = %(SELECT CASE [transaction_isolation_level]
            WHEN 0 THEN NULL
            WHEN 1 THEN 'READ UNCOMITTED'
            WHEN 2 THEN 'READ COMITTED'
            WHEN 3 THEN 'REPEATABLE READ'
            WHEN 4 THEN 'SERIALIZABLE'
            WHEN 5 THEN 'SNAPSHOT' END AS [isolation_level]
            FROM [sys].[dm_exec_sessions]
            WHERE [session_id] = @@SPID).squish
    select_value sql, 'SCHEMA'
  else
    user_options['isolation_level']
  end
end

#user_options_language ⇒ Object 



179
180
181
182
183
184
185
 
# File 'lib/active_record/connection_adapters/sqlserver/database_statements.rb', line 179

def user_options_language
  if sqlserver_azure?
    select_value 'SELECT @@LANGUAGE AS [language]', 'SCHEMA'
  else
    user_options['language']
  end
end

#with_identity_insert_enabled(table_name) ⇒ Object 



125
126
127
128
129
130
131
 
# File 'lib/active_record/connection_adapters/sqlserver/database_statements.rb', line 125

def with_identity_insert_enabled(table_name)
  table_name = quote_table_name(table_name_or_views_table_name(table_name))
  set_identity_insert(table_name, true)
  yield
ensure
  set_identity_insert(table_name, false)
end