Как выполнить такой запрос в MSSQL Server из Laravel?

Question

[К. А.]

Доброго дня! Столкнулся с такой вот проблемой:

Есть удаленный SQL Server, вроде 2012 (но это не точно), в котором есть хранимая процедура, которую необходимо использовать для авторизации в приложении на Laravel, пример запроса:

declare @Entity int, @CompName varchar(250)
exec [foo].[prcBar] 'userlogin', 'userpass', @Entity output, @CompName output
select @Entity, @CompName

В ответ получаю 2 поля - некий id и название. Соответственно при выполнении в консоли я получаю все эти данные. Но при отправке из лары я либо получаю пустой массив, либо ошибку что не объявлены переменные.

Как пробовал:
1.

$test = DB::transaction(function(){
        DB::statement('declare @Entity int, @CompName varchar(250)');
        DB::statement('exec [foo].[prcBar] ?, ?, @Entity output, @CompName output',  ['userlogin', 'userpass']);
        return DB::select('select @Entity, @CompName');
});

Получаю ошибку:

SQLSTATE[HY000]: General error: 20018 Must declare the scalar variable "@Entity". [20018] (severity 15) [exec [foo].[prcBar] 'userlogin', 'userpass', @Entity output, @CompName output] (SQL: exec [foo].[prcBar] userlogin, userpass, @Entity output, @CompName output)",

Без транзации результат такой же

2.

$test = DB::select(DB::raw('declare @Entity int, @CompName varchar(250)
                exec [foo].[prcBar] ?, ?, @Entity output, @CompName output
                select @Entity, @CompName'), ['userlogin', 'userpass']);

Получаю пустой массив

3.

$db = DB::connection()->getPdo();
$stmt = $db->prepare("
            declare @Entity int, @CompName varchar(250)
            exec [foo].[prcBar] 'userlogin', 'userpass', @Entity output, @CompName output
            select @Entity, @CompName
");
$stmt->execute();
$test = $stmt->fetchAll(\PDO::FETCH_CLASS, 'stdClass');

получаю пустой массив

Это только основные варианты, из тех, которые я пробовал. Были еще всякие комбинации с DB:raw(),DB::table()->select() и совсем отбитые варианты "методом" тыка, которые уже и не припомню (все равно они не работали).

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

Собственно вопрос - как можно выполнить такой запрос? Заранее благодарен.

Answer 1

[К. А.]

В общем, после нескольких часов пинания встроенного в laravel драйвера sqlsrv, пришел к выводу, что проблема в самом драйвере. Пошел ковырять и искать решения для работы через odbc и нашел такой пакет: techscope/laravel-sqlserver

При установке композером возникает ошибка, мол не установлен ext-odbc, установил командой (ubuntu)

sudo apt update && sudo apt install unixodbc php7.2-odbc

Далее, у меня проблема осталась, но ext-odbc точно установился ,т.к. выводился в списке подклчюенных. Списал на багулю пакета и установил командой

composer require techscope/laravel-sqlserver:dev-master --ignore-platform-reqs

После поправил в config/database.php секцию sqlsrv, добавив

/* ... */
'sqlsrv' => [
...
    'odbc_driver'   => '{ODBC Driver 17 for SQL Server}',
    'TrustServerCertificate' => 'yes',
...
],
/* ... */

Значение для `odbc_driver` смотрим командой
head -1 /etc/odbcinst.ini
То, что в квадратных скобках пихаем в конфиге в фигурные.

Ну и добавить провайдер в donfig/app.php не забываем.

После проделанных манипуляций удалось выполнить целиком весь запрос в DB::select():

test = DB::select("declare @Entity int, @CompName varchar(250)
    exec [foo].[prcBar] 'testlogin', 'testpass', @Entity output, @CompName output
    select @Entity as entity, @CompName as comp_name
");

Важный момент - надо явно указать в выборке значения `@Var as col_name` иначе в массиве будет 1 элемент с пустым ключом и последним значением

Answer 2

[Hedy]

Возможно как-то так, идея в том чтобы привязать параметры:

$entity = '';
$comp_name  '';

$db = DB::connection()->getPdo();
$stmt = $db->prepare("exec [foo].[prcBar] 'userlogin', 'userpass', ?, ?");
$stmt->bindParam(1, $entity, PDO::PARAM_INPUT_OUTPUT);
$stmt->bindParam(2, $comp_name, PDO::PARAM_INPUT_OUTPUT);
$stmt->execute();

print($entity);
print($comp_name);

скорее всего без явного указания PDO::PARAM_INPUT_OUTPUT тоже будет работать.

Comments

[К. А.]

К сожалению получаю 2 пустых строки в переменных. На всякий случай проверил еще раз запрос в БД через консоль с теми же учетными данными - все ок, отвечает как положено

[Hedy]

А если добавить out (или output) в сам запрос?

$stmt = $db->prepare("exec [foo].[prcBar] 'userlogin', 'userpass', ? out, ? out");

[К. А.]

Hedy, первое что попробовал, после вашего примера) Получил ошибку

SQLSTATE[HY000]: General error: 20018 Cannot use the OUTPUT option when passing a constant to a stored procedure. [20018] (severity 15) [exec [foo].[prcBar] 'testuser', '12345', '' output, '' output]