php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #38805 PDO Truncates Text from SQL Server Text Data Type Field
Submitted: 2006-09-13 13:06 UTC Modified: 2012-09-13 04:35 UTC
Votes:135
Avg. Score:4.7 ± 0.8
Reproduced:102 of 106 (96.2%)
Same Version:37 (36.3%)
Same OS:48 (47.1%)
From: gkrajci at arescorporation dot com Assigned: ssufficool (profile)
Status: Closed Package: PDO related
PHP Version: 5.1.6 OS: Windows NT PBMA-WB2 5.2 build 37
Private report: No CVE-ID: None
 [2006-09-13 13:06 UTC] gkrajci at arescorporation dot com
Description:
------------
When using PDO to retrieve text from a SQL Server text data type field the text is truncated when I display it on a Web page

PDO Transcript length = 4096 (truncated)
PEAR Transcript length = 6139(full text)

Using SQL Server 2000

Reproduce code:
---------------
$sql = "SELECT title AS VideoTitle, transcript_text AS TranscriptText FROM video WHERE video_id = 324";

$dbh = new PDO($pdo_dsn, $db_user, $db_password);
$transcript_q = $dbh->query($sql);
$transcript_rs = $transcript_q->fetch();

$pear_dsn = "$db_type://$db_user:$db_password@$db_host/$db_name";
require_once( 'DB.php' );
$db = DB::connect( $pear_dsn, true );
if ( DB::isError($db) ) die( $db->getMessage() );

$res = $db->query($sql);
$row = $res->fetchRow();

Expected result:
----------------
The text in TranscriptText to be the text and the same length.

Actual result:
--------------
See Description for this bug report.

Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2006-09-13 15:40 UTC] iliaa@php.net
what PDO driver are you using?
 [2006-09-13 15:45 UTC] wez@php.net
I assume you're using ODBC?
 [2006-09-14 11:47 UTC] gkrajci at arescorporation dot com
Microsoft SQL Server connections

PHP.INI
---------------------------
extension=php_pdo.dll
extension=php_pdo_mssql.dll
extension=php_pdo_mysql.dll

PDO
PDO support enabled 
PDO drivers  mssql, mysql  

pdo_mssql
PDO Driver for MSSQL DB-lib enabled 
Flavour  MSSQL_70  

pdo_mysql
PDO Driver for MySQL, client library version 5.0.22
 [2006-09-26 16:22 UTC] ritch at bugsoftware dot co dot uk
I'm also suffering this problem after changing my database connection type to PDO.

I'm running PHP 5.1.2 on Windows server 2003 with MSSQL 2005.

In the old mssql ext. you had to specificaly tell the configure it in php.ini to bring back larger text fields:

; Valid range 0 - 2147483647.  Default = 4096.
mssql.textlimit = 2147483647

; Valid range 0 - 2147483647.  Default = 4096.
mssql.textsize = 2147483647

I have not found the same for the new PDO extension - so I'm assuming this has some thing to do with the problem. (My text fields are also truncated to 4096)

Help with this issue would be greatley appreciated.
 [2006-11-08 14:30 UTC] mark dot pearson at capita dot co dot uk
I have found the same problem with PHP 5.1.6 running on Windows XP Pro SP2 and MS SQL Server 2000 (using the pdo_mssql driver).

I also tried to solve it by setting the mssql.textlimit and mssql.textsize INI options, since that used to cause the same problem in the mssql extension but it doesn't have any effect on the values returned by PDO.
 [2006-12-06 14:50 UTC] frank at interlevel dot com
Seems I am not alone on this. I have also tried altering the PHP.ini with no result. IS there an update to this PDO?
 [2007-02-14 16:16 UTC] matt at educause dot edu
I too can reproduce this problem ... a critical hurdle for us.
 [2007-08-13 19:22 UTC] jagwire16 at hotmail dot com
http://us3.php.net/manual/en/function.mssql-query.php#64470 reports how to make it work and that solution worked for me.
 [2007-09-11 08:36 UTC] rockyjl at gmail dot com
ini_set('mssql.textlimit', '65536');
ini_set('mssql.textsize', '65536'); 

it is work for php_mssql.dll

but not work for php_pdo_mssql.dll

how can i get more than 4096 byte in TEXT with php_pdo_mssql.dll ???
 [2007-11-17 03:15 UTC] rockyjl at gmail dot com
why can't fix in php 5.2.5 ???? this is very importance for developer !!!!!
 [2007-11-17 03:21 UTC] rockyjl at gmail dot com
