rails-sqlserver/activerecord-sqlserver-adapter

TinyTds::Error: Cannot insert an explicit value into a timestamp column. Use INSERT with a column list to exclude the timestamp column, or insert a DEFAULT into the timestamp column. (ActiveRecord::StatementInvalid)

rpcolom opened this issue · 4 comments

rpcolom commented

Issue

From Rails when a make a INSERT to a table I get the error:
TinyTds::Error: Cannot insert an explicit value into a timestamp column. Use INSERT with a column list to exclude the timestamp column, or insert a DEFAULT into the timestamp column. (ActiveRecord::StatementInvalid)

Expected behavior

In a gem previous version (4.2.18) the INSERT skip timestamp fields types
EXEC sp_executesql N'INSERT INTO [ModificacionesDesdeCRM] ([EntityName], [Guid], [codigo_erp], [Processed], [datos]) VALUES (@0, @1, @2, @3, @4); SELECT CAST(SCOPE_IDENTITY() AS bigint) AS Ident', @0 nvarchar(255), @1 nvarchar(255), @2 nvarchar(255), @3 int, @4 nvarchar(max)', @1 = N'Account', @2 = N'42c798a1-zzzz-ef11-9f89-000d3ab55143', @3 = N'008304', @4 = 0, @5 = N'sample test'

Actual behavior

The gem try to INSERT a NULL value in a timestamp column.
See sample:

EXEC sp_executesql N'INSERT INTO [dbo].[ModificacionesDesdeCRM] ([timestamp], [EntityName], [Guid], [codigo_erp], [Processed], [datos]) VALUES (@0, @1, @2, @3, @4, @5); SELECT CAST(SCOPE_IDENTITY() AS bigint) AS Ident', N'@0 timestamp, @1 nvarchar(255), @2 nvarchar(255), @3 nvarchar(255), @4 int, @5 nvarchar(max)', @0 = NULL, @1 = N'Account', @2 = N'42c798a1-zzzz-ef11-9f89-000d3ab55143', @3 = N'008304', @4 = 0, @5 = N'sample test'

How to reproduce

require "bundler/inline"

gemfile(true) do
source "https://rubygems.org"
gem "tiny_tds"
gem "activerecord", "7.1.3.4"
gem "activerecord-sqlserver-adapter", "7.1.4"
end

require "active_record"
require "minitest/autorun"
require "logger"

ActiveRecord::Base.establish_connection(
adapter: "sqlserver",
timeout: 5000,
pool: 100,
encoding: "utf8",
database: "test_database",
username: "SA",
password: "StrongPassword!",
host: "locahost",
port: 1433
)
ActiveRecord::Base.logger = Logger.new(STDOUT)

ActiveRecord::Schema.define do
drop_table :bug_tests rescue nil

create_table :bug_tests, force: true do |t|
t.timestamp :timestamp_id, null: false
t.bigint :external_id, null: false
end
end

class BugTest < ActiveRecord::Base
end

class TestBugTest < Minitest::Test
def setup
@bug_test = BugTest.create!(external_id: 10)
end

def test_count
assert_equal 1, BugTest.count
end
end

Details

  • Rails version: 7.1.3.4

  • SQL Server adapter version: 7.1.4

  • TinyTDS version: 2.1.7

  • FreeTDS details: 1.4.17

    run `tsql -C` and paste here the output.

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

Thanks!!
rpcolom commented

Hi Aidanharan!!

With test I've tried if timestamp is nullable, the INSERT is correct and there isn´t bug. The bug is only if timestamp is NOT NULL. But database create by Microsoft ERP like NAVISION/BC use columns timestamp NOT NULL. If bug is not resolved the solution for me is create view with all columns less timestamp. I think that timestamp columns must be excluded in INSERT stmt.

Thanks!!
Rafa

aidanharan commented

@rpcolom The script you added above creates a column of type datetime2(6) not of type timestamp.

When the line:

create_table :bug_tests, force: true do |t|
  t.timestamp :timestamp_id, null: false
  t.bigint :external_id, null: false
end

runs it generates the SQL:

CREATE TABLE [bug_tests] ([id] bigint NOT NULL IDENTITY(1,1) PRIMARY KEY, [timestamp_id] datetime2(6) NOT NULL, [external_id] bigint NOT NULL)

As you can see the timestamp_id column is of type datetime2(6). In your bug report you said your column was of type timestamp. Could you clarify which it is?

If you replace the create_table call in your script with the following (datetime2(6) is replaced with timestamp) then the test passes.

execute <<-SQL
   CREATE TABLE [bug_tests] ([id] bigint NOT NULL IDENTITY(1,1) PRIMARY KEY, [timestamp_id] timestamp NOT NULL, [external_id] bigint NOT NULL)
SQL
aidanharan commented

@rpcolom To create a SQL Server timestamp column use ss_timestamp in your migration:

create_table :bug_tests, force: true do |t|
  t.ss_timestamp :timestamp_id, null: false
  t.bigint :external_id, null: false
end

Your test case passes with this change.