Статья: "Разработка расширенных хранимых процедур для MS SQL Server 2000. Часть 2"

Статья

Разработка расширенных хранимых процедур для MS SQL Server 2000. Часть 2

Типы данных, определённые в ODS API.
Передача наборов записей клиентскому приложению.
Отладка расширенных хранимых процедур.
Пишем расширенную хранимую процедуру xp_GetUserList.

Типы данных, определённые в ODS API.

Ниже в таблице приведены типы данных, определённые в ODS API, и их соответствие стандартным типам данных языка SQL, поддерживаемых в MS SQL Server 2000. Думаю, что здесь всё должно быть понятно:

Тип данных, определённый в ODS API	Тип данных в MS SQL Server 2000	Описание
SRVBIGBINARY	binary	Бинарные данные. Максимальная размер 8000 байт.
SRVBIGCHAR	char	Строка символов. Максимальный размер 8000 байт.
SRVBIGVARBINARY	varbinary	Бинарные данные с переменным размером. 0 - 8000 байт.
SRVBIGVARCHAR	varchar	Строка символов с переменным размером 0 - 8000 байт.
SRVBINARY	binary	Бинарный тип данных
SRVBIT	Bit	Бит
SRVBITN	bit null	Бит разрешающие хранение значения NULL.
SRVCHAR	char	Строка символов. Максимальный размер 8000 байт.
SRVDATETIME	datetime	Тип данных для хранения: значения даты/времени в 8 байтовом формате.
SRVDATETIM4	smalldatetime	Тип данных для хранения: значения даты/времени в 4 байтовом формате.
SRVDATETIMN	datetime null	smalldatetime или datetime тип данных разрешающий хранение значения NULL.
SRVDECIMAL	decimal	Десятичные числа в диапазоне 0 - 99. Размер 2 - 17 байт (в зависимости от количества цифр).
SRVDECIMALN	decimal null	Десятичные числа в диапазоне 0 - 99. Разрешает хранение значения NULL.
SRVFLT4	real	Десятичные числа в диапазоне -3,40Е+38 - 3,40Е+38. Размер 4 байта.
SRVFLT8	float	Десятичные числа в диапазоне -1,79Е+308 1,79Е+308. Размер 4 или 8 байт (в зависимости от величины мантиссы).
SRVFLTN	real \| float null	real или float тип данных, разрешающий хранение значения NULL.
SRVIMAGE	image	Бинарные данные. Максимальный размер 2Гб.
SRVINT1	tinyint	Целые числа в диапазоне 0 - 255. Размер 1 байт.
SRVINT2	smallint	Целые числа в диапазоне -32768 - 32767. Размер 2 байта.
SRVINT4	Int	Целые числа в диапазоне -2147483648 - 2147483647. Размер 4 байта.
SRVINTN	tinyint \| smallint \| int null	tinyint, smallint, или int тип данных, разрешающий хранения значения NULL.
SRVMONEY4	smallmoney	Денежный тип данных. Обеспечивает хранение до 4-ёх цифр после десятичной точки. Диапазон значений: -214748,3648 - +214748,3647. Размер 4 байта.
SRVMONEY	money	Денежный тип данных. Обеспечивает хранение до 4-ёх цифр после десятичной точки. Диапазон значений:-922337203685477,5808 - +922337203685477,5807.Размер 8 байт.
SRVMONEYN	money \| smallmoney null	smallmoney или money, разрешающий хранение значения NULL.
SRVNCHAR	nchar	Строка символов в формате UNICODE. Размер 8000 байт (4000 символов).
SRVNTEXT	ntext	Обеспечивает хранение текстовых блоков данных в формате UNICODE длиной до 1073741823 символов.
SRVNUMERIC	numeric	Десятичные числа в диапазоне 0 - 99. Размер 2 - 17 байт (в зависимости от количества цифр).
SRVNUMERICN	numeric null	numeric тип данных, разрешающий хранение значения NULL.
SRVNVARCHAR	nvarchar	Строка символов переменной длины в формате UNICODE. Максимальный размер 8000 байт (4000 символов).
SRVTEXT	text	Обеспечивает хранение текстовых блоков данных длиной до 2147483647 символов.
SRVVARCHAR	varchar	Строка символов переменной длины. Размер 0 - 8000 байт.

