Whether to use N” to quote strings, which allows unicode characters inside the strings. True by default for compatibility, can be set to false for a possible performance increase. This sets the default for all datasets created from this Database object.
Execute the given stored procedure with the given name.
Options:
Arguments to stored procedure. For named arguments, this should be a hash keyed by argument named. For unnamed arguments, this should be an array. Output parameters to the function are specified using :output. You can also name output parameters and provide a type by using an array containing :output, the type name, and the parameter name.
The server/shard on which to execute the procedure.
This method returns a single hash with the following keys:
The result code of the stored procedure
The number of rows affected by the stored procedure
Values for any output paramters, using the name given for the output parameter
Examples:
DB.call_mssql_sproc(:SequelTest, {:args => ['input arg', :output]}) DB.call_mssql_sproc(:SequelTest, {:args => ['input arg', [:output, 'int', 'varname']]}) named params: DB.call_mssql_sproc(:SequelTest, :args => { 'input_arg1_name' => 'input arg1 value', 'input_arg2_name' => 'input arg2 value', 'output_arg_name' => [:output, 'int', 'varname'] })
# File lib/sequel/adapters/shared/mssql.rb, line 79 def call_mssql_sproc(name, opts=OPTS) args = opts[:args] || [] names = ['@RC AS RESULT', '@@ROWCOUNT AS NUMROWS'] declarations = ['@RC int'] values = [] if args.is_a?(Hash) named_args = true args = args.to_a method = :each else method = :each_with_index end args.send(method) do |v, i| if named_args k = v v, type, select = i raise Error, "must provide output parameter name when using output parameters with named arguments" if v == :output && !select else v, type, select = v end if v == :output type ||= "nvarchar(max)" if named_args varname = select else varname = "var#{i}" select ||= varname end names << "@#{varname} AS #{quote_identifier(select)}" declarations << "@#{varname} #{type}" value = "@#{varname} OUTPUT" else value = literal(v) end if named_args value = "@#{k}=#{value}" end values << value end sql = "DECLARE #{declarations.join(', ')}; EXECUTE @RC = #{name} #{values.join(', ')}; SELECT #{names.join(', ')}" ds = dataset.with_sql(sql) ds = ds.server(opts[:server]) if opts[:server] ds.first end
Microsoft SQL Server uses the :mssql type.
# File lib/sequel/adapters/shared/mssql.rb, line 132 def database_type :mssql end
Return foreign key information using the system views, including :name, :on_delete, and :on_update entries in the hashes.
# File lib/sequel/adapters/shared/mssql.rb, line 143 def foreign_key_list(table, opts=OPTS) m = output_identifier_meth im = input_identifier_meth schema, table = schema_and_table(table) current_schema = m.call(get(Sequel.function('schema_name'))) fk_action_map = FOREIGN_KEY_ACTION_MAP fk = Sequel[:fk] fkc = Sequel[:fkc] ds = metadata_dataset.from(Sequel.lit('[sys].[foreign_keys]').as(:fk)). join(Sequel.lit('[sys].[foreign_key_columns]').as(:fkc), :constraint_object_id => :object_id). join(Sequel.lit('[sys].[all_columns]').as(:pc), :object_id => fkc[:parent_object_id], :column_id => fkc[:parent_column_id]). join(Sequel.lit('[sys].[all_columns]').as(:rc), :object_id => fkc[:referenced_object_id], :column_id => fkc[:referenced_column_id]). where{{object_schema_name(fk[:parent_object_id]) => im.call(schema || current_schema)}}. where{{object_name(fk[:parent_object_id]) => im.call(table)}}. select{[fk[:name], fk[:delete_referential_action], fk[:update_referential_action], pc[:name].as(:column), rc[:name].as(:referenced_column), object_schema_name(fk[:referenced_object_id]).as(:schema), object_name(fk[:referenced_object_id]).as(:table)]}. order(fk[:name], fkc[:constraint_column_id]) h = {} ds.each do |row| if r = h[row[:name]] r[:columns] << m.call(row[:column]) r[:key] << m.call(row[:referenced_column]) else referenced_schema = m.call(row[:schema]) referenced_table = m.call(row[:table]) h[row[:name]] = { :name => m.call(row[:name]), :table => (referenced_schema == current_schema) ? referenced_table : Sequel.qualify(referenced_schema, referenced_table), :columns => [m.call(row[:column])], :key => [m.call(row[:referenced_column])], :on_update => fk_action_map[row[:update_referential_action]], :on_delete => fk_action_map[row[:delete_referential_action]] } end end h.values end
# File lib/sequel/adapters/shared/mssql.rb, line 184 def freeze server_version super end
Microsoft SQL Server namespaces indexes per table.
# File lib/sequel/adapters/shared/mssql.rb, line 137 def global_index_namespace? false end
Use the system tables to get index information
# File lib/sequel/adapters/shared/mssql.rb, line 190 def indexes(table, opts=OPTS) m = output_identifier_meth im = input_identifier_meth indexes = {} i = Sequel[:i] ds = metadata_dataset.from(Sequel.lit('[sys].[tables]').as(:t)). join(Sequel.lit('[sys].[indexes]').as(:i), :object_id=>:object_id). join(Sequel.lit('[sys].[index_columns]').as(:ic), :object_id=>:object_id, :index_id=>:index_id). join(Sequel.lit('[sys].[columns]').as(:c), :object_id=>:object_id, :column_id=>:column_id). select(i[:name], i[:is_unique], Sequel[:c][:name].as(:column)). where{{t[:name]=>im.call(table)}}. where(i[:is_primary_key]=>0, i[:is_disabled]=>0). order(i[:name], Sequel[:ic][:index_column_id]) if supports_partial_indexes? ds = ds.where(i[:has_filter]=>0) end ds.each do |r| index = indexes[m.call(r[:name])] ||= {:columns=>[], :unique=>(r[:is_unique] && r[:is_unique]!=0)} index[:columns] << m.call(r[:column]) end indexes end
# File lib/sequel/adapters/shared/mssql.rb, line 47 def mssql_unicode_strings=(v) @mssql_unicode_strings = v reset_default_dataset end
The version of the MSSQL server, as an integer (e.g. 10001600 for SQL Server 2008 Express).
# File lib/sequel/adapters/shared/mssql.rb, line 217 def server_version(server=nil) return @server_version if @server_version if @opts[:server_version] return @server_version = Integer(@opts[:server_version]) end @server_version = synchronize(server) do |conn| (conn.server_version rescue nil) if conn.respond_to?(:server_version) end unless @server_version m = /^(\d+)\.(\d+)\.(\d+)/.match(fetch("SELECT CAST(SERVERPROPERTY('ProductVersion') AS varchar)").single_value.to_s) @server_version = (m[1].to_i * 1000000) + (m[2].to_i * 10000) + m[3].to_i end @server_version end
MSSQL 2008+ supports partial indexes.
# File lib/sequel/adapters/shared/mssql.rb, line 233 def supports_partial_indexes? dataset.send(:is_2008_or_later?) end
MSSQL supports savepoints, though it doesn’t support committing/releasing them savepoint
# File lib/sequel/adapters/shared/mssql.rb, line 238 def supports_savepoints? true end
MSSQL supports transaction isolation levels
# File lib/sequel/adapters/shared/mssql.rb, line 243 def supports_transaction_isolation_levels? true end
MSSQL supports transaction DDL statements.
# File lib/sequel/adapters/shared/mssql.rb, line 248 def supports_transactional_ddl? true end
Microsoft SQL Server supports using the INFORMATION_SCHEMA to get information on tables.
# File lib/sequel/adapters/shared/mssql.rb, line 254 def tables(opts=OPTS) information_schema_tables('BASE TABLE', opts) end
Microsoft SQL Server supports using the INFORMATION_SCHEMA to get information on views.
# File lib/sequel/adapters/shared/mssql.rb, line 260 def views(opts=OPTS) information_schema_tables('VIEW', opts) end