Model.all versus Model.all.first or Model.where(...).first

bwbasheer commented 6 years ago

Before submitting an issue please check these first!

On Windows? If so, do you need devkit for your ruby install?
Using Ubuntu? If so, you may have forgotten to install FreeTDS first.
Are you using FreeTDS 0.95.80 or later? Check $ tsql -C to find out.
If not, please update then uninstall the TinyTDS gem and re-install it.
Have you made sure to enable SQL Server authentication?
Doing work with threads and the raw client? Use the ConnectionPool gem?

If none of these help. Please fill out the following:

Environment

Rails 5.1.4 tiny_tds 2.1.1-x86-mingw32)

Operating System Windows 10 Pro

Please describe your operating system and version here. If unsure please try the following from the command line:

For Windows: systeminfo | findstr /C:OS c:\projects\graphql\ttds>systeminfo | findstr /C:OS OS Name: Microsoft Windows 10 Pro OS Version: 10.0.16299 N/A Build 16299 OS Manufacturer: Microsoft Corporation OS Configuration: Member Workstation OS Build Type: Multiprocessor Free BIOS Version: Dell Inc. A15, 11/8/2015
For Linux: lsb_release -a; uname -a
For Mac OSX: sw_vers

TinyTDS Version and Information

Please paste the full output of `ttds-tsql -C` (or `tsql -C` for older versions
of TinyTDS) here. If TinyTDS does not install, please provide the gem version.

c:\projects\graphql\ttds>ttds-tsql -C 'ttds-tsql' is not recognized as an internal or external command, operable program or batch file.

c:\projects\graphql\ttds>tsql -C

Compile-time settings (established with the "configure" script) Version: freetds v1.00.27 freetds.conf directory: C:Sites MS db-lib source compatibility: no Sybase binary compatibility: no Thread safety: yes iconv library: yes TDS version: 7.3 iODBC: no unixodbc: no SSPI "trusted" logins: yes Kerberos: no OpenSSL: yes GnuTLS: no MARS: no

FreeTDS Version

Please provide your system's FreeTDS version. If you are using the pre-compiled windows gem you may omit this section.

Description

Have been away from Ruby/Rails for a while. Now trying to get back up to speed with Rails 5, etc. The problem recorded here is that .all results in syntax error report from TinyTds (no ordr by clause). Other modes seem t be OK.

I thought I had successfully reported this earlier, but don't see an indication of that anywhere. Thanks for any feedback. And thanks for the work on the phenomenal work on activerecord-sqlserver-adapter gem, in general.

Please describe the bug or feature request here. irb(main):005:0> MatterType.where("MatterTypeID=2").first SQL (0.0ms) USE [COA_Test] MatterType Load (2.0ms) EXEC sp_executesql N'SELECT [matter_type]. FROM [matter_type] WHERE (MatterTypeID=2) ORDER BY [matter_type].[MatterTypeID] ASC OFFSET 0 ROWS FETCH NEXT @0 ROWS ONLY', N'@0 int', @0 = 1 [["LIMIT", nil]] => # irb(main):006:0> MatterType.all.first MatterType Load (0.0ms) EXEC sp_executesql N'SELECT [matter_type]. FROM [matter_type] ORDER BY [matter_type].[MatterTypeID] ASC OFFSET 0 ROWS FETCH NEXT @0 ROWS ONLY', N'@0 int', @0 = 1 [["LIMIT", nil]] => # irb(main):007:0> MatterType.all MatterType Load (2.0ms) EXEC sp_executesql N'SELECT [matter_type]. FROM [matter_type] OFFSET 0 ROWS FETCH NEXT @0 ROWS ONLY', N'@0 int', @0 = 11 [["LIMIT", nil]] ActiveRecord::StatementInvalid: TinyTds::Error: Incorrect syntax near '0'.: EXEC sp_executesql N'SELECT [matter_type]. FROM [matter_type] OFFSET 0 ROWS FETCH NEXT @0 ROWS ONLY', N'@0 int', @0 = 11

metaskills commented 6 years ago

What version of SQL Server? Thinking you dont have 2012 or up which is required by the adapter?

bwbasheer commented 6 years ago

It's 2012, but doesn't say R2.

