Разработка внешних хранимых процкдур

Writing MS SQL Server Extended Stored Procedures with Delphi

Microsoft SQL Server 6.5 и 7 обладают мощной способностью делать функции DLL доступными в виде хранимых процедур. Microsoft называет их расширенными хранимыми процедурами. Если вы прочитали эту статью, вы знаете, что такое расширенные хранимые процедуры, что вы можете с ними делать и как их установить на SQL-сервер. Вы также сможете использовать написанную мной объектно-ориентированную структуру, которая чрезвычайно упрощает написание расширенных хранимых процедур в Delphi.

Я предполагаю, что вы знакомы с SQL Server и концепцией хранимых процедур. Код и примеры в этой статье применимы как к SQL Server 6.5, так и к SQL Server 7.

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

Расширенные хранимые процедуры (впоследствии называемые xp) являются частью Microsoft Open Data Services (ODS) для SQL Server. С ODS вы можете сделать три вещи:

1. Сделать подпрограммы в DLL доступными в виде хранимых процедур для любого пользователя SQL Server.

2. Написание серверных приложений процедур. Они похожи на XP, однако запускаются как отдельное приложение сетевого сервера и могут даже работать на другом компьютере (3-уровневом).

3. Написание шлюзов для сред, отличных от SQL Server.

На следующем рисунке представлен графический обзор архитектуры ODS.

В этой статье я обсуждаю искусство написания хранимых процедур с помощью Delphi. Технически эта DLL является частью SQL-сервера, поэтому ошибки программиста могут повредить ваш SQL-сервер, поэтому это небезопасное искусство.

Доступ к частям вашего приложения на сервере имеет некоторые преимущества, например:

1. Некоторые вещи легко написать на Delphi, но сложно или невозможно с помощью Transact SQL. Например, вы можете использовать некоторые процедуры, написанные на языке, который вы не понимаете или для которого у вас нет исходного кода, поэтому вы не можете перевести их на Transact SQL (с возможностью появления ошибок во время этого перевода). ).

2. Подпрограммы Delphi выполняются намного быстрее, чем Transact SQL. Возьмем, к примеру, численные расчеты.

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

Xp живут в DLL и поэтому могут быть написаны на любом языке, который может создавать DLL, как это может Delphi. Прежде чем вдаваться в подробности о том, как писать XP, сначала несколько примеров с точки зрения пользователя. Давайте предположим, что у нас есть xp с именем xp_incbyone1, который увеличивает заданное число на единицу. Мы можем вызвать xp_incbyone1 следующим образом:

declare
  @mynumber integer
select @mynumber = 1
exec master..xp_incbyone1 @mynumber output
select @mynumber

Оператор объявления объявляет переменную @mynumber целочисленного типа. Затем мы устанавливаем его в единицу, передаем его в XP и позволяем XP изменить его, добавляя выходные данные к параметру. Наконец, мы отображаем число с помощью оператора select, чтобы проверить, было ли оно обновлено. Результат, конечно, должен быть 2.

В этом примере у нас есть xp, который возвращает выходной параметр. Xp также может возвращать набор результатов. Пример xp_incbyone2 возвращает число в качестве набора результатов. Код для его вызова будет:

declare
 @mynumber integer

select @mynumber = 1

exec master..xp_incbyone2 @mynumber

xp_incbyone2 вернет таблицу, состоящую всего из одного столбца и одной строки, содержащей значение 1.

И xp_incbyone1, и xp_incbyone2 подробно описаны в следующем разделе, где я представляю структуру.

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

Каждая реализация XP должна делать одно и то же:

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

2. Определите столбцы для возврата набора результатов.

3. Создайте каждую запись для возврата к звонящему.

4. Настройте все выходные параметры и статусы возврата, используемые процедурой.

5. По завершении возврата результатов отправьте сообщение о завершении результатов с помощью srv_senddone с флагом состояния SRV_DONE_MORE.

6. Вернитесь из процедуры с желаемым статусом возврата Transact-SQL.

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

