Базы данных и ADO Объект ADODB.Command и запуск хранимых процедур в VBA

9.6 Объект Command и коллекция Parameters

Объект ADODB.Command, запуск хранимых процедур в VBA, объект Parameter и коллекция Parameters, метод Execute(), возврат значений в Recordset

В самых простых вариантах, когда можно получить и изменять данные напрямую в таблицах, можно обойтись объектом Recordset. Однако во многих ситуациях возможностей только этого объекта недостаточно. Как уже говорилось, предпочтительнее производить любое внесение изменений на источник данных при помощи хранимых процедур. Часто существует потребность в создании временных таблиц и других объектов на сервере. Бизнес-логика многих приложений (начисление процентов, абонентской платы, формирование специальных отчетов с вычислениями и т.п.) также реализована в виде хранимых процедур, поэтому в реальных приложениях одним объектом Recordset не обойтись.

Для выполнения команд SQL на сервере (в том числе запуска хранимых процедур, команд DDL для создания объектов, выполнения служебных операций типа резервного копирования, восстановления, изменения параметров работы) необходимо использовать объект Command.

Создание этого объекта производится очень просто:

Dim cmd As ADODB.Command

Set cmd = CreateObject("ADODB.Command")

Следующее, что нужно сделать — назначить объекту Command объект подключения Connection. Для этой цели предназначено свойство Command.ActiveConnection. Ему можно передать готовый объект Connection, а можно сформировать этот объект неявно, используя в качестве значения свойства ActiveConnection строку подключения. Рекомендуется всегда предавать готовый объект подключения: во-первых, так для соединения можно настроить больше параметров, а во-вторых, если вы используете в приложении несколько объектов Command, можно использовать для каждого такого объекта одно-единственное подключение, что экономит ресурсы. В нашем примере мы используем созданный нами ранее объект Connection:

cmd.ActiveConnection = cn

Следующая наша задача — выбрать тип команды. В принципе, для многих источников можно его и не выбирать — модули ADO постараются сами выяснить у источника данных, что это за команда (хранимая процедура, SQL-запрос и т.п.), однако лучше всегда его определять: экономится время и системные ресурсы, уменьшается вероятность ошибок. Для выбора типа команды используется свойство CommandType. Значения, которые ему можно присвоить, аналогичны возможным значениям параметра Options метода Open() объекта Recordset, которое было рассмотрено выше. Например, если мы передаем команду на выполнение хранимой процедуры, то присвоить соответствующее значение можно так:

cmd.CommandType = adCmdStoredProc

Следующее действие — определить текст команды, которая будет выполняться. Делается это при помощи свойства CommandText. Например, если мы хотим запустить на выполнение хранимую процедуру CustOrderHist, то соответствующий код может выглядеть так:

cmd.CommandText = "CustOrderHist"

Чаще всего хранимая процедура требует передачи ей одного или нескольких параметров. Делается это при помощи коллекции Parameters и объектов Parameter. Для определения параметров можно использовать два способа:

• создать объекты Parameter автоматически путем запроса к серверу (используется метод Refresh() коллекции Parameters) и затем присвоить им значения:

cmd.Parameters.Refresh

cmd.Parameters(1) = "ALFKI"

• создать объекты Parameter вручную и вручную добавить их в коллекцию Parameters. Этот способ более экономичен (нет необходимости лишний раз обращаться на сервер), но требует предварительных выяснений точных свойств параметра и кода большего размера:

Dim Prm As ADODB.Parameter

Set Prm = cmd.CreateParameter("CustomerID", adVarWChar, adParamInput, 5, "ALFKI")

cmd.Parameters.Append Prm

После этого команду необходимо запустить на выполнение. Для этого используется метод Execute(). Самый простой способ его вызова выглядит так:

cmd.Execute

Этот метод принимает также три необязательных параметра, при помощи которых можно дополнительно определить параметры, тип вызываемой команды и т.п.

Некоторые хранимые процедуры и передаваемые команды не требуют возврата каких-либо значений (кроме кода ошибки), но так бывает редко. Как же принять значения, возвращаемые выполняемой командой?

Если возвращаемое значение официально зарегистрировано как возвращаемый параметр (например, оно помечено ключевым словом OUT в определении хранимой процедуры), то это значение будет присвоено соответствующему параметру объекта Command, и до него можно будет добраться обычным способом — при помощи свойства Value.

Если же, как в нашем примере с CustOrderHist, возвращаемое значение просто сбрасывается в поток вывода (в нашем случае возвращается набор записей), то можно использовать два способа:

• первый способ — использовать то, что метод Execute() возвращает объект Recordset, заполненный полученными при помощи команды записями:

Dim rs2 As ADODB.Recordset

Set rs2 = cmd.Execute()

Debug.Print rs2.GetString

• второй способ — воспользоваться тем, что метод Open() объекта Recordset может принимать в качестве параметра объект Command (в этом случае объект Connection передавать этому методу уже нельзя):

Dim rs2 As ADODB.Recordset

Set rs2 = CreateObject("ADODB.Recordset")

rs2.Open cmd

Debug.Print rs2.GetString

Некоторые другие свойства и методы объекта Command:

• CommandStream — возможность вместо прямого назначения текста команды (через свойство CommandText) принять значение из потока ввода (например, из текстового файла или другой программы). Допустимый формат потока зависит от провайдера — драйвера для данного подключения. Использовать одновременно и CommandStream, и CommandText нельзя (второе свойство автоматически становится пустым);
• CommandTimeout — возможность указать, сколько времени в секундах ждать результата выполнения команды на источнике, прежде чем вернуть ошибку;
• Dialect — это свойство позволяет указать особенности разбора (parsing) текста команды на провайдере;
• NamedParameters (только true или false, по умолчанию false) — возможность определить, будут ли передаваться провайдеру имена параметров, или будет использоваться просто передача по порядку значений.
• Prepared — свойство, которое может влиять на производительность. Если установить его в true (по умолчанию false), то при первом выполнении команды провайдер создаст ее откомпилированную версию, которую и будет использовать при последующих выполнениях. Первый раз команда будет выполнять медленнее, чем обычно, зато последующие разы — быстрее. Такое "приготовление команды" (command preparation) поддерживают далеко не все драйверы подключений.
• свойство State возвращает те же значения и используется в тех же целях, что и для объекта Recordset;
• метод Cancel() позволяет прекратить выполнение команды (можно использовать, если выполнение затянулось), если такую возможность поддерживает провайдер.