Поделиться через

Программирование ядро СУБД расширенных хранимых процедур

  • Статья

Область применения: SQL Server

Внимание

Эта функция будет удалена в будущей версии SQL Server. Избегайте использования этого компонента в новых разработках и запланируйте изменение существующих приложений, в которых он применяется. Вместо этого используйте интеграцию CLR.

Как работают расширенные хранимые процедуры

Принцип работы расширенной хранимой процедуры заключается в следующем.

  1. Когда клиент выполняет расширенную хранимую процедуру, запрос передается в табличном потоке данных (TDS) или формате протокола SOAP из клиентского приложения в SQL Server.

  2. SQL Server выполняет поиск библиотеки DLL, связанной с расширенной хранимой процедурой, и загружает библиотеку DLL, если она еще не загружена.

  3. SQL Server вызывает запрошенную расширенную хранимую процедуру (реализованную как функцию внутри библиотеки DLL).

  4. Расширенная хранимая процедура передает результирующий набор и возвращает параметры обратно на сервер через API-интерфейс расширенной хранимой процедуры.

В прошлом службы Open Data Services использовались для создания серверных приложений, таких как шлюзы к СУБД, отличных от SQL Server. SQL Server не поддерживает устаревшие части API Open Data Services. Единственная часть исходного API Open Data Services, поддерживаемого SQL Server, — это расширенные функции хранимой процедуры, поэтому API был переименован в API расширенных хранимых процедур.

При возникновении распределенных запросов и интеграции среды CLR потребность в приложениях API расширенных хранимых процедур была заменена.

Если у вас есть существующие приложения шлюза, вы не можете использовать opends60.dll те, которые поставляется с SQL Server для запуска приложений. Приложения шлюзов больше не поддерживаются.

Расширенные хранимые процедуры и интеграция CLR

Интеграция СРЕДЫ CLR обеспечивает более надежную альтернативу написанию логики на стороне сервера, которая была сложной или невозможной для записи в Transact-SQL. В предыдущих выпусках SQL Server расширенные хранимые процедуры (XPS) предоставили единственный механизм, доступный разработчикам приложений базы данных для написания такого кода.

С интеграцией СРЕДЫ CLR логика, которая использовалась для записи в виде хранимых процедур, часто лучше выражается как табличное значение функций, что позволяет выполнять запросы результатов функции в инструкциях, внедряя их в SELECT FROM предложение.

Дополнительные сведения см. в обзоре интеграции СРЕДЫ CLR.

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

Выполнение расширенных хранимых процедур имеет следующие характеристики:

  • Расширенная функция хранимой процедуры выполняется в контексте безопасности SQL Server.

  • Расширенная функция хранимой процедуры выполняется в пространстве процесса SQL Server.

  • поток, связанный с выполнением расширенной хранимой процедуры, тот же, что используется для клиентского соединения.

Внимание

Перед добавлением расширенных хранимых процедур на сервер и предоставлением разрешений на выполнение другим пользователям системный администратор должен тщательно проверить каждую расширенную хранимую процедуру, чтобы убедиться, что она не содержит вредоносный или вредоносный код.

После загрузки библиотеки DLL расширенной хранимой процедуры библиотека DLL остается загруженной в адресное пространство сервера, пока SQL Server не будет остановлена или администратор явно выгрузит библиотеку DLL с помощью DBCC <DLL_name> (FREE).

Расширенная хранимая процедура может выполняться из Transact-SQL в качестве хранимой процедуры с помощью инструкции EXECUTE :

EXECUTE @retval = xp_extendedProcName @param1, @param2 OUTPUT;

Параметры

@ retval

Возвращаемое значение.

@ param1

Входной параметр.

@ param2

Входной или выходной параметр.

Внимание

Расширенные хранимые процедуры предлагают улучшения производительности и расширяют функциональные возможности SQL Server. Однако, поскольку библиотека DLL расширенной хранимой процедуры и SQL Server совместно используют одно адресное пространство, процедура проблемы может негативно повлиять на функционирование SQL Server. Хотя исключения, создаваемые библиотекой DLL расширенной хранимой процедуры, обрабатываются SQL Server, можно повредить области данных SQL Server. В качестве меры предосторожности только системные администраторы SQL Server могут добавлять расширенные хранимые процедуры в SQL Server. Перед установкой эти процедуры следует тщательно протестировать.

Отправка результирующих наборов на сервер с помощью API расширенных хранимых процедур

При отправке результирующих наборов в SQL Server расширенная хранимая процедура должна вызвать соответствующий API следующим образом:

  • Функция srv_sendmsg может вызываться в любом порядке до или после всех строк (если таковые имеются srv_sendrow). Все сообщения должны отправляться клиенту до отправки состояния завершения.srv_senddone

  • Функция srv_sendrow вызывается один раз для каждой строки, отправляемой клиенту. Все строки должны отправляться клиенту до отправки сообщений, значений состояния или состояний завершения с srv_sendmsgsrv_status аргументом или аргументом .srv_senddonesrv_pfield

  • Отправка строки, которая не имеет всех его столбцов, srv_describe приводит к тому, что приложение вызывает информационное сообщение об ошибке и возвращается FAIL клиенту. В этом случае строка не отправляется.