Шаги 2 и 3 не являются обязательными и применимы только в том случае, если вы возвращаете набор результатов.

Шаг 4 также является необязательным и применяется только в том случае, если вы возвращаете выходные параметры.

Написание XP с помощью Delphi

Программист C, желающий разрабатывать XP, должен установить инструменты разработки SQL Server 7. Эту опцию можно включить при установке SQL Server 7. В каталоге \MSSQL7\devtools\ вы найдете все необходимые заголовочные файлы и демонстрационные программы. К сожалению, Inprise не предоставила перевод этих заголовочных файлов в Delphi. Поэтому мне пришлось вручную переводить самые важные части в Delphi. Это означает, что вам не нужно устанавливать инструменты разработки SQL Server 7, если вы используете эту структуру для написания XP. Если вы хотите добавить больше частей, вам понадобится этот набор ресурсов. Или вы можете спросить меня, есть ли у меня время немного расширить структуру, чтобы покрыть недостающие части. Примечание. В предыдущей версии SQL Server инструменты разработки входили в комплект ресурсов BackOffice.

В предыдущем параграфе было упомянуто 6 шагов, которые должен выполнить каждый опыт. Фреймворк упрощает шаги с 1 по 4, заботясь о деталях. Вы также можете использовать типы Delphi, поскольку платформа выполняет преобразование типов между типами SQL Server и типами Delphi. Фреймворк полностью берет на себя выполнение шагов 5 и 6.

Вы используете эту структуру следующим образом:

1. Создайте объект класса TSQLXProc и реализуйте его метод Execute.

2. Напишите процедуру, которая выделяет этот объект, вызывает его метод Run и освобождает объект. Имя этой процедуры должно совпадать с именем вашей расширенной хранимой процедуры. Его вызывающий метод должен быть stdcall.

Чтобы сделать это более конкретным, давайте реализуем хранимую процедуру xp_incbyone1. Первый шаг — создать новый объект на основе TSQLXProc и реализовать его метод Execute. Его заголовок выглядит так:

type
  TXPIncByOne1 = class(TSQLXProc)
    function Execute: Boolean; override;
  end;

The Execute method looks like this:

function TXPIncByOne1.Execute: Boolean;
begin
  Params[1] := Params[1] + 1;
  Result := True;
end;

Второй шаг — написать процедуру, вызывающую этот объект. Это процедура, которую на самом деле вызывает SQL Server. Для xp_incbyone1 это выглядит так:

function xp_incbyone1(srvproc: PSRV_PROC): SRVRETCODE; stdcall;
const
  ExpectedParams = 1;
var
  xp: TSQLXProc;
begin
  xp := TXPIncByOne1.Create(srvproc, ExpectedParams);
  Result := xp.Run;
  xp.Free;
end;

Это так просто!

Давайте рассмотрим более подробно первый шаг. Единственное, что вам когда-либо понадобится сделать, это реализовать метод Execute. Эта функция возвращает True или False. Если возвращается False, вызывающему приложению или пользователю возвращается ошибка. Исключения перехватываются кодом, который вызывает ваш метод Execute, и аналогичная ошибка возвращается вызывающему приложению или пользователю.

Доступ к параметрам хранимой процедуры можно получить с помощью массива вариантов Params. Параметры нумеруются начиная с единицы. Как отмечалось ранее, SQL Server не выполняет проверку типов параметров XP. Платформа возвращает параметры как варианты, поэтому она немного более устойчива к различным параметрам, но могут возникнуть ошибки преобразования вариантов, если тип параметра не соответствует. Возможно, вы захотите использовать вызов srv_paramtype API ODS для явного получения и проверки типов параметров, но пока я не нашел в этом необходимости. Другим решением для проверки типов параметров является использование функции VarType. В таблице 1 приведен список типов данных Transact-SQL и соответствующих типов данных Delphi.

Если параметр имеет значение Null, свойство Params возвращает тип варианта Null. Аналогичным образом, если вы хотите вернуть значение Null, установите для соответствующего параметра в Params значение Null.

