Решение проблемы вставки записей с использованием FDQuery и FDUpdateSQL в Delphi

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

Введение

При работе с базами данных в Delphi, особенно при использовании компонентов FireDAC, разработчики часто сталкиваются с проблемами при выполнении операций вставки (INSERT) в сложных запросах с соединениями (JOIN). В данной статье мы рассмотрим конкретную проблему, с которой столкнулся пользователь BushAl на форуме, и предложим несколько решений.

Описание проблемы

Пользователь BushAl описал следующую ситуацию:

1. Имеется FDQuery с LEFT JOIN между таблицей центров затрат (CC) и таблицей деталей оценок (ED)
2. Запрос успешно обновляет существующие записи, но не может вставить новые
3. При попытке вставки возникает ошибка: "update affected [0] rows, while [1] was requested"
4. Попытка использовать FDUpdateSQL и обработчик OnUpdateRecord не решила проблему

Исходный SQL-запрос выглядит так:

select b.*, a.csc_id as refcsc_id, a.cscdsc as cscdsc, a.cstpls as refcstpls, 
a.gstinc as refgstinc, a.gstpct as refgstpct 
from csc a 
left join estdtl b on a.csc_id = b.csc_id and b.est_id = !est_id

Анализ проблемы

Основные причины проблемы:

1. Сложность соединения таблиц: LEFT JOIN создает виртуальную таблицу, с которой сложно работать при операциях вставки
2. Автоинкрементные поля: Поле ESTDTL_ID генерируется базой данных (Firebird), что усложняет процесс вставки
3. Ограничения FireDAC: Компоненты FireDAC не всегда корректно обрабатывают вставки в сложных запросах с соединениями

Решение 1: Использование UPDATE OR INSERT (Firebird 4.0+)

Для Firebird 4.0 и выше можно использовать синтаксис UPDATE OR INSERT:

UPDATE OR INSERT INTO ESTDTL
(EST_ID, ESTDTL_ID, CSC_ID, ESTDTLDSC, CSTPLS, GSTINC, 
 GSTPCT, QTYCLC, QTY, CST, GST, 
 SLE, VAL) OVERRIDING USER VALUE
VALUES (:NEW_EST_ID, coalesce(:NEW_ESTDTL_ID, -1), :NEW_CSC_ID, :NEW_ESTDTLDSC, 
        :NEW_CSTPLS, :NEW_GSTINC, 
        :NEW_GSTPCT, :NEW_QTYCLC, :NEW_QTY, :NEW_CST, :NEW_GST, 
        :NEW_SLE, :NEW_VAL)
MATCHING (EST_ID, ESTDTL_ID)  
RETURNING EST_ID, ESTDTL_ID

Ключевые моменты: - OVERRIDING USER VALUE позволяет игнорировать значение автоинкрементного поля при вставке - coalesce(:NEW_ESTDTL_ID, -1) обеспечивает значение по умолчанию - MATCHING (EST_ID, ESTDTL_ID) определяет условия для поиска существующей записи

Решение 2: Использование генератора и триггера (Firebird 3.0)

Для Firebird 3.0 можно использовать генератор и триггер:

1. Создаем генератор:

CREATE GENERATOR GenEstDtl_Id;

1. Создаем триггер:

CREATE TRIGGER EstDtl_BI FOR ESTDTL
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (NEW.ESTDTL_ID IS NULL) THEN
    NEW.ESTDTL_ID = GEN_ID(GenEstDtl_Id, 1);
END

1. В коде Delphi перед вставкой получаем новое значение ID:

procedure TForm1.GetNewEstDtlId;
var
  Qry: TFDQuery;
begin
  Qry := TFDQuery.Create(nil);
  try
    Qry.Connection := FDConnection1;
    Qry.SQL.Text := 'SELECT GEN_ID(GenEstDtl_Id, 1) FROM RDB$DATABASE';
    Qry.Open;
    FDQuery1.FieldByName('ESTDTL_ID').AsLargeInt := Qry.Fields[0].AsLargeInt;
  finally
    Qry.Free;
  end;
end;

Решение 3: Использование отдельного запроса для вставки

Альтернативный подход - выполнять вставку отдельным запросом:

procedure TForm1.InsertEstDtlRecord;
var
  Qry: TFDQuery;
