Msphpsql: sqlsrv_get_field truncate long json output

[sirio3mil]

I have a store procedure that output long JSON (strlen = 4940) when I use sqlsrv_get_field it truncate return value at character 2033.

I try

$data = sqlsrv_get_field($stmt, 0, SQLSRV_PHPTYPE_STRING('UTF-8'))
echo $data

or

$stream = sqlsrv_get_field($stmt, 0)
fpassthru($stream )

or

$stream = sqlsrv_get_field( $stmt, 0, 
  SQLSRV_PHPTYPE_STREAM(SQLSRV_ENC_CHAR))
fpassthru($stream )

or

$data = '';
$stream = sqlsrv_get_field( $stmt, 0, 
                            SQLSRV_PHPTYPE_STREAM( SQLSRV_ENC_CHAR));
while( !feof( $stream))
{ 
    $data .= fread( $stream, 10000);
}
echo $data;

The last example take a long time and return an empty string

There is any workaround about it?

PHP 7.1.5 on CentOS 7 (php-fpm)
ExtensionVer 4.0.8.0

Regards

[yukiwongky]

Hi @sirio3mil ,
I was unable to reproduce this issue with the latest SQLSRV driver. Would you mind providing me a repro script?
One thing to double check is the encoding. Make sure the encoding when you insert your json string into your table is consistent with the encoding in sqlsrv_get_field.

[sirio3mil]

Hi @v-kaywon
It's not a stored JSON, I make it on the fly using FOR JSON syntax, I use UTF-8 to store and retrieve data usually. But it fails even if I try to retrieve data as a resource.
Regards

[yukiwongky]

@sirio3mil , can you try to use the latest driver to see if the problem persist?

[meet-bhagdev]

@sirio3mil Any updates on this? We had a truncation error (could be related to this). We fixed this in the recent releases. Give the latest driver a shot and let us know what you think

[sirio3mil]

I will try it today, any prevision for version 4.2 stable release?

[sirio3mil]

It doesn't work

PHP 7.1.5
SQLSRV 4.2.0-preview
ODBC 13.1.4
CentOS 7
Microsoft SQL Server 2017 (CTP2.1) - 14.0.600.250 (X64) May 10 2017 12:21:23 Copyright (C) 2017 Microsoft Corporation. All rights reserved. Developer Edition (64-bit) on Linux (CentOS Linux 7 (Core))

SP contain something like this query

declare @parts nvarchar(MAX) = (
    SELECT several_fields
    FROM one_table
    WHERE several_filters 
    ORDER BY one_field DESC
    FOR JSON PATH
)

SELECT TOP 1 several_fields
    ,JSON_QUERY(@parts) parts
FROM one_table
INNER JOIN other_table ON match_field1
    AND match_field2 
    AND match_field3 
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER

Thanks a lot

[yukiwongky]

@sirio3mil , I'm able to reproduce the issue. Let me do some investigation and get back to you.

[yukiwongky]

@sirio3mil , seems like this is because SQL Server spread a JSON string of longer than 2033 characters over multiple rows (see here for more detail). One workaround for it is to iterate through the result set and append the string retrieved from sqlsrv_get_field() together untilsqlsrv_fetch() returns NULL.

$data = '';
while (sqlsrv_fetch($stmt)) {
    $data .= sqlsrv_get_field($stmt, 0, SQLSRV_PHPTYPE_STRING('UTF-8'));
}
echo $data;

[sirio3mil]

Thanks, @v-kaywon I will test it tomorrow, but there is something wrong (or old) in this article, on SSMS 17.1 I see only one output line (with full JSON), but it's possible that SQLSRV spread it and SSMS implode it all again.

[sirio3mil]

It works!