Rails3.1, SQLServer adapter "find" fails with views

[napcs]

Similar to issue #117.

When we call .find on the table, we get this in Rails 3.0.9 with sqlserver-adapter 3.0.15:

NoMethodError: undefined method `eq' for nil:NilClass

When we do this on 3.1, I'm hitting your exception:

RuntimeError: Unknown bind columns. We can account for this.
from /Users/brianhogan/.rvm/gems/ruby-1.8.7-p174@rails31/gems/activerecord-sqlserver-adapter-
    3.1.0.0/lib/active_record/connection_adapters/sqlserver/database_statements.rb:250:in `do_exec_query'

This is a legacy table with a very ugly schema so we've used a view to remap it.

class PersonRecord < ActiveRecord::Base set_table_name "person_record" set_primary_key "personid" end This seems to happen with Rails 3 as well. This is with TDS and I tested this on Rails 3.0.9 with sqlserver-adapter 3.0.15 and on Rails 3.1 with sqlserver-adapter 3.1.0.

I cannot get dbi and friends working with Rails 3 - getting all sorts of nomethod errors related to DBI::Date.

 Users/brianhogan/.rvm/gems/ruby-1.8.7-p174@app105/gems/activesupport-
 3.0.9/lib/active_support/core_ext/module/aliasing.rb:31:in `alias_method': undefined method `public' for class `DBI::Date'
 (NameError)

Our Rails 2 apps that work use sqlserver-adapter + odbc, so I cannot say for sure if the problem is a change made in sqlserver adapter or with FreeTDS and views.

[metaskills]

It sounds like you have some extra code in your stack that may be causing the issue. I commented on #117 and in both situations, I am going to need some help getting thru the noise above. Can you write a simple tests that fails in the views context?

FWIW, I use views in our day job too. Some are nasty. so I try to make sure our app passes all these tests, but you need to help me define a failing tests case.

Lastly, the "NoMethodError: undefined method `eq' for nil:NilClass" typically something simple on your end. Was reported in #74 and has been discussed on the adapter list. I doubt it is directly related to your view issue. But I still need to understand the problem and see a failing test case. The master branch is very easy to get setup and running tests. I recently did a refractor of what was needed, so start there.

[napcs]

I'll start digging through any extra code, but this all started with a simple test 3.1 project. I only went down to 3.0 and back to DBI to see if I could find the last version that worked, in order to help. Sorry if it was info overload.

As it stands now a simple Rails app with the code I showed, connected to our db, is throwing the

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

when I call .find

I'm closing 117 because it's probably related to this.

I will see if I can get something more concrete than that to show how this fails.

[metaskills]

Well if it is a simple rails 3.1 project with nothing but the adapter and the required deps (no extra dbi, etc) then you are likely seeing an edge case for a view that was not explicitly tested for by us. Should be easy to track down and write a failing test for, I just need to know what is failing.

[rhettbarber]

I had that error this morning. My table was not set to auto-increment on the id column.

This error that is: RuntimeError: Unknown bind columns. We can account for this.

[napcs]

If that's the case, there's nothing I can do - this is a view that will never have that. I can't get the tests to run on my machine or I would dig into this again - I get a bunch of passing tests followed by about 3000 errors that don't make any sense to me. I will keep digging.

[metaskills]

If you are getting that many errors, you are likely using the wrong version of FreeTDS. The 3.1 adapter with DBLIB/TinyTDS connection mode requires the 0.91 version of FreeTDS.

[napcs]

The documentation doesn't say anything about needing 0.9.1 so I didn't know that. I have 0.8.2 on my server. I see references for 0.9.1 if I am using Azure, which I'm not. The testing instructions indicate 0.8.2 wosk also.

I guess I'll figure out how to get a higher version of TDS working on OSX.

But is there an easier way to verify that this is an issue with the missing autoincrement on the table?

[metaskills]

It's OK, there is no way I could tell at run time what version of FreeTDS is installed. It has a horrible version schema, none really. I've been talking about the 0.91 only for 3.1 on the TinyTDS page, adapter wiki and a mailing list and change log, but have not done a read me. Patches welcome, doing the best I can with little time. You can install a TinyTDS with 0.91 using the git project, see it's README on rake compiler and miniportile. That way you do not have to worry about a system ruby. Tho I have contributed to the Homebrew project to make sure they have a proper Formula for 0.91, even tho I use MacPorts.

Re: your ticket. I do not have time to debug for you. I say this knowing that we are passing all the tests for views now. So I do not understand your problem and I will need to rely on you to tell me what the bug is. I can help write a regression test if needed, but I need to understand the problem.

[napcs]

Not asking you to debug my code. There's nothing to debug other than a stock rails app, a model with two lines in it connecting to a view with no ID auto-increment column. I can connect, I can pull the first and last rows rom the table. I cannot use any finders. This isn't an existing app, it's a fresh app with one model, no plugins.

So, now that I know what I have to do to get the tests passing, I can work towards getting this fixed.

[metaskills]

Good luck, again sorry for the vibe. I'm dealing with power outages here still from the hurricane and short on time and apparently manners while helping as much as I can. I am being curt, but not trying to be rude.

I wrote this demo legacy app for 3.1. I need to update the Gemfile to match the release. It may help in only that it is a base project that has a legacy crazy DB on the backend. Tho it is easier to write a test if you know what the issue is. Hell, the whole adapter could be borked for views like I said and only the limited tests we have pass. I just need an affirmation before I take the time and pitch in.

https://github.com/rails-sqlserver/AdventureWorks.Ruby

