SQL Query return Json

Ask about general coding issues or problems here.

Moderators: egami, macek, gesf

Post Reply
dqninh
New php-forum User
New php-forum User
Posts: 5
Joined: Mon May 24, 2021 8:38 am

Thu Jun 03, 2021 5:55 am

Hi All,

I am developing a small project at home using XAMPP php 7.45 and MS SQL 2019.

I have a store procedure that return the result set as a single record/single field in a form of Json string as below:

SELECT V.* FROM viethongadm.ClassPerson CP
INNER JOIN viethongadm.PersonActive PA ON(CONVERT(nvarchar(128),PA.PersonActiveId) = @usrGuid AND CP.PersonId = PA.personId)
INNER JOIN viethongadm.ClassPerson CP2 ON(CP2.classId = CP.ClassId)
INNER JOIN viethongadm.V_Student V ON(V.PersonId = CP2.PersonId)
FOR JSON PATH, ROOT('AllRecord')

My PHP and the json_decode work fine if the return json above return a single record that is less than 2032 bytes. If the return json is greater than 2032 bytes, it chopped of at byte 2032.

I tried different methods using sqlsrv_fetch(), sqlsrv_fetch_object(), sqlsrv_fetch_array(), but none of them works. 2032 is nothing. I expect the json result of the a bove will be millions of bytes.

What is the limitation of PHP on handling string. How do I increase the return json string from 2032 to a billion of bytes?
dqninh
New php-forum User
New php-forum User
Posts: 5
Joined: Mon May 24, 2021 8:38 am

Sat Jun 05, 2021 6:22 am

I found the answers on other websites. Basically, it it the MS SQLSRV that broke the result set into multiple records, each has 2032 bytes. There are two ways to resolve this issue, and both can be applied:

1. Wrap the query that returns the JSON string inside another SELECT statement, like this.

SELECT
(
SELECT V.* FROM ClassPerson CP
INNER JOIN PersonActive PA ON(CONVERT(nvarchar(128),PA.PersonActiveId) =
@usrGuid AND CP.PersonId = PA.personId)
INNER JOIN ClassPerson CP2 ON(CP2.classId = CP.ClassId)
INNER JOIN V_Student V ON(V.PersonId = CP2.PersonId)
FOR JSON PATH, ROOT('AllRecord')
)
the wrapping will "fool" SQLSRV and it will return a single record that has a single column.

2. Using a PHP while loop to make sure the code will read more that 1 record, just in case:

$strJson = "";
while(sqlsrv_fetch($result))
{
$strJson .= sqlsrv_get_Field($result,0,SQLSRV_PHPTYPE_STRING('UTF-8'));
}

Actually, the code will need only one of the above "work around", but it does not hurt to use both.
Post Reply