-------- Original message -------- From: Ken Collins notifications@github.com Date: 1/19/18 7:26 PM (GMT-05:00) To: rails-sqlserver/tiny_tds tiny_tds@noreply.github.com Cc: "B.W. Basheer" bwbasheer@retrievalsystems.com, Author author@noreply.github.com Subject: Re: [rails-sqlserver/tiny_tds] Model.all versus Model.all.first or Model.where(...).first (#403)

What version of SQL Server? Thinking you dont have 2012 or up which is required by the adapter?

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHubhttps://github.com/rails-sqlserver/tiny_tds/issues/403#issuecomment-359126449, or mute the threadhttps://github.com/notifications/unsubscribe-auth/ABe26IVbCmf44SLs39L-Q7F6bGFEBknNks5tMTKWgaJpZM4RlJrF.

Disclaimer

The information contained in this communication from the sender is confidential. It is intended solely for use by the recipient and others authorized to receive it. If you are not the recipient, you are hereby notified that any disclosure, copying, distribution or taking action in relation of the contents of this information is strictly prohibited and may be unlawful.

This email has been scanned for viruses and malware, and may have been automatically archived by Mimecast Ltd, an innovator in Software as a Service (SaaS) for business. Providing a safer and more useful place for your human generated data. Specializing in; Security, archiving and compliance. To find out more visit the Mimecast website.

metaskills commented 6 years ago

Pretty sure this has nothing to do with TinyTDS and is more related to the adapter (https://github.com/rails-sqlserver/activerecord-sqlserver-adapter). I've seen issues like this before and it usually involves the DB not supporting FETCH which the adapter needs for Rails v4.2 and upward. I've also seen issue reported in the adapter where 3rd party ActiveRecord gems like "composite-primary-keys" gem breaks things. Have you done some investigation to see if your 2012 DB version supports FETCH? I'm thinking you do support it.

Maybe start here https://github.com/rails-sqlserver/activerecord-sqlserver-adapter/issues?q=is%3Aissue+Incorrect+syntax+is%3Aclosed

metaskills commented 6 years ago

And break down the problem. Does this happen for all tables? Just one? Why? Etc. Help break down the issue to see if we can help identify it.

bwbasheer commented 6 years ago

Based on two of the three results shown below, that would seem to confirm that FETCH is supported. The first two query forms show the ORDER BY clause and return expected results. The third form does not contain the ORDER BY clause, and fails as shown.

irb(main):003:0> MatterType.where("MatterTypeID=2").first.MatterTypeID MatterType Load (1.0ms) EXEC sp_executesql N'SELECT [matter_type]. FROM [matter_type] WHERE (MatterTypeID=2) ORDER BY [matter_type].[MatterTypeID] ASC OFFSET 0 ROWS FETCH NEXT @0 ROWS ONLY', N'@0 int', @0 = 1 [["LIMIT", nil]] => 2 irb(main):004:0> MatterType.where("MatterTypeID=2").first.MatterTypeID MatterType Load (1.0ms) EXEC sp_executesql N'SELECT [matter_type]. FROM [matter_type] WHERE (MatterTypeID=2) ORDER BY [matter_type].[MatterTypeID] ASC OFFSET 0 ROWS FETCH NEXT @0 ROWS ONLY', N'@0 int', @0 = 1 [["LIMIT", nil]] => 2 irb(main):005:0> MatterType.all MatterType Load (3.0ms) EXEC sp_executesql N'SELECT [matter_type]. FROM [matter_type] OFFSET 0 ROWS FETCH NEXT @0 ROWS ONLY', N'@0 int', @0 = 11 [["LIMIT", nil]] ActiveRecord::StatementInvalid: TinyTds::Error: Incorrect syntax near '0'.: EXEC sp_executesql N'SELECT [matter_type]. FROM [matter_type] OFFSET 0 ROWS FETCH NEXT @0 ROWS ONLY', N'@0 int', @0 = 11From: Ken Collins [mailto:notifications@github.com]

BUT, I tried this and it worked:

irb(main):012:0> MatterType.all.to_a MatterType Load (1.0ms) SELECT [matter_type].* FROM [matter_type] => [#, #, #, #, #, #, #, #, #, #, #]

metaskills commented 6 years ago

It looks like the DB reflection via INFORMATION_SCHEMA for the needed column information by Rails is lacking. Rails needs this information to form queries properly. Perhaps there is something about this table blocking that?

The primary key is most important and I bet that is missing? Check MatterType.primary_key output and if needed explicitly set one.

bwbasheer commented 6 years ago

Ken,

Don’t’ know if this might help the discussion, but here’s a sequence that checks on the primary_key issue that you raised (running Rails Console in RubyMine):

C:\RailsInstaller\Ruby2.3.3\bin\ruby.exe -e $stdout.sync=true;$stderr.sync=true;load($0=ARGV.shift) C:/projects/graphql/ttds/bin/rails console development Temporarily enhancing PATH to include DevKit... Loading development environment (Rails 5.1.4) Switch to inspect mode.

MatterType.primary_key "MatterTypeID"

MatterType.all SQL (2.0ms) USE [COA_Test] MatterType Load (2.0ms)ActiveRecord::StatementInvalid: TinyTds::Error: Incorrect syntax near '0'.: EXEC sp_executesql N'SELECT [matter_type]. FROM [matter_type] OFFSET 0EXEC sp_executesql N'SELECT [matter_type]. FROM [matter_type] OFFSET 0 ROWS FETCH NEXT @0 ROWS ONLY', N'@0 int', @0 = 11 ROWS FETCH NEXT @0 ROWS ONLY', N'@0 [["LIMIT", nil]] int', @0 = 11

MatterType.all.first MatterType Load (1.0ms) EXEC sp_executesql N'SELECT [matter_type].* FROM [matter_type] ORDER BY [matter_type].[MatterTypeID] ASC OFFSET 0 ROWS FETCH NEXT @0 ROWS ONLY', N'@0 int', @0 = 1 [["LIMIT", nil]]

MatterType.all.first.MatterTypeID MatterType Load (1.3ms) EXEC sp_executesql N'SELECT [matter_type].* FROM [matter_type] ORDER BY [matter_type].[MatterTypeID] ASC OFFSET 0 ROWS FETCH NEXT @0 ROWS ONLY', N'@0 int', @0 = 1 [["LIMIT", nil]] 2

As you can see, only the “all” method, used alone, exhibits the “bad” behavior (which, I would think, would be the only one that would not even concern itself with a definition for a primary_key?)

And the behavior is the same, whether class MatterType extends ApplicationRecord or ActiveRecord::Base.

Here’s models/matter_type.rb file:

class MatterType < ApplicationRecord self.table_name = "matter_type" self.primary_key = "MatterTypeID" has_many :matters end

and the models/matter.rb file:

class Matter < ApplicationRecord self.table_name = "matter" self.primary_key = "MatterId" belongs_to :matter_type, foreign_key: :MatterTypeID end

metaskills commented 6 years ago

Would you mind using the web interface to avoid dumping duplicate email reply garbage into GitHub? Using the web interface means you can also properly format the Markdown so I can follow along more cleaning. It general, better communication and considerate to others.

To sum up, here is your issue (in clean markdown code block)

MatterType.all

ActiveRecord::StatementInvalid: TinyTds::Error: 
Incorrect syntax near '0'.: 
EXEC sp_executesql N'
SELECT  [matter_type].* 
FROM [matter_type] 
OFFSET 0 ROWS FETCH NEXT @0 ROWS ONLY', N'@0 int', @0 = 11

Did you answer the questions I had last time?

Did you search the adapter link issues I sent?
Did you make sure this is on one table or many?
Did you check if you have conflicting gems like composite primary keys?

bwbasheer commented 6 years ago

Apologies for not using the web interface before:

Did you search the adapter link issues I sent? Yes. Learned some things, but nothing obviously on point

Did you make sure this is on one table or many? Many tables

Did you check if you have conflicting gems like composite primary keys? No conflicting gems that I can see (e.g., no composite primary key gem)

Thanks for your observations. Will try to furnish additional information as time permits.

metaskills commented 6 years ago

Q: Did you make sure this is on one table or many? A: Many tables

Hmmm... does this happen in all environments or just a certain one like production? Have you tested the sa account to ensure that you are not seeing a permissions problem with INFORMATION_SCHEMA reflection?

bwbasheer commented 6 years ago

I have only been seeing this behavior in a development environment. I will try to test with the sa account. Can you pint me at an article that would show how one might do waht you have in mind? Another question: Does that fact that it happens for the all method, but not when a requirement for the order function exists (e.g., Model.all.first works OK), suggest a clue as to the cause of the behavior. Is the SQL statement generated in code that is "more or less all in one place", such that it could be observed conveniently in a debugger?

metaskills commented 6 years ago

Can you show me the output from this. I'd like to see what the underlying data for that primary key column looks like.

MatterType.columns_hash['MatterId']

bwbasheer commented 6 years ago

c:\projects\graphql\ttds>rails console Temporarily enhancing PATH to include DevKit... Loading development environment (Rails 5.1.4) irb(main):001:0> MatterType.columns_hash['MatterId'] SQL (1.0ms) USE [COA_Test] => nil

bwbasheer commented 6 years ago

Why would there be a value for key 'MatterId' in the MatterType.columns_hash? In case it's of interest, however: irb(main):018:0> Matter.columns_hash['MatterId'] => nil

metaskills commented 6 years ago

There needs to be a column object for each column. That is likely the core issue. Please show me the output for all of them... Matter.columns

metaskills commented 6 years ago

I bet you set your primary key to the wrong case? Showing Matter.columns will tell for sure.

bwbasheer commented 6 years ago

@metaskills - want to report that after switching to a sql server 2017 instance, all seems to be working as one would expect. I'm happy to take that as a resolution of the issue. Thanks for following along on this.

rails-sqlserver / tiny_tds