mORMot Open Source

class procedure TDocDao.SaveDocument(documentNo: string; documentDate: TDateTime);
var
  query: rawUtf8;
  dbStatement: ISQLDBStatement;
  lastInsertedId: integer;
  i: integer;
begin
  try
    Props.MainConnection.StartTransaction;
    query := 'insert into tst_document(documentNo, documentDate) values (?,?)';
    dbStatement := Props.NewThreadSafeStatementPrepared(query, false);
    dbStatement.BindVariant(1, 'DOC/1234', false);
    dbStatement.BindDateTime(2, Now);
    dbStatement.ExecutePrepared;
    with Props.Execute('SELECT @@IDENTITY as ''Identity''', []) do
      while Step do
      begin
        lastInsertedId := ColumnVariant(0);
      end;
    for i := 1 to 3 do
    begin
      SaveDocumentItem(lastInsertedId, 'Assort ' + IntToStr(i), i);
    end;

    Props.MainConnection.Commit; //here I get error because transaction was commited in SaveDocumentItem procedure
  except
    on e: Exception do
      Props.MainConnection.Rollback;
  end;
end;


class procedure TDocDao.SaveDocumentItem(documentId: integer; name: string; quantity: currency);
var
  isOuterTrans: boolean;
  dbStatement : ISQLDBStatement;
  query: rawUtf8;
begin
try
  query :=  'insert into tst_documentItem(documentId, name, quantity) values (?,?,?)';
  dbStatement := Props.NewThreadSafeStatementPrepared(query, false);
  isOuterTrans := Props.MainConnection.InTransaction;
  //I get here FALSE despite the fact that transaction is already opened (it was opened in procedure SaveDocument)
  if not isOuterTrans then
    Props.MainConnection.StartTransaction;
  dbStatement.Bind(1, documentId);
  dbStatement.BindVariant(2, name, false);
  dbStatement.BindVariant(3, quantity, false);
  dbStatement.ExecutePrepared;
  if not isOuterTrans then
    Props.MainConnection.Commit;
except
  on e:Exception do
  begin
    if not isOuterTrans then
      Props.MainConnection.Rollback;
    raise Exception.Create('Save DocumentItem Error ');
  end;
end;
end;

program mormotTransactionsTest;

{$APPTYPE CONSOLE}

{$R *.res}
//SQL Script
 {
      CREATE TABLE TST_Document
      (
        documentId bigint not null identity(1,1) primary key,
        documentDate datetime,
        documentNo varchar(15)
      )

      GO
      CREATE TABLE TST_DocumentItem
      (
        documentItemId bigint not null identity(1,1) primary key,
        documentId bigint,
        name nvarchar(200),
        quantity numeric(12,3)
      )
      GO

      CREATE PROCEDURE TST_SP_DOCUMENTS
       AS
       BEGIN
		    SET NOCOUNT ON;
		    declare
		      @CURRENDOCTS table(
			      id_document	int
		      )
		    insert into @CURRENDOCTS select documentId from tst_document;
		    select * from @CURRENDOCTS;
       END
}


uses
  System.SysUtils,
  System.Variants,
  System.Classes,
  SynCommons,
  SynDB,
  SynOleDB,
  mORMot;

Type
  TdocDao = class
    class procedure SaveDocument(documentNo: string; documentDate: TDateTime) ;
    class procedure SaveDocumentItem(documentId: integer; name: string; quantity: currency);
  end;



var
  Props: TOleDBConnectionProperties;
  Model: TSQLModel;


class procedure TDocDao.SaveDocument(documentNo: string; documentDate: TDateTime);
var
  query: rawUtf8;
  dbStatement: ISQLDBStatement;
  lastInsertedId: integer;
  i: integer;
begin
  try
    Props.MainConnection.StartTransaction;
    query := 'insert into tst_document(documentNo, documentDate) values (?,?)';
    dbStatement := Props.NewThreadSafeStatementPrepared(query, false);
    dbStatement.BindVariant(1, 'DOC/1234', false);
    dbStatement.BindDateTime(2, Now);
    dbStatement.ExecutePrepared;
    with Props.Execute('SELECT @@IDENTITY as ''Identity''', []) do
      while Step do
      begin
        lastInsertedId := ColumnVariant(0);
      end;
    for i := 1 to 3 do
    begin
      SaveDocumentItem(lastInsertedId, 'Assort ' + IntToStr(i), i);
    end;

    Props.MainConnection.Commit;//here I get error because transaction was commited in SaveDocumentItem procedure
  except
    on e: Exception do
      Props.MainConnection.Rollback;
  end;
end;


class procedure TDocDao.SaveDocumentItem(documentId: integer; name: string; quantity: currency);
var
  isOuterTrans: boolean;
  dbStatement : ISQLDBStatement;
  query: rawUtf8;
begin
try
  query :=  'insert into tst_documentItem(documentId, name, quantity) values (?,?,?)';
  dbStatement := Props.NewThreadSafeStatementPrepared(query, false);
  isOuterTrans := Props.MainConnection.InTransaction;
  //I get here FALSE despite the fact that transaction is already opened (it was opened in procedure SaveDocument)
  if not isOuterTrans then
    Props.MainConnection.StartTransaction;
  dbStatement.Bind(1, documentId);
  dbStatement.BindVariant(2, name, false);
  dbStatement.BindVariant(3, quantity, false);
  dbStatement.ExecutePrepared;
  if not isOuterTrans then
    Props.MainConnection.Commit;
except
  on e:Exception do
  begin
    if not isOuterTrans then
      Props.MainConnection.Rollback;
    raise Exception.Create('Save DocumentItem Error ');
  end;
end;
end;






procedure InitializeConnection;
begin
  Props := TOleDBMSSQLConnectionProperties.Create('localhost', 'MyDb', 'sa', 'sa');
end;




procedure TestRetrieveFromSP;
var
  stmt: ISQLDBStatement;
begin
  Writeln('Start TestRetrieveFromSP');
  stmt := Props.NewThreadSafeStatementPrepared('exec TST_SP_DOCUMENTS', true);
  stmt.ExecutePrepared;
  //WITHOUT SET NOCOUNT ON step returns false and no row is fetched
  while stmt.Step do
    Writeln('fetch row...');
  Writeln('End TestRetrieveFromSP');
end;




procedure TestTransaction;
begin
  TdocDao.SaveDocument('DOC/1234', Now);
end;



begin
    with TSQLLog.Family do begin
    Level := LOG_VERBOSE;
    HighResolutionTimeStamp := true;
    AutoFlushTimeOut := 0;
    ArchiveAfterDays := 1; // archive after one day
  end;

  InitializeConnection;
  TestTransaction;
  TestRetrieveFromSP;
  Readln;

end.