Tests failing with "String or binary data would be truncated" error

[lranj]

I have column_test_sqlserver.rb tests failing,in ODBC mode, with the error ActiveRecord::ValueTooLong: ODBC::Error: 22001 (8152) [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]String or binary data would be truncated: EXEC sp_executesql N'INSERT INTO [sst_datatypes] ([smallint]) OUTPUT INSERTED.[id] VALUES (@0)', N'@0 smallint', @0 = -32768

Our application connects to SQLServer with ODBC driver. Rails upgrade removed ODBC support. To reduce the complexity we are trying get the activerecord-sqlserver-adapter working in ODBC mode. We have forked version of this adaper with the removed ODBC code being put back.

We knew that the ODBC part of the code was not maintained from 4.x version. But I just thought of posting it here so someone can shed some light on this issue as you would have encountered this error when in 4.x adapter.

It seems the same query works fine when the server is connected to sqlserver in dblib mode. This is the query fetched from logs
EXEC sp_executesql N'INSERT INTO [sst_datatypes] ([bigint]) OUTPUT INSERTED.[id] VALUES (@0)', N'@0 bigint', @0 = -9223372036854775808

We get around twenty failures in column_test_sqlserver.rb file. Tried tweaking sql_for_insert method but it is of not much help. Is it something to do with the schema that creates sst_datatypes table?

Hope someone would respond as we have no clue on this issue. Thanks

[gr8bit]

You should talk to the author of issue #682! Maybe you could bring ODBC back in together...!

[smlsml]

I wouldn't say these are exactly the same. One is smallint and the other bigint.

ActiveRecord::ValueTooLong: ODBC::Error: 22001 (8152) [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]String or binary data would be truncated: EXEC sp_executesql N'INSERT INTO [sst_datatypes] ([smallint]) OUTPUT INSERTED.[id] VALUES (@0)', N'@0 smallint', @0 = -32768

vs

EXEC sp_executesql N'INSERT INTO [sst_datatypes] ([bigint]) OUTPUT INSERTED.[id] VALUES (@0)', N'@0 bigint', @0 = -9223372036854775808

The lower bound of smallint should be "-32768" - I wonder if you can find the place that is defined and try with -32767 instead just to see if that works without the error.

https://docs.microsoft.com/en-us/sql/t-sql/data-types/int-bigint-smallint-and-tinyint-transact-sql?view=sql-server-2017

Are you using ruby-odbc gem? Maybe it is possible that is causing a problem too.

[lranj]

You are right that ruby-odbc gem is causing a problem. When I did require 'odbc_utf8' in the spec file those errors did not show up.

Thanks for helping out.

[wpolicarpo]

Hi @lranj, is this still a problem?