why can't fix in php 5.2.5 ???? this is very importance for developer !!!!!
 [2008-01-09 15:14 UTC] rob at tdd dot org dot uk
I have found that it doesn't get truncted with ODBC, however I have found certain stored procedures don't work with ODBC. My solution is to create a DAL class which has a factory method to create a PDO connection based on driver selection. Then during my testing if I find it to work in one and not in another then I switch the database connection over to the one that works.
 [2008-03-27 08:25 UTC] andrew-law at yandex dot ru
It's a pity php developers don't hear their colleagues. This is a critical bug and it hasn't fixed yet.
 [2008-06-18 12:04 UTC] me at opensol dot com
I can confirm this bug in php 5.2.5

This is a heavy bug, please fix this soon! 

A setting like 
mssql.textlimit = 2147483647
mssql.textsize = 2147483647
would be great!
 [2008-06-19 07:01 UTC] Phil dot H at gmx dot net
please fix that bug, this is also a problem if you want to use zend_db or something like that! This makes the whole zend_db classes useless!
 [2008-06-20 13:39 UTC] mcleod at spaceweb dot nl
Also on:
OS: Windows XP Pro
PHP version: 5.2.5
MSSQL server 2005
It worries me that it affects Zend_Db. I experienced it using PDO directly.
 [2008-07-30 13:18 UTC] kristaps at kraksti dot lv
Hello!

 Have you tried $pdo->query('SET TEXTSIZE {number} ');
Where number is text size (in bytes). Max number is 2 GB in bytes.
 [2008-08-04 12:40 UTC] gkrajci at arescorporation dot com
Trying $pdo->query('SET TEXTSIZE {number}'); did not work...

$dbh = new PDO($pdo_dsn, $db_user, $db_password);
$sql = "SELECT the_content, DATALENGTH(the_content) AS d_l FROM content WHERE content_id = 578";
$dbh->query('SET TEXTSIZE 300000'); 
$stmt = $dbh->prepare($sql);
$stmt->execute();
while ( $row = $stmt->fetch() ) {
    $video_count++;
    echo 'LENGTH: '.strlen($row["the_content"]).', '.$row["d_l"].'<br />';
}

OUTPUT:

LENGTH: 4096, 24868
 [2008-09-18 14:04 UTC] chinnet at 126 dot com
My php version is 5.2.3.
Now,I use PDO_MSSQL read the image type data from SQL Server 2000.
But i only get 4096B from the db.

so what can i do?

; Valid range 0 - 2147483647.  Default = 4096.
mssql.textlimit = 2147483647

; Valid range 0 - 2147483647.  Default = 4096.
mssql.textsize = 2147483647

This is supported by php_mssql.dll.But not supported by PDO_MSSQL!

so it is very important!
 [2008-09-19 12:49 UTC] J dot Antonio at jaruz dot com
This issue is now more then TWO YEARS old. Can "wez" please have a look at it?
 [2008-10-14 22:31 UTC] mario dot estrada at gmail dot com
Come on guys, please fix this! This is a MAJOR bug and it seems like you really don't care!
 [2008-11-20 06:56 UTC] gmtfn at yahoo dot com
Maybe wez is dead or is no longer associated with the PHP community for another reason. If this bug is still present, try filing a new issue. Maybe it will get assigned to somebody who can help.
 [2008-11-21 14:57 UTC] andrew dot henze at gmail dot com
Still having the same problem. Anyone have any news?
 [2008-11-21 15:01 UTC] pajoye@php.net
Is it still present in latest 5.2 RC?
 [2008-11-21 15:31 UTC] andrew dot henze at gmail dot com
This is where I get slaughtered: I'm using PHP Version 5.2.3
and I still have this problem.
 [2008-11-21 15:47 UTC] pajoye@php.net
Please try using this CVS snapshot:

  http://snaps.php.net/php5.2-latest.tar.gz
 
For Windows:

  http://windows.php.net/snapshots/

5.2.3 is not the latest. Please give it a try
 [2008-12-09 15:08 UTC] mcleod at spaceweb dot nl
Also on:
PHP 5.2.6 (cli) (built: May  2 2008 18:02:07)
Copyright (c) 1997-2008 The PHP Group
Zend Engine v2.2.0, Copyright (c) 1998-2008 Zend Technologies

Using Zend_DB with mssql driver!
On windows XP Pro SP 3 in command line php script.
Have set the mssql textlimit and textsize in php.ini, but indeed, these do not help.
 [2008-12-09 17:21 UTC] sfox@php.net
