Delphi.FireDAC. Выполнение SQL команд

Данная статья о выполнении SQL команд в FireDAC. Основа для статьи – официальная документация.

-старая версия

-новая версия

Далее мы рассмотрим как отправлять команды через различные компоненты FireDAC.

TFDConnection

TFDConnection предлагает несколько перегруженных ExecSQL методов. Они просты для использования и полезны, когда

-Не нужно возвращать никаких результатов

-Когда SQL команда выполняется только однажды и не требуется, чтобы она находилась в подготовленном состоянии

-Нет необходимости для расширенной настройки параметров

-Нет необходимости для работы с SQL в дизайн режиме

Далее, в документации приводится такой пример

FDConnection1.ExecSQL('drop table testtab');
FDConnection1.ExecSQL('create table testtab (id integer, name varchar(10))');
FDConnection1.ExecSQL('insert into testtab values (1, ''FireDAC'')');

FDConnection1.ExecSQL('drop table testtab');

FDConnection1.ExecSQL('create table testtab (id integer, name varchar(10))');

FDConnection1.ExecSQL('insert into testtab values (1, ''FireDAC'')');

Для параметризованного запроса достаточно выполнить

FDConnection1.ExecSQL('insert into testtab values (:id, :name)', [1, 'FireDAC']);

1	FDConnection1.ExecSQL('insert into testtab values (:id, :name)', [1, 'FireDAC']);

Также TFDConnection предлагает методы ExecSQLScalar, отличные от ExecSQL. Как написано в документации ExecSQLScalar возвращает значение первой колонки в первом ряду первого result set

sName := FDConnection1.ExecSQLScalar('select name from testtab where id = :id', [1]);

1	sName := FDConnection1.ExecSQLScalar('select name from testtab where id = :id', [1]);

TFDQuery

FDQuery это более универсальный компонент для работы с SQL запросами. Он может и выполнять команды, не возвращающие множеств и наоборот, выполнять команды, возвращающие множества.

FDQuery можно использовать как в Runtime так и в Design режимах. В документации приводится пример как можно пользоваться компонентом в Design режиме

FDQuery в дизайн режиме

Чтобы воспользоваться FDQuery в дизайн режиме, нужно бросить его на форму. Свойство TFDQuery.Connection ,будет автоматически установлено, если на форме присутствует FDConnection. Двойной клик по компоненту и мы получим Query Editor

В этом окне можно задавать SQL команды, определять параметры и макросы, а также настраивать опции.

При нажатии на Execute выполнится запрос. Если команда возвращает множество, оно будет отображено во вкладке RecordSet, а структура множества будет отражена во вкладке Structure. Если DBMS возвращает сообщения об ошибках или предупреждения, они будут отражены во вкладке Messages.

Кнопка NextRecordSet позволяет пройтись по всем множествам, возвращаемым командой.

При тестировании DML команд, таких как UPDATE/INSERT/DELETE, если не подразумевается изменение множества из данного окна, то лучше поставить галочку AutoRollBack.

Кнопка OK для сохранения изменений в TFDQuery.

Использование параметров

Параметры в запросах это один из лучших приемов при разработке приложений БД. Главные достоинства это:

-Мы строим только одну SQL команду и используем её несколько раз с разными значениями параметров. DBMS, в свою, очередь будет строить план выполнения команды только один раз. Это снижает загрузку DBMS.

-В следующий раз, когда вы дадите команду на исполнение, только значения параметров будут передаваться в DBMS. Это снижает нагрузку на сеть.

-Не придется заботиться о формате SQL констант, например константа даты в Access SQL.

Чтобы добавить параметр в SQL запрос, достаточно использовать следующий синтакс :<name>

То есть, в SQL запросе просто пишем двоеточие и имя константы. После этого ассоциируем соответствующие значения, используя коллекцию параметров. Далее в документации приводится такой пример

FDQuery1.SQL.Text := 'select * from tab where code = :Code';
FDQuery1.ParamByName('code').AsString := '123';
FDQuery1.Open;

FDQuery1.SQL.Text := 'select * from tab where code = :Code';

FDQuery1.ParamByName('code').AsString := '123';

FDQuery1.Open;

Но для того, чтобы этот код сработал корректно, в приложении необходимо корректно заполнить параметры.

ПАРАМЕТРЫ

Заполнение параметров

Это можно сделать как из кода так и из дизайн режима.

