Array DML using IDBStatement

BIZ TMS XData
1

Is it possible to bulk upsert data with an array dml using IDBStatement? Or is it necessary to retrieve the FDConnection and create a FDQuery? (Multiple requests to xdata server to run a regular query)

I've tried several ways to add more than one value to a parameter, but only the first or last is executed. Even though the array size is 2. E.g. I always get one row in the database.

self.XDataBasis.cQuery := 'INSERT INTO foo (a, b) VALUES (:a, :b)';

I've tried several options: (Params is a TObjectList;)

1:

  self.XDataBasis.Params.Add(TDBParam.Create('a', ftstring, 'test1'));
  self.XDataBasis.Params.Add(TDBParam.Create('a', ftstring, 'test2'));

  self.XDataBasis.Params.Add(TDBParam.Create('b', ftinteger, 1));
  self.XDataBasis.Params.Add(TDBParam.Create('b', ftinteger, 1));

2:

  self.XDataBasis.Params.Add(TDBParam.Create('a', ftstring, ['test1', 'test2']));
  self.XDataBasis.Params.Add(TDBParam.Create('b', ftinteger,[ 1, 1]));

3:

self.XDataBasis.Params.AddRange(TDBParam.Create('a', ftstring, ['test1', 'test2']));
self.XDataBasis.Params.AddRange(TDBParam.Create('b', ftinteger,[ 1, 1]));

function TXDataBasis.RunExecute: integer;
begin
  Statement.SetSQLCommand(cQuery);
  if (Params<>nil) and (Params.Count > 0) then Statement.SetParams(Params);
  if not Supports(Statement, IDBDatasetStatement, DBStmt) then
  begin
    Result := -1; //'not supported';
  end
  else
  begin
    Result := Statement.Execute;
  end;
end;
2

As mentioned in Multiple requests to xdata server to run a regular query, it is also possible to get the FDQuery, which supports array DML's. I tried that and that works fine. However, there is also mentioned that the connection might not be thread-safe. If I get the Connection via TXDataOperationContext.Current.Connection, I receive a pointer to the connection already used by the OM for this call. So there should be no threading-issue. Or am I mistaken?

function TXDataBasis.CreateFDQuery: TFDQuery;
var
  FDConnection: TFDConnection;
begin
  //https://support.tmssoftware.com/t/multiple-requests-to-xdata-server-to-run-a-regular-query/13618
  FDConnection := (TXDataOperationContext.Current.Connection as IDBConnectionAdapter).AdaptedConnection as TFDConnection;
  result := TFDQuery.Create(nil);  //LET OP! Wordt niet beheerd door TXDataBasis!!
  result.Connection := FDConnection;
end;
3

It's safe to do what you are doing. If you are getting the connection from XData context, then it's already exclusive for that thread, including the underlying TFDConnection.

But you can also execute batch statements using IDBStatement. Here is how you would do it:

      if Supports(FConnection, IDBBatchConnection, BatchConnection)
        and BatchConnection.CanExecuteBatch then
      begin
        Result := BatchConnection.ExecuteBatch(SQL, Params, BatchSize);
      end;

The Params is a regular TList<TDBParam>, but for each TDBParam you will do something like this:

DBParam.ArraySize := 2;
DBParam.Values[0] := 'first';
DBParam.Values[1] := 'second';
4

Hi Wagner,

Thank you! I'll try that out