windows 7, php 5.3, when fetching binary data with sqlsrv from mssql the data is doubled

Question

ok, so I have images on a MSSQL 2005 (express) server which I want to write to files. with the same piece of code, on linux it works fine, on windows it writes the data twice in the file

file_put_contents($file, $val);
$val = basename($file);

I know it is not a file_put_contents() problem because I also tried with fwrite the output file in windows has double the size it has in linux

-rw-rw-r-- 1 dimitris dimitris  891768 2011-11-22 16:13 eshop_products__2201.jpg
-rw-rw-r-- 1 dimitris dimitris  445884 2011-11-21 19:15 eshop_products__2201_linux.jpg

I am using the freetds driver for linux and php_pdo_sqlsrv_53_nts_vc9 in windows

any ideas on something I could do to get the correct data in windows? maybe some configuration I missed?

topmost bytes of each file:

windows file:

ASCII (php substr):
FFD8FFE000104A46494600010100000100010000FFDB0043000302020302020303030304030304050805050404050A070706080C0...etc...

hex:
00000000:  46 46 44 38 46 46 45 30  30 30 31 30 34 41 34 36  FFD8FFE000104A46
00000010:  34 39 34 36 30 30 30 31  30 31 30 30 30 30 30 31  4946000101000001
00000020:  30 30 30 31 30 30 30 30  46 46 44 42 30 30 34 33  00010000FFDB0043
00000030:  30 30 30 33 30 32 30 32  30 33 30 32 30 32 30 33  0003020203020203
00000040:  30 33 30 33 30 33 30 34  30 33 30 33 30 34 30 35  0303030403030405
00000050:  30 38 30 35 30 35 30 34  30 34 30 35 30 41 30 37  0805050404050A07
00000060:  30 37 30 36 30 38 30 43  30 41 30 43 30 43 30 42  0706080C0A0C0C0B

linux file:

ASCII (php substr):
����JFIF��C

hex:
00000000:  ff d8 ff e0 00 10 4a 46  49 46 00 01 01 00 00 01  ......JFIF......
00000010:  00 01 00 00 ff db 00 43  00 03 02 02 03 02 02 03  .......C........
00000020:  03 03 03 04 03 03 04 05  08 05 05 04 04 05 0a 07  ................
00000030:  07 06 08 0c 0a 0c 0c 0b  0a 0b 0b 0d 0e 12 10 0d  ................
00000040:  0e 11 0e 0b 0b 10 16 10  11 13 14 15 15 15 0c 0f  ................
00000050:  17 18 16 14 18 12 14 15  14 ff db 00 43 01 03 04  ............C...
00000060:  04 05 04 05 09 05 05 09  14 0d 0b 0d 14 14 14 14  ................
00000070:  14 14 14 14 14 14 14 14  14 14 14 14 14 14 14 14  ................

The code extracting the file from the database is base on the yii framework, using yii's commands feature (php cli)

Connection strings:

linux

'connectionString' => 'dblib:host=192.168.56.101;port=1433;dbname=mssqldb',

windows

'connectionString' => 'sqlsrv:Server=192.168.56.101;Database=mssqldb',

Code:

$rows = $this->db->createCommand("SELECT * FROM $viewName WHERE 1=1")->queryAll();

/*
 * Convert charset and extract photos from view
 */
foreach ($rows as $row) {
    // convert charset / export photos
    foreach ($row as $key => &$val) {
        // charset conversion, on fields that do not contain photos
        // $viewCfg is an array I maintain to know which field does what
        if (empty($viewCfg['photos']) || !in_array($key, $viewCfg['photos'])) {
            $val = @iconv("Windows-1253", "UTF-8", $val);
        // grab image in file
        } else {
            $id = '';
            foreach ($viewCfg['keys'] as $fieldName) {
                $id .= '_' . $row[$fieldName];
            }

            $file = Yii::app()->params['pathPhotos'] . '/' . $viewName . '_' . $id . '.jpg';

            if ($val) {
                if (file_exists($file) && (file_get_contents($file)!=$val))
                    unlink($file);

                file_put_contents($file, $val);
                $val = basename($file);
            }
        }
    }
// ... do the rest

this is not enough helpful information on your question. please insert more code (with query). When you echo the result of count($val) instead of writing the string to a file, is the result the same? (double the size on windows as opposed to linux) i guess "file_put_contents" is not the culprit here, so we really need to see your database handling code! — Kaii, Nov 22 '11 at 14:44
Please post the first few lines of each file (in hex & ascii preferably), maybe your windows part did not convert from Hex back to binary? — Stephan B, Nov 22 '11 at 14:45
did provide some more information... any ideas are very much appreciated, thanks! — dimvic, Nov 23 '11 at 10:01
actually, it was exactly what @StephanB suggested. my current solution is to `if (PHP_OS != 'Linux') { $val = pack('H*', $val); }` which of course is not satisfactory enough — dimvic, Nov 23 '11 at 11:10

score 1 · Answer 1 · answered Nov 23 '11 at 13:10

1

Good to hear you found some solution, if not a nice one : /

I had that situation while testing Mssql on a PHP app but never got any further. Another hint you may check is the PHP doc on PDO Large Objects (like varbinary and image SQL Datatypes): http://de2.php.net/manual/en/pdo.lobs.php

Maybe the result is consistent when you read the image as a stream?

answered Nov 23 '11 at 13:10

Stephan B

3,671
20
33

Might work really, but at this time the other solution works and it's online on a past project which I rather not touch. Until I do I will leave the question unanswered so if anyone faces the same problem and doesn't want to bother looking at pdo large objects can read up and see the workaround solution – dimvic Mar 16 '12 at 16:45
Doesn't work! The SQLSRV driver still converts everything to hex `$res = hex2bin($res);` works :/ – Josef Oct 02 '15 at 14:55

windows 7, php 5.3, when fetching binary data with sqlsrv from mssql the data is doubled

1 Answers1