#38805 [Com]: PDO Truncates Text from SQL Server Text Data Type Field

From: Date: Wed, 02 Sep 2009 15:28:50 +0000
Subject: #38805 [Com]: PDO Truncates Text from SQL Server Text Data Type Field
References: 1  Groups: php.doc.bugs 
Request: Send a blank email to doc-bugs+get-2715@lists.php.net to get a copy of this message 
 ID:               38805
 Comment by:       aballard at gmail dot com
 Reported By:      gkrajci at arescorporation dot com
 Status:           To be documented
 Bug Type:         Documentation problem
 Operating System: Windows NT PBMA-WB2 5.2 build 37
 PHP Version:      5.1.6
 Assigned To:      sfox
 New Comment:

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.


Previous Comments:
------------------------------------------------------------------------

[2009-07-27 10:52:45] 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-03-20 22:17:01] sfox@php.net

Should all work as advertised from 5.2.10 up.

Now to go change the advertising ;)

- Steph

------------------------------------------------------------------------

[2009-02-15 22:30:36] 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-02-13 19:24:48] 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-13 11:01:02] 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

------------------------------------------------------------------------

The remainder of the comments for this report are too long. To view
the rest of the comments, please view the bug report online at
    http://bugs.php.net/38805

-- 
Edit this bug report at http://bugs.php.net/?id=38805&edit=1

Thread (9 messages)

« previous php.doc.bugs (#2715) next »