Firedac will not allow the TFDQuery.Open command when the associated SQL does not return a resultset

Another possible issue with TInsertExecutor.Execute method in Spring.Persistence.SQL.Commands.Insert unit. The code creates an insert statement, but then calls ExecuteQuery on the Statement.

TFireDACStatementAdapter.ExecuteQuery in the Spring.Persistence.Adapters.FireDAC unit then Creates a TFDQuery and calls Open. Firedac then throws an exception because no ResultSet was returned. The exception then recommends that you use TFDQuery.Execute rather than TFDQuery.Open. when your SQL statment does not return a resultset.

I have for now Modified the TFireDACStatementAdapter.ExecuteQuery method to look for Select on the front of the SQL statement and call Open else call execute. This is not pretty but it solves my immediate problem .

function TFireDACStatementAdapter.ExecuteQuery(
  serverSideCursor: Boolean): IDBResultSet;
var
  query: TFDQuery;
begin
  inherited;
  query := TFDQuery.Create(nil);
  query.Connection := Statement.Connection;
  query.SQL.Text := Statement.SQL.Text;
  query.Params.AssignValues(Statement.Params);
  query.DisableControls;
  if serverSideCursor then
    query.FetchOptions.CursorKind := ckForwardOnly;
  try
    if StartsText('Select', Statement.SQL.Text) then
      query.Open
    else
      Query.Execute;
    Result := TFireDACResultSetAdapter.Create(query);
  except
    on E:Exception do
    begin
      //make sure that resultset is always created to avoid memory leak
      Result := TFireDACResultSetAdapter.Create(query);
      raise EFireDACAdapterException.CreateFmt(EXCEPTION_CANNOT_OPEN_QUERY, [E.Message]);
    end;
  end;
end;

After looking at the code in TInsertExecutor.Execute method it looks like some Data Access Libraries might actually return the inserted result in a resultset so as to get the generated Identity Value, as seen in this code

  resultSet := statement.ExecuteQuery(false);
  value := GetIdentityValue(resultSet);
  if not value.IsEmpty then
    entityWrapper.SetPrimaryKeyValue(value);

So I wasn't sure it would be a good idea to just change TInsertExecutor.Execute to call the Statements.Execute method for reasons of compatability with other Access Libraries.

Comments (5)