Hello.
I have a problem with a case sensitive SQL Server DB.
When I call `DB#schema(table)` it ends with `TinyTds::Error: Invalid object
name 'SYS.COLUMNS'.`
Example code:
DB = Sequel.tinytds(
:host => SERVER,
:database=> DBNAME,
)
DB.identifier_input_method = nil #Case sensitive table names
DB.identifier_output_method = nil #Case sensitive table names (shows
effect in DB#tables)
DB.tables.each{|table|
DB.schema(table)
}
Without the `identifier_[in|out]put_method` I already get 'table not found'
errors for simple selections.
But now I get the error for table `SYS.COLUMNS`.
I located the problem in adapters/shared/mssql.rb:438:
identity_cols = metadata_dataset.from(:sys__columns).
where(:object_id=>table_id, :is_identity=>true).
select_map(:name)
The generated SQL is
SELECT [NAME] FROM [SYS].[COLUMNS] WHERE (([OBJECT_ID] = 2099048) AND
([IS_IDENTITY] = 1))
`[SYS].[COLUMNS]` is not correct if MSSQL is case sensitive.
The correct selection should be (table name in down letters):
SELECT [NAME] FROM [sys].[columns] WHERE (([OBJECT_ID] = 2099048) AND
([IS_IDENTITY] = 1))
It seems `DB#identifier_input_method`and `DB#identifier_output_method` are
not used for the internal tables to get the schema.
Location of the problem (I checked in meantime also with version 4.24):
C:/Appl/Ruby21-x64/lib/ruby/gems/2.1.0/gems/sequel-4.19.0/lib/sequel/adapters/tinytds.rb:234:in
`fetch_rows':
from
C:/Appl/Ruby21-x64/lib/ruby/gems/2.1.0/gems/sequel-4.19.0/lib/sequel/dataset/actions.rb:139:in
`each'
from
C:/Appl/Ruby21-x64/lib/ruby/gems/2.1.0/gems/sequel-4.19.0/lib/sequel/dataset/actions.rb:390:in
`map'
from
C:/Appl/Ruby21-x64/lib/ruby/gems/2.1.0/gems/sequel-4.19.0/lib/sequel/dataset/actions.rb:390:in
`map'
from
C:/Appl/Ruby21-x64/lib/ruby/gems/2.1.0/gems/sequel-4.19.0/lib/sequel/dataset/actions.rb:849:in
`_select_map_single'
from
C:/Appl/Ruby21-x64/lib/ruby/gems/2.1.0/gems/sequel-4.19.0/lib/sequel/dataset/actions.rb:879:in
`_select_map'
from
C:/Appl/Ruby21-x64/lib/ruby/gems/2.1.0/gems/sequel-4.19.0/lib/sequel/dataset/actions.rb:613:in
`select_map'
from
C:/Appl/Ruby21-x64/lib/ruby/gems/2.1.0/gems/sequel-4.19.0/lib/sequel/adapters/shared/mssql.rb:438:in
`schema_parse_table'
from
C:/Appl/Ruby21-x64/lib/ruby/gems/2.1.0/gems/sequel-4.19.0/lib/sequel/database/query.rb:158:in
`schema'
from PIM_DB_Analyze.rb:73:in `<main>'
I tried a patch:
class Sequel::TinyTDS::Database
public :metadata_dataset
#patch it
def metadata_dataset
ds = super
ds.quote_identifiers = true
##patch >>
ds.identifier_input_method =
self.identifier_input_method
ds.identifier_output_method =
self.identifier_output_method
##patch <<
ds
end
end
p main.db.metadata_dataset.from(:sys__columns)
##<Sequel::TinyTDS::Dataset: "SELECT * FROM [sys].[columns]">
This solved the problem with sys__columns, but it adds a new problem
`TinyTds::Error: Invalid object name 'information_schema.tables'`
So this seems to be a wrong solution for my problem.
In the end I patched tinytds.rb:
=begin
Add a patch to support case sensitive DB
=end
class Sequel::TinyTDS::Dataset
alias :old_fetch_rows :fetch_rows
def fetch_rows(sql, &block)
sql =
sql.gsub(/\[SYS\].\[COLUMNS\]|SYSINDEXES|SYSINDEXKEYS|SYSCOLUMNS/){|x|
#~ STDERR << "%s#%s:%s: patch %s\n" % [__FILE__,
__method__, __LINE__, sql]
x.downcase
}
old_fetch_rows(sql, &block)
end
end
Now it works as expected, I can execute DB.schema.
But it is a really ugly hack.
Did I miss something in my DB-definition?
Is there a better solution?
Or is this a bug for databases with specific settings?
Thanks.
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sequel-talk.
For more options, visit https://groups.google.com/d/optout.
