mORMot Open Source

zed

Member

From: Belarus

Registered: 2015-02-26

Posts: 105

MS SQL ODBC error when set EngineAddUseSelectMaxID to True

Hi, Ab. I try to use MS SQL Server 2008 R2 with mORMot. Simple test:

unit u_ODBC_AutoIDTest;

interface

procedure DoTest;

implementation

uses
  SysUtils,
  Classes,
  mORMot,
  mORMotSQLite3,
  mORMotDB,
  SynDB,
  SynDBODBC,
  SynSQLite3Static,
  SynCommons;

type
  TSQLTestRec = class(TSQLRecord)
  public
    FTestField: Int64;
  published
    property TestField: Int64 read FTestField write FTestField;
  end;

procedure DoTest;
const
  cDNS = 'TEST_MSSQL';
var
  I: Integer;
  VId: TID;
  FModel: TSQLModel;
  FClientDB: TSQLRestClientDB;
  FProps: TSQLDBConnectionProperties;
  VStorage: TSQLRestStorageExternal;
  VRec: TSQLTestRec;
begin
  FModel := TSQLModel.Create([TSQLTestRec]);
  try
    FProps := TODBCConnectionProperties.Create(cDNS, '', '', '');
    try

      for I := 0 to High(FModel.Tables) do begin
        if not VirtualTableExternalRegister(FModel, FModel.Tables[i], FProps, '') then begin
          raise Exception.Create('VirtualTableExternalRegister failed');
        end;
      end;

      FClientDB := TSQLRestClientDB.Create(FModel, nil, ':memory:', TSQLRestServerDB);
      try
        FClientDB.Server.AcquireExecutionMode[execORMWrite] := amBackgroundThread;

        FClientDB.Server.CreateMissingTables;

        for I := 0 to High(FModel.Tables) do begin
          VStorage := TSQLRestStorageExternal.Instance(FModel.Tables[i], FClientDB.Server);
          if Assigned(VStorage) then begin
            VStorage.EngineAddUseSelectMaxID := True;
          end;
        end;

        VRec := TSQLTestRec.Create;
        try
          VRec.FTestField := GetTickCount64;

          for I := 0 to 1 do begin
            FClientDB.TransactionBegin(TSQLTestRec, 1000);
            try
              VId := FClientDB.Add(VRec, True);
              Assert(VId > 0);
              FClientDB.Commit(1000, True);
            except
              FClientDB.RollBack(1000);
              raise;
            end;
          end;
        finally
          VRec.Free;
        end;

      finally
        FClientDB.Free;
      end;
    finally
      FProps.Free;
    end;
  finally
    FModel.Free;
  end;
end;

end.

First insertion is OK, but second is FAIL. If I set VStorage.EngineAddUseSelectMaxID := False; then both insertions is OK.

ODBC driver report error on second insertion: "TODBCStatement - TODBCLib error: [24000] [Microsoft][SQL Server Native Client 10.0]Invalid cursor state (0)\r\n". Full log is here: http://pastebin.com/RaBf1NkU

Can you tell me what is wrong?

P.S. The same test code work fine with MySQL.

Last edited by zed (2015-10-16 13:28:17)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,655

Website

Re: MS SQL ODBC error when set EngineAddUseSelectMaxID to True

I don't know by now.
Are you using latest source from nightly zip?

I won't touch a computer till Tuesday so I am not able to try to reproduce it...

Someone else?

zed

Member

From: Belarus

Registered: 2015-02-26

Posts: 105

Re: MS SQL ODBC error when set EngineAddUseSelectMaxID to True

Are you using latest source from nightly zip?

Yes, from git repository: 1.18.2003

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,655

Website

Re: MS SQL ODBC error when set EngineAddUseSelectMaxID to True

Try with UseCache = false at SynDB properties level.

zed

Member

From: Belarus

Registered: 2015-02-26

Posts: 105

Re: MS SQL ODBC error when set EngineAddUseSelectMaxID to True

Yes, this helped.

FProps.UseCache := False;

zed

Member

From: Belarus

Registered: 2015-02-26

Posts: 105

Re: MS SQL ODBC error when set EngineAddUseSelectMaxID to True

[offtop]
Can you, please, add option EngineAddUseSelectMaxID to MongoDB Engine too?
[/offtop]

zed

Member

From: Belarus

Registered: 2015-02-26

Posts: 105

Re: MS SQL ODBC error when set EngineAddUseSelectMaxID to True

[offtop]
Can you, please, add option EngineAddUseSelectMaxID to MongoDB Engine too?
[/offtop]

My pull request: https://github.com/synopse/mORMot/pull/8/files

Last edited by zed (2015-11-02 13:43:07)