activerecord-sqlserver-adapter-3.2.14 with rails 3.2.22.5, problem in user options

[nielsjansendk]

Activerecord-sqlserver-adapter throws the following error at me:

/gems/activerecord-sqlserver-adapter-3.2.14/lib/active_record/connection_adapters/sqlserver/database_statements.rb:139:in block in user_options': undefined method gsub' for ["textsize", "2147483647"]:Array (NoMethodError)

I have tried this on ruby-2.1.10, ruby-2.2.6 and ruby-2.3.3, same result.

My database.yml file looks like this

development:
adapter: sqlserver
mode: dblib
dataserver: my_server
database: my_database
username: my_username
password: xxxxxx
timeout: 0

My Gemfile looks like this:

source 'https://rubygems.org'
gem 'rails', '3.2.22.5'

gem 'tiny_tds', '> 1.1'
gem "activerecord-sqlserver-adapter", "> 3.2.14"

group :assets do
gem 'sass-rails', '> 3.2.3'
gem 'coffee-rails', '> 3.2.1'

gem 'uglifier', '>= 1.0.3'
end

gem 'jquery-rails'

The application has nothing else in it, to isolate the problem. The error is thrown when I attempt to start a console. FreeTDS and Tinytds have been tested and work just fine.

[metaskills]

So this line here: https://github.com/rails-sqlserver/activerecord-sqlserver-adapter/blob/3-2-stable/lib/active_record/connection_adapters/sqlserver/database_statements.rb#L139

Does your users have permissions to run dbcc useroptions? If not, it is needed.

[nielsjansendk]

Yes, I can execute "dbcc useroptions" from irb using tinytds, same connection properties. The reuslts are:

{"Set Option"=>"textsize", "Value"=>"64512"}
{"Set Option"=>"language", "Value"=>"us_english"}
{"Set Option"=>"dateformat", "Value"=>"mdy"}
{"Set Option"=>"datefirst", "Value"=>"7"}
{"Set Option"=>"lock_timeout", "Value"=>"-1"}
{"Set Option"=>"isolation level", "Value"=>"read committed snapshot"}

[metaskills]

So select_rows is going to return an array of arrays vs the array of hashes like you posted. So technically the first row in the loop should be equal to ["textsize", "2147483647"] but for some reason your error seems to indicate the value from select_rows is a double nested array.

Can you do a $ bundle open activerecord-sqlserver-adapter and open this file and do some debugging. I'd like to know the return value for select_rows("dbcc useroptions",'SCHEMA') both in structure and the class of that object. TIA.

[nielsjansendk]

If I insert a print statement like this:

def user_options
return {} if sqlserver_azure?
print select_rows("dbcc useroptions",'SCHEMA')
....

I get this output:

[[["textsize", "2147483647"], ["language", "us_english"], ["dateformat", "mdy"], ["datefirst", "7"], ["lock_timeout", "-1"], ["quoted_identifier", "SET"], ["ansi_null_dflt_on", "SET"], ["ansi_warnings", "SET"], ["ansi_padding", "SET"], ["ansi_nulls", "SET"], ["concat_null_yields_null", "SET"], ["isolation level", "read committed snapshot"]], []]

Is that what you were looking for?

[metaskills]

Yes... but can you also print the .class.name of that object? I just want to make sure it is an Array or an ActiveRecord::Result which looks like an array too or even something else.

[nielsjansendk]

obejct.class outputs Array.

[metaskills]

Thanks! I don't know why, but I suspect TinyTds is returning the data like that which could be it, the server, some client config, or something else. Can you show me the output from IRB with a TinyTds client connection of the following:

@client.execute('dbcc useroptions').each(as: :array) 

[nielsjansendk]

The output in tinytds is as follows:

[[["textsize", "64512"], ["language", "us_english"], ["dateformat", "mdy"], ["datefirst", "7"], ["lock_timeout", "-1"], ["isolation level", "read committed snapshot"]], []]

The class is also an array.

[metaskills]

ODD! I get the following. The adapter would have been broken years ago for everyone if your output was normal. I wonder why it is coming back with what appears to be two result sets?

[["textsize", "2147483647"], ["language", "us_english"], ["dateformat", "mdy"], ["datefirst", "7"], ["lock_timeout", "-1"], ["quoted_identifier", "SET"], ["ansi_null_dflt_on", "SET"], ["ansi_warnings", "SET"], ["ansi_padding", "SET"], ["ansi_nulls", "SET"], ["concat_null_yields_null", "SET"], ["isolation level", "read committed"]]

[metaskills]

What version of FreeTDS are you using? The output of tsql -C should show it.

[nielsjansendk]

Version: freetds v1.00.23
TDS version: 7.3

I am on a Mac OS Sierra and I just upgraded my homebrew (that is what started this whole thing).

[metaskills]

So odd. I am on 1.00.21 and I have never seen a double result set like that in any version. I wonder what that second result set is? Check out this TinyTDS issue where I talk about how it handles multiple result sets.

rails-sqlserver/tiny_tds#29

If you fire up IRB again, you can do this to see what was in that set. Also, what version of SQL Server are you using? The answers could be in that info too.

result = @client.execute('dbcc useroptions')
result.each # => ...
result.each # => ...

[nielsjansendk]

result.each gives me:

[[{"Set Option"=>"textsize", "Value"=>"64512"}, {"Set Option"=>"language", "Value"=>"us_english"}, {"Set Option"=>"dateformat", "Value"=>"mdy"}, {"Set Option"=>"datefirst", "Value"=>"7"}, {"Set Option"=>"lock_timeout", "Value"=>"-1"}, {"Set Option"=>"isolation level", "Value"=>"read committed snapshot"}], []]

[nielsjansendk]

This is the version of mssql server: 11.0.3128.0

[metaskills]

result.each gives me:

For both calls? Or did the second one give you nil?