0

I have faced a interesting challenge to call MSSQL procedure from LAMP environment. I can preferrable use php 5.3 and ubuntu 12.04 (PDO and dblib driver, because of current production setup) or in worst case ubuntu 16.04 and php 7.1 (with official ms ODBC driver, and another micro service with updated environment). My challenge is to call stored procedure, which takes XML as param, and return some XML result. Connection works except I am unable to read output params.

MSSQL stored procedure looks like this (also query works as expected if runned from SQLPro for MSSQL):

DECLARE @return_value int
DECLARE @lpc_odgovor xml

EXEC @return_value =
    store_proc_name
        @lpx_plan = '',
        @lpc_odgovor = @lpc_odgovor out

SELECT
    'Return Value' = @return_value,
    'Response'=@lpc_odgovor

Response is in 2 column
1 col:
Return Value
-5 (which is some status code...

2 col:
Response
'<xml>some xml string</xml>' (what i need for further work)

Currently I am leaning more towards php 7.1 and ubuntu 16.04 due to official MS support. My latest most successfull attempt looks like this:

$connectionInfo = array(
    "Database" => "xx",
    "UID" => "x",
    "PWD" => "xx"
);

$conn = sqlsrv_connect('server, port', $connectionInfo);

$document = '<someXml></someXML>';
$myparams = [
    'lpx_plan' => $document,
    'lpc_odgovor' => '',
];

$odgovor = null;
$params = array(
    array(
        &$myparams['lpx_plan'],
        SQLSRV_PARAM_IN,
        SQLSRV_PHPTYPE_STRING('UTF-8'),
        SQLSRV_SQLTYPE_XML
    ),
    array(
        &$odgovor,
        SQLSRV_PARAM_INOUT,
        SQLSRV_PHPTYPE_STRING('UTF-8'),
        SQLSRV_SQLTYPE_XML

    )
);

$sql = "EXEC store_proc_name @lpx_plan=?,@lpc_odgovor=?;";


$result = sqlsrv_query($conn, $sql, $params);
if ($result === false) {
    echo "Error in executing statement 3.\n";
    die(print_r(sqlsrv_errors(), true));
}
$rss = array();
do {
    $rs = array();
    var_dump($result);
    while ($row = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC)) {
        array_push($rs, $row);
    }
    array_push($rss, $rs);
} while (sqlsrv_next_result($result));


var_dump($rs);

But for now without any success of retrieving output params...

I tried different variations of
How to execute a stored procedure in php using sqlsrv and "?" style parameters
http://trentrichardson.com/2011/08/10/making-sense-of-stored-procedures-with-php-pdo-and-sqlsrv/
Executing a Stored Procedure with pdo_sqlsrv
Calling stored procedure from PHP using PDO to MSSQL Server using INPUT Paramters

I read most of the relevant documentation on MS Side about specifying IN/OUT param types: https://learn.microsoft.com/en-us/sql/connect/php/how-to-retrieve-output-parameters-using-the-sqlsrv-driver

I am trying to get data for at leat one week straight, but for now without any bigger success (and even for every new keyword i came up with, I already opened majority of links and try other people solutions).

But at the other side seems that my connection is OK. I am also able to run basic SQL 'SELECT * FROM some_table WHERE ....' and I am probably not the first person trying to read any of the stored procedure response.

Can and will provide any further data if needed. What am I missing? I am open for new drivers, new ways of connection... but I am quite sure sqlsrv should work. Any advice?

Thanks in advance.

Matej Murn
  • 11
  • 2
  • Just a pedantic note: if you're using MS SQL Server then it's not a LAMP stack. The M in LAMP stands for MySQL. – ADyson Oct 18 '17 at 09:53
  • I agree. Just to be totally clear I want to say that I am making a call FROM a LAMP stack. I am running LAMP, and I need to retrieve Information from another server which runs MSSQL and other stuff not related to my stack, and I am not in possession of it. – Matej Murn Oct 18 '17 at 10:45
  • you can't make a call from a whole "stack". In your case (as in most) you make a call from an application. In this situation it appears to be a PHP application. The fact it's running on Apache, and on Linux, is largely irrelevant to the question, and the fact you're querying SQL Server means that you're not using MySQL (unless you are calling it in another part of the PHP app?), so that's probably irrelevant too, and means it's unrelated to LAMP. Sorry to be pedantic but I find it helps to be clear about the terminology and the tools you've got and how you're using (or not using) them. – ADyson Oct 18 '17 at 10:49

1 Answers1

0

After 14 days of trying all possible combinations.. this is the thing i came up with (and it is preety straight forward if you are familiar with all details about other experimental drivers and cross influence of drivers, versions, etc,...)

Lessons learned:

  1. For any MS integration make isolated microservice
  2. if you can (because in most cases update is hard to do..) update php and ubuntu versions
  3. DO NOT f*** use sored procedure to work like API!!!!! (in my case this external provider use it this way)
  4. Read point 3 again and remember!!!!!!

So here is my solution: Requirements:

  • Ubuntu 16.04
  • php >7.0 (because MS have some "official" support)

  • php modules odbc, sqlsrv, pdo_sqlsrv

    $connectionInfo = [
    "Database" => "xxx",
    "UID" => "xx",
    "PWD" => "xxx"
];
$conn = sqlsrv_connect('xx, xx', $connectionInfo);

$document = 'some xml';
$myparams = [
    'lpx_plan' => $document,
    'lpc_odgovor' => '',
];

$params = [
    [
        &$myparams['lpx_plan'],
        SQLSRV_PARAM_IN,
        SQLSRV_PHPTYPE_STRING('UTF-8'),
        SQLSRV_SQLTYPE_XML
    ],
    [
        &$myparams['lpc_odgovor'],
        SQLSRV_PARAM_INOUT,
        SQLSRV_PHPTYPE_STRING('UTF-8'),
        SQLSRV_SQLTYPE_XML

    ]
];

$sql = "EXEC call_procedure_name @lpx_plan=?,@lpc_odgovor=?;";
var_dump($conn);

$result = sqlsrv_query($conn, $sql, $params);
if ($result === false) {
    echo "Error in executing statement 3.\n";
    die(print_r(sqlsrv_errors(), true));
}

sqlsrv_next_result($result);
var_dump($myparams['lpc_odgovor']);
var_dump($myparams['lpx_plan']);
Matej Murn
  • 11
  • 2