php ms sql server stored procedure return json string max 2034

PHP reads just 2034 characters from the returned JSON string value from stored procedure “sp_test”. Why just 2034 characters and not more?

Using PHP Version 5.6.31 with SQL Express 2017

$query = "EXEC sp_test";
$stmt = sqlsrv_prepare($GLOBALS['connIntern'], $query);
if (!sqlsrv_execute($stmt)) {
    if (($errors = sqlsrv_errors() ) != null) {
        foreach ($errors as $error) {
            echo "SQLSTATE: " . $error['SQLSTATE'] . "<br />";
            echo "code: " . $error['code'] . "<br />";
            echo "message: " . $error['message'] . "<br />";
        }
    }
    die;
}
$array = [];
if (!empty($result)) {
    while ($row = sqlsrv_fetch_array($result)) {
        array_push($array, $row[0]);
    }
}
return $array;

Stored Procedure “sp_test”:

SELECT [Tracking].[orderNumber],[Tracking].[tcpState], [Stations].[startdate],[Stations].[enddate],[Stations].[tcpState],[Stations].[name]
FROM [Tracking]
LEFT JOIN [Stations] ON [Tracking].[orderNumber] = [Stations][orderNumber]
FOR JSON AUTO

Formatted result:

[
    {
        "orderNumber": 123455,
        "tcpState": 3,
        "Stations": [
            {
                "startdate": "2011-05-06",
                "enddate": "2012-09-15",
                "tcpState": 3,
                "name": "Roger"
            },
            {
                "startdate": "2011-02-06",
                "enddate": "2012-05-15",
                "tcpState": 4,
                "name": "Hans"
            }
        ]
    },
    {
        "orderNumber": 1566,
        "tcpState": 3,
        "Stations": [
            {
                "startdate": "2011-06-06",
                "enddate": "2012-08-15",
                "tcpState": "6",
                "name": "Mike"
            },
            {
                "startdate": "2011-03-06",
                "enddate": "2012-03-15",
                "tcpState": "6",
                "name": "Tom"
            }
        ]
    }
]

Answer

Found the issue

if (!empty($result)) {
while ($row = sqlsrv_fetch_array($result)) {
    array_push($array, $row[0]);
}

I thought i get the whole string in one row but i have to merge it.Like this:

if (!empty($result)) {
        while ($row = sqlsrv_fetch_array($result)) {
            array_push($array, $row);
        }
    }

    if(count($array) > 0){
        foreach ($array as $json){
            $jsonPiece = $json[0];
            $jsonString .= $jsonPiece;
        }
    }

Advertisement

Answer