mORMot Open Source

augusto

Member

Registered: 2017-01-16

Posts: 4

MSSQL table with an IDENTITY field issue

Hi All,

Please help.

I'm having trouble to get mORmot(2.4) ORM insert working with an existing MSSQL table with an IDENTITY field.

when I run

NewID := Server.Add(Rec, true);

It runs

"select max(ID) from dbo.RemoteProcessTest"

...and insists on sending the ID column in the insert statement - "insert into dbo.RemoteProcessTest (ID,ProcessId,Title) values (?,?,?))"
- then get an error :

Cannot insert explicit value for identity column in table 'RemoteProcessTest' when IDENTITY_INSERT is set to OFF

I'm not sure if this is a bug or if I'm missing something in my configuration.

Table:

CREATE TABLE dbo.RemoteProcessTest
(
    ID int IDENTITY(10000,1) NOT NULL,
    ProcessId varchar(40) NOT NULL,
    Title varchar(40) NOT NULL,

    CONSTRAINT PK_RemoteProcessTest
    PRIMARY KEY(ID)
); 

Test code :

program InsertTest;

{$APPTYPE CONSOLE}

{$I mormot.defines.inc}

uses
  SysUtils,
  mormot.core.base,
  mormot.core.os,
  mormot.core.text,
  mormot.orm.core,
  mormot.orm.sql,
  mormot.core.log,
  mormot.rest.core,
  mormot.rest.sqlite3,
  mormot.db.raw.sqlite3.static,
  mormot.db.core,
  mormot.db.sql,
  mormot.db.sql.oledb;



type
  TRemoteProcessTest = class(TOrm)
  private
    fProcessId: RawUtf8;
    fTitle: RawUtf8;
  published
    property ProcessId: RawUtf8 read fProcessId write fProcessId;
    property Title: RawUtf8 read fTitle write fTitle;
  end;

procedure InsertRecord;
var
  Model: TOrmModel;
  DBProps: TSqlDBConnectionProperties;
  Server: TRestServerDB;
  Rec: TRemoteProcessTest;
  NewID: TID;
  ErrMsg: RawUtf8;
begin

  Model := TOrmModel.Create([TRemoteProcessTest]);
  try

Catalog=CrownDevData_Test;Data Source=LONPDDBS02');
    DBProps :=TSqlDBOleDBMSSQL2018ConnectionProperties.Create(

        'MyMSSQLServer',
        'MyDB',
        '',
        '');
   try
      DBProps.ThreadSafeConnection.Connect;

      WriteLn('Database connection successful!');
    except
      on E: Exception do
      begin
        WriteLn(Format('Connection failed [%s]: %s', [E.ClassName, E.Message]));
        Exit;
      end;
    end;

    try
      VirtualTableExternalRegister(Model, TRemoteProcessTest, DBProps, 'RemoteProcessTest');
      Server := TRestServerDB.Create(Model, false);
      try
        Server.CreateMissingTables;
        Rec := TRemoteProcessTest.Create;
        try
          Rec.ProcessId := 'PROC-2026-99A';
          Rec.Title     := 'Background Sync Worker';
          NewID := Server.Add(Rec, true);
          if NewID > 0 then
            WriteLn(Format('Record inserted successfully! Row ID: %d', [NewID]))
          else
            WriteLn('Failed to insert record.');
        finally
          Rec.Free;
        end;

      finally
        Server.Free;
      end;
    finally
      DBProps.Free;
    end;
  finally
    Model.Free;
  end;
end;

begin
  try
   TSynLog.Family.Level := LOG_VERBOSE;
    TSynLog.Family.EchoToConsole := LOG_VERBOSE;
    InsertRecord;
  except
    on E: Exception do
      WriteLn(Format('Error [%s]: %s', [E.ClassName, E.Message]));
  end;

  WriteLn('Press Enter to exit...');
  ReadLn;
end.