I have recently encountered a problem loading BLOBs from SQL server 2012. Until moving to PHP 7.x we previously used the mssql libraries. Now we are using the dblib driver in PDO the php_mssql libraries are gone for good.

It appeared that, once again, files were being truncated to 60Kb (an old problem). It’s not clear why this started to break again. No new packages have been installed. Using php_mssql it was possible to:

// Allow more than 64KB in MSSQL recordset
    ini_set ( 'mssql.textlimit' , '1048576' );
    ini_set ( 'mssql.textsize' , '1048576' );

These ini_set statements don’t have any effect when you are using dblib. The method of forcing a file as a download would no longer work if the files are over 60Kb:

The file delivery was succeeding using php7.1 on our development machine but not working with php7.0. I updated the php version on the production server to match up to the working environment but that alone was not enough to fix the issue.

Files were downloading OK but the contents were corrupt. Using the following delivery code:

        header("Content-type: $doc_file_type");
        header("Content-Disposition: attachment; filename=somefile.pdf");
        echo ($doc_file_data);

Without going into great detail about setting up PDO objects which is well covered elsewhere there are a couple of specific changes I had to make. First, set TEXTSIZE variable to -1 (unlimited) before retrieving the LOB record set with PDO::FETCH_ASSOC.

        $stmt = $dbh->prepare("SET TEXTSIZE -1;");
        $stmt->execute();

This sets the allowed TEXTSIZE to unlimited on MSSQL Server before any query is executed on the same connection.

There is also a slight change to the actual delivery of the file in php. Just the file_type and file_length are added. The previous version was causing inconsistent results so the PDF now loads inside the browser :

     header("Content-type: $doc_file_type");
     header("Content-Length: $doc_file_length");
     echo ($doc_file_data);

I cleared the opcache in PHP after implementing this change as I wasn’t sure if the file delivery script was being frozen, ignoring my changes. The new code is definitely in use and our PDF files are being fetched correctly again.