This is an aspect of PDO design that should be better documented, as confirmed in privmail with Wez earlier today.

There shouldn't be any INI settings in PDO drivers at all, the whole idea being to allow more portable code. Things like field length should be set directly using the RDBMS API.

The original MSSQL extension simply wraps a SET TEXTSIZE query during its connection routine when the INI directive mssql.textsize is set. To the database engine, there's no difference between that query being made from a PHP script or from the extension/C wrapper.

So, the upshot is, this bug is not a bug, and I'm passing it to the docs team.
 [2009-02-13 11:01 UTC] janpolsen at gmail dot com
I have done some various testing and to me it still seems that PHP's PDO-class is the problem.

The following is the output from my script:
  Zend Framework
  - WITHOUT TEXTSIZE 20480
    - PHP strlen()          : 4096
    - MSSQL2005 datalength(): 12418
  - WITH TEXTSIZE 20480
    - PHP strlen()          : 4096
    - MSSQL2005 datalength(): 12418

  PHP PDO-class
  - WITHOUT TEXTSIZE 20480
    - PHP strlen()          : 4096
    - MSSQL2005 datalength(): 12418
  - WITH TEXTSIZE 20480
    - PHP strlen()          : 4096
    - MSSQL2005 datalength(): 12418
  
  PHP mssql_*()
  - WITHOUT TEXTSIZE 20480
    - PHP strlen()          : 12418
    - MSSQL2005 datalength(): 12418
  
Everything fails except PHP's mssql-* functions and I assume that Zend_Db fails because it uses PHP's PDO-class.

This test was run with:
  PHP 5.2.6 (cli) (built: May  2 2008 18:02:07)
  mssql
  - MSSQL Support => enabled
  - Active Persistent Links => 0
  - Active Links => 0
  - Library version => 7.0
  pdo_mssql
  - PDO Driver for MSSQL DB-lib => enabled
  - Flavour => MSSQL_70
  Zend Framework 1.7.4 (r13879)

If needed then the script for the output can be found at http://2p0.dk/phps/test_4096.phps
 [2009-02-13 19:24 UTC] sfox@php.net
On further analysis, it seems SET_TEXTSIZE doesn't work even when you set it explicitly.

I have a working fix for this, but no telling when it'll get into CVS as I have to install an entirely new second build environment for PHP 5.3 before I can commit it.

The documentation comment still holds, and setting the textsize works for all related extensions except pdo_dblib (AKA pdo_mssql.dll).

Best workaround: use PDO_ODBC to connect to MSSQL Server instead.

//$dsn = 'mssql:host=MYBOX;dbname=testdb'; 
$dsn = 'odbc:DRIVER={SQL Server};SERVER=MYBOX;DATABASE=testdb;'; 

HTH

- Steph
 [2009-02-15 22:30 UTC] janpolsen at gmail dot com
Thanks for the fast response.

I will try to see how my scripts run when using the ODBC driver instead :).
 [2009-03-20 22:17 UTC] sfox@php.net
Should all work as advertised from 5.2.10 up.

Now to go change the advertising ;)

- Steph
 [2009-07-27 10:52 UTC] danhen at web dot de