Для преобразования типов данных Вы можете использовать функцию srv_convert, вот её прототип:

Код:

int srv_convert (
        SRV_PROC * 	srvproc, 
        int 		srctype, 
        void *	 	src, 
        DBINT 		srclen, 
        int 		desttype, 
        void * 		dest, 
        DBINT 		destlen 
)

srvproc[/b] - определяет тип исходных данных, которые будут конвертированы. Этот параметр может описывать любой тип данных, определённый в ODS API;
src - указатель на буфер с исходными данными, которые будут конвертированы;
srclen - длина в байтах буфера с исходными данными подлежащими конвертированию. Если этот параметр равен нулю, функция помещает значение NULL в параметр dest. Если srclen не равен нулю, то он будет игнорироваться для типов данных постоянной длины. В этом случае предполагается, что исходные данные допускают хранение значения NULL. Для типа SRVCHAR допускается указывать значение -1, обозначающее строку с завершающим нулевым символом.
destype - определяет тип данных, в которые будут конвертированы исходные данные. Этот параметр может описывать любой тип данных, определённый в ODS API;
dest - указатель на буфер, который получает преобразованные данные. Если этот параметр равен NULL, функция вызывает обработчик ошибки и возвращает -1;
destlen - длина в байтах буфера, получающего преобразованные данные. Этот параметр игнорируется для типов данных постоянной длины. Если данные преобразуются к типу SRVCHAR, значение destlen должно определять полную длину буфера, получающего данные. Допускается указывать значение -1 для строк с завершающим нулём.

Возвращаемое значение:
Если конвертирование успешно - функция возвращает длину в байтах преобразованных данных. Если функция встречает не поддерживаемый для преобразования тип данных, вызывается обработчик ошибки, определённый пользователем, устанавливается глобальный номер ошибки и функция возращает -1.

В MSDN описаны допустимые в ODS преобразования типов данных, которое я привожу и вам:

Передача наборов записей клиентскому приложению.

В этой главе объясняется, как расширенная хранимая процедура может формировать наборы данных и возвращать их клиентскому приложению. Здесь я предлагаю вам написать расширенную хранимую процедуру xp_hello, которая возвращает строку Hello world!, но уже в клиентский набор данных. Но сначала, как обычно, разберём несколько функций.
Перед отправкой записи клиентскому приложению расширенная хранимая процедура должна выполнить несколько подготовительных действий, а именно, описать:
- имя и порядковый номер каждого столбца в строке;
- тип исходных данных и тип данных, которые будут посланы в клиентский набор записей;
- связать адрес буфера отправляемых данных с номером столбца.
Для этого и предназначена функция srv_describe:

Код:

int srv_describe (
SRV_PROC *	srvproc, 
int 			colnumber, 
DBCHAR * 		column_name, 
int 			namelen, 
DBINT 		desttype, 
DBINT 		destlen, 
DBINT 		srctype, 
DBINT 		srclen, 
void * 			srcdata
)

colnumber - порядковый номер столбца. Номера столбцов начинаются с 1;
column_name - указатель на буфер, содержащий имя столбца. Этот параметр можно установить в NULL, так как имя столбца не является обязательным атрибутом;
namelen - длина в байтах имени столбца. Если строка с именем столбца заканчивается нулевым символом, значение этого параметра можно установить равным константе SRV_NULLTERM.

---------- аргументы, влияющие на посылаемые клиенту данные (destination data) -----------

destype - указывает тип данных элемента записи (т.е ячейки, образуемой на пересечении строки и столбца), посылаемых клиенту. Этот параметр должен быть указан, даже если параметр srcdata = NULL.
destlen - длина в байтах данных, которые будут посланы клиенту. Для типов данных фиксированной длины, не разрешающих хранение значения NULL, этот параметр игнорируется. Для типов данных переменной длины и типов данных фиксированной длинны, которые разрешают хранения значения NULL, параметр destlen задаёт максимальную длину передаваемых клиентскому приложению данных.

---------- аргументы, влияющие на исходные данные (source data) -----------

srctype - описывает тип исходных данных.
srclen - указывает длину в байтах исходных (source) данных. Это значение игнорируется для типов данных фиксированной длины.
srcdata - указатель на буфер с исходными данными, которые функция сопоставляет с номером столбца (задаёт параметр colnumber). Память, выделенная под буфер, не должна быть освобождена перед вызовом функции srv_sendrow. (см. ниже)

