insert into with "returning into" clause

BIZ TMS RemoteDB
1

I'm trying to get value (or values) from a INSERT statement with RETURNING INTO. Example:

insert into parameterstore (name) values (:name) returning id into :ret_id

Is this not supported?

See my example below.

uses
  System.SysUtils,
  RemoteDB.Client.Dataset,
  RemoteDB.Client.Database,
  Data.DB,
  System.Variants;


procedure TestInsertReturning();
begin
  var vDB := TRemoteDBDatabase.Create(nil);
  try
    vDB.UserName := 'remotedb';
    vDB.Password := 'business';
    vDB.ServerUri := 'http://localhost:2001/tms/remotedb';
    vDB.Connected := true;
    var vDataset := TXDataset.Create(nil);
    try
      vDataset.Database := vDB;
      vDataset.SQL.Text := 'insert into parameterstore (name) values (:name) returning id into :ret_id ';
      vDataset.ParamByName('ret_id').ParamType := TParamType.ptOutput;
      vDataset.ParamByName('ret_id').DataType := TFieldType.ftInteger;
      vDataset.ParamByName('name').Value := 'Test';
      vDataset.ExecSQL;

      Writeln(VarToStr(vDataset.ParamByName('ret_id').Value));
    finally
      vDataset.Free;
    end;
  finally
    vDB.Free;
  end;
end;
2

Unfortunately no, RemoteDB doesn't support ptOutput parameters, only ptInput.

3

That kind of brakes our intention to replace our DB access layer with RemoteDB.
Is this something you eventually will implement?

But how do you handle features like server generated id (sequence for Oracle or autoinc columns) when using a regular SELECT statement?

If you append a record, you must have back the primary key (KeyFields) to do further updates. Do you do another round trip with SELECT filtered by all columns from the INSERT statement?

4

I tested this by using FireDac directly and there it works out of the box:

  var vQuery := TFDQuery.Create(nil);
  try
    vQuery.Connection := FDConnection1;
    vQuery.SQL.Text := 'insert into parameterstore (name) values (:name) returning id into :ret_id ';
    vQuery.ParamByName('ret_id').ParamType := TParamType.ptInputOutput;
    vQuery.ParamByName('ret_id').DataType := TFieldType.ftInteger;
    vQuery.ParamByName('name').Value := 'Test';

    vQuery.Execute();

    var vId := VarToStr(vQuery.ParamByName('ret_id').Value);  // This works
  finally
    vQuery.Free;
  end;
5

There is no current plan for that yet.

Each database uses its own mechanism. We have TMS Aurelius fully working over RemoteDB, for example.

Aurelius uses specific mechanism for each database to retrieve id.

Some of them use sequences/generators. In those cases, we execute a previous SQL command to retrieve the next value in sequence, and then perform the insert with the retrieved value.

Some databases provide a separate SQL command to retrieve the last inserted value, like SELECT LAST_INSERT_ID() for MySQL or SELECT last_insert_rowid() for SQLite.

Finally, for some databases Aurelius execute a kind of "RETURNING" clause like you did. That is used for MS SQL Server, Postgres and Firebird, for example. But we don't return them in a parameter. We just use something like "SELECT ... RETURNING `. The dataset is open and the inserted value is retrieved by doing a simple Fields[0].AsInteger call.

1 Like