mORMot Open Source

tomek

Member

Registered: 2017-10-24

Posts: 46

Interface based service not commiting (insert with ExpectResult true)

Hello
I've insert statement with returning clause, it's executing fine but it's not committing to db (it's committed with client close).

  fProps: TSQLDBConnectionPropertiesThreadSafe;
  fProps.ThreadingMode := tmMainConnection;

function TMyService.Test: UTF8String;
var
 aStmt: TSQLDBStatement;
 sSql: string;
begin
  sSql := 'insert into my_table(table_id, some_value)'
        + 'values (gen_id(gen_my_table, 1), ?)'
        + 'returning table_id';
  try
    aStmt := fProps.NewThreadSafeStatement;
    aStmt.Prepare(sSql, True);                 // <-- ExpectResult := True
    aStmt.BindTextS(1, 'abc');
    aStmt.ExecutePrepared;
    if aStmt <> nil then
      Result := aStmt.FetchAllAsJSON(True);
  finally
    aStmt.Free;
  end;
end;

Same statement without returning clause (with ExpectResult := False) commits immediately.

Regards, Tomek

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: Interface based service not commiting (insert with ExpectResult true)

With which database?

tomek

Member

Registered: 2017-10-24

Posts: 46

Re: Interface based service not commiting (insert with ExpectResult true)

Firebird 2.5

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: Interface based service not commiting (insert with ExpectResult true)

It is typical to the Firebird (complex) transaction system, I guess.
There is no auto-commit in Firebird, IIRC.

You need to specify an explicit transaction and a commit, or tune the connection properties to have an automated transaction each time.

tomek

Member

Registered: 2017-10-24

Posts: 46

Re: Interface based service not commiting (insert with ExpectResult true)

I'm not sure if it's Firebird issue. Auto-commit works fine in Firebird with any update/delete/insert, except this only case: insert with returning clause (ExpectResult := True).

Regards, Tomek

EgonHugeist

Member

From: Germany

Registered: 2013-02-15

Posts: 190

Re: Interface based service not commiting (insert with ExpectResult true)

Regression in Zeos. Please update from SVN