begin
  Qry := TFDQuery.Create(nil);
  try
    Qry.Connection := FDConnection1;
    Qry.SQL.Text := 
      'INSERT INTO ESTDTL ' +
      '(EST_ID, CSC_ID, ESTDTLDSC, CSTPLS, GSTINC, ' +
      ' GSTPCT, QTYCLC, QTY, CST, GST, ' +
      ' SLE, VAL) ' +
      'VALUES (:EST_ID, :CSC_ID, :ESTDTLDSC, :CSTPLS, :GSTINC, ' +
      ' :GSTPCT, :QTYCLC, :QTY, :CST, :GST, ' +
      ' :SLE, :VAL) ' +
      'RETURNING ESTDTL_ID';

    Qry.ParamByName('EST_ID').AsLargeInt := FDQuery1.FieldByName('EST_ID').AsLargeInt;
    Qry.ParamByName('CSC_ID').AsLargeInt := FDQuery1.FieldByName('CSC_ID').AsLargeInt;
    // Устанавливаем остальные параметры

    Qry.ExecSQL;

    // Обновляем основной запрос
    FDQuery1.Refresh;
  finally
    Qry.Free;
  end;
end;

Решение 4: Настройка FDUpdateSQL

Правильная настройка компонента FDUpdateSQL:

procedure TForm1.SetupFDUpdateSQL;
begin
  FDUpdateSQL1.InsertSQL.Text := 
    'INSERT INTO ESTDTL ' +
    '(EST_ID, CSC_ID, ESTDTLDSC, CSTPLS, GSTINC, ' +
    ' GSTPCT, QTYCLC, QTY, CST, GST, ' +
    ' SLE, VAL) ' +
    'VALUES (:NEW_EST_ID, :NEW_CSC_ID, :NEW_ESTDTLDSC, :NEW_CSTPLS, :NEW_GSTINC, ' +
    ' :NEW_GSTPCT, :NEW_QTYCLC, :NEW_QTY, :NEW_CST, :NEW_GST, ' +
    ' :NEW_SLE, :NEW_VAL) ' +
    'RETURNING ESTDTL_ID';

  FDUpdateSQL1.ModifySQL.Text := 
    'UPDATE ESTDTL SET ' +
    'CSC_ID = :NEW_CSC_ID, ' +
    'ESTDTLDSC = :NEW_ESTDTLDSC, ' +
    'CSTPLS = :NEW_CSTPLS, ' +
    'GSTINC = :NEW_GSTINC, ' +
    'GSTPCT = :NEW_GSTPCT, ' +
    'QTYCLC = :NEW_QTYCLC, ' +
    'QTY = :NEW_QTY, ' +
    'CST = :NEW_CST, ' +
    'GST = :NEW_GST, ' +
    'SLE = :NEW_SLE, ' +
    'VAL = :NEW_VAL ' +
    'WHERE EST_ID = :OLD_EST_ID AND ESTDTL_ID = :OLD_ESTDTL_ID';

  FDUpdateSQL1.DeleteSQL.Text := 
    'DELETE FROM ESTDTL ' +
    'WHERE EST_ID = :OLD_EST_ID AND ESTDTL_ID = :OLD_ESTDTL_ID';
end;

Обработка события OnUpdateRecord

Для правильной обработки вставки можно использовать событие OnUpdateRecord:

procedure TForm1.FDQuery1UpdateRecord(ASender: TDataSet;
  ARequest: TFDUpdateRequest; var AAction: TFDErrorAction; AOptions: TFDUpdateRowOptions);
begin
  if (ARequest = arUpdate) and (ASender.FieldByName('EstDtl_ID').IsNull) then
  begin
    // Выполняем вставку вместо обновления
    FDUpdateSQL1.ExecSQL(arInsert, ASender);
    AAction := eaApplied;
  end
  else
    FDUpdateSQL1.ExecSQL(ARequest, ASender);
end;

Заключение

Проблема вставки записей в сложных запросах с соединениями является распространенной при работе с FireDAC. В статье представлено несколько решений:

1. Использование UPDATE OR INSERT для Firebird 4.0+
2. Применение генератора и триггера для Firebird 3.0
3. Выполнение вставки отдельным запросом
4. Правильная настройка FDUpdateSQL

Выбор решения зависит от версии Firebird и конкретных требований приложения. Для большинства случаев оптимальным будет использование UPDATE OR INSERT в Firebird 4.0+, так как этот подход наиболее прост и эффективен.

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

Проблема вставки записей в сложных запросах с соединениями в FireDAC и возможные решения для разных версий Firebird.

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

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

:: Главная :: Interbase ::