Давайте рассмотрим более подробно второй шаг. Этот шаг, вероятно, всегда будет одинаковым, за исключением значения константы ExpectedParams и конкретного объекта, экземпляр которого нужно создать. Эта процедура вызывается SQL Server с одним параметром: srvproc. Мы передаем этот параметр экземпляру объекта и передаем ему ожидаемое количество параметров. Если фактическое количество параметров отличается от этого, сообщение об ошибке будет отправлено обратно вызывающему приложению/пользователю. Передайте ноль, если вы не хотите проверять количество параметров, например, для поддержки переменного количества параметров.

Затем мы вызываем метод Run созданного объекта, который, в свою очередь, вызывает наш метод Execute (окруженный, например, блоком try..Exception). Наконец мы освобождаем объект.

Теперь давайте займемся XP, которая возвращает набор результатов. Заголовок такой:

type
  TXPIncByOne2 = class(TSQLXProc)
    function Execute: Boolean; override;
  end;

Тело вот такое:

function TXPIncByOne2.Execute: Boolean;
var
  myint: integer;
begin
  DescribeColumn('my column name', SRVINT4, 4, SRVINT4, 4, @myint);
  Myint := Params[1] + 1;
  SendRow;
  Result := True;
end;

И процедура вызова этого объекта такова:

function xp_incbyone2(srvproc: PSRV_PROC): SRVRETCODE; stdcall;
const
  ExpectedParams = 1;
var
  xp: TSQLXProc;
begin
  xp := TXPIncByOne2.Create(srvproc, ExpectedParams);
  Result := xp.Run;
  xp.Free;
end;

Теперь у нас есть немного более сложный метод Execute. Если мы хотим вернуть набор результатов, нам нужно описать каждую строку в результирующей таблице: имя столбца, тип назначения, длина назначения, тип источника, длина источника и указатель на исходные данные. Вы должны вызывать DescribeColumn для каждого столбца в таблице результатов. Следующий шаг — заполнить исходные данные, это задание myint. Теперь строка завершена, поэтому мы можем отправить ее на SQL Server с помощью SendRow. Вам следует подготовить исходные данные и вызвать SendRow для каждой строки в таблице результатов. И, наконец, просто верните True и выйдите. После этого SQL Server отправит клиенту всю таблицу результатов.

Процедура xp_incbyone2 по-прежнему представляет собой простой вызов объекта и выход. В остальных примерах я опущу эту процедуру.

Таблица 1: поддерживаемые типы для использования с DescribeColumn.

ODS constant	TSQL data type(s)	Delhi data type(s)
SRVVARCHAR	varchar	string
SRVCHAR	char	string
SRVINTN	tinyint, smallint, int	shortint,smallint,integer
SRVBIT	bit	Boolean
SRVDECIMAL	numeric/decimal	n/a (string)
SRVNUMERIC	numeric/decimal	n/a (string)
SRVFLTN	real, float	single, double
SRVMONEYN	smallmoney, money	n/a (integer, DBMONEY)
SRVDATETIMN	smalldatetime, datetime	TDateTime

Я реализовал две XP из образцов XP, которые Microsoft реализовала в xp.c. Первый просто копирует содержимое первого параметра во второй параметр. Второй возвращает свободное место на каждом диске, доступном на компьютере SQL Server.

Чтобы избежать конфликтов имен, я назвал первый xp xp_delphiecho вместо xp_echo. Второй называется xp_delphidisklist вместо xp_disklist. Особенно xp_echo выглядит более элегантно, чем пример программы Microsoft. Вам действительно стоит взглянуть на xp.c!

Код для xp_delphiecho:

function TXPEcho.Execute: Boolean;
begin
  Params[2] := Params[1];
  Result := True;
end;

Код для xp_delphidisklist:

function TXPDiskList.Execute: Boolean;
var
  drivename: char;
  space_remaining: Int32;
  drivenums: Int32;
  rootname: string;
  SectorsPerCluster,
    BytesPerSector,
    NumberOfFreeClusters,
    TotalNumberOfClusters: dword;

  function IsDrive(drive: char): Boolean;
  begin
    IsDrive := (drivenums and (1 shl (Ord(drive) - Ord('A')))) <> 0;
  end;

