Почему FireDAC в Delphi всегда возвращает RowsAffected равным 1, даже если ни одна запись не была вставлена?

Delphi , Базы данных , SQL

При работе с FireDAC в Delphi и Firebird, разработчики часто сталкиваются с неожиданным поведением свойства RowsAffected у TFDQuery после выполнения оператора INSERT ... SELECT. Даже если условие WHERE в SELECT не соответствует ни одной строке, и, следовательно, ни одна запись не была вставлена, RowsAffected все равно возвращает 1. Почему так происходит и как это обойти?

Проблема:

При выполнении SQL-запроса INSERT INTO t(c1) SELECT c2 FROM t2 WHERE c2 = 0 через TFDQuery.ExecSQL, свойство RowsAffected может возвращать 1, даже если ни одна запись не была вставлена, потому что SELECT не вернул ни одной строки, удовлетворяющей условию WHERE.

Объяснение:

Поведение RowsAffected в данном случае связано с тем, как FireDAC обрабатывает операторы INSERT ... SELECT. Возможно, FireDAC оптимизирует обработку запроса, предполагая, что INSERT всегда должен произвести либо вставку (одной или нескольких строк), либо ошибку. В случае INSERT ... SELECT, даже если SELECT не возвращает строк, FireDAC может считать, что операция INSERT была "выполнена", хотя и не привела к вставке каких-либо данных, и поэтому возвращает 1. Это может быть связано с внутренними механизмами FireDAC и его взаимодействием с Firebird API.

Решение 1: Использовать OPEN с RETURNING (только для Firebird 5 и выше, с ограничениями для более старых версий):

Один из предложенных обходных путей - использовать метод Open вместо ExecSQL и добавить предложение RETURNING 1 в запрос. Это заставит Firebird вернуть строку даже в случае, если ни одна запись не была вставлена.

FDQuery1.SQL.Text := 'INSERT INTO T2 (C2) SELECT C1 FROM T1 WHERE C1=0 RETURNING 1';
FDQuery1.Open;
ShowMessage(Format('rows affected %d, rows inserted %d',[FDQuery1.RowsAffected,FDQUery1.RecordCount]));

Однако, стоит учитывать следующие ограничения:

• Firebird 4 и старше: RETURNING работает только для единичных вставок (INSERT ... VALUES ...). Если SELECT вернет несколько строк, возникнет ошибка "multiple rows in singleton select".
• Firebird 5: RETURNING всегда возвращает ровно одну строку. Если ни одна запись не была вставлена, поля в этой строке будут NULL.

Решение 2: Отдельный запрос SELECT COUNT(*):

Наиболее надежным и универсальным решением является выполнение отдельного запроса SELECT COUNT(*) перед INSERT ... SELECT, чтобы определить, будет ли вставка произведена.

function InsertAndGetCount(const SQLInsert: string; const SQLCount: string; Q: TFDQuery): integer;
begin
  Q.SQL.Text := SQLCount;
  Q.Open;
  Result := Q.FieldByName('RecordCount').AsInteger; // Предполагаем, что поле с количеством называется RecordCount
  Q.Close;

  if Result > 0 then
  begin
    Q.SQL.Text := SQLInsert;
    Q.ExecSQL;
  end;
end;

// Пример использования
var
  InsertSQL, CountSQL: string;
begin
  InsertSQL := 'INSERT INTO t(c1) SELECT c2 FROM t2 WHERE c2 = 0';
  CountSQL := 'SELECT COUNT(*) AS RecordCount FROM t2 WHERE c2 = 0';

  InsertAndGetCount(InsertSQL, CountSQL, FDQuery1);

  // RowsAffected будет 0, если вставка не была произведена.
  WriteLn(FDQuery1.RowsAffected);
end;

Преимущества этого решения:

• Совместимость: Работает со всеми версиями Firebird.
• Надежность: Точно определяет, будет ли произведена вставка.
• Прозрачность: Явно показывает логику проверки наличия данных перед вставкой.

Альтернативные решения:

1. Хранимая процедура: Можно создать хранимую процедуру в Firebird, которая выполняет SELECT COUNT(*) и INSERT ... SELECT внутри транзакции. Это позволяет избежать двух отдельных запросов из Delphi. Внутри процедуры можно использовать FOR ... SELECT ... DO с счетчиком.
2. Использование триггеров: Можно использовать триггеры BEFORE INSERT для подсчета количества вставленных записей. Однако это может повлиять на производительность.

Заключение:

Несмотря на то, что поведение RowsAffected в FireDAC может показаться нелогичным, существуют способы обойти эту проблему и точно определить, сколько записей было вставлено при использовании INSERT ... SELECT. Наиболее надежным и универсальным решением является выполнение отдельного запроса SELECT COUNT(*) перед вставкой. Альтернативные решения, такие как использование хранимых процедур или триггеров, могут быть более подходящими в определенных сценариях. Выбор оптимального решения зависит от конкретных требований проекта и версии Firebird.

Создано по материалам из источника по ссылке.

В FireDAC при использовании INSERT ... SELECT в Firebird RowsAffected всегда возвращает 1, даже если ни одна запись не была вставлена, что требует обходных путей, таких как использование OPEN с RETURNING или отдельного запроса SELECT COUNT(*), для точног

Комментарии и вопросы

Получайте свежие новости и обновления по Object Pascal, Delphi и Lazarus прямо в свой смартфон. Подпишитесь на наш Telegram-канал delphi_kansoftware и будьте в курсе последних тенденций в разработке под Linux, Windows, Android и iOS

:: Главная :: SQL ::