8. Продвинутые возможности ado

8.1. Использование языка определения данных (Data Definition Language - DDL) Язык определения данных - это такие SQL операторы, которые поддерживают определения или объявления объектов БД, например CREATE TABLE, DROP TABLE или ALTER TABLE. Выполнение запросов DDL не генерирует никаких данных и, поэтому, нет необходимости использовать объект Recordset. Для выполнения запросов DDL идеальным является объект Command. Чтобы отличить запросы DDL от имен хранимых процедур или таблиц, свойство CommandType объекта Command должно быть установлено в adCmdText. SQL Server обеспечивает ряд опций выполнения запросов, которые могут быть установлены оператором SET. Эти SET опции не генерируют никаких результирующих наборов данных и, поэтому они могут рассматриваться в обном ряду с запросами DDL. Следующий пример показывает использование объекта Command для отключения опции SET NOCOUNT оператора SET SQL Server Transact-SQL - SET: Dim Cn As New ADODB.Connection Dim Cmd As New ADODB.Command ' If the ADOTestTable does not exist On Error GoTo AdoError Cn.Open "pubs", "sa" Set Cmd.ActiveConnection = Cn Cmd.CommandText = "drop table ADOTestTable" Cmd.CommandType = adCmdText Cmd.Execute Done: Cmd.CommandText = "set nocount on" Cmd.Execute Cmd.CommandText = "create table ADOTestTable (id int, name char(100))" Cmd.Execute Cmd.CommandText = "insert into ADOTestTable values(1, 'Jane Doe')" Cmd.Execute Cn.Close Exit Sub AdoError: Dim errLoop As Error Dim strError As String ' Enumerate Errors collection and display properties of ' each Error object. Set Errs1 = Cn.Errors For Each errLoop In Errs1 Debug.Print errLoop.SQLState Debug.Print errLoop.NativeError Debug.Print errLoop.Description Next GoTo Done End Sub Этот пример сбрасывает таблицу, открывает таблицу и вставляет данные в таблицу с использованием метода Execute объекта Command. Для этого типа запросов не создаются объекты Recordset. Таблица ADOTestTable может не существовать в БД и поэтому выполнение drop table ADOTestTable может генерировать ошибку, которая покажет, что таблица не существует. Для обработки такой ситуации предусмотрена обработка ошибок. Затем выполняется установка опции set - set nocount on. 8.2. Использование подготовленных (Prepared) операторов Запросы могут быть подготовлены перед их выполнением или могут быть непосредственно выполнены. Свойство Prepared объекта Command позволяет определить будет запрос подготавливаться или нет. Если свойство Prepared установлено в TRUE, то строка запроса будет подвергнута разбору и оптимизации при первом выполнении. При любм последующем выполнении этого запроса будет использоваться "откомпилированная" версия запроса. Это потребует некоторого времени при первом выполнении запроса, но зато потом, при последующих выполнениях, должен наблюдаться заметный рост производительности. Если предполагается, что запрос будет выполняться один раз, то он должен быть выполнен без предварительной подготовки. Свойство Prepared может также использоваться при выполнении запросов со многими параметрами. Приложение может выполнять запросы с параметрами более чкм один раз с подстановкой разных наборов параметров при каждом выполнении запроса вместо полной перестройки строки запроса всякий раз при изменении параметров. Однако, если запрос с параметрами выполняется один раз, то нет необходимости в его предварительной подготовке. SQL Server не поддерживает непосредственно модель Prepare/Execute для ODBC. Когда оператор подготавливается, ODBC драйвер SQL server создает временную хранимую процедуру для этого оператора. Эта временная хранимая процедура существует в tempdb и не сбрасывается до тех пор, пока не закроются объекты Recordset или Connection. Эта опция может быть отключена в диалоге SQL Server ODBC Data Source Setup если для подсоединения к SQL Server используется источник данных ODBC. Если эта опция отключена, то каждый раз при выполнении запроса SQL оператор сначала запоминается и затем посылается к серверу на выполнение. Следующий пример показывает использование подготовленного оператора для обновления запроса и динамической сборке запроса с различными наборами параметров во время выполнения: Dim Cn As New ADODB.Connection Dim Cmd As New ADODB.Command Dim prm1 As New ADODB.Parameter Dim prm2 As New ADODB.Parameter Cn.Open "DSN=pubs", "sa" Set Cmd.ActiveConnection = Cn Cmd.CommandText = "update titles set type=? where title_id=?" Cmd.CommandType = adCmdText Cmd.Prepared = True Set prm1 = Cmd.CreateParameter("Type", adChar, adParamInput, 12, "New Bus") Cmd.Parameters.Append prm1 Set prm2 = Cmd.CreateParameter("Title_id", adChar, adParamInput, 6, "BU7832") Cmd.Parameters.Append prm2 Cmd.Execute Cmd("Type") = "New Cook" Cmd("title_id") = "TC7777" Cmd.Execute Cmd("Type") = "Cook" Cmd("title_id") = "TC7778" Cmd.Execute Cn.Close Этот пример обновляетданные в таблице titles с использованием разных значений параметров. Строка запроса подготавливается так, чтобы при его выполнении могли применяться разные наборы параметров. Для операции обновления требуются два параметра: type и title_id. Они создаются с помощью метода CreateParameters и добавляются к набору объектов Parameters с помощью метода Append. Первый набор параметров имеет значения New Bus и BU7832. Другие значения параметров могут использоваться перед выполнением метода Execute без перестройки строки запроса, так как свойство Prepared установлено в TRUE. 8.3. Выполнение хранимых процедур Выполнение хранимых процедур очень похоже на выполнение подготовленных запросов, за исключением того, что хранимая процедура существует как объект в БД даже когда выполнение запроса закончено. Хранимая процедура может также использоваться для сокрытия из приложения сложных SQL операторов. Когда в объекте Command выполняется хранимая процедура , свойство CommandType должно быть определено как adCmdStoredProc. При таком определениии свойства CommandType генерируется соответствующий оператор SQL для основного провайдера. Для ODBC провайдера генерируются ODBC escape-последовательности для вызовов процедур {[?=]call procedure-name[([parameter][,[parameter]]...)]} и ODBC драйвер SQL Server оптимизирует запрос, чтобы использовать все преемущества этих последовательностей. Следующий пример показывает выполнение хранимой процедуры sp_who: Dim Cmd As New ADODB.Command Dim rs As New ADODB.Recordset Cmd.ActiveConnection = "DSN=pubs;uid=sa" Cmd.CommandText = "sp_who" Cmd.CommandType = adCmdStoredProc Set rs = Cmd.Execute() Debug.Print rs(0) rs.Close Для оптимальной производительности приложение никогда не должно подготавливать хранимые процедуры SQL Server. Иначе возникнут дополнительные накладные расходы при создании временных хранимых процедур. Коды возврата и выходные параметры хранимых процедур Хранимые процедуры могут содержать входные и выходные параметры и возвращать значения. Например, следующая хранимая процедура myProc содержит выходной параметр @ioparm и возвращает значение 99. CREATE PROCEDURE myProc @ioparm int OUTPUT AS SELECT name FROM sysusers WHERE uid < 2 SELECT @ioparm = 88 RETURN 99 Входной параметр для хранимой процедуры можно определить через объект Parameter. Выходной параметр и возвращенное значение могут также быть определены через объект Parameter, но действительные значения обоих этих параметров не будут возвращены до тех пор, пока объект Recordset не будет полностью выбран (в смысле выборки данных) или пока объект Recordset не будет закрыт. ADO следующий: Dim Cmd As New ADODB.Command Dim rs As New ADODB.Recordset Dim param As Parameter Cmd.ActiveConnection = "DSN=pubs;UID=sa" Cmd.CommandText = "myproc" Cmd.CommandType = adCmdStoredProc ' Set up parameters. Set param = Cmd.CreateParameter("Return", adInteger, adParamReturnValue, , 0) Cmd.Parameters.Append param Set param = Cmd.CreateParameter("Output", adInteger, adParamOutput, , 0) Cmd.Parameters.Append param Set rs = Cmd.Execute If Not rs.EOF And Not rs.BOF Then Debug.Print rs(0) rs.Close End If Debug.Print Cmd(0) ' The return code Debug.Print Cmd(1) ' The Output parameter Для хранимой процедуры myProc требуются два параметра: выходной параметр для сохранения возвращенного значения и выходной параметр @ioparam. В этом примере сначала создаются два параметра: Return и Output. Параметр Return создается как тип adParamReturnValue и имеет тип данных - adInteger, что соответствует integer. Параметр Output - adParamReturnValue для выходного параметра и тоже имеет тип данных integer. Так как оба параметра имеют тип integer, то нет необходимости определять длину данных. После добавления параметров и выполнения запроса создается набор записей. Затем объект Recordset закрывается, чтобы получить код возврата и выходной параметр. 8.4. Использование пакетного обновления (Batch Updating) Метод Update объекта Recordset позволяет обновить текущую запись. Метод UpdateBatch позволяет применить все ожидающие новые, обновленные и удаленные записи к объекту Recordset. Используя LockType adLockBatchOptimistic, метод UpdateBatch позволяет выполнить все изменения, находящиеся в состоянии ожидания, на клиентском компьютере и одновременно послать все эти изменения серверу БД. Изменения, находящиеся в состоянии ожидания, могут быть отменены с помощью метода CancelBatch. Только если все изменения в БД при выполнении метода UpdateBatch будут неудачными, будет возвращена ошибка. Если же только некоторые изменения будут неудачными, то будет возвращено предупреждение. В SQL Server метод UpdateBatch является допустимым только, когда свойство LockType установлено в adLockBatchOptimistic и тип курсора - либо keyset-driven, либо static. Курсор типа keyset-driven может открываться с таблицами, имеющими уникальные индексы. Следующий пример демонстрирует использование метода UpdateBatch: Dim rs As New ADODB.Recordset rs.CursorType = adOpenKeyset rs.LockType = adLockBatchOptimistic rs.Open "select * from titles","DSN=pubs;uid=sa" ' Change the type for a specified title. While (Not rs.EOF) If Trim(rs("Type")) = "trad_cook" Then rs("Type") = "Cook" End If rs.MoveNext Wend rs.UpdateBatch rs.Close В этом примере сохдается набор записей используя курсор типа keyset-driven со свойством LockType установленным в adLockBatchOptimistic. После создания объекта Recordset, тип trad-cook изменяется на Cook, новый тип для всех записей в таблице title. После выполнения всех изменений, сделанные изменения данных подтверждаются применением метода UpdateBatch. 8.5. Генерация нескольких наборов записей Вместо выполнения одного запроса несколько раз, SQL Server позволяет выполнять пакеты запросов. В результате выполнении пакета запросов может генерироваться более чем один набор записей. Кроме пакетных запросов, множественные наборы записей могут также генерироваться SQL операторами, включающими предложения COMPUTE BY и COMPUTE, или хранимыми процедурами, которые содержат более одного оператора SELECT. Когда генерируются множественные наборы записей, важно иметь возможность последовательно выбирать наборы записей до тех пор, пока наборы записей являются доступными. Метод NextRecordset объекта Recordset позволяет выбрать любые последующие наборы записей. Если нет больше доступных наборов записей, то возвращенный объект Recordset будет установлен в Nothing. Следующий пример показывает использование метода NextRecordset для выборки нескольких наборов записей. Синтаксис хранимой процедуры следующий: drop proc myNextproc go create proc myNextproc as select * from titles select * from publishers Эта хранимая процедура генерирует два набора записей: один - в результате выполнения запроса select * from titles, другой - в результате выполнения запроса select * from publishers. ADO код имеет следующий вид: Dim cmd As New ADODB.Command Dim rs As ADODB.Recordset Cmd.ActiveConnection = "DSN=pubs;UID=sa" Cmd.CommandText = "myNextProc" Cmd.CommandType = adCmdStoredProc Set rs = Cmd.Execute() While Not rs Is Nothing If (Not rs.EOF) Then Debug.Print rs(0) End If Set rs = rs.NextRecordset() Wend После выполнения хранимой процедуры myNextProc создается объект Recordset. Так как в результате выполнения процедуры myNextProc создается два набора записей, каждые объект Recordset может быть получен при помощи метода NextRecordset. 8.6. Использование серверных курсоров SQL Server обеспечивает целый ряд типов серверных курсоров для использования в приложениях. По умолчанию ADO приложение не использует серверных курсоров когда вместе с SQL Server используется ODBC провайдер. По умолчанию ADO приложение использует несерверный курсор типа forward и только для чтения. Серверные курсоры полезны при обновлении, вставке или удалении записей. Серверные курсоры также позволяют иметь множественные активные операторы (active statements) при одном соединении. SQL Server намеренно не позволяет множественных активных операторов на соединение, пока используются серверные курсоры. Активный оператор предназначен для того, чтобы в обработке оператора существовало несколько незавершенных результатов (pending results). Если серверные курсоры не используются и приложение пытается иметь более одного активного оператора, то приложение получит сообщение об ошибке "Connection Busy with Another Active Statement". Чтобы в приложении использовать серверный курсор необходимо сделать следующее: * Установить тип курсора отличный от устанавливаемого по умолчанию при помощи метода RecordsetOpen. По умолчанию тип курсора устанавливается в adOpenForwardOnly и изменение типа на adOpenKeyset, adOpenDynamic или adOpenStatic приведет к использованию серверного курсора. * Установить LockType отличный от устанавливаемого по умолчанию при помощи метода RecordsetOpen. По умолчанию LockType установлен в adLockReadOnly и любое изменение его на adLockPessimistic, adLockOptimistic или adLockBatchOptimistic приведет к использованию серверного курсора. * Установить значение свойства CacheSize на любое другое, отличное от устанавливаемого по умолчанию (по умолчанию устанавливается 1). Серверные курсоры: * Создаются только для операторов, начинающахся с SELECT, EXEC[ute] procedure_name или {call procedures_name}. Даже если приложение явно потребует создания серверного курсора, серверный курсор не будет создан для операторов таких как INSERT. * Не могут использоваться с операторами, которые генерируют белее одного набора записей. Это ограниечение применимо ко всем операторам, описанным в разделе "Генерация множественных наборов записей". Если серверный курсор используется с любым оператором, генерирующим множественные наборы записей, то приложение может получить следующие ошибки: * "Cannot open a cursor on a stored procedure that has anything other than a single select statement in it." * "sp_cursoropen. The statement parameter can only be a single select or a single stored procedure." Следующий пример демонстрирует открытие динамического (dynamic) серверного курсора: Dim rs As New ADODB.Recordset rs.Open "select * from titles","DSN=pubs;UID=sa", adOpenDynamic, adLockOptimistic rs.Close 8.7. Использование объемных типов данных Объемными типами данных в SQL server являются типы text и image. Тексты и рисунки иногда могут поместиться в памяти, но они также могут оказаться такими большими, что не смогут быть возвращены за одну операцию или целиком разместиться в памяти. Если объемные данные помещаются в памяти, то для получения всех данных за одну операцию может использоваться свойство Value объекта Field. Если данные слишком велики, чтобы поместиться в памяти, то операции с такими данными должны проводиться по частям. Существует два способа манипулирования данными большого размера. Первый - через объект Field и второй - через объект Parameter. Оба объекта Field и Parameter поддерживают метод AppendChunk, а объект Field, кроме того, обеспечивает метод GetChunk для операций с данными большого размера. Объект Field позволяет записать или прочитать данные через объект Recordset. Метод AppendChunk объекта Field позволяет добавить данные к концу текущих данных когда запрос уже выполнен. Метод GetChunk позволяет читать данные по частям. Объект Parameter обрабатывает данные большого размера подобным образом. Только у объекта Parameter нет метода GetChunk и нет объекта Recordset при работе с данными большого размера в режиме выполнения. С объектом Parameter данные большого размера свянаны в режиме выполнения и выполняются с объектом Command. Существует несколько ограничений при работе с данными большого размера при использовании ODBC провайдера. Если не используется серверный курсор, то все колонки с объемными данными должны располагаться справа от всех остальных колонок (с обычными данными). Если есть несколько колонок с данными большого размера, то доступ к ним должен производиться в порядке слева-направо. Следующий пример демонстрирует использование методов AppendChunk и GetChunk для чтения и записи данных большого размера: Структура таблицы-приемника следующая: drop table myBLOB go create table myBLOB( id int unique, info text) go insert into myBLOB values(1, 'test') go Таблица myBLOB - это таблица-приемник, в которую будут вставлены данные большого размера: ADO код имеет следующий вид: Dim Cn As New ADODB.Connection Dim rsRead As New ADODB.Recordset Dim rsWrite As New ADODB.Recordset Dim strChunk As String Dim Offset As Long Dim Totalsize As Long Dim ChunkSize As Long Cn.Open "pubs", "sa" rsRead.CursorType = adOpenStatic rsRead.Open "select pr_info from pub_info", Cn rsWrite.CursorType = adOpenKeyset rsWrite.LockType = adLockBatchOptimistic rsWrite.Open "select * from myBLOB", Cn ChunkSize = 1000 Totalsize = rsRead("pr_info").ActualSize Do While Offset < Totalsize strChunk = rsRead("pr_info").GetChunk(ChunkSize) Offset = Offset + ChunkSize rsWrite("info").AppendChunk strChunk Loop rsWrite.UpdateBatch rsWrite.Close rsRead.Close End Sub В этом примере данные pr_info считываются из таблицы pub_info и вставляются в таблицу myBLOB. После создания наборов записей rsRead и rsWrite, размер данных запоминаетсяв переменной Totalsize. Затем в цикле WHILE данные вставляются частями по 1000 байт. Выход из цикла предусмотрен, когда размер вставленных данных превысит размер первоначальный размер данных. После вставки для подтверждения сделанных изменений применяется метод UpdateBatch.