RuntimeError (Unknown bind columns. We can account for this.):

[CloCkWeRX]

Similar to #118

$ cat Gemfile.lock | grep sql
activerecord-sqlserver-adapter (3.2.10)
activerecord-sqlserver-adapter (~> 3.2.0)

Creating a view with a lower case name fails to correctly map.

The below fails to map to a 'Views::ExampleViewName' class
CREATE VIEW example_view_name AS ...

Combined with
Views::ExampleViewName.find(1)

produces:
RuntimeError (Unknown bind columns. We can account for this.):

Creating the view with uppercase appears to work
CREATE VIEW EXAMPLE_VIEW_NAME AS ...

Misc other things:
$ dpkg -l | grep tds
ii freetds-bin 0.91-1build1 amd64 FreeTDS command-line utilities
ii freetds-common 0.91-1build1 all configuration files for FreeTDS SQL client libraries
ii freetds-dev 0.91-1build1 amd64 MS SQL and Sybase client library (static libs and headers)
ii libjtds-java 1.2.5+dfsg-2ubuntu1 all JDBC 3.0 driver for Microsoft SQL Server(tm) and Sybase(tm)
ii tdsodbc:amd64 0.91-1build1 amd64 ODBC driver for connecting to MS SQL and Sybase SQL servers

[coldnebo]

I'm getting something like this too -- there is a possibility in our case that connection permissions don't allow schema access -- but if that's the case, I'd like to see a "permission denied" error on the attempt to introspect the schema (fail-fast), not an unknown bind columns error. What does 'we can account for this' mean? We obviously can't account for it because the exception is raised and not processed within the gem.

The code that breaks is:

> Foo.find(1)
RuntimeError: Unknown bind columns. We can account for this.

My problem is with 3.2.10 of this gem and using SQL Server 2008 R2 (10.50.4000).

(I also noticed the use of "Load" in the source which might be deprecated in 2008?)

I'm having trouble providing more context because this schema plan stuff is happening well before and out of the stack trace for the actual exception being raised. Is there a better way to debug this on my end to provide more useful data?

Thanks!

[metaskills]

I'd like to see a "permission denied"

Nothing like that on schema reflection. Only you know if your user should see table "a" or not.

[coldnebo]

yes, but if the user can see the tables for queries and not see the table for schema... AFAIK, those are two different permission levels, no? Anyway, this is guesswork on my part because I can't actually see the SQL that produces the actual error.

I've traced it around ActiveRecord::relation that somehow gets @bind_values = [nil, 1] but clearly something got messed up in assigning the columns. Because of this column.is_a?(ActiveRecord::ConnectionAdapters::Column) is false and falls through to the raise in activerecord-sqlserver-adapter-3.2.10/lib/active_record/connection_adapters/sqlserver/database_statements.rb @ line 340 ActiveRecord::ConnectionAdapters::Sqlserver::DatabaseStatements#do_exec_query

Also note that the following call works fine:

> Foo.where('id = ?', 1)
[<Foo>]

which I am at a loss to explain.

[metaskills]

I think so. But reflection is already way complicated and expensive too. It slows down development a fair bit. My gut says there are diminishing returns on the assumed complexity to make this happen. I could be wrong. Perhaps we should just open a ticket to have a better message :)

[coldnebo]

