ZF-3493: MSSQL: Zend_Db truncates TEXT field

Description

Hello,

Zend-Db does not return the correct data when querying a TEXT-field from a MSSQL database. It returns only the first 4096 bytes.

I think the pdo-functions are buggy, because other (older) Framworks like PEAR (mssql_-functions) don't have this bug, and we can configure the textsize in php.ini (mssql.textlimit and mssql.textsize)..

If this is a bug in php itself, please fix that! Zend is one of the main developers of php, and this bug must be fixed in order to use ZendFramework completely.

More information: http://bugs.php.net/bug.php?id=38805

Are there any known workarounds?

Comments

This bug is not fixed...

Fix Version/s Next Mini Release (1.5.3) [ 10161 ] Fix Version/s 1.6.1 [ 10201 ]

But still not working in 1.6.2!

Need some info:

What version of PHP? What version of MSSQL? Which driver are you using? Presumably pdo_mssql? or are you using the odbc driver?

Thanks, Ralph

Hello,

I'm using PHP 5.2.6. MSSQL Server 2005. Using pdo_mssql. You can get more information of other people having the same problem in the link above ( http://bugs.php.net/bug.php?id=38805 ). This is an old bug, why is it not fixed?

This seems to be a bug in php itself, not in Zend_Db (I think, you have to check that).... But we cannot use Zend_Db with this bug. Perhaps you have better connections to php-developers to fix this soon!

I believe this is an issue with the PDO MSSQL driver. It was one of the reasons I actually wrote a Zend_Db adapter to use the Microsoft SQL Server Driver for PHP for our sites that use ZF and SQL Server.

Steph Fox is currently looking to create a fix for pdo_mssql. We had a long discussion about this. Wez Furlong also helpt to get things very clear.

As I cannot fix pdo_mssql I could do something else: add support for odbc in Zend_Db_Adapter_Pdo_Mssql.

I will provide two patches that will offer this support. Note that is not a fix for the php bug. It is an improvement (I hope) that will let you use a workaround by going through pdo_odbc instead of pdo_mssql.

In order for this workaround to work, you will have to enable php_pdo_odbc.dll in your php.ini if you are working on Windows.

In addition, you will have to add extra options when you instantiate your Zend_Db_Adapter:


$db = Zend_Db::factory('Pdo_Mssql', array(
                                     'pdoType' => 'odbc',//extra
                                     'DRIVER'   => '{SQL Server}',//extra
                                     'host'   => $server,//required = $_SERVER['COMPUTERNAME']
                                     'username' => '*************',
                                     'password' => '*************',
                                     'dbname'    => '*************',
                                 )); 

Patches added for odbc support so that you can use this workaround.

Assign to component owner for evalutation.

With the new odbc-2 patch you do not need to specify the extra DRIVER option, only the pdoType option:


$db = Zend_Db::factory('Pdo_Mssql', array(
                                     'pdoType' => 'odbc',
                                     'host'   => $server,
                                     'username' => $user,
                                     'password' => $pass,
                                     'dbname'    => $dbname,
                                 ));

I changed it from Bug to Patch, because there now is a patch that you can use. Besides, this is not a bug in ZF, it is a bug in pdo_mssql.

Can you please check 5.2.10, the php.net site claims this limitation does not exist in that version:

http://bugs.php.net/bug.php?id=38805

-ralph

Tested on Windows Vista BU against 5.2.10 (which was a pain to get working...), confirmed that the problem still exists


<?php
//phpinfo();
require_once 'Zend/Loader/AutoLoader.php';
$loader = Zend_Loader_AutoLoader::getInstance();

$db = Zend_Db::factory('Pdo_Mssql', array(
                                     'pdoType' => 'mssql',
                                     'host'   => 'HIGHLANDER',
                                     'username' => '*',
                                     'password' => '*',
                                     'dbname'    => '*',
                                 ));

$result = $db->query('SELECT top 1 inhoudtekst1 as tekst FROM O_ProductenInhoud WHERE DATALENGTH(inhoudtekst1) > 4096');

foreach($result as $row) {
    var_dump($row['tekst']);
}

exit();

The result is a truncated text of exactly 4096 chars, as var_dump conveniently reports. I must add that I had to download the ntwdblib.dll and replace the one that is shipped with php, because that one does not work at all (part of the pain of getting this installed). I also had to put it in system32.

I also added these settings in php.ini: ; Valid range 0 - 2147483647. Default = 4096. mssql.textlimit = 2147483647

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

Which patch should I be looking at?

Also, in very succinct terms, what exactly is the proposed solution? I've tried creating an ODBC driver for Zend_Db but it does not pass QA (there are alot of feature missing, the standard unit tests fail, see ZF-905).

Not really any patch at all yet. My patch was basically a naive and dirty way to work around the issue for a specific use case. No unit tests tried at all at the time.

The problem lies in pdo_mssql I am sure. But for windows, we now have the sqlserver driver, of which I wasn't very much aware at the time.

I suppose there already is a db adapter for that...?

The difference between pdo_mssql and regular mssql is that the regular one can retrieve longer text columns completely (over 4096 bytes) if you change some php.ini settings. The pdo driver does not respond to that. If pdo_mssql can be fixed by the php community, than this issue will likely be resolved as well.

Proposing to close as wont-fix since it's an issue in pdo_mssql that cannot be easily worked around through php.

Does anybody know if PDO is maintained and if yes by whom? If not, maybe someone can stand up and try to fix it. Once fixed, we can prove that this issue no longer exist and close it. In the meantime, we are not able to fix it and indeed, should change the status accordingly.

I just went through updating ZendFramework from 0.9.3 to 1.10.0 and while it should've been a simple overwrite of all the files then it took a bit longer than that.

The reason... I had to manually add the attached mssql-odbc-patch as well as the changes from ZF-6739.

I can understand that there is the right way of implementing stuff as well as the wrong way, but reading that this issue might get changed to "wont-fix" (as well as ZF-905) and the lack of response in issue ZF-6739 (which is depending on this fix) then I fear that I have to manually make changes in all future releases of Zend Framework.

This fix doesn't break any functionality and just adds an extra option/setting, so why is it, that it can't be implemented?

Do we really have to install Microsofts SQLserver driver and if so, does that then solve the problems in this issue and the related issues (text fields above 4096 bytes, trusted connection and multiple queries).