These are chat archives for rails-sqlserver/activerecord-sqlserver-adapter

15th
Jul 2016
Michael R. Cook
@mrcook
Jul 15 2016 08:02

Hi Ken, the issue seems related to stored procedures, which is how the SQL is constructed. Here's the final statement that's passed to _raw_select:

EXEC sp_executesql N'SELECT  [users].* FROM [users] WHERE [users].[code] = @0  ORDER BY [users].[id] ASC OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY', N'@0 nvarchar(5)', @0 = N'123456'

What's happening in MSSQL itself is that 123456 is being truncated because of the nvarchar(5) - this seems to be known behaviour. Here's an SO post which gives a nice overview: http://stackoverflow.com/questions/4628140/sql-server-silently-truncates-varchars-in-stored-procedures

The proposed solution there is to use nvarchar(max), even though there would be a small performance hit. With this change INSERTs using the GEM would not truncate (ActiveRecord validations on length would kick in). And the .find_by would fail as expected.

I don't see how to move forward with this. Do you have any thoughts?

Ken Collins
@metaskills
Jul 15 2016 13:21
Hey Michael!
Ken Collins
@metaskills
Jul 15 2016 13:31
WOW, this is really helpful information. I might be able to apply this to why some DB constraints never really kicked in for a few tests. Not sure how to move forward either. I assume this would be the case for anything via a store proc, inserts and updates alike.
Michael R. Cook
@mrcook
Jul 15 2016 13:59
Hi Ken, certainly for inserts it also truncates, so it wouldn't seem unreasonable to happen elsewhere.
For the moment I've put the issue aside - it's Friday after all! Maybe after the weekend I'll have another think about it. Thanks for your help.
Ken Collins
@metaskills
Jul 15 2016 14:08
Same! Appreciate you rounding up that info.
Michael R. Cook
@mrcook
Jul 15 2016 14:10
No problem!