По умолчанию, при использовании SQL запроса с параметрами, список параметров создается автоматически, но это можно отключить в свойстве ResourceOptions.ParamCreate. Посмотрим как создаются и настраиваются параметры в design-time

Кликнем 2 раза по FDQuey и напишем такой запрос

Теперь перейдем на вкладку параметры

Здесь, как видно, мы можем настроить кое-какие свойства параметров. Также список параметров и их свойства мы можем увидеть в традиционном объектном инспекторе. Для этого, в объектном инспекторе, перейдем в FDQuery.Params и нажмем на многоточие. Увидим следующую картину

Автоматически заполнились свойства Name и ParamType (по умолчанию ставится в ptInput). Далее, для того, чтобы приложение заработало корректно, нам нужно указать DataType, Position и, возможно, другие свойства. Position, насколько я понимаю это порядковый номер параметра в запросе, нумерация начинается с единицы. Параметры выстраиваются в таком же порядке как они написаны в SQL команде. Это, в принципе очень удобно.

Также, можно проделать аналогичную операцию из кода. В документации приводится такой пример

FDQuery1.ResourceOptions.ParamCreate := False;
FDQuery1.SQL.Text := 'select * from tab where code = :Code';
with FDQuery1.Params do begin
  Clear;
  with Add do begin
    Name := 'CODE';
    DataType := ftString;
    Size := 10;
    ParamType := ptInput;
  end;
end;

FDQuery1.ResourceOptions.ParamCreate := False;

FDQuery1.SQL.Text := 'select * from tab where code = :Code';

with FDQuery1.Params do begin

Clear;

with Add do begin

Name := 'CODE';

DataType := ftString;

Size := 10;

ParamType := ptInput;

end;

Параметры могут быть связаны с SQL командой по имени, или по позиции. Это можно настроить в Params.BindMode

FDQuery1.Params.BindMode:=pbByName; // Например так
FDQuery1.Params.BindMode:=pbByNumber; // Или так

1 2	FDQuery1.Params.BindMode:=pbByName; // Например так FDQuery1.Params.BindMode:=pbByNumber; // Или так

При этом, нужно в ручную пронумеровать позиции параметров, начиная с единицы.

DataType может быть использован явно или неявно через ассоциацию значения параметра value или через свойство ASxxx, например Asinteger и так далее. В ином случае, используются значения по умолчанию FormatOptions.DefaultParamDataType

ParamType аналогично, если не определены в ручную, то используются значения по умолчанию из ResourceOptions.DefaultParamType. Также, по умолчанию, все параметры типа input. То есть, чтобы использовать output параметры, нужно это явно указать в ParamType.

По поводу ParamType в справке написано следующее

Здесь параметры нужно указывать в зависимости от того как мы их создали в хранимой процедуре, например. Если они там input значит input, если output, значит output. Для query, насколько я понимаю всегда input.

Значания Input параметров должны быть установлены до выполнения запроса. Для этого, можно воспользоваться одной из следующих возможностей.

Явно задать значение параметра

FDQuery1.ParamByName('code').AsString := 'DA1001';
FDQuery1.Open;

1 2	FDQuery1.ParamByName('code').AsString := 'DA1001'; FDQuery1.Open;

Использовать один из перегруженных ExecSQL методов

FDQuery1.Open('', ['DA1001']);

1	FDQuery1.Open('', ['DA1001']);

Также можно установить значение параметра в Null следующим способом

with FDQuery1.ParamByName('name') do begin
  DataType := ftString;
  Clear;
end;
FDQuery1.ExecSQL;

with FDQuery1.ParamByName('name') do begin

DataType := ftString;

Clear;

end;

FDQuery1.ExecSQL;

Значения Output параметров доступны после выполнения query. Некоторые SQL команды и DBMS могут требовать обработать все множества в команде, и только потом выдадут output параметр. Чтобы ускорить получение output параметра, можно использовать TFDAdaptedDataSet.GetResults.

Подготовка запроса

До того как запрос будет исполнен, он должен быть подготовлен. FireDAC автоматически готовит следующие действия

-Ставит соединение в активное или онлайн состояние

-Проверяет, что все параметры установлены корректно

-Делает препроцессинг команд (Preprocesses the command text)

-Опционально применяет RecsSkip и RecsMax к командному тексту

-Опционально стартует транзакцию FireBird / IB DBMS, когда нет активных соединений

-Передает финальный текст команды к DBMPS API. Финальный текст можно получить в свойстве TFDQuery.Text

