On Thursday, July 23, 2015 at 7:39:17 AM UTC-7, [email protected] wrote:
>
> 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))
>
Does this fix it:
diff --git a/lib/sequel/adapters/shared/mssql.rb
b/lib/sequel/adapters/shared/mssql.rb
index 3993930..b7acc4e 100644
--- a/lib/sequel/adapters/shared/mssql.rb
+++ b/lib/sequel/adapters/shared/mssql.rb
@@ -433,7 +433,7 @@ module Sequel
inf_sch_qual = lambda{|s| info_sch_sch ?
Sequel.qualify(info_sch_sch, s) : Sequel.expr(s)}
sys_qual = lambda{|s| info_sch_sch ? Sequel.qualify(info_sch_sch,
Sequel.qualify(Sequel.lit(''), s)) : Sequel.expr(s)}
- identity_cols = metadata_dataset.from(:sys__columns).
+ identity_cols =
metadata_dataset.from(Sequel.lit('[sys].[columns]')).
where(:object_id=>table_id, :is_identity=>true).
select_map(:name)
This passes specs locally, so if it also works in your setup, I can commit
it.
It seems `DB#identifier_input_method`and `DB#identifier_output_method` are
> not used for the internal tables to get the schema.
>
Correct. Users are allowed to change the input/output methods, but doing
so would in general break the metadata methods unless they are hardcoded to
use the default settings.
> 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.
>
This patch only changes sys.columns, not the information_schema stuff, so
it should hopefully not have this problem.
Thanks,
Jeremy
--
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.