Создание расширенных хранимых процедур

Расширенная хранимая процедура — это функция C/C++ с прототипом:

SRVRETCODE xp_extendedProcName ( SRVPROC *);

Использование префикса xp_ является необязательным. Расширенные имена хранимых процедур зависят от регистра при ссылке на инструкции Transact-SQL независимо от порядка кодовой страницы или сортировки, установленного на сервере. При создании DLL-библиотеки нужно сделать следующее.

  • Если требуется точка входа, напишите функцию DllMain .

    Эта функция является необязательной. Если он не указан в исходном коде, компилятор связывает свою собственную версию, которая ничего не делает, кроме возврата TRUE. Если вы предоставляете DllMain функцию, операционная система вызывает эту функцию при подключении потока или процесса к библиотеке DLL или отсоединяется от нее.

  • Все функции, которые вызываются за пределами DLL-библиотеки (все расширенные хранимые процедуры и функции), должны быть экспортированы.

    Вы можете экспортировать функцию, указав ее имя в EXPORTS разделе .def файла или префиксируйте имя функции в исходном коде с __declspec(dllexport)расширением компилятора Майкрософт (__declspec() начинается с двух символов подчеркивания).

Для создания DLL-библиотеки расширенной хранимой процедуры необходимы следующие файлы.

Файл Description
srv.h Файл заголовка API-интерфейса расширенных хранимых процедур
opends60.lib Импорт библиотеки для opends60.dll

Чтобы создать DLL-библиотеку расширенной хранимой процедуры, создайте проект типа «Динамическая библиотека». Дополнительные сведения о создании DLL-библиотек см. в документации по среде разработки.

Все библиотеки DLL расширенных хранимых процедур должны реализовывать и экспортировать следующую функцию:

__declspec(dllexport) ULONG __GetXpVersion()
{
   return ODS_VERSION;
}

__declspec(dllexport) — это расширение компилятора, определенное корпорацией Майкрософт. Если компилятор не поддерживает эту директиву, следует экспортировать эту функцию в DEF файл в EXPORTS разделе.

При запуске SQL Server с флагом -T260 трассировки или при запуске DBCC TRACEON (260)пользователя с правами администратора системы, а если библиотека DLL расширенной хранимой процедуры не поддерживается __GetXpVersion(), в журнал ошибок выводится следующее предупреждение (__GetXpVersion() начинается с двух подчеркивания).

Error 8131: Extended stored procedure DLL '%' does not export __GetXpVersion().

Если библиотека DLL расширенной хранимой процедуры экспортируется __GetXpVersion(), но версия, возвращаемая функцией, меньше версии, требуемой сервером, выводится предупреждение о том, что версия, возвращаемая функцией, и версия, ожидаемая сервером, выводится в журнал ошибок. Если вы получите это сообщение, вы возвращаете неверное значение или __GetXpVersion()компилируетсяе с более старой версией srv.h.

Примечание.

SetErrorModeФункция Win32 не должна вызываться в расширенных хранимых процедурах.

Длительные расширенные хранимые процедуры должны периодически вызываться srv_got_attention , чтобы процедура может завершиться, если подключение будет убито, или пакет прерван.

Чтобы выполнить отладку библиотеки DLL расширенной хранимой процедуры, скопируйте ее в каталог SQL Server \Binn . Чтобы указать исполняемый файл для сеанса отладки, введите путь и имя файла исполняемого файла SQL Server (например, C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Binn\sqlservr.exe). Сведения о аргументах см. в sqlservr приложении sqlservr.

Добавление расширенной хранимой процедуры в SQL Server

Библиотека DLL, содержащая расширенные функции хранимой процедуры, выступает в качестве расширения для SQL Server. Чтобы установить библиотеку DLL, скопируйте файл в каталог, например файл, содержащий стандартные ФАЙЛЫ DLL SQL Server (C:\Program Files\Microsoft SQL Server\MSSQL16.0.<x>\MSSQL\Binn по умолчанию).

После копирования библиотеки DLL расширенной хранимой процедуры на сервер системный администратор SQL Server должен зарегистрировать в SQL Server каждую расширенную функцию хранимой процедуры в библиотеке DLL. Это делается с помощью sp_addextendedproc системной хранимой процедуры.

Внимание

Системный администратор должен тщательно проверить расширенную хранимую процедуру, чтобы убедиться, что он не содержит вредоносный или вредоносный код, прежде чем добавлять его на сервер и предоставлять разрешения на выполнение другим пользователям. Проверяйте все данные, вводимые пользователем. Не сцепляйте входные данные пользователя перед проверкой. Никогда не выполняйте команду, построенную на основании непроверенных пользовательских входных данных.

Первый параметр sp_addextendedproc указывает имя функции, а второй параметр указывает имя библиотеки DLL, в которой находится эта функция. Необходимо указать полный путь библиотеки DLL.

Примечание.

