Database query not returning full string

Question

I'm saving a json string in the database which appears to be stored correctly in SQL Server, however when trying to fetch the data it only returns part of the json string.

I'm using PDO and json_encode to save the data. The json string stored is approximately 1000 characters long, and the table field allows a length of 4096.

Fetching result:

$sql = "SELECT TOP 1 * FROM MyTable WHERE id = :id ORDER BY id DESC;";

$params = array(
    ":id" => $id
);

$sth = $this->db->prepare($sql);
$sth->execute($params);
$result = $sth->fetch(PDO::FETCH_ASSOC);

Saving result:

$json = json_encode($_POST);

$sql = "INSERT INTO MyTable(data) VALUES (:data);";

$params = array(
    ":data" => $data
);

$stmt = $this->db->prepare($sql);
$stmt->execute($params);

Example Json stored in SQL Server:

{  
    "checkbox_1":"on",
    "checkbox_2":"on",
    "checkbox_3":"on",
    "text_1":"my text",
    "images":[  
        13685
    ],
    "date":"11-11-2015"
}

Example Json returned:

{  
    "checkbox_1":"on",
    "checkbox_2":"on",
    "checkbox_3":"on",
    "text_1

Update

It appears that the length of the string returned is always: 255

Could this be an SQL Server configuration or perhaps PDO?

Are you sure the string isnt longer than the database limit? `echo strlen($json);` — Steve, Nov 11 '15 at 14:47
What's the encoding on the table/field? I think this might be an encoding issue, where you're counting characters versus bytes. — Jake Bathman, Nov 11 '15 at 14:48
@JakeB. The collation of the table is SQL_Latin1_General_CP1_CI_AS. — iswinky, Nov 11 '15 at 14:53
And/or are their other large rows in the table? Finally: can you verify that the original string is stored in full, or is it truncated when inserted? — Jake Bathman, Nov 11 '15 at 15:03
Note that you should be using a UTF-8 encoding for any field in which you are going to store JSON as, by definition JSON is to use Unicode encoding. — Mike Brant, Nov 11 '15 at 15:03
@JakeB. It is varchar(4096), this is the largest row so far, the only other column is ld. And it appears to be stored in full, if I view it in SQL Server, it's all there — iswinky, Nov 11 '15 at 15:07
@MikeBrant I've tried running all the data through `utf8_encode()` before using `json_encode()` but I'm still having no luck — iswinky, Nov 12 '15 at 07:39

score 3 · Accepted Answer · edited May 23 '17 at 11:51

3

Turns out that the protocol I was using to connect to SQL Server via PDO limits to 255 characters when fetching from a varchar column.

The workaround is to either change the column to TEXT or cast it to text in the SQL

SELECT CAST(my_column as TEXT)

ODBC query on MS SQL Server returning first 255 characters only in PHP PDO (FreeTDS)

edited May 23 '17 at 11:51

Community

1
1

answered Nov 12 '15 at 08:16

iswinky

1,951
3
16
47

Glad you got it figured out. What a weird setting. – Jake Bathman Nov 12 '15 at 14:41
@JakeB. Yeah I know, frustrating! Thanks for the help anyway – iswinky Nov 12 '15 at 15:08

Jake Bathman · Answer 2 · 2015-11-12T14:42:28.037

1

Edit: Looks like OP's issue was something else, but I'll leave this below for future people that might have a similar issue, which can be solved by looking to TEXTSIZE.

Looks like the issue might be with the TEXTSIZE variable in SQL Server, which limits the length of returned text via PHP.

See what the current value is using

SELECT @@TEXTSIZE

and update it to a higher value using

SET TEXTSIZE 2147483647

Where the number is the max character count (defaults to/maxes out at the above value).

Here's the MSDN page on TEXTSIZE: https://msdn.microsoft.com/en-us/library/ms186238.aspx

edited Nov 12 '15 at 14:42

answered Nov 11 '15 at 15:16

Jake Bathman

1,268
3
18
25

1

It's already set to 2147483647, which is equivalent to roughly 2gb. The json strings being saved / retrieved are no where near that size. It has a string length of 894, and the size is roughly 1.04KB. I think it must be the way it's being stored. – iswinky Nov 12 '15 at 07:38

Database query not returning full string

2 Answers2