TinyTds::Error: Invalid column name 'pk1,pk2'.

[jmouka]

Hi! I am getting an exception raised when inserting into a table that has no primary key defined, where I modelled a composite key (pk1, pk2), and the database is SQLServer.

The logged SQL is (I edited it to protect the guilty and make it a bit more readable, so hopefully there are no mistakes):

EXEC sp_executesql N'INSERT INTO [dbo].[Test_table] ([pk1], [pk2], [value]) OUTPUT INSERTED.[pk1,pk2] VALUES (@0, @1, @2)', N'@0 char(5), @1 int, @2 char(255)', @0 = '123', @1 = 100, @2 = 'test'

I'm assuming TinyTDS is complaining about the OUTPUT INSERTED.[pk1,pk2]. I'm not sure where the issue is, so I'm posting it here more as an FYI and if someone might have some ideas. The ActiveRecord class can fetch and update records fine, just can't insert. Any ideas is greatly appreciated, and it goes without saying, thanks for the awesome Gem!

Libraries are: tiny_tds (2.1.5) activerecord-sqlserver-adapter (6.1.2.1) activesupport (6.1.7.1) composite_primary_keys (13.0.7)

Error is: ActiveRecord::StatementInvalid (TinyTds::Error: Invalid column name pk1,pk2'.)

Stack Trace (probably not useful, but in case...):

Traceback (most recent call last):
       16: from .../activerecord-sqlserver-adapter-6.1.2.1/lib/active_record/connection_adapters/sqlserver/database_statements.rb:307:in `sp_executesql'
       15: from .../activerecord-sqlserver-adapter-6.1.2.1/lib/active_record/connection_adapters/sqlserver/database_statements.rb:412:in `raw_select'
       14: from .../activerecord-6.1.7.1/lib/active_record/connection_adapters/abstract_adapter.rb:687:in `log'
       13: from .../activesupport-6.1.7.1/lib/active_support/notifications/instrumenter.rb:24:in `instrument'
       12: from .../activerecord-6.1.7.1/lib/active_record/connection_adapters/abstract_adapter.rb:695:in `block in log'
       11: from .../activesupport-6.1.7.1/lib/active_support/concurrency/load_interlock_aware_monitor.rb:21:in `synchronize'
       10: from .../activesupport-6.1.7.1/lib/active_support/concurrency/load_interlock_aware_monitor.rb:21:in `handle_interrupt'
        9: from .../activesupport-6.1.7.1/lib/active_support/concurrency/load_interlock_aware_monitor.rb:25:in `block in synchronize'
        8: from .../activesupport-6.1.7.1/lib/active_support/concurrency/load_interlock_aware_monitor.rb:25:in `handle_interrupt'
        7: from .../activesupport-6.1.7.1/lib/active_support/concurrency/load_interlock_aware_monitor.rb:26:in `block (2 levels) in synchronize'
        6: from .../activerecord-6.1.7.1/lib/active_record/connection_adapters/abstract_adapter.rb:696:in `block (2 levels) in log'
        5: from .../activerecord-sqlserver-adapter-6.1.2.1/lib/active_record/connection_adapters/sqlserver/database_statements.rb:412:in `block in raw_select'
        4: from .../activerecord-sqlserver-adapter-6.1.2.1/lib/active_record/connection_adapters/sqlserver/database_statements.rb:417:in `_raw_select'
        3: from .../activerecord-sqlserver-adapter-6.1.2.1/lib/active_record/connection_adapters/sqlserver/database_statements.rb:438:in `handle_to_names_and_values'
        2: from .../activerecord-sqlserver-adapter-6.1.2.1/lib/active_record/connection_adapters/sqlserver/database_statements.rb:447:in `handle_to_names_and_values_dblib'
        1: from .../activerecord-sqlserver-adapter-6.1.2.1/lib/active_record/connection_adapters/sqlserver/database_statements.rb:447:in `each'
ActiveRecord::StatementInvalid (TinyTds::Error: Invalid column name 'Chq_Toy_Id_Num,Chq_Dlr_No'.)

Thanks!

[jmouka]

Ok... I got it working ... and it's a bit strange. I'll leave my comment here in case someone else runs into it.

The solution was to edit the Gemfile and put "composite_primary_keys" AFTER "activerecord-sqlserver-adapter". I have a feeling that activerecord-sqlserver-adapter was overwriting the database statements, because the file lib/composite_primary_keys/sqlserver/database_statements.rb looks correct.

The SQL statement logging now looks like this (and is working):

EXEC sp_executesql N'INSERT INTO [dbo].[Test_table] ([pk1], [pk2], [value]) OUTPUT INSERTED.[pk1], INSERTED.[pk2] VALUES (@0, @1, @2)', N'@0 char(5), @1 int, @2 char(255)', @0 = '123', @1 = 100, @2 = 'test'

Like I said, thanks for the great Gem!

[jmouka]

CPK looks good, the issue is probably somewhere else.

[cfis]

I bet it is coming from here - https://github.com/composite-primary-keys/composite_primary_keys/blob/master/lib/composite_primary_keys/connection_adapters/sqlserver/database_statements.rb.

Its not auto required - see https://github.com/composite-primary-keys/composite_primary_keys/blob/master/lib/composite_primary_keys.rb#L63 - because Rails does not ship with sql server support built in.

So I think you have to require that file manually (its been a while since I checked that so I might be misremembering). Maybe there is a smarter way that CPK can handle this better?