SQL

Вызов API средствами T-SQL

Время прочтения: 5 мин.

Возможно ли обратиться к API только с помощью T-SQL? Спойлер — да! Какие настройки при этом нужны на MS SQL сервере и пошаговое описание процесса описаны в статье.

Возможно ли? Да. Лукавить не буду, это не очень распространённая ситуация, и обычно работа с API происходит из других сред, но может возникнуть потребность обратиться к другому приложению по API именно из SQL кода.

Необходимый подготовительный бэкграунд на сервере. Для возможности вызова методов API на SQL сервере глобальные параметры конфигурации ‘show advanced options’ и ‘Ole Automation Procedures’ должны быть равны 1. По умолчанию они выключены – для их установки можно выполнить следующие SQL команды:

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'Ole Automation Procedures', 1;
RECONFIGURE;

Первая команда задаёт значение параметра конфигурации, вторая вводит это параметр в действие. NB! Возможность изменения глобальных настроек сервера предоставлена предопределённым ролям sysadmin и serveradmin. Второй параметр конфигурации позволяет включить возможность создание экземпляров объектов OLE-автоматизации, за счёт которых в нашем кейсе будет осуществляться запрос к API.

План:

  1. Создаём экземпляр объекта и сохраняем его токен
  2. С использованием токена открываем соединение с удалённым источником
  3. С использованием токена отправляем запрос
  4. С использованием токена получаем ответ на запрос
  5. Уничтожаем токен и работаем с полученными данными

Шаг 1. Создание экземпляра объекта

DECLARE @Object as int

Объявляем переменную для хранения токена экземпляра объекта. Далее вызываем хранимую процедуру sp_OACreate

EXEC sp_OACreate 'MSXML2.ServerXMLHTTP', @Object out

Создаём экземпляр объекта, у метода есть out параметр, значение (токен) которого мы сохраняем и который в дальнейшем будем использовать при вызове других методов. Если учесть возможность возникновения исключения, код лучше модифицировать следующим образом:

DECLARE @ReturnCode as int
EXEC @ReturnCode = sp_OACreate 'MSXML2.ServerXMLHTTP', @Object out
IF @ReturnCode <> 0 GOTO EXCEPTION

При успешном выполнении в переменную @ReturnCode будет записан 0, и соответственно число, отличное от 0, при возникновении исключения. В последнем случае можно вызвать sp_OAGetErrorInfo для получения информации об ошибке, но пропишем в одном месте, чтобы с помощью перехода GOTO EXCEPTION логировать исключения из любого места нашего кода

Шаг 2. Открытие соединения

EXEC @ReturnCode = sp_OAMethod @Object, 'open', NULL, 'get', @url, 'false', 'login', 'password'
IF @ReturnCode <> 0 GOTO EXCEPTION

Сигнатура метода sp_OAMethod: первым аргументом передаётся созданный на предыдущем этапе токен, вторым указывается метод – в нашем случае OPEN открывает HTTP соединение, третьим аргументом указываются выходные значения, в нашем кейсе NULL, а дальше указываются параметры метода: тип запроса (GET, POST, PUT, DELETE…), url адрес API метода, указание на то, синхронный или асинхронный запрос будет выполняться (в нашем кейсе FALSE – запрос асинхронный, в противном случае пришлось бы использовать WaitForResponse), далее указывается логин и пароль авторизации на стороне API (в случае открытого API эти параметры можно опустить)

На этом шаге есть возможность установить дополнительные заголовки запроса с помощью той же хранимой процедуры sp_OAMethod, но в качестве второго аргумента указывая метод SetRequestHeader:

EXEC @ReturnCode = sp_OAMethod @Object, 'SetRequestHeader', NULL, 'Content-Type', 'application/json'

Шаг 3. Отправление запроса

Также используем sp_OAMethod, но уже с методом Send

EXEC @ReturnCode = sp_OAMethod @Object, 'Send', NULL

В нашем кейсе мы рассматриваем пример GET запроса, в случае же POST запроса параметр в теле запроса можно передать дополнительным аргументом этого метода

EXEC @ReturnCode = sp_OAMethod @Object, 'Send', NULL, '{"json object key": "json object value"}'

Шаг 4. Получение ответа

Создаём переменную для записи ответа сервера. Единственный момент – не получится задать ей значение MAX, при этом не удастся получить ответ. Максимальное ограничение в 8000 символов

DECLARE @ResponseText as varchar(8000)
EXEC @ReturnCode = sp_OAMethod @Object, 'ResponseText', @ResponseText output

Шаг 5. Уничтожение токена

Вуаля! Требуемые данные из API получены – можно приступать к парсингу JSON ответа и дальнейшей обработке данных. Но не забываем освободить ресурсы OLE-объекта, которые нам больше не понадобятся с помощью процедуры sp_OADestroy:

EXEС sp_OADestroy @Object

Подводные камни. В правах – для вызова методов OLE автоматизации требуется членство в предопределённой роли сервера sysadmin либо разрешение EXECUTE хранимых процедур OLE объектов.

Обработка ошибок. Приведённые выше шаги были написаны с учётом получения кода ответа метода (записывали в переменную в @ReturnCode), и на первом шаге было показано, что можно при получении кода ошибки обрабатывать исключение переходом GOTO EXCEPTION. Проверка кода ответа и переход в случае значения, отличного от 0, подразумевался в каждом методе OLE автоматизации, но для наглядности был опущен. Так вот, при возникновении исключения можно записывать информацию об этом в табличную переменную

EXCEPTION:
    BEGIN
        DECLARE @Exception TABLE
        (
                Error binary(4),
                Source varchar(8000),
                Description varchar(8000),
                HelpFile varchar(8000),
                HelpID varchar(8000)
        )

        INSERT INTO @Exception EXEC sp_OAGetErrorInfo @Object
    END

В статье мы рассмотрели случай обращения к API непосредственно из T-SQL кода. Конечно, от задачи к задаче можно предложить для каждого случая своё оптимальное решение. Например, можно обращаться к API из CLR встроенной процедуры или функции (написанной как вариант на C# и развёрнутой на SQL сервере). Это может быть интересно в случаях детальной валидации ответа API, например когда может потребоваться использование регулярных выражений или каких-то специфичных функций, реализованных на платформе .NET, но недоступных для T-SQL, или когда обработка ответа предполагает сложные математические вычисления, а CLR в этом плане как правило обладает большей производительностью. А при некоторых условиях оптимальным вариантом может быть написание SISS пакета для подтягивания данных из внешних источников. Здесь была рассмотрена возможность отправки HTTP запроса и получения ответа от API только средствами T-SQL. В общем, принять к сведению, и использовать обдуманно.

Советуем почитать