begin
  DescribeColumn('drive', SRVCHAR, 1, SRVCHAR, 1, @drivename);
  DescribeColumn('bytes free', SRVINT4, 4, SRVINT4, 4, @space_remaining);
  drivenums := GetLogicalDrives;
  for drivename := 'C' to 'Z' do
  begin
    if IsDrive(drivename) then
    begin
      rootname := drivename + ':\';
      GetDiskFreeSpace(
        PChar(rootname),
        SectorsPerCluster,
        BytesPerSector,
        NumberOfFreeClusters,
        TotalNumberOfClusters);
      space_remaining := SectorsPerCluster * NumberOfFreeClusters * BytesPerSector;
      SendRow;
    end;
  end;
  Result := True;
end;

В первых двух строках дано описание таблицы результатов. Таблица результатов состоит из двух столбцов: «диск» и «свободные байты». Далее для каждого диска мы заполняем переменные имя диска и пространство_оставшийся и отправляем строку обратно с помощью SendRow.

Подробнее о системе

Сам фреймворк находится в модуле odsxp.pas. На следующем рисунке показано, как эта структура вписывается в архитектуру ODS.

SQL Server загружает и вызывает DLL. Вы написали простой метод, который создает объект типа TSQLXProc. Вы вызываете его метод Run.

Метод Run выполняет некоторые проверки и вызывает вас обратно к написанному вами методу Execute. Когда вы закончите, вы вернетесь в Run, который в свою очередь отправит результаты обратно на SQL Server.

Установка XP на SQL Server

Весь материал этого раздела также можно найти в наборе инструментов Microsoft SQL Programmers Toolkit или в справочнике Microsoft Transact-SQL.

Установка XP различается в SQL Server 6.5 и SQL Server 7.0. Все, что работает в SQL Server 6.5, работает и в SQL Server 7.

Установка XP на SQL Server 7

Установку расширенной хранимой процедуры на SQL Server 7 можно выполнить с помощью диспетчера SQL Enterprise:

1. Откройте сервер.
2. Перейдите к пункту «Базы данных».
3. Выберите главную базу данных.
4. Щелкните правой кнопкой мыши и выберите «Новая расширенная хранимая процедура», см. рисунок ниже.
5. Укажите имя функции в DLL, а также расположение и имя самой DLL.

Установка XP на SQL Server 6.5

Когда вы скомпилировали DLL, вам необходимо установить ее в соответствующий каталог. Скопируйте файл в тот же каталог, что и стандартные файлы DLL SQL Server. Обычно это что-то вроде c:\mssql\binn, обратите внимание, что binn с двумя n, а не каталог bin с одним n, который также существует! Как и в случае с другими DLL, как только расширенная хранимая процедура DLL будет помещена в соответствующий каталог и установлены соответствующие пути, вы можете немедленно сделать ее функции доступными для пользователей. Перезапускать сервер не обязательно.

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

sp_addextendedproc 'xp_delphiecho', 'xpdelphi.dll'

Эта команда регистрирует функцию xp_delphiecho, расположенную в файле xpdelphi.dll, как расширенную хранимую процедуру SQL Server. Вы должны запустить sp_addextendedproc в основной базе данных.

Чтобы удалить отдельные расширенные хранимые процедуры, системный администратор использует системную процедуру sp_dropextendedproc.

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

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

   grant exec on xp\_delphiecho to public

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

Теперь каждый пользователь может вызвать xp_delphiecho из любой базы данных, добавив к xp_delphiecho префикс 'master..'. Например, чтобы вызвать xp_delphiecho из базы данных пабов, вы говорите:

   exec master..xp_delphiecho @paramin, @paramout output

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

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

DBCC xpdelphi(FREE)

Эта команда выгружает xpdelphi.dll, позволяя системному администратору скопировать более новую версию этого файла без выключения сервера. Вам, вероятно, понадобится эта команда довольно часто для отладки вашего XP!