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

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

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

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

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


TFDConnection

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

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

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

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

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

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

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

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


TFDQuery

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

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

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

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

 

21

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

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

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

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

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

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

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

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

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

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

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

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

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


ПАРАМЕТРЫ

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

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

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

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

22

 

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

23

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

24

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

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

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

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

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

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

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

25

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

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

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

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

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

Значения 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 возбуждает исключение.

При первой операции  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

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

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

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

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

 

This entry was posted in Delphi, FireDAC. Bookmark the permalink.