Во время подготовки команды, соединение должно быть активным и запрос (query) использует ресурсы сервера. Чтобы освободить ресурсы сервера, можно использовать методы Unprepare или Disconnect

Когда необходимо выполнить команду только один раз, можно установить ResourceOptions.DirectExecute в True. Для некоторых DBMS это ускорит выполнение, так как не будет операций на подготовку команд SQL.

Executing the Query (Выполнение запроса)

Чтобы выполнить запрос, которые не требует возвращаемого множества, используйте ExecSQL методы. Если множество возвращается, выйдет исключение [FireDAC][Phys][MSAcc]-310. Cannot execute command returning result sets

Чтобы выполнить запрос, возвращающий множество и открыть его, используйте методы Open. Если множество не возвращается, возникнет исключение Cannot open / define command that does not return result sets

Чтобы выполнить ad-hoc запрос, используйте ExecuteOrOpen метод.

Замечание. Запрос может быть выполнен асинхронно (executed asynchronously). Если запрос это набор команд – проверьте раздел Command Batches для деталей.

Ошибка при изменении типа параметра

Когда тип параметра изменился, после первого запроса Prepare/ExecSQL /Open, FireDAC возбуждает исключение.

[FireDAC][Phys][IB]-338. Parameter [Xxxx] data type is changed.
Query must be reprepared.

1 2	[FireDAC][Phys][IB]-338. Parameter [Xxxx] data type is changed. Query must be reprepared.

При первой операции Prepare/ExecSQL/Open FireDAC запоминает тип параметра. Тип данных может быть определен явно, через установку свойства TFDParam.DataType, или неявно, через установку значения свойства TFDParam.AsXxxx или TFDParam.Value.

TFDParam.Value не меняет тип параметра при следующем вызове.Оно ассоциирует значение с текущим типом данных. Чтобы избежать исключения, используйте

The TFDParam.Value
The TFDParam.AsXxxx

Обратная связь от DBMS

1) Используйте свойство TFDQuery.RowsAffected, чтобы получить число строк, обработанных командой (например, число удаленных рядов, после команды Delete)

2) Через try / except / end

try
  FDQuery1.ExecSQL;
except
  on E: EFDDBEngineException do
    ; // do something here
end;

try

FDQuery1.ExecSQL;

except

on E: EFDDBEngineException do

; // do something here

end;

3) Через обработку TFDQuery.OnError

4) Через обработку TFDConnection.OnError, например

procedure TForm1.FDConnection1Error(ASender: TObject; const AInitiator: IFDStanObject;
  var AException: Exception);
begin
  if (AException is EFDDBEngineException) and (EFDDBEngineException(AException).Kind = ekRecordLocked) then
    AException.Message := 'Please, try the operation later. At moment, the record is busy';
end;
 
FDConnection1.OnError := FDConnection1Error;

procedure TForm1.FDConnection1Error(ASender: TObject; const AInitiator: IFDStanObject;

var AException: Exception);

begin

if (AException is EFDDBEngineException) and (EFDDBEngineException(AException).Kind = ekRecordLocked) then

AException.Message := 'Please, try the operation later. At moment, the record is busy';

end;

FDConnection1.OnError := FDConnection1Error;

5) Через получение сообщений от DBMS. Для этого свойство FDConnection.ResourceOptions.ServerOutput нужно поставить в True

var
  i: Integer;
begin
  FDConnection1.ResourceOptions.ServerOutput := True;
  FDQuery1.ExecSQL;
  if FDConnection1.Messages <> nil then
    for i := 0 to FDConnection1.Messages.ErrorCount - 1 do
      Memo1.Lines.Add(FDConnection1.Messages[i].Message);
end;

var

i: Integer;

begin

FDConnection1.ResourceOptions.ServerOutput := True;

FDQuery1.ExecSQL;

if FDConnection1.Messages <> nil then

for i := 0 to FDConnection1.Messages.ErrorCount - 1 do

Memo1.Lines.Add(FDConnection1.Messages[i].Message);

end;

В документации есть дополнительная информация, но основные моменты мы разобрали.

Delphi.FireDAC. Выполнение SQL команд

TFDConnection

TFDQuery

FDQuery в дизайн режиме

Использование параметров

ПАРАМЕТРЫ

Заполнение параметров

Подготовка запроса

Executing the Query (Выполнение запроса)

Ошибка при изменении типа параметра

Обратная связь от DBMS

MY PROJECTS

Recent Posts

Categories