Well, it could be something else too... I don't know what the actual error is because I can't find where the columns are actually bound (p.s. our DBAs refuse to look unless I can come up with the actual error 😦 ) -- I think there is a condition somewhere that should probably be an explicit raise rather than a silent error that blows up standard AR calls (that's why I was curious about the original intent of that error message).

I understand the reflection being expensive... is there a better way to make standard ActiveRecord calls work? AFAIK, Foo.find(id) should "just work".

[metaskills]

As soon as Foo is defined (in development) ActiveRecord will do it's first order of business, reflect on the DB for the schema and populate the .columns, figure out what the PK is, etc. It will use those columns when building queries, in this case for bind vars and prepared statement support. So in short, yes, just work, assuming that all happened :)

[coldnebo]

and the schema reflection is delgated per adapter, in this case sqlserver adapter, right? is there a sql equivalent that I might run under the same account to verify access?

i.e. I'm looking for a snippet that I can run in MS SQL Server Manager directly to rule out the ruby part of this problem.

(note: I have SQL logging to console, and I can see the result of the Foo.where() call:

 Foo Load (1.9ms)  EXEC sp_executesql N'SELECT TOP (1) [FOO].* FROM [FOO] WHERE (id = 1)'

But I can't see any logs for the schema reflection.

[metaskills]

But I can't see any logs for the schema reflection

I am rusty on this one. But I do know that we conform to the ActiveRecord conventions in that sql issued to the low level methods uses a name parameter of 'SCHEMA'. This does not log by default, but you can enable it. I would have to Google for it. Can you check around?

[coldnebo]

yep, that's a great clue... will check.. thanks!

FYI, I had that same error message and it ended up being that the user was not a member of the db_owner group. Adding them to that role granted some permission which was missing.

[jwoertink]

I'm getting this error too when trying to call an association. My database was pre-existing, so my rails app doesn't really have a schema or anything. Here are my two models

class Employee < ActiveRecord::Base
  self.table_name = "Employee"
  belongs_to :company, foreign_key: 'CompanyId'
end

class Company < ActiveRecord::Base
  self.table_name = "Company"
  has_many :employees
end

And here is my call

Company.first.employees
# => blow up stuff

Here's the error stack.

RuntimeError: Unknown bind columns. We can account for this.
    from /Users/jeremy/.rvm/gems/ruby-2.1.5@ard-checkin/gems/activerecord-sqlserver-adapter-4.1.0/lib/active_record/connection_adapters/sqlserver/database_statements.rb:349:in `block in do_exec_query'
    from /Users/jeremy/.rvm/gems/ruby-2.1.5@ard-checkin/gems/activerecord-sqlserver-adapter-4.1.0/lib/active_record/connection_adapters/sqlserver/database_statements.rb:332:in `each'
    from /Users/jeremy/.rvm/gems/ruby-2.1.5@ard-checkin/gems/activerecord-sqlserver-adapter-4.1.0/lib/active_record/connection_adapters/sqlserver/database_statements.rb:332:in `each_with_index'
    from /Users/jeremy/.rvm/gems/ruby-2.1.5@ard-checkin/gems/activerecord-sqlserver-adapter-4.1.0/lib/active_record/connection_adapters/sqlserver/database_statements.rb:332:in `do_exec_query'
    from /Users/jeremy/.rvm/gems/ruby-2.1.5@ard-checkin/gems/activerecord-sqlserver-adapter-4.1.0/lib/active_record/connection_adapters/sqlserver/database_statements.rb:24:in `exec_query'
    from /Users/jeremy/.rvm/gems/ruby-2.1.5@ard-checkin/gems/activerecord-sqlserver-adapter-4.1.0/lib/active_record/connection_adapters/sqlserver/database_statements.rb:295:in `select'
    from /Users/jeremy/.rvm/gems/ruby-2.1.5@ard-checkin/gems/activerecord-4.1.8/lib/active_record/connection_adapters/abstract/database_statements.rb:24:in `select_all'
    from /Users/jeremy/.rvm/gems/ruby-2.1.5@ard-checkin/gems/activerecord-4.1.8/lib/active_record/connection_adapters/abstract/query_cache.rb:70:in `select_all'
    from /Users/jeremy/.rvm/gems/ruby-2.1.5@ard-checkin/gems/activerecord-4.1.8/lib/active_record/querying.rb:39:in `find_by_sql'
    from /Users/jeremy/.rvm/gems/ruby-2.1.5@ard-checkin/gems/activerecord-4.1.8/lib/active_record/relation.rb:611:in `exec_queries'
    from /Users/jeremy/.rvm/gems/ruby-2.1.5@ard-checkin/gems/activerecord-4.1.8/lib/active_record/association_relation.rb:19:in `exec_queries'
    from /Users/jeremy/.rvm/gems/ruby-2.1.5@ard-checkin/gems/activerecord-4.1.8/lib/active_record/relation.rb:493:in `load'
    from /Users/jeremy/.rvm/gems/ruby-2.1.5@ard-checkin/gems/activerecord-4.1.8/lib/active_record/relation.rb:238:in `to_a'
    from /Users/jeremy/.rvm/gems/ruby-2.1.5@ard-checkin/gems/activerecord-4.1.8/lib/active_record/associations/collection_association.rb:416:in `find_target'
    from /Users/jeremy/.rvm/gems/ruby-2.1.5@ard-checkin/gems/activerecord-4.1.8/lib/active_record/associations/collection_association.rb:380:in `load_target'
    from /Users/jeremy/.rvm/gems/ruby-2.1.5@ard-checkin/gems/activerecord-4.1.8/lib/active_record/associations/collection_proxy.rb:44:in `load_target'
... 3 levels...
    from /Users/jeremy/.rvm/gems/ruby-2.1.5@ard-checkin/gems/railties-4.1.8/lib/rails/commands/console.rb:9:in `start'
    from /Users/jeremy/.rvm/gems/ruby-2.1.5@ard-checkin/gems/railties-4.1.8/lib/rails/commands/commands_tasks.rb:69:in `console'
    from /Users/jeremy/.rvm/gems/ruby-2.1.5@ard-checkin/gems/railties-4.1.8/lib/rails/commands/commands_tasks.rb:40:in `run_command!'
    from /Users/jeremy/.rvm/gems/ruby-2.1.5@ard-checkin/gems/railties-4.1.8/lib/rails/commands.rb:17:in `<top (required)>'
    from /Users/jeremy/.rvm/gems/ruby-2.1.5@ard-checkin/gems/activesupport-4.1.8/lib/active_support/dependencies.rb:247:in `require'
    from /Users/jeremy/.rvm/gems/ruby-2.1.5@ard-checkin/gems/activesupport-4.1.8/lib/active_support/dependencies.rb:247:in `block in require'
    from /Users/jeremy/.rvm/gems/ruby-2.1.5@ard-checkin/gems/activesupport-4.1.8/lib/active_support/dependencies.rb:232:in `load_dependency'
    from /Users/jeremy/.rvm/gems/ruby-2.1.5@ard-checkin/gems/activesupport-4.1.8/lib/active_support/dependencies.rb:247:in `require'
    from /Users/jeremy/Sites/alice/ard-checkin/bin/rails:8:in `<top (required)>'
    from /Users/jeremy/.rvm/gems/ruby-2.1.5@ard-checkin/gems/activesupport-4.1.8/lib/active_support/dependencies.rb:241:in `load'
    from /Users/jeremy/.rvm/gems/ruby-2.1.5@ard-checkin/gems/activesupport-4.1.8/lib/active_support/dependencies.rb:241:in `block in load'
    from /Users/jeremy/.rvm/gems/ruby-2.1.5@ard-checkin/gems/activesupport-4.1.8/lib/active_support/dependencies.rb:232:in `load_dependency'
    from /Users/jeremy/.rvm/gems/ruby-2.1.5@ard-checkin/gems/activesupport-4.1.8/lib/active_support/dependencies.rb:241:in `load'
    from /Users/jeremy/.rvm/rubies/ruby-2.1.5/lib/ruby/site_ruby/2.1.0/rubygems/core_ext/kernel_require.rb:54:in `require'
    from /Users/jeremy/.rvm/rubies/ruby-2.1.5/lib/ruby/site_ruby/2.1.0/rubygems/core_ext/kernel_require.rb:54:in `require'

All other normal queries with the model seem to work fine. Just the associations that blow up.

[metaskills]

There is a change in master that fixes this for the upcoming 4.2 release which should be out in alpha later today. I have accounted for integer and string types and we can add more support later if needed.

[enwood]

I'm just going to add a comment here that might help anyone else searching for this problem.

I hit this very same error message using version 4.0.0 of this gem under Rails 4.0.8.

My User model has_many Filings. And I had a Filing belonging to a User, but named as an owner, with the class name User:

belongs_to    :owner, :class_name => "User" 

When querying a specific user for filings, I was getting this error:

crs »  u = User.find(1)
crs »  u.filings
(pry) output error: # RuntimeError: Unknown bind columns. We can account for this.

When I changed a Filing to belong to a User (without the name owner and without expressly specify the class name):

belongs_to :user

the problem disappeared.

[1] crs »  u = User.find(1)
[2] crs »  u.filings
  Filing Load (5.0ms)  EXEC sp_executesql N'SELECT [filings].* FROM [filings] WHERE [filings].[user_id] = @0', N'@0 int', @0 = 1  [["user_id", 1]]
=> [

So, the adapter was having difficulty mapping the owner_id to the User class in the first example?