CODEWITHSUNDEEP
phpsqlsql-serverpdo
iswinky
iswinky

Reputation: 1969

Database query not returning full string

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?

Upvotes: 2

Views: 4932

Answers (2)

Jake Bathman
Jake Bathman

Reputation: 1278

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

Upvotes: 1

iswinky
iswinky

Reputation: 1969

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)

Upvotes: 3

Related Questions