CODEWITHSUNDEEP
sql-serverrubyrails-activerecord
Wayne Conrad
Wayne Conrad

Reputation: 107989

Character columns are silently truncated

When using ActiveRecord to store a string into a SqlServer nvchar column, and the length of the string is larger than the maximum length of the column, the string is silently truncated to the column's size. How can I make a warning or error be issued instead of silently truncating the value?

Code that reproduces the issue

Database model:

class Email < ActiveRecord::Base
  self.table_name = "Email"
end

The insert that silently truncates:

 Email.create!(Address: "X" * 76)
 email.reload
 p email.Address.size    # => 75

The log of the insert statement:

D, [2017-06-30T16:04:35.320283 #9061] DEBUG -- :   dest SQL (0.5ms)  EXEC sp_executesql N'INSERT INTO [Email] ([Address]) OUTPUT INSERTED.[Id] VALUES (@0)', N'@0 nvarchar(75)', @0 = N'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'  [["Address", "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX"]]

Schema

CREATE TABLE [dbo].[Email](
        [Id] [int] IDENTITY(1,1) NOT NULL,
        [Address] [nvarchar](75) NOT NULL,
 CONSTRAINT [PK_dbo.Email] PRIMARY KEY CLUSTERED
(
        [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Versions

Additional info

Upvotes: 2

Views: 494

Answers (2)

Wayne Conrad
Wayne Conrad

Reputation: 107989

In order to cause SqlServer to respond to a text overflow with an error, two things are required:

  • SqlServer's ANSI_WARNINGS connection setting must be ON
  • You must prevent the adapter from truncating the column.

Setting ANSI_WARNINGS

Among the effects of the ANSI_WARNINGS setting is to abort an INSERT or UPDATE if a character column is overflowed. There is a setting that controls that effect:

If an INSERT or UPDATE is tried on a character, Unicode, or binary column in which the length of a new value exceeds the maximum size of the column. If SET ANSI_WARNINGS is ON, the INSERT or UPDATE is canceled as specified by the ISO standard.

This setting is probably already on, but you can force it on with this monkey-patch:

class ActiveRecord::ConnectionAdapters::SQLServerAdapter
  orig_configure_connection = instance_method(:configure_connection)
  define_method(:configure_connection) do
    orig_configure_connection.bind(self).call
    @connection.execute('SET ANSI_WARNINGS ON').do
  end
end

Preventing the adapter from truncating the column

You must also prevent the adapter from truncating this column. This monkey-patch will do it:

class ActiveRecord::ConnectionAdapters::SQLServerColumn

  orig_sql_type_for_statement = instance_method(:sql_type_for_statement)
  define_method(:sql_type_for_statement) do
    if sql_type =~ /\A(nvarchar|nchar|varchar|char|text|ntext|image|varbinary|xml)\(\d+\)\Z/
      "#{$1}(max)"
    else
      orig_sql_type_for_statement.bind(self).call
    end
  end

end

Now a text overflow raises an exception

With these monkey-patches in place, overflowing a character column:

email = Dest::Email.create!(Address: "X" * 76)

will cause an exception:

 ActiveRecord::StatementInvalid:
   TinyTds::Error: String or binary data would be truncated.: EXEC sp_executesql N'INSERT INTO [Email] ([Address]) OUTPUT INSERTED.[Id] VALUES (@0)', N'@0 nvarchar(max)', @0 = N'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'

Upvotes: 4

FedericoG
FedericoG

Reputation: 197

You may use ActiveRecord validations as following:

class Email < ActiveRecord::Base
   self.table_name = "Email"
   validates :Address, length: { maximum: 75 }
end

In this case, Email.create!(Address: "X" * 76) will raise an exception.

You can access the errors by doing the following:

email = Email.create(Address: "X" * 76)
email.valid? # => false
email.errors[:Address] # => ["is too long (maximum is 75 characters)"]

You have more information available here.

Upvotes: 1

Related Questions