Существующие библиотеки DLL, которые не были зарегистрированы с полным путем, не работают после обновления до SQL Server 2005 (9.x) или более поздней версии. Чтобы устранить проблему, используйте sp_dropextendedproc для отмены регистрации библиотеки DLL, а затем повторно зарегистрируйте ее с sp_addextendedproc, указанием полного пути.

Имя функции, передаваемое хранимой процедуре sp_addextendedproc, должно полностью совпадать с именем функции в DLL-библиотеке, включая регистр символов. Например, эта команда регистрирует функцию xp_hello, , расположенную в библиотеке DLL с именем xp_hello.dll, как расширенная хранимая процедура SQL Server:

sp_addextendedproc 'xp_hello', 'c:\Program Files\Microsoft SQL Server\MSSQL13.0.MSSQLSERVER\MSSQL\Binn\xp_hello.dll';

Если имя функции, указанной в sp_addextendedproc не совсем совпадает с именем функции в библиотеке DLL, новое имя зарегистрировано в SQL Server, но имя не подходит для использования. Например, хотя xp_Hello он зарегистрирован как расширенная хранимая процедура SQL Server, расположенная в xp_hello.dll, SQL Server не может найти функцию в библиотеке DLL, если вы используете xp_Hello для вызова функции позже.

-- Register the function (xp_hello) with an initial upper case
sp_addextendedproc 'xp_Hello', 'c:\xp_hello.dll';

-- Use the newly registered name to call the function
DECLARE @txt VARCHAR(33);
EXEC xp_Hello @txt OUTPUT;

Ниже приведено сообщение об ошибке:

Server: Msg 17750, Level 16, State 1, Procedure xp_Hello, Line 1
Could not load the DLL xp_hello.dll, or one of the DLLs it references. Reason: 127(The specified procedure could not be found.).

Если имя функции, указанной в sp_addextendedproc точности имя функции в библиотеке DLL, и параметры сортировки экземпляра SQL Server не учитывает регистр, пользователь может вызывать расширенную хранимую процедуру, используя любое сочетание букв нижнего и верхнего регистра имени.

-- Register the function (xp_hello)
sp_addextendedproc 'xp_hello', 'c:\xp_hello.dll';

-- The following example succeeds in calling xp_hello
DECLARE @txt VARCHAR(33);
EXEC xp_Hello @txt OUTPUT;

DECLARE @txt VARCHAR(33);
EXEC xp_HelLO @txt OUTPUT;

DECLARE @txt VARCHAR(33);
EXEC xp_HELLO @txt OUTPUT;

Если параметры сортировки экземпляра SQL Server чувствительны к регистру, SQL Server не может вызывать расширенную хранимую процедуру, если процедура вызывается с другим случаем. Это верно, даже если он был зарегистрирован точно с тем же именем и сортировкой, что и функция в библиотеке DLL.

-- Register the function (xp_hello)
sp_addextendedproc 'xp_hello', 'c:\xp_hello.dll';

-- The following example results in an error
DECLARE @txt VARCHAR(33);
EXEC xp_HELLO @txt OUTPUT;

Ниже приведено сообщение об ошибке:

Server: Msg 2812, Level 16, State 62, Line 1

Вам не нужно останавливать и перезапускать SQL Server.

Запрос расширенных хранимых процедур, установленных в SQL Server

Пользователь, прошедший проверку подлинности SQL Server, может отобразить определенные расширенные хранимые процедуры и имя библиотеки DLL, к которой принадлежит каждая из них, выполнив системную процедуру sp_helpextendedproc . Например, в следующем примере возвращается библиотека DLL, к которой xp_hello относится:

sp_helpextendedproc 'xp_hello';

Если sp_helpextendedproc выполняется без указания расширенной хранимой процедуры, отображаются все расширенные хранимые процедуры и их библиотеки DLL.

Удаление расширенной хранимой процедуры из SQL Server

Чтобы удалить каждую расширенную функцию хранимой процедуры в библиотеке DLL расширенной хранимой процедуры, системный администратор SQL Server должен запустить sp_dropextendedproc системную хранимую процедуру, указав имя функции и имя библиотеки DLL, в которой находится эта функция. Например, эта команда удаляет функцию xp_hello, расположенную в библиотеке DLL с именем xp_hello.dll, SQL Server:

sp_dropextendedproc 'xp_hello';

sp_dropextendedproc не удаляет системные расширенные хранимые процедуры. Вместо этого системный администратор должен запретить EXECUTE разрешение на расширенную хранимую процедуру общедоступной роли.

Выгрузка библиотеки DLL расширенной хранимой процедуры

SQL Server загружает библиотеку DLL расширенной хранимой процедуры сразу после вызова одной из функций библиотеки DLL. Библиотека DLL остается загруженной до завершения работы сервера или до тех пор, пока системный администратор не будет использовать инструкцию DBCC для ее выгрузки. Например, эта команда выгрузит xp_hello.dllсервер, позволяя системным администратору скопировать более новую версию этого файла в каталог без завершения работы сервера:

DBCC xp_hello(FREE);

Связанный контент