Возвращаемое значение:
В случае успешного завершения - номер столбца, с которым сопоставлены данные;
Если возникли ошибки - функция вернёт 0.

Перед отправкой данных нам необходимо описать номера всех столбцов клиентского recordset-а и связать их с адресом буфера, который содержит подготовленные для отправки клиенту данные. После этого нужно вызвать функцию srv_sendrow, которая передаст данные в клиентское приложение. Вот её прототип:
int srv_sendrow ( SRV_PROC * srvproc );
Этот фрагмент кода формирует набор записей из одной строки и одного столбца и передаёт его клиентскому приложению:
char szText[15] = "Hello World!";
srv_describe(pSrvProc, 1, "Column 1", SRV_NULLTERM, SRVBIGCHAR, 15, SRVBIGCHAR, strlen(szText), szText);
srv_sendrow(pSrvProc);
srv_senddone(pSrvProc, (SRV_DONE_COUNT | SRV_DONE_MORE), 0, 1);

Добавьте этот фрагмент в пример xp_helloworld из первой части статьи, и вы получите расширенную хранимую процедуру, которая возвращает строку Hello world! в клиентский набор данных! Но не радуйтесь, ибо это, ИМХО, самый простой случай! А как быть, если необходимо динамически формировать наборы данных? Если размер передаваемых данных заранее не известен? Каждый раз заново описывать все столбцы при помощи функции srv_describe? Ответ на все три вопроса один: НЕТ!
Для того, чтобы связать нужный столбец с новыми данными (буфер для которых должен быть к этому моменту уже выделен), необходимо вызвать функцию srv_setcoldata. Вот её прототип:

Код:

int srv_setcoldata (
SRV_PROC * 	srvproc, 
int 			column, 
void * 			data
 )

column - номер столбца, с которым будут сопоставлены данные. Столбцы нумеруются с единицы.
data - указатель на буфер с данными. Память, выделенная для этого буфера, не должна быть освобождена, пока не будет завершён вызов функции srv_sendrow.
Если для отправки данных вы выделили в памяти буфер, размер которого остаётся постоянным, а меняется только его содержимое (т.е. сами данные), то вызова функции srv_setcoldata будет достаточно, чтобы сопоставить новые данные с номером нужного столбца. Но так, скорее всего, бывает редко. Если размер отправляемых данных изменяется вместе с их содержимым, нам придется (каждый раз перед отправкой строки данных клиенту!), указывать новую длину отправляемого буфера. И делать это нужно обязательно перед тем, как будет вызвана srv_sendrow! Для этого предназначена функция srv_setcollen:

Код:

int srv_setcollen (
SRV_PROC *	srvproc, 
int 			column, 
int		 	len
 )

column - номер столбца, для которого указывается новое значение длины данных.
len - задаёт длину в байтах столбца с данными, которые будут посланы клиенту.
Следующий фрагмент кода динамически формирует набор данных, состоящий из четырёх столбцов и трёх строк разной длины, и передаёт его клиентскому приложению:

Код:

struct UserInfo{
int id;
	char *Name, *FullName, *Comment;
};

UserInfo Buff[3] = {1001, "Вася", "Вася Пупкин", "Лётчик-испытатель",
		         1002, "Николай", "Николай Иванов","Архитектор",
		         1003, "Петя", "Петя Булочкин", "Дизайнер"};


/*Здесь вместо указателя на буфер с данными задаём NULL, а длину буфера равной 0 */ 
srv_describe(pSrvProc, 1, "ID", SRV_NULLTERM, SRVINT4, 4, SRVINT4, 4, NULL);
srv_describe(pSrvProc, 2, "Name", SRV_NULLTERM, SRVBIGCHAR, 8000, SRVBIGCHAR, 0,  NULL);
srv_describe(pSrvProc, 3, "Full name", SRV_NULLTERM, SRVBIGCHAR, 8000, SRVBIGCHAR, 0, NULL);
srv_describe(pSrvProc, 4, "Comment", SRV_NULLTERM, SRVBIGCHAR, 8000, SRVBIGCHAR, 0, NULL);

