Статья
Версия для печати
Обсудить на форуме
Разработка расширенных хранимых процедур для 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 байт.
SRVBINARYbinaryБинарный тип данных
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:
Материал к статье в виде проекта качать здесь.
Версия для печати
Обсудить на форуме