Если у вас возникли сложности с курсовой, контрольной, дипломной, рефератом, отчетом по практике, научно-исследовательской и любой другой работой - мы готовы помочь.
Предоплата всего
Подписываем
Продолжительность: 2 часа (90 мин.)
Каждая СУБД помимо интерактивной SQL-утилиты обязательно имеет библиотеку доступа и набор драйверов для различных операционных систем. Схема взаимодействия клиентского приложения с сервером базы данных в этом случае выглядит, как показано на рисунке 23.1.
Рисунок 23.1 — Схема взаимодействия клиентского приложения с сервером БД
Библиотека доступа — это, как правило, объектный файл, исходный код которого создан на универсальном языке типа C. Эта библиотека содержит набор функций, позволяющих пользовательскому приложению соединяться с базой данных, передавать запросы серверу и получать ответные данные.
Программа, обеспечивающая взаимодействие пользователя с СУБД, компилируется совместно с библиотекой доступа. Библиотечные вызовы преобразуются драйвером базы данных в сетевые вызовы и передаются сетевым программным обеспечением на сервер.
На сервере происходит обратный процесс преобразования: сетевые пакеты -> функции библиотеки -> SQL-запросы, запросы обрабатываются, их результаты передаются клиенту.
Такой способ создания приложений чрезвычайно гибок, позволяет реализовать практически любое приложение, но в то же время имеет явные недостатки:
В результате получаем приложение, которое привязано как к сетевой среде, так и к программно-аппаратной платформе и используемой базе данных.
Некоторой модификацией данного способа является использование "встроенного" языка SQL. В этом случае в текст программы на языке третьего поколения включаются не вызовы библиотек, а непосредственно предложения SQL, которые предваряются ключевым выражением "EXEC SQL". Перед компиляцией в машинный код такая программа обрабатывается препроцессором, который транслирует смесь операторов "собственного" языка СУБД и операторов SQL в "чистый" исходный код. Затем коды SQL замещаются вызовами соответствующих процедур из библиотек исполняемых модулей, служащих для поддержки конкретного варианта СУБД.
Такой подход позволил несколько снизить степень привязанности к СУБД, например, при переключении прикладной программы на работу с другим сервером базы данных достаточно было заново обработать ее исходный текст новым препроцессором и перекомпилировать.
Большим достижением явилось появление в 1994 г. в стандарте SQL интерфейса уровня вызова CLI (Call Level Interface), в котором стандартизован общий набор рабочих процедур, обеспечивающий совместимость со всеми основными серверами баз данных. Ключевой элемент CLI - специальная библиотека для компьютера-клиента, в которой хранятся вызовы процедур и большинство часто используемых сетевых компонентов для организации связи с сервером. Это ПО поставляется разработчиком средств SQL, не является универсальным и поддерживает разнообразные транспортные протоколы.
Использование программных вызовов позволяет свести к минимуму операции на компьютере-клиенте. В общем случае клиент формирует оператор языка SQL в виде строки и пересылает ее на сервер посредством процедуры исполнения (execute). Когда же сервер в качестве ответа возвращает несколько строк данных, клиент считывает результат с помощью серии вызовов процедуры выборки данных. Далее информация из столбцов полученной таблицы может быть связана с соответствующими переменными приложения. Вызов специальной процедуры позволяет клиенту определить считанное число строк, столбцов и типы данных в каждом столбце.
Интерфейс CLI построен таким образом, что перед передачей запроса серверу клиент не должен заботиться о типе оператора SQL, будь то выборка, обновление, удаление или вставка.
Очень важный шаг к созданию переносимых приложений обработки данных сделала фирма Microsoft, опубликовавшая в 1992 году спецификацию ODBC (Open Database Connectivity - открытого интерфейса к базам данных), предназначенную для унификации доступа к данным с персональных компьютеров работающих под управлением операционной системы Windows. (Заметим, что ODBC опирается на спецификации CLI). Структурная схема доступа к данным с использованием ODBC приведена на рисунке 23.2.
ODBC представляет из себя программный слой, унифицирующий интерфейс приложений с базами данных. За реализацию особенностей доступа к каждой отдельной СУБД отвечает специальный ODBC-драйвер. Пользовательское приложение этих особенностей не видит, т.к. взаимодействует с универсальным программным слоем более высокого уровня. Таким образом, приложение становится в значительной степени независимым от СУБД. Однако, этот способ также не лишен недостатков:
Рисунок 23.2 — Структурная схема доступа к данным с использованием ODBC
JDBC (Java DataBase Connectivity) — это интерфейс прикладного программирования (API) для выполнения SQL-запросов к базам данных из программ, написанных на языке Java. Напомним, что язык Java, созданный компанией Sun, является платформенно-независимым и позволяет создавать как собственно приложения (standalone application), так и программы (апплеты), встраиваемые в web-страницы.
JDBC во многом подобен ODBC, также построен на основе спецификации CLI, однако имеет ряд отличий. Во-первых, приложение загружает JDBC-драйвер динамически, следовательно, администрирование клиентов упрощается, более того, появляется возможность переключаться на работу с другой СУБД без перенастройки клиентского рабочего места. Во-вторых, JDBC, как и Java в целом, не привязан к конкретной аппаратной платформе, следовательно, проблемы с переносимостью приложений практически снимаются. В-третьих, использование Java-приложений и связанной с ними идеологии "тонких клиентов" обещает снизить требования к оборудованию клиентских рабочих мест.
OLE DB представляет собой набор COM-интерфейсов (Component Object Model), которые предоставляют приложению-клиенту унифицированный доступ к различным источникам данных.
Можно сказать, что OLE DB — это метод доступа к любым данным через стандартные COM-интерфейсы, вне зависимости от типа данных и места их расположения. В качестве данных могут выступать базы данных, простые документы, таблицы Excel и любые другие источники данных. В отличие от доступа, предоставляемого посредством драйверов OBDC, OLE DB позволяет реализовывать доступ к источникам данных, как с применением языка SQL (к SQL-серверам), так и к любым другим произвольным источникам данных.
Средства, предоставляющие доступ к источнику данных с использованием технологии OLE DB, называются OLE DB провайдерами. Программы-клиенты, использующие для доступа OLE DB провайдеры, называются потребителями данных.
В том случае, если существует только ODBC-драйвер для доступа к конкретному источнику данных, то для применения технологии OLE DB можно использовать OLE DB провайдер, предназначенный для доступа к ODBC-источнику данных.
Так как архитектура OLE DB основана на COM, то механизм создания результирующих наборов состоит из последовательностей шагов типа: создание объекта -> запрос указателя на интерфейс созданного объекта -> вызов метода интерфейса.
Аналогично комплексу действий, который производится после создания результирующего набора при применении технологии ODBC — выполнению связывания, в технологии OLE DB используется механизм аксессоров. Аксессоры описывают, каким образом данные записываются в область памяти потребителя данных, устанавливая адресное соответствие между областью памяти в буфере потребителя данных и столбцами данных в результирующем наборе. Иногда такой набор связей называют картой столбцов (column map).
Объектная модель OLE DB
Спецификация OLE DB описывает набор интерфейсов, реализуемых объектами OLE DB. Каждый объектный тип определен как набор интерфейсов. Спецификация OLE DB определяет набор интерфейсов базового уровня, которые должны реализовываться любыми OLE DB провайдерами.
В базовую модель OLE DB входят следующие объекты:
Спецификация OLE DB определяет объект Command (команда), предназначенный для выполнения текстовой команды. В качестве такой команды может выступать и SQL-оператор. При этом выполнение команды может создавать результирующий набор (в случае SQL-оператора - это оператор SELECT).
Некоторые OLE DB провайдеры поддерживают работу со схемой (Schema), которая предоставляет метаданные по базе данных. Метаданные становятся доступны как обычные результирующие наборы. В заголовочном файле oledb.h содержатся уникальные идентификаторы всех доступных типов результирующих наборов схемы данных (например, для получения информации по таблицам базы данных следует указать уникальный идентификатор DBSCHEMA_TABLES). Столбец результирующего набора с именем TABLE_NAME содержит имя таблицы, столбец TABLE_TYPE указывает один из следующих типов таблицы: ALIAS, TABLE, SYNONYM, SYSTEM TABLE, VIEW, GLOBAL TEMPORARY, LOCAL TEMPORARY, SYSTEM VIEW.
Представление (View) определяет подмножество строк и столбцов из набора данных, но само не содержит их. Представления не могут объединять данные из нескольких наборов данных.
Для обеспечения расширенных возможностей управления транзакциями объектная модель OLE DB включает объект Transaction.
OLE DB провайдеры, как и все COM-компоненты, регистрируются в реестре Windows. Для поиска информации о зарегистрированных источниках данных используются специальные объекты, называемые нумераторами. Нумератор — это обычный СОМ-сервер, позволяющий получить информацию об источниках данных в виде результирующего набора. Для создания такого результирующего набора в объектном типе DataSource специфицирован интерфейс IDBEnumerateSources.
Для каждого объектного типа спецификация OLE DB определяет набор интерфейсов, который должен обязательно быть реализован для данного объекта. Такие интерфейсы отмечаются как [mandatory]. Интерфейсы, которые могут отсутствовать, отмечаются как [optional].
Для объекта "источник данных" специфицирован следующий набор интерфейсов:
CoType TDataSource {
[mandatory] interface IDBCreateSession;
[mandatory] interface IDBInitialize;
[mandatory] interface IDBProperties;
[mandatory] interface IPersist;
[optional] interface IConnectionPointContainer;
[optional] interface IDBAsynchStatus;
[optional] interface IDBDataSourceAdmin;
[optional] interface IDBInfo;
[optional] interface IPersistFile;
[optional] interface ISupportErrorInfo;
}
Для объекта "сеанс" специфицирован следующий набор интерфейсов:
CoType TSession {
[mandatory] interface IGetDataSource;
[mandatory] interface IOpenRowset; // Создание набора данных
[mandatory] interface ISessionProperties;
[optional] interface IAlterIndex;
[optional] interface IAlterTable;
[optional] interface IBindResource;
[optional] interface ICreateRow;
[optional] interface IDBCreateCommand;
[optional] interface IDBSchemaRowset;
[optional] interface IIndexDefinition;
[optional] interface ISupportErrorInfo;
[optional] interface ITableCreation;
[optional] interface ITableDefinition; // Для создания таблицы
[optional] interface ITableDefinitionWithConstraints;
[optional] interface ITransaction;
[optional] interface ITransactionJoin;
[optional] interface ITransactionLocal;
[optional] interface ITransactionObject;
}
Для объекта "результирующий набор" специфицирован следующий набор интерфейсов:
CoType TRowset {
[mandatory] interface IAccessor;
[mandatory] interface IColumnsInfo;
[mandatory] interface IConvertType;
[mandatory] interface IRowset;// Последовательное
// чтение таблицы
[mandatory] interface IRowsetInfo;
[optional] interface IChapteredRowset;
[optional] interface IColumnsInfo2;
[optional] interface IColumnsRowset;
[optional] interface IConnectionPointContainer;
[optional] interface IDBAsynchStatus;
[optional] interface IGetRow;
[optional] interface IRowsetChange; // Для удаления, изменения и добавления
// строк в набор данных
[optional] interface IRowsetChapterMember;
[optional] interface IRowsetCurrentIndex;
[optional] interface IRowsetFind;
[optional] interface IRowsetIdentity;
[optional] interface IRowsetIndex;
[optional] interface IRowsetLocate; // Прямое
// позиционирование на запись набора данных
[optional] interface IRowsetRefresh; // Для
// обновления данных в созданном наборе данных
[optional] interface IRowsetScroll; // Поддержка
// скроллинга по набору данных
[optional] interface IRowsetUpdate;
[optional] interface IRowsetView;
[optional] interface ISupportErrorInfo;
[optional] interface IRowsetBookmark;
}
Все объекты объектного типа Rowset должны реализовывать следующие интерфейсы:
Создание результирующего набора
При реализации доступа к БД посредством OLE DB провайдера сначала следует создать объект данных и установить соединение с базой данных. Далее необходимо создать объект "сеанс". И только потом можно создавать результирующий набор.
Результирующий набор может быть создан одним из следующих способов:
Чтобы результирующий набор, хранимый на сервере, можно было использовать, необходимо выполнить связывание и извлечение данных. Для этого следует определить структуры типа DBBINDING, описывающие столбцы, и создать аксессор. Далее для получения строк результирующего набора можно использовать один из следующих методов:
В заключение для записи данных в структуру, определенную аксессором, вызывается метод IRowset::GetData.
После получения и обработки строк их следует освободить, вызвав метод IRowset::ReleaseRows.
После просмотра всего результирующего набора следует также освободить аксессор, вызвав метод IRowset::ReleaseAccessor, и освободить сам результирующий набор, вызвав метод IRowset::Release.
Интерфейс IAccessor определяет следующие методы:
Для создания аксессора следует запросить интерфейс IAccessor и выполнить следующий код:
HRESULT hr=pIAccessor-> CreateAccessor();
Метод CreateAccessor имеет следующее формальное описание:
HRESULT CreateAccessor (
DBACCESSORFLAGS dwAccessorFlags, // Свойства
// аксессора и как он используется
DBCOUNTITEM cBindings, // Число связей
// в аксессоре
const DBBINDING rgBindings[], // Описание
// столбца или параметра
DBLENGTH cbRowSize, // Число байтов,
// используемых для одного набора параметров
HACCESSOR *phAccessor, // Указатель
//на созданный аксессор
DBBINDSTATUS rgStatus[]); // Массив значений,
// определяющий статус
// каждого связывания
Каждый столбец формируемого результирующего набора или параметр описывается структурой DBBINDING, которая имеет следующее формальное описание:
typedef struct tagDBBINDING {
DBORDINAL iOrdinal; // Порядковый номер
// столбца или параметра (начиная с 1)
DBBYTEOFFSET obValue; // Сдвиг в байтах для
// значения столбца или параметра в буфере
// (указатель на буфер задается при
// создании аксессора)
DBBYTEOFFSET obLength;
DBBYTEOFFSET obStatus;
ITypeInfo *pTypeInfo;
DBOBJECT *pObject;
DBBINDEXT *pBindExt;
DBPART dwPart;
DBMEMOWNER dwMemOwner;
DBPARAMIO eParamIO;
DBLENGTH cbMaxLen;
DWORD dwFlags;
DBTYPE wType;
BYTE bPrecision;
BYTE bScale;
} DBBINDING;
Поле wType определяет тип столбца или параметра, который описывается следующим образом:
typedef WORD DBTYPE;
enum DBTYPEENUM {
// Следующие значения точно соответствуют VARENUM
// при автоматизации и не могут быть использованы
// как VARIANT.
DBTYPE_EMPTY = 0, // Значение отсутствует,
// соответствующего типа С нет
DBTYPE_NULL = 1, // Значение равно NULL,
// соответствующего типа С нет
DBTYPE_I2 = 2, // Двухбайтовое целое со знаком,
// соответствует С типу short
DBTYPE_I4 = 3, // Четырехбайтовое целое со знаком,
// соответствует С типу long
DBTYPE_R4 = 4,
DBTYPE_R8 = 5, // Вещественное двойной точности,
// соответствует С типу Double
DBTYPE_CY = 6, // Тип для значения Cyrrency
DBTYPE_DATE = 7, // Тип для значения даты
// (дата хранится в виде вещественного числа:
// целочисленная часть определяет дату,
// а дробная - время)
DBTYPE_BSTR = 8, // Указатель на строку BSTR
DBTYPE_IDISPATCH = 9, // Указатель на интерфейс
// IDispatch
DBTYPE_ERROR = 10, // 32-битовый код ошибки
DBTYPE_BOOL = 11, // Для логического значения
DBTYPE_VARIANT = 12, // Для значения VARIANT
DBTYPE_IUNKNOWN = 13, // Указатель на интерфейс
// IUnknown
DBTYPE_DECIMAL = 14,
DBTYPE_UI1 = 17, // Однобайтовое беззнаковое целое,
// соответствует С типу byte
DBTYPE_ARRAY = 0x2000,
DBTYPE_BYREF = 0x4000,
DBTYPE_I1 = 16,
DBTYPE_UI2 = 18,
DBTYPE_UI4 = 19,
// Следующие значения точно соответствуют VARENUM
// при автоматизации, но не могут быть использованы
// как VARIANT.
DBTYPE_I8 = 20,
DBTYPE_UI8 = 21,
DBTYPE_GUID = 72, // Для уникального
идентификатора GUID
DBTYPE_VECTOR = 0x1000,
DBTYPE_FILETIME = 64,
DBTYPE_RESERVED = 0x8000,
// Следующие значения недопустимы в VARENUM для OLE.
DBTYPE_BYTES = 128,
DBTYPE_STR = 129,
DBTYPE_WSTR = 130,
DBTYPE_NUMERIC = 131,
DBTYPE_UDT = 132,
DBTYPE_DBDATE = 133,// Для даты, определяемой
// как структура
// Typedef struct tagDBDATE {
// SHORT year;
// USHORT month;
// USHORT day;
// } DBDATE;
DBTYPE_DBTIME = 134,
DBTYPE_DBTIMESTAMP = 135 // Для даты и времени,
// определяемых как структура
// Typedef struct tagDBTIMESTAMP {
// SHORT year;
// USHORT month;
// USHORT day;
// USHORT hour;
// USHORT minute;
// USHORT second;
// ULONG fraction;
} DBTIMESTAMP;
DBTYPE_HCHAPTER = 136
DBTYPE_PROPVARIANT = 138,
DBTYPE_VARNUMERIC = 139
};
Объекты COMMAND
Перед использованием объекта Command следует определить, поддерживается ли данный объект. Для этого с помощью метода QueryInterface следует запросить интерфейс IDBCreateCommand объекта "сеанс".
Объект Command должен реализовывать следующие интерфейсы:
Для создания команды вызывается метод IDBCreateCommand::CreateCommand объекта "сеанс".
Например:
ICommandText pICommandText;
HRESULT hr= pIDBCreateCommand-> CreateCommand(
NULL, // если есть агрегирование, то указатель
// на управляющий IUnknown
IID_ICommandText, // Запрашиваемый интерфейс
(IUnknown**)& pICommandText); // Указатель
// на запрашиваемый интерфейс
Текст, выполняемый командой, устанавливается при вызове метода ICommandText::SetCommandText. При этом указывается уникальный идентификатор GUID синтаксиса команды (например, DBGUID_SQL (только для версий OLE DB начиная с 1.5)).
Например:
pICommandText->SetCommandText(DBGUID_SQL,
"SELECT * FROM TBL1");
Для выполнения команды вызывается метод ICommand::Execute (этот метод наследуется интерфейсом ICommandText).
Например:
ULONG ulRs=0;
IRowset** ppRowsets=NULL;
HRESULT hr= pICommandText->Execute (
NULL, // если есть агрегирование, то указатель
// на управляющий IUnknown
IID_IRowset, // Запрашиваемый интерфейс
NULL, // Указатель на структуру типа
// struct DBPARAMS {
// void *pData;
// DB_UPARAMS cParamSets;
// HACCESSOR hAccessor;
//};
ulRs, // Количество строк, на которые
// воздействовала команда INSERT, UPDATE
//или DELETE
(IUnknown**)& ppRowsets); // Указатель на
// указатели наборов данных
До выполнения команды можно определить поведение создаваемого результирующего набора вызовом метода ICommandProperties::SetProperties.
Для многократного выполнения запроса и при использовании параметров следует вызвать метод ICommandPrepare::Prepare, а затем определить параметры вызовом метода ICommandWithParameters::SetParameterInfo.
Если в результате выполнения команды возвращается несколько результирующих наборов, то используется метод IMultipleResults::GetResult.
Объекты TRANSACTION
Применение OLE DB позволяет поддерживать простые, вложенные и распределенные транзакции.
Объект Session для работы с транзакциями поддерживает следующие интерфейсы:
Объект Transaction позволяет реализовывать более широкие возможности управления транзакциями, поддерживая следующие интерфейсы:
Технология Active Data Objects (ADO) - это программное расширение технологии активных серверных страниц ASP, реализованное в Web-сервере Internet Information Server (IIS) компании Microsoft с целью организации подключений к базам данных. В технологии ADO поддерживаются следующие основные функции:
Основными преимуществами технологии ADO является простота использования, высокая скорость, небольшие потребности в оперативной памяти и незначительные затраты дисковой памяти.
Объектная модель ADO определяет набор (коллекцию) программируемых объектов, которые могут применяться с Visual Basic, Visual C++, VBScript. Объектная модель ADO разработана для выполнения большинства особенностей интерфейса OLE DB.
Если при помощи интерфейса ODBC операционная система осуществляет, как правило, доступ к реляционным БД, использующим структурированный язык запросов SQL, то интерфейс OLE DB (Object Linking and Embedding for DataBases) является более универсальной технологией для доступа к любым источникам данных.
В интерфейс OLE DB включен механизм провайдеров, под которыми понимаются поставщики данных, находящиеся в надстройке над физическим форматом данных. Их называют сервис-провайдерами, они помогают объединять в однотипную совокупность объекты, связанные с разнообразными источниками данных.
Интерфейс OLE DB может использовать для доступа к источникам данных интерфейс ODBC. В этом случае применяется провайдер OLE DB для доступа к ODBC-данным. Таким образом, интерфейс OLE DB не заменяет интерфейс ODBC, а позволяет организовать доступ к источникам данных через различные интерфейсы, и в том числе через ODBC.
ADO содержит семь объектов, которые инкапсулируют в себе большинство операций с базой данных - Connection (соединение), Command (команда), Parameter (параметр), Recordset (набор данных), Field (поле), Property (свойство) и Error (ошибка), - а также четыре набора объектов (коллекции) - Fields (поля), Properties (свойства), Parameters (параметры) и Errors (ошибки).
Объект Connection устанавливает коммуникационную связь между приложением и источником данных, позволяет выполнять команды. Класс Connection включает следующие методы:
Объект Command. Команда, направляемая в базу данных, как правило, представляет собой SQL-команду или вызов некоторой хранимой процедуры. Объект Command может настраиваться с помощью параметров, задаваемых посредством создания коллекции объектов класса Parameter. Класс Command включает следующие методы:
Коллекция Parameters. Содержит любые параметры, которые используются вместе с данным объектом Command. Класс Parameters содержит следующие методы:
На некоторый объект Parameter в коллекции Parameters можно ссылаться, используя его порядковый номер или значение, присвоенное свойству Name этого объекта.
Объект Recordset представляет набор записей, полученный в результате выполнения запроса в базе данных. При создании объекта Recordset указатель текущей записи устанавливается на его первую запись (если таковая имеется), а свойствам BOF и EOF присваивается значение FALSE. Если никаких записей нет, то свойству Recordcount присваивается значение 0, а свойствам BOF и EOF - значение TRUE. Класс Recordset включает следующие методы:
Коллекция Fields. Объект Recordset содержит коллекцию Fields, состоящую из объектов класса Field, каждый из которых соответствует столбцу данных объекта Recordset. Как и при работе с объектами Parameter, на отдельный объект Field в коллекции Fields можно ссылаться, используя его порядковый номер или имя.
Рассмотрим возможность соединения с базой данных из языков сценариев. ASP-сценарий, работающий с базой данных, в первую очередь должен подключиться к ней. После этого можно выполнять инструкции SQL и создавать наборы записей. Существует специальный объект Connection, управляющий подключением к базе данных.
Объект Connection позволяет устанавливать сеансы связи с источниками данных; он обеспечивает функционирование механизма для инициализации и установления соединения, выполнения запросов и использования транзакций.
Создание соединения с базой данных включает следующие шаги:
Dim cn
Set cn=Server.CreateObject("ADODB.Connection")
cn.provider "MSDASQL"
Сослаться на базу данных SQL Server из ASP-сценария возможно двумя способами:
В первом случае созданный заранее источник данных с именем mystud используется для соединения с базой данных следующим образом:
cn.open "mystud","user1","123"
Здесь для метода Open указывается DSN базы данных, имя пользователя и пароль.
Во втором случае строки соединения содержат всю информацию, необходимую ADO для того, чтобы создать соединение с внешним источником данных. Пример такой строки:
"uid=user1;driver={SQL Server};server=its; database=basa_user1"
Строка создает соединение с базой данных basa_user1 на MS SQL Server с именем its, используя драйвер для SQL Server и имя пользователя user1. Если нужен пароль, то необходимо добавить подстроку pwd=<пароль>:
"uid=user1;pwd=123;driver{SQL Server}; server=its; database=basa_user1"
Такая строка подойдет для соединения с базой данных в тексте сценария вместо источника данных:
cn.open "uid=user1;driver={SQL Server}; server=its; database=basa_user1"
или
cn.open "uid=user1; pwd=123; driver={SQL Server}; server=its;database=basa_user1"
Опустив некоторые подробности работы с API-интерфейсом, приведем небольшой фрагмент сценария на языке VBScript для серверной стороны, выполняющий выборку и изменение информации в базе данных учебного примера.
Dim cn
Dim rst
Set cn=Server.CreateObject("ADODB.Connection")
Set rst=Server.CreateObject("ADODB.Recordset")
cn.open "uid=user1;pwd=123;
driver={SQL Server};
server=its;database=basa_user1"
rst.open "SELECT название, тип, цена
FROM товар", cn
do until rst.eof
response.write rst.fields(0)
response.write rst.fields(1)
response.write rst.fields(2) & "<br>"
rst.movenext
loop
Для создания динамического запроса изменения данных на сервере воспользуемся объектом Command с параметрами.
Dim cn
Dim rst
Dim cmd
Set cn=Server.CreateObject("ADODB.Connection")
Set rst=Server.CreateObject("ADODB.Recordset")
Set cmd=Server.CreateObject("ADODB.Command")
cn.open "uid=user1;pwd=123;
driver={SQL Server};
server=its;database=basa_user1"
Set cmd.ActiveConnection=cn
Dim prm1, prm2
' создание параметров
prm1=Server.CreateObject("ADODB.Parameter")
prm2=Server.CreateObject("ADODB.Parameter")
' определение динамического запроса
cmd.CommandText="update товар set цена=?
where название=?"
cmd.CommandType=adCmdText
cmd.Prepared=True
' определение параметра, соответствующего
' первому знаку ? в SQL-запросе
Set prm1=сmd.CreateParameter("par1",
adInteger, adParamInput, , 160)
' определение параметра, соответствующего
' второму знаку ? в SQL-запросе
Set prm2=сmd.CreateParameter("par2", adChar,
adParamInput,20 , "Трюфели" )
' добавление параметров в коллекцию параметров
cmd.Parameters.Append prm1
cmd.Parameters.Append prm2
' выполнение SQL-запроса
cmd.Execute
Продолжительность: 2 часа (90 мин.)
Выполнение операций INSERT, SELECT, UPDATE и DELETE, используемых соответственно для добавления, выборки, изменения и удаления данных, не вызывает особых затруднений. Пользователь может написать запрос непосредственно в окне Query Analyzer и выполнить его. Тем не менее, основными клиентами БД являются приложения, специально созданные для выполнения определенных задач. Эти приложения часто реализуют сложные операции, требующие использования множества команд.
Чтобы выполнить такие операции, приложение отсылает на сервер одну или более команд, которые там выполняются. В ответ сервер отправляет клиенту (приложению) результат обработки запроса. Этим результатом может быть как сообщение об успешном завершении выполнения команды, занимающее всего несколько байт, так и огромный массив данных, включающий тысячи строк и занимающий несколько мегабайт. Клиент может обработать полученный результат и на основе своего полученного результата отослать серверу новый запрос.
В рассмотренной ситуации логика обработки данных реализована на клиенте. Сервер просто принимает набор инструкций и выполняет их. При написании приложения разработчик должен позаботиться о разработке запросов, корректно работающих с данными и выполняющих все нужные действия.
Предложенный подход к обработке данных имеет несколько недостатков. Например, если нужно изменить логику обработки данных, то следует изменять исходный код программы, после чего заново компилировать ее и распространять всем пользователям. Кроме того, если одна и та же логика обработки данных используется в нескольких приложениях, то в худшем случае для каждого из этих приложений нужно будет повторять процесс разработки запросов, а в лучшем -переносить код из уже работающего приложения. Также следует обратить внимание на сам процесс взаимодействия сервера и клиента. По-видимому, алгоритмы обработки данных будут реализованы в виде набора блоков команд, поочередно отправляемых на сервер. После выполнения блока команд приложение получает определенный результат, после обработки которого, решается, какой следующий блок и с какими параметрами должен быть выполнен. В некоторых ситуациях обмен между клиентом и сервером наборами команд и результатами может занимать много времени и генерировать большой сетевой трафик, что отрицательно сказывается на работе приложения в целом и на работе других пользователей сети.
В данном контексте необходимо сказать о безопасности. Для выполнения обработки данных пользователь должен иметь соответствующие права доступа. Предполагается, что эти права будут использованы приложением для доступа к данным. Однако нельзя быть до конца уверенным, что пользователь не сможет обратиться к данным напрямую, например, с помощью Query Analyzer, и выполнить неразрешенные действия. Нельзя быть уверенным и в том, что команды, отправляемые приложением, осуществляют верные действия. Ошибка разработчика при создании запроса может иногда привести к повреждению данных. Кроме того, нельзя не учитывать, что злоумышленник или тот же разработчик способны изменит код запроса для получения несанкционированного доступа к данным или для их повреждения, или даже уничтожения.
Все сказанное выше демонстрирует недостатки подхода к разработке систем, когда логика обработки данных реализуется на клиенте. Описанные проблемы могут быть решены за счет переноса алгоритмов обработки данных на сервер. В этом случае приложение просто сообщает серверу, какой именно набор команд необходимо выполнить. Дополнительно могут быть указаны параметры, которые в зависимости от реализации алгоритма будут влиять на ход выполнения процесса обработки данных. При этом приложение сможет получать только конечный результат выполнения. Промежуточные результаты будут обработаны сервером, что позволяет снизить сетевой трафик. Этот набор команд Transact-SQL, сохраненных специальным образом на сервере и выполняемых как одно целое, называется хранимой процедурой (англ. stored procedure).
Использование хранимых процедур позволяет снизить стоимость сопровождения системы и дает возможность избавиться от необходимости изменять клиентские приложения. Если понадобится изменить логику обработки данных, чтобы она отразилась для всех приложений сети, количество которых может насчитывать десятки и сотни, то достаточно будет изменить только хранимую процедуру.
Кроме того, использование хранимых процедур также позволяет значительно повысить безопасность данных. Приложение или пользователь получают лишь специальное право на выполнение хранимой процедуры, которая и будет обращаться к данным. Доступа же к самим данным пользователь не получает. В хранимой процедуре можно реализовать проверки на правильность выполняемых изменений, что обеспечит логическую целостность данных. Также можно реализовать проверки на права пользователя выполнять те или иные действия.
В SQL Server 2000/2005 различают несколько типов хранимых процедур:
Созданию хранимой процедуры предшествует этап предварительной работы, в ходе которой необходимо найти решение следующих задач.
Определение типа создаваемой хранимой процедуры. Необходимо решить, будет ли процедура временной или пользовательской. Кроме этого, можно создать свою собственную системную хранимую процедуру, назначив ей имя с префиксом sp_ и поместив ее в системную базу данных master. Такая процедура будет доступна в контексте любой базы данных локального сервера.
Планирование прав доступа. При создании хранимой процедуры следует учитывать, что она будет иметь те же права доступа к объектам базы данных, что имеет создавший ее пользователь. Таким образом, выдавая пользователям права на выполнение процедуры, следует учитывать, что хранимая процедура будет иметь права доступа не того пользователя, который ее запускает, а того, кто ее создал. Более того, хранимая процедура наследует некоторые параметры, которые были установлены во время ее создания. В частности, если во время создания процедуры был разрешен прямой доступ к системным таблицам, то процедура будет всегда иметь возможность обращения к системным данным, даже если впоследствии он будет запрещен.
Определение параметров хранимой процедуры. Подобно процедурам, входящим в состав большинства языков программирования, хранимые процедуры могут иметь входные и выходные параметры. SQL Server позволяет определить для одной хранимой процедуры до 1024 параметров. Полученные процедурой параметры могут использоваться как обыкновенные переменные Transact-SQL, участвуя в любых операциях наравне с другими переменными. Часто хранимая процедура возвращает какой-то результат. В SQL Server имеется несколько способов передачи хранимой процедурой результата своей работы. При разработке процедуры необходимо решить, какой именно метод будет использоваться, в частности, возможна передача результата через параметры хранимой процедуры. Для того чтобы вернуть значение с помощью параметра, необходимо указать ключевое слово OUTPUT при его определении.
Разработка кода хранимой процедуры. Код процедуры может содержать последовательность любых команд Transact-SQL, включая вызов других хранимых процедур.
Включение создаваемой процедуры в группу. Для удобства управления процедурами вы можете группировать логически однотипные хранимые процедуры. Для этого всем процедурам группы при создании дают одинаковые имена, но разные идентификационные номера. Таким образом, получается несколько хранимых процедур, которые обозначаются следующим образом: <имя_процедуры>;<идентификационный_номер>.
Ссылка на группу осуществляется по имени процедур. Например, в группе процедур ##proced отдельные хранимые процедуры могут иметь имена ##proced;1, ##proced;2 и т. д. Достоинство такого подхода заключается в том, что вы можете одновременно управлять сразу всей группой процедур. При этом, например, можно удалить сразу все процедуры, входящие в состав группы. Когда все эти вопросы решены, можно приступать непосредственно к созданию хранимой процедуры. Как и большинство объектов SQL Server, хранимую процедуру можно создать тремя способами:
Рассмотрим процесс создания хранимой процедуры средствами Transact-SQL с помощью команды CREATE PROCEDURE, имеющей следующий синтаксис:
CREATE PROC[EDURE] procedure_name [; number]
[ {@parameter datatype} [VARYING] [= default] [OUTPUT]][,...n] [WITH {RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]
[FOR REPLICATION] AS sql_statement [ ,...n ]
Рассмотрим аргументы команды создания хранимой процедуры.
В момент создания хранимой процедуры система осуществляет только проверку синтаксиса команд. Поэтому можно создавать хранимые процедуры в любом порядке, не особенно заботясь о том, что некоторые упоминаемые в них объекты (в том числе и другие хранимые процедуры) пока еще не существуют. Проверка правильности ссылок производится только в процессе компиляции хранимой процедуры, которая выполняется непосредственно перед ее выполнением.
При вызове одной хранимой процедуры из тела другой образуются так называемые вложенные процедуры (nested procedure). При работе с такими процедурами следует учитывать ограничение SQL Server 2000 на количество уровней вложенности — 32. Для определения текущего уровня вложенности можно воспользоваться переменной @@NESTLEVEL.
Существует два способа выполнения хранимой процедуры:
В первом варианте вызов хранимой процедуры должен быть единственной командой передаваемого на исполнение пакета. Когда вызов хранимой процедуры не является единственной командой в пакете, то присутствие команды EXECUTE обязательно. Более того, использование этой команды требуется при вызове процедуры из тела другой процедуры или триггера. Синтаксис команды EXECUTE следующий:
[[EXEC[UTE]] {[@return_status = ]
{procedure_name [;number] | @procedure_name_var}
[[©parameter = ] {value | @variable [OUTPUT] | [DEFAULT]}]
[,...n]
[WITH RECOMPILE]
Помимо указания полного названия команды — EXECUTE — также допускается и укороченный вариант — EXEC. Обычно для вызова хранимой процедуры используется упрощенный синтаксис:
EXEC procedure_name
[[@parameter = ] { value | @variable [OUTPUT] | [DEFAULT]}]
[,...n]
Указывая ключевое слово OUTPUT, вы тем самым предписываете присвоить соответствующей локальной переменной (внешней по отношению к процедуре) при завершении хранимой процедуры значение соответствующего параметра. Отметим, что значения параметров могут свободно изменяться в ходе работы процедуры. Использование слова OUTPUT при вызове процедуры разрешается только для параметров, которые были объявлены при создании процедуры с ключевым словом OUTPUT.
Когда же при вызове процедуры для параметра указывается ключевое слово DEFAULT, то для этого параметра будет использовано значение по умолчанию. Естественно, указание слова DEFAULT разрешается только для тех параметров, для которых определено значение по умолчанию.
Часто разработчикам приходится реализовывать сложные алгоритмы поддержки целостности данных. Использование ограничений целостности первичного ключа, внешнего ключа и других предоставляют разработчикам достаточно эффективные механизмы обеспечения целостности данных. Однако их бывает недостаточно. Например, с помощью упомянутых механизмов нельзя разрешить изменение данных в том случае, если в одном из столбцов находится определенное значение.
Описанная ситуация является простейшим примером того, какие проверки нередко приходится выполнять перед изменением, удалением или вставкой данных в таблицу. В реальной ситуации применяются гораздо более сложные алгоритмы предварительной проверки данных. Помимо выполнения простых проверок, при модификации данных одной таблицы иногда бывает необходимо соответствующим образом модифицировать данные одной или нескольких таблиц. Решением описанной задачи является использование триггеров.
Триггеры (triggers) в SQL Server представляют собой набор команд Transact-SQL, выполняемых автоматически при осуществлении тех или иных модификаций данных в таблице. Физически триггеры являются ни чем иным, как хранимыми процедурами специального типа. Каждый триггер связан с конкретной таблицей и запускается сервером автоматически каждый раз, когда пользователи пытаются произвести вставку, изменение или удаление данных. Триггер получает всю информацию о выполняемых пользователем изменениях в таблице. Разработчик реализовывает в триггере необходимые проверки и изменения данных в других таблицах базы данных.
Когда пользователь начинает изменение данных, сервер автоматически начинает транзакцию, в которой и выполняется триггер. В теле транзакции разработчик может реализовывать произвольные алгоритмы, которые могут выполнять как проверку, так и изменения данных. В конце концов, работа триггера сводится либо к фиксации, либо к откату транзакции, которая осуществляет изменение данных. Если выполняется откат транзакции, то попытка пользователя изменить данные отменяется. При этом также отменяются все исправления, сделанные самим триггером в различных таблицах (если они выполнялись). При фиксации транзакции производится как фиксирование изменений, выполненных пользователем, так и изменений, сделанных самим триггером.
Триггеры различаются по типу команд, на которые они реагируют:
Триггеры не запускаются при выполнении команд обработки текстовых блоков, таких как WRITETEXT, UPDATETEXT, READTEXT.
Параметры FOR, AFTER и INSTEAD OF, указываемые при создании триггера, определяют его поведение следующим образом:
Можно определить несколько AFTER - триггеров для каждой операции INSERT, UPDATE и DELETE. По умолчанию все триггеры являются AFTER -триггерами. Триггеры нельзя создавать для временных или системных таблиц. Команда создания триггера должна быть первой в пакете и применяться только к одной таблице. Ее формат следующий:
CREATE TRIGGER Имя триггера ON (Имя таблицы\Имя представления}
[WITH ENCRYPTION] -- шифрование кода триггера;
{{{FOR|AFTER|INSTEAD OF}
{[DELETE] [,] [INSERT] [,] [UPDATE]}
[NOT FOR REPLICATION] -- не для репликации;
AS sql_statement [,...n] —- тело триггера;
}
{{FOR|AFTER|INSTEAD OF}
{[INSERT] [,] [UPDATE]}
[NOT FOR REPLICATION] -- не для репликации;
AS {IF UPDATE (column) -- при изменении столбца;
[{AND|OR} UPDATE (column) [,...n]]}
IF (COLUMNS_UPDATED() {bitwise_operator} Update_bitmask)
{comparison_operator} column_bitmask [,...n]
}
sql_statement [,...n] -— тело триггера.
}
}
Вторая альтернатива команды {IF UPDATE...} используется для детального анализа изменений содержимого колонок с помощью специальных функций, битовых масок, операторов побитовой обработки, оператор сравнения и логических операторов.
Команда ALTER TRIGGER позволяет изменить параметры и тело триггера. С помощью команды DROP TRIGGER можно удалить любой триггер базы данных. Переименовать триггер можно системной хранимой процедурой sp_rename, a получить информацию о триггере можно при помощи системных хранимых процедур sp_helptext и sp_helptrigger.
Внутри триггера допускается использование любых команд языка Transact-SQL с некоторыми ограничениями. Также допускается и вызов хранимых процедур, включая системные хранимые процедуры.