/* Длину посылаемых данных и сами данные указываем каждый раз перед вызовом srv_sendrow*/
for(int k=0; k<3; k++)
	{
		srv_setcoldata(pSrvProc, 1, (void*)&Buff[k].id);

		srv_setcollen(pSrvProc, 2, strlen(Buff[k].Name));
		srv_setcoldata(pSrvProc, 2, Buff[k].Name);

		srv_setcollen(pSrvProc, 3, strlen(Buff[k].FullName));
		srv_setcoldata(pSrvProc, 3, Buff[k].FullName);

		srv_setcollen(pSrvProc, 4, strlen(Buff[k].Comment));
		srv_setcoldata(pSrvProc, 4, Buff[k].Comment);

		srv_sendrow(pSrvProc);
	}    
	
/* Сообщаем о завершении передачи данных клиенту */
srv_senddone(pSrvProc, (SRV_DONE_COUNT | SRV_DONE_MORE), 0, 1);

Отладка расширенных хранимых процедур

Техника отладки расширенных хранимых процедур ничем не отличается от отладки обычных DLL библиотек. Всё сказанное ниже относится к отладчику интегрированной среды разработки MS Visual Studio 6.0. Если Вы установили MS SQL Server 2000 на свой компьютер, сделайте следующие настройки:

Откройте панель управления службами windows и остановите сервис MS SQL Server.
Откройте проект расширенной хранимой процедуры и соберите его (F7).
Скопируйте dll библиотеку расширенной хранимой процедуры в каталог (C:Program FilesMicrosoft SQL ServerMssqlBinn).
Откройте диалоговое окно Project Setings (выполнив команды меню Project → Setings ) и перейдите на вкладку Debug. (см. рис. 1) В списке Category выберите General. В поле Executing for debug session укажите путь к исполнимому файлу MS SQL Server 2000 (или выберите его, щёлкнув на кнопке со стрелкой справа от поля ввода). По умолчанию (если вы не меняли каталог во время установки MS SQL Server 2000) путь к файлу MS SQL Server 2000 - C:Program FilesMicrosoft SQL ServerMssqlBinn.

Щёлкните кнопку <OK>, чтобы сохранить внесённые изменения.

Установите точку прерывания (F9) в исходном коде расширенной хранимой процедуры.
Выполните команду GO из меню Build → Start Debug (F5).

После запуска отладчика MS SQL Server 2000 будет выполняться в режиме обычного приложения, (а не сервиса Windows NT) при этом все диагностические сообщения будут выводиться в консольном окне. (см. рис. 2)

Вызовите расширенную хранимую процедуру из клиентского приложения. Если предыдущие пункты выполнены правильно - произойдёт срабатывание установленной вами точки прерывания, и отладчик MS Visual Studio 6.0 перейдёт в режим трассировки исходного кода.

Пишем расширенную хранимую процедуру xp_GetUserList

Вот мы и подошли к реализации нашего технического задания, о котором я говорил в первой части статьи. Теперь мы знаем достаточно, чтобы справиться с этой задачей. Вкратце напомню её:

Разработать расширенную хранимую процедуру для MS SQL Server 2000, которая получает полный список пользователей, зарегистрированных в домене, и возвращает его клиенту в виде стандартного набора записей (record set). В качестве первого входного параметра функция получает имя сервера, содержащего базу данных каталога (Active Directory), т.е имя контроллера домена. Если этот параметр равен NULL, тогда необходимо передать клиенту список локальных групп. Второй параметр будет использоваться extended stored procedure для возврата значения - результата успешной/неуспешной работы (OUTPUT параметр). Если расширенная хранимая процедура выполнена успешно, необходимо передать количество записей, возвращённых в клиентский record set, если в процессе работы не удалось получить требуемую информацию, значение второго параметра необходимо установить в -1, как признак неуспешного завершения.

Для получения списка доменных пользователей мы будем использовать только одну функцию Network Management API - NetQueryDisplayInformation. Эта функция предназначена для получения списка пользователей, групп или компьютеров, зарегистрированных в домене. Тема использования Network Management API выходит за рамки этой статьи, и поэтому я не буду подробно останавливаться на описании его возможностей. Здесь я предполагаю, что любознательный читатель сам заглянет в MSDN, чтобы поближе познакомиться со всеми возможностями, которые предоставляет Network Management API. Итак, пожалуй, можно начинать.

