mORMot Open Source

George

Member

Registered: 2016-04-05

Posts: 140

ORM + MSSql

Hello!

I've read a lot of documentation and trying to make test app to see how i can use mORMot in future projects.
Test application use MSSQL as DBMS via ODBC Driver 11 for SQL Server.
Simple class "TSQLUsers", and few basic methods (like read, add, delete and update) was implemented.
Everything works when i use one server application.
But if i start two or more instances and try to add records in same time, i get errors while insertion - primary key is not unique.

So, here is my questions:
1. Is there a way to safely use more than one ORM server with single database (in MSSQL)?
2. Documentation says, if some properties or classes was deleted, method "CreateMissingTables" will not update DB structure automatically.
I suppose, that i must implement table with model versions and after calling CreateMissingTables i should execute SQL statements that will update DB properly.
Am i right?

Can someone point me on answers if it is already posted somewhere.

UnitSQLConnection

unit UnitSQLConnection;

interface

uses
  SynLog,              // logging features
  mORMot,              // RESTful server & ORM
  SynSQLite3,
  mORMotSQLite3,       // SQLite3 engine as ORM core
  SynSQLite3Static,    // staticaly linked SQLite3 engine
  mORMotDB,            // ORM using external DB
  SynDB,               // external DB core
  SynDBODBC,           // external DB access via ODBC
  UnitSQLDataModel;    // data model unit

var
  DataBase: TSQLRestServerDB;

implementation

var
  aProps: TSQLDBConnectionProperties;

procedure ConnectToDataBase();
begin
  // set logging abilities
  SQLite3Log.Family.Level := LOG_VERBOSE;
  // SQLite3Log.Family.EchoToConsole := LOG_VERBOSE;
  SQLite3Log.Family.PerThreadLog := ptIdentifiedInOnFile;
  // ODBC driver
  aProps := TODBCConnectionProperties.Create('', 'Driver={ODBC Driver 11 for SQL Server}; Server=127.0.0.1; Database=test_mORMot_ORM; Uid=IISUSR; Pwd=password_here; MARS_Connection=Yes;', '', '');
  // get the shared data model
  Model := CreateTabbleModel();
  // use MSSQL database for all tables
  VirtualTableExternalRegisterAll(Model, aProps);
  // create the main mORMot server
  DataBase := TSQLRestServerDB.Create(Model, ':memory:', false); // authentication=false
  // optionally execute all MSSQL requests in a single thread
  DataBase.AcquireExecutionMode[execORMGet] := amBackgroundORMSharedThread;
  DataBase.AcquireExecutionMode[execORMWrite] := amBackgroundORMSharedThread;
  // create tables or fields if missing
  DataBase.CreateMissingTables();
end;

procedure PrepareForShutdown();
begin
  aProps.Free;
  Model.Free;
  DataBase.Free;
end;

initialization

ConnectToDataBase();

finalization

PrepareForShutdown();

end.

UnitSQLDataModel

unit UnitSQLDataModel;

interface

uses
  System.SysUtils,
  SynCommons,
  mORMot;

type
  TSQLUsers = class(TSQLRecord)
  private
    fName: RawUTF8;
    fSurname: RawUTF8;
    //fLastName: RawUTF8;
  published
    property Name: RawUTF8 read fName write fName;
    property Surname: RawUTF8 read fSurname write fSurname;
    //property LastName: RawUTF8 read fLastName write fLastName;
  end;

var
  Model: TSQLModel;

  function CreateTabbleModel: TSQLModel;

implementation

function CreateTabbleModel: TSQLModel;
begin
  result := TSQLModel.Create([TSQLUsers]);
end;

end.

Parts from UI unit

procedure TForm1.FillUserList();
var
  Users: TSQLUsers;
begin
  ListBoxUsers.Clear;
  EditUserName.Clear;
  EditUserSurname.Clear;
  Users := TSQLUsers.CreateAndFillPrepare(Database, '');
  ListBoxUsers.Items.BeginUpdate;
  while Users.FillOne() do
    ListBoxUsers.Items.Add(UTF8ToString(Users.Name) + '=' + IntToStr(Users.ID));
  ListBoxUsers.Items.EndUpdate;
end;

procedure TForm1.AddUser();
var
  User: TSQLUsers;
begin
  User := TSQLUsers.Create();
  User.Name := StringToUTF8(EditUserName.Text);
  User.Surname := StringToUTF8(EditUserSurname.Text);
  Database.Add(User, True);
end;

procedure TForm1.ButtonUserAddClick(Sender: TObject);
var
  i: integer;
begin
  for i := 1 to 10000 do
    AddUser();
  FillUserList();
end;

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,207

Website

Re: ORM + MSSql

By design, there should be a single Server per DB.
It is a requirement for the ORM, e.g. for ID generation or data caching.

If you want several servers on a single DB, you should either:
- by-pass the ORM and define some SOA services using interfaces, which would connect to the MS SQL DB directly;
- or tune the TSQLRestStorageExternal ID generation e.g. to use OnEngineAddComputeID and an external ID generator/sequence, or trying EngineAddUseSelectMaxID=true (but it may fail if two Add are done at the same time).

George

Member

Registered: 2016-04-05

Posts: 140

Re: ORM + MSSql

Thanks!

Now i have only one question)

2. Documentation says, if some properties or classes was deleted, method "CreateMissingTables" will not update DB structure automatically.
I suppose, that i must implement table with model versions and after calling CreateMissingTables i should execute SQL statements that will update DB properly.
Am i right?

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,207

Website

Re: ORM + MSSql

My guess is that you should better update the DB properly before calling CreateMissingTables, before the ORM starts...
But most probably it won't make any difference.