When using PDO_MSSQL with PHP 5.3 (Not PDO_ODBC - queries aren't compatible in many cases - especially those with placeholders) pdo::query(SET TEXTSIZE 2147483647) works fine with MSSQL 2008. PDO_ODBC isn't a good replacement.
 [2009-09-02 15:28 UTC] aballard at gmail dot com
According to the SQL Server documenation, SET TEXTSIZE { number } by itself is not sufficient. That's why the original mssql library has two configuration directives: mssql.textlimit and mssql.textsize

Since PDO is configured not to use configuration directives, it would be nice if the pdo_mssql driver added two driver_options to configure these values.


A quote from SQL Server Books Online:


Setting SET TEXTSIZE affects the @@TEXTSIZE function.

The DB-Library variable DBTEXTLIMIT also limits the size of text data returned with a SELECT statement. If DBTEXTLIMIT is set to a smaller size than TEXTSIZE, only the amount specified by DBTEXTLIMIT is returned. For more information, see "Programming DB-Library for C" in SQL Server Books Online.

The SQL Server ODBC driver and Microsoft OLE DB Provider for SQL Server automatically set TEXTSIZE to 2147483647 when connecting.

The setting of set TEXTSIZE is set at execute or run time and not at parse time.
 [2010-01-10 23:22 UTC] kalle@php.net
Steph, does this need any additional changes to pdo_mssql/pdo_dblib? And what exactly should be documented?
 [2010-01-22 22:51 UTC] ssufficool at gmail dot com
I imagine the problem is that PDO DBLIB just mem-copies the first packet of TEXT without calling dbgettext() to retrieve the remainder.

MSSQL handles TEXT fields using dbconvert() which may call dbgettext() downstream.

Possible fix: remove "case SQLTEXT" from ext/pdo_dblib/dblib_stmt.c:execute and let it fall though to default.
 [2010-02-11 15:40 UTC] philipp at servicemail24 dot de
php 5.3.2 dotdeb still suffers from this problem.

does this fix help?

"Possible fix: remove "case SQLTEXT" from
ext/pdo_dblib/dblib_stmt.c:execute and let it fall though to default."
 [2010-02-12 09:05 UTC] philipp at servicemail24 dot de
This problem is actually fixed in cvs:

http://www.mail-archive.com/php-cvs@lists.php.net/msg40731.html
http://www.mail-archive.com/php-cvs@lists.php.net/msg40711.html

Here is the working source code:

http://cvs.php.net/viewvc.cgi/php-src/ext/pdo_dblib/

I have no idea why these fixes aren't included in the 5.2 and 5.3 releases!

@sfox can you ensure that pdo_dblib is updated with the release of 5.2.13 and 5.3.2?
 [2010-02-12 16:57 UTC] sfox@php.net
Those changes are still in SVN. That means the TEXTLIMIT var is being set to its highest possible value, which in turn means that truncation shouldn't be an issue now.

$pdo->query('SET TEXTSIZE 300000');

should work from PHP 5.2.11 up, it just needs doccing.
 [2010-03-04 20:55 UTC] juan dot pineda at resultstel dot com
I solved this problem by adding to my php script a TEXTSIZE that is less than the allowed memory from the MSSQL server. 

Remember, all the number are in Bytes, so I kept playing with the numbers, until this worked:
// ranges from 0 - 3145728 = 3Megabytes.  Default to 4096.
$sql = "SET TEXTSIZE 3145728";
mssql_query($sql, $db) or die(mssql_get_last_message());

Remember to always know what the allowed upload size for your server is.

I hope this helps someone
 [2010-12-17 21:07 UTC] kalle@php.net
-Status: Assigned +Status: Open -Type: Bug +Type: Documentation Problem -Assigned To: sfox +Assigned To:
 [2010-12-17 21:07 UTC] kalle@php.net
-Status: Open +Status: To be documented
 [2010-12-17 21:07 UTC] kalle@php.net
.
 [2011-12-04 02:48 UTC] ssufficool@php.net
-Status: Open +Status: To be documented
 [2011-12-04 02:48 UTC] ssufficool@php.net
This bug has been fixed in SVN.

Snapshots of the sources are packaged every three hours; this change
will be in the next snapshot. You can grab the snapshot at
http://snaps.php.net/.
 
Thank you for the report, and for helping us make PHP better.


 [2012-09-13 04:35 UTC] ssufficool@php.net
This bug has been fixed in SVN.

Snapshots of the sources are packaged every three hours; this change
will be in the next snapshot. You can grab the snapshot at
http://snaps.php.net/.

 For Windows:

http://windows.php.net/snapshots/
 
Thank you for the report, and for helping us make PHP better.


 [2012-09-13 04:35 UTC] ssufficool@php.net
-Status: Open +Status: Closed -Assigned To: +Assigned To: ssufficool
 [2014-11-06 20:44 UTC] desiovincenzo at gmail dot com
hi all

you can solve this issue by using the sql convert function as below : 

SELECT CONVERT(TEXT,field) as field_name from table

hope it is useful for someone

byez
 [2017-10-03 17:41 UTC] ryan dot brainerd at gmail dot com
i am using PDO with SQL Server 2013 trying to SELECT large JSON strings and was hitting this issue

according to SQL Server docs, "SET TIMEOUT -1" indicates unlimited size and fixed the problem for me as well, better than limiting to 3MB as in previous solution

this had to be executed first in a separate query, and then my SELECT could be executed

i am including this as a standard 'pre query' now when using PDO + MSSQL, so i do not have to remember to do this for every large column. it does not seem to impact performance when used this way, but if anyone has info otherwise a post would be appreciated

good luck all
 [2017-10-03 17:43 UTC] ryan dot brainerd at gmail dot com
[CORRECTION] previous post should have said "SET TEXTSIZE = -1"
sry!
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Tue Mar 19 09:01:30 2024 UTC