Перед использованием функции NetQueryDisplayInformation необходимо подключить к вашему проекту заголовочный файл Lm.h и указать имя библиотечного файла NETAPI32.lib в опциях компоновщика. На этом подготовительный этап закончен. Теперь коротко рассмотрим саму функцию. Вот её прототип:

Код:

NET_API_STATUS NetQueryDisplayInformation(
  LPCWSTR 	ServerName,
  DWORD 	Level,
  DWORD 	Index,
  DWORD 	EntriesRequested,
  DWORD 	PreferredMaximumLength,
  LPDWORD 	ReturnedEntryCount,
  PVOID* 	SortedBuffer
);

ServerName - указатель на строку с именем удалённого сервера. Если этот параметр равен NULL, функция работает с локальным компьютером;
Level - определяет тип информации, которую перечисляет функция. Доступны следующие значения:

Возвращает список учётных записей пользователей. Параметр SortedBuffer должен указывать на массив структур типа NET_DISPLAY_USER.
Возвращает список учётных записей компьютеров. Параметр SortedBuffer должен указывать на массив структур типа NET_DISPLAY_MACHINE.
Возвращает список учётных записей групп. Параметр SortedBuffer должен указывать на массив структур типа NET_DISPLAY_GROUP.

Для структуры NET_DISPLAY_USER я приведу описание только основных полей :

Код:

	typedef struct _NET_DISPLAY_USER {
  	  LPWSTR usri1_name;	 //указатель на строку, содержащую имя пользователя
  	  LPWSTR usri1_comment; //указатель на строку комментария, ассоциированную
  	  DWORD usri1_flags;            с данным пользователем
  	  LPWSTR usri1_full_name; //указатель на строку, содержащую полное имя
  	  DWORD usri1_user_id;          пользователя
  	  DWORD usri1_next_index; //индекс последней записи возвращённой функцией
	} NET_DISPLAY_USER, *PNET_DISPLAY_USER;

Index - определяет индекс первой записи, начиная с которой функция будет выводить информацию. Укажите значение 0 для перечисления информации, начиная с первой доступной записи;

EntriesRequested - задаёт максимальное число записей объектов, для которых функция возвращает информацию. В Windows 2000, а также более поздних версиях, каждый вызов NetQueryDisplayInformation способен вернуть информацию не более чем о 100 записях объектов;

PreferredMaximumLength - определяет максимальный размер в байтах выделяемого функцией буфера, на который указывает параметр SortedBuffer. Рекомендуется установить значение этого параметра равным константе MAX_PREFERRED_LENGTH;

ReturnedEntryCount - указатель на переменную, в которую функция заносит количество записей, содержащихся в буфере, на который указывает параметр SortedBuffer. Если значение этого параметра после вызова функции оказалось равно 0, значит, указан слишком большой номер индекса, для которого нет доступных записей в базе SAM;

SortedBuffer - указатель на переменную, которая получает адрес буфера выделенного функцией. Буфер содержит список записей с информацией о запрашиваемых объектах. Формат этих данных зависит от значения параметра Level, указанного при вызове функции. Так как этот буфер выделяет система, вы должны освободить его, используя функцию NetApiBufferFree. Вы должны освобождать буфер после каждого вызова NetQueryDisplayInformation, даже если она завершилась со значением ERROR_MORE_DATA.

Возвращаемые значения:
ERROR_ACCESS_DENIED - пользователь не имеет доступа к запрашиваемой информации;
ERROR_INVALID_LEVEL - неверно указан параметр Level;
ERROR_MORE_DATA - предыдущие записи, возвращённые функцией, не последние. Доступны ещё данные. Для получения следующей порции данных вызовите NetQueryDisplayInformation снова, установив параметр Index равным значению поля структуры next_index последней записи, полученной от предыдущего вызова функции.
NERR_Success - успешный вызов, все данные получены.

Вот, собственно, и всё, что я хотел рассказать о функции NetQueryDisplayInformation.
Пример расширенной хранимой процедуры GetUserList вы можете скачать здесь. Если у вас возникли вопросы - буду рад возможности ответить на них. Пишите сюда: release@kuben.elektra.ru

А я снова прощаюсь с вами, надеюсь, что не надолго.
Release.

PS:
Материал к статье в виде проекта качать здесь.

Версия для печати

Обсудить на форуме