Sequel: How to define / create schema with SQL Server (tds)

[mikelogaciuk]

Hi,

I am trying to create table with Sequel, connected to SQL Server with tinytds adapter.

However I retrieve an error that the schema does not exists:

/var/lib/gems/3.0.0/gems/sequel-5.61.0/lib/sequel/adapters/tinytds.rb:75:in `each': TinyTds::Error: The default schema does not exist. (Sequel::DatabaseError)

The direct query through fetch, returns proper table name, but schema being returned is NULL:

DB.fetch("SELECT SCHEMA_NAME() AS current_schema, DB_NAME() AS current_table") do |row|
  puts row
end

# Output:
# {:current_schema=>nil, :current_table=>"POS_Lake"}

Is there a method to specify a schema? Or how should I do that?

I've checked documentation, but didn't find anything about specifying schema for further coding.

Additional screenshot:

EDIT:

I've tried to do it a little bit other way around, but the errors is the same:

require 'sequel'
require 'tiny_tds'
require 'yaml'
require 'erb'
require 'socket'

settings = YAML.load(ERB.new(File.read("config/settings.yaml")).result)

DB = Sequel.tinytds(
  :dataserver => settings['target']['development'],
  :database   => settings['target']['database']['lake'],
  :user       => "#{settings['auth']['domain']}\\#{settings['auth']['user']}",
  :password   => settings['auth']['password']
)

puts "Connection established successfully." if DB.test_connection true

DB.fetch("SELECT SCHEMA_NAME() AS current_schema, DB_NAME() AS current_table") do |row|
  puts row
end

DB.create_table :sales do
  primary_key :sale_id
  String :store
end

[jeremyevans]

Never seen that error before. Once you find out what schema you should be using (since apparently your connection does not have a working default schema), you can use: DB.create_table Sequel[:schema_name][:sales] do to create the table.

[mikelogaciuk]

Thank you Jeremy for a snippet and for heads up.

I've checked the user settings on a server and found that the DBA Guy forgot to set the default schema while creating the user/role.

However I have another question:

How can I change schema in Sequel?

E.g. I will have 6-7 schemas and there will be probably a situation where I will need to jump e.g. from sales to staging etc.
Is there any a snippet for a such behavior?

Best regards,
Mike

PS. I've read the code you've written and I must admin that... It is a superb work and framework.

[jeremyevans]

The answer depends on what you mean by "jump to". You can deal with multiple schemas in a single application by specifying them using the Sequel[:schema_name][:table_name] syntax. However, if you mean using unqualified table names and changing the default schema at runtime (so the unqualified table names start referencing a different underlying table), that's different. For that it may be best to using the sharding support with a separate shard per schema.

[mikelogaciuk]

I think your response explains everything that I wanted to know.

Cheers!