[metaskills]

I just updated the AdventureWorks.Ruby project to 3.1 and it works like a champ, even with dynamic finders. It's all views and crazy schemas. Have you found out more about your problem and/or a way to define it so I can reproduce it?

[metaskills]

I just committed a test that shows that dynamic finders to work with views https://github.com/rails-sqlserver/activerecord-sqlserver-adapter/commit/3a57c11f27da3b97cb1dcd5cf819b5171acea31a

I am willing to bet this has something to do with your views schema/owner and the latest adapters changes that try to keep different schema reflection from bleeding in. So something like this or ensuring the user had the default schema to match.

class PersonRecord < ActiveRecord::Base
  set_table_name  "schema.person_record"
  set_primary_key "personid"
end

[napcs]

That was the perfect nudge. We spent about a week digging through this adapter's code, testing it on a few servers. Couldn't get the tests to run for quite some time. Turns out that the only way it worked is if the user had the dbo schema. If that's a hard requirement, I think that should be documented, and I'd be happy to send a patch in for that.

[metaskills]

Sweet! I'm not sure if setting "dbo." as a prefix for everyone would make sense, so I just added some documentation to the read me. Glad things are working for you now!

[wannado]

I wanna start of by thanking you all for all the hard work you all are putting into this. I created this account just to report this, and please excuse me if I break any protocol.

I am having the same issue. hoping you can lead me to the right direction. I wrote the simplest model and in rails console when I write Order.where( "id=1") ... it executes fine!

But if I write something like Order.find(1) the sql that gets generated is something like EXEC sp_executesql N'SELECT TOP (1) [dbo].[order].* FROM [dbo].[order] WHERE [dbo].[order].[id] = @0', N'', @0 = 1 the second parameter does not say N'@0 int' instead it is a blank string.

in the file
gems\activerecord-sqlserver-adapter-3.1.0.0\lib\active_record\connection_adapters\sqlserver\database_statements.rb inside def do_exec_query(sql, name, binds) around line 250 I changed to line to something like raise "Unknown bind columns. We can account for this. #{column.inspect}" and saw it prints nil Here is my stack trace...

Loading development environment (Rails 3.1.0) irb(main):001:0> Order.find(1) ←[1m←[36mEXECUTE (1.0ms)←[0m ←[1mUSE [lep_live_backup_test]←[0m nil Could not log "sql.active_record" event. NoMethodError: undefined method name' for nil:NilClass TinyTds::Error: Must declare the scalar variable "@0".: EXEC sp_executesql N'SELECT TOP (1) [dbo].[order].* FROM [dbo].[order] WHERE [dbo].[order].[id] = @0', N'', @0 = 1 ActiveRecord::StatementInvalid: TinyTds::Error: Must declare the scalar variable "@0".: EXEC sp_executesql N'SELECT TOP (1) [dbo].[order].* FROM [dbo].[order] WHERE [dbo].[order].[id] = @0', N'', @0 = 1 from C:/Ruby192/lib/ruby/gems/1.9.1/gems/activerecord-sqlserver-adapter-3.1.0.0/lib/active_record/connection_adapters/sqlserver/database_statements.rb:318:ineach' from C:/Ruby192/lib/ruby/gems/1.9.1/gems/activerecord-sqlserver-adapter-3.1.0.0/lib/active_record/connection_adapters/sqlserver/database_statements.rb:318:in `handle_to_names_and_values_dblib'

If a has only has autoint, varchar, date I do not run into " RuntimeError: Unknown bind columns. We can account for this. "

the tables thats giving me the error have following type of columns bit char(1) datetime decimal(10, 5) decimal(12, 5) decimal(12, 5) decimal(12, 5) decimal(15, 5) int money text varchar(n)

[metaskills]

@wannado Is this table a view? If not, your issue should be reported in a new ticket so all that participated in this ticket do not have to get spammed with something that may be off topic to their original issue. If that is the case, please do that and we can chat there. If and when doing so, please take any replies below I have that might seem useful.

1) Again, is this table a view? 2) I do not see any column names in the data types above. It is hard for me to get a grip on what your name/type/pk settings are with that information. Please provide more detail and an abstract of what your model looks like. 3) I am willing to bet you are not doing some simple wiring correctly. For instances, does this table have an auto incrementing primary key? Which column? Did you setup the PK with something like set_primary_key in your model?

[alextakitani]

I had the same error, the problem was with the

set_primary_key :field

My field has some crazy casing like FielDName.

I've changed to set_primary_key "FielDName" and now it works.

[leviwilson]

+1 for what @alextakitani said. I originally had the following:

  class ClientCase < ActiveRecord::Base
    def self.table_name
      :ClientCase
    end

    def self.primary_key
      :pkClientCase
    end
  end

With this configuration, ClientCase.find 1 would error in the manner described (Unknown bind columns. We can account for this.). Updating my table to the following fixed my issue:

  class ClientCase < ActiveRecord::Base
    def self.table_name
      :ClientCase
    end

    def self.primary_key
      "pkClientCase"
    end
  end

[metaskills]

@leviwilson ActiveRecord's API is to use self.table_name = ..., not to define methods. If you had used the API, it would have cast your symbol to a string for you. See here.

https://github.com/rails/rails/blob/3-2-stable/activerecord/lib/active_record/model_schema.rb#L112

[leviwilson]

@metaskills nice, I was not aware. First time using ActiveRecord :-) Thanks for the heads up.

[metaskills]

Have fun :) glad you are able to use the adapter too. Cheers!