4

I will post this sample for anybody who need help with insert file to varbinary(max) field in sqlsrv DB. Also if you need to read from varbinary field, you can see how I made that. This code is working but all your comments and suggestions are welcome. This is sample table:

CREATE TABLE [dbo].[files]
(
[id] [int] NOT NULL IDENTITY(1, 1),
[content] [varbinary] (max) NULL,
[filename] [varchar] (max) COLLATE Croatian_CI_AS NULL
)

First enter your data for server that you use. If you need to insert file in varbinary field in db:

<?php
$server = "";
$konekcionistring = array( "Database"=>"", "UID"=>"", "PWD"=>"");
$konekcija = sqlsrv_connect( $server, $konekcionistring);

$newfileName = 'testX.pdf';
$newtmpName  = 'C:\xampp\htdocs\testX.pdf';

$fp = fopen($newtmpName, 'rb');
$file_content = fread($fp, filesize($newtmpName));
fclose($fp);

$sql = "INSERT INTO files ([content],[filename]) SELECT CONVERT(VARBINARY(MAX),?),?";

$parametri = array(
                array($file_content),
                array($newfileName)
                );
$r_blob = sqlsrv_query($konekcija, $sql, $parametri);

if( $r_blob === false) 
    {
      die( print_r( sqlsrv_errors(), true) );
    }
?>

When you need to read data from varbinary filed in DB you can use this code:

<?php
$server = "";
$konekcionistring = array( "Database"=>"", "UID"=>"", "PWD"=>"");
$konekcija = sqlsrv_connect( $server, $konekcionistring);

$skript = "SELECT F.content, F.filename AS Template FROM dbo.files AS F";

$izvrsiSQL = sqlsrv_query($konekcija, $skript);
$result = sqlsrv_fetch_array($izvrsiSQL, SQLSRV_FETCH_ASSOC);
$filename = $result['filename'];
$X = $result['content'];


header("Content-type:application/pdf");
header('Content-Disposition: attachment inline; filename="'.$filename.'"');
echo $X;
?>

If you have any comments or suggestions you are welcome.

user7414051
  • 41
  • 1
  • 2
  • 2
    Thats amazing! But do you have a question ? ( May consider to use documentation instead http://stackoverflow.com/tour/documentation ) – DuKes0mE Jan 13 '17 at 10:23
  • No, I dont have any question. This is sample that work, for anybody who need help with php-sqlsrv-varbinary. Also, if anybody have any comments or suggestions on my code, that will be good for all us. P.S. I did not know for stackoverflow and documentation.I will post it there. – user7414051 Jan 13 '17 at 11:05
  • Instead of converting from string/varchar to varbinary on server side, you can also configure the parameter to varbinary like this: `$parameters = [[$file_content, SQLSRV_PARAM_IN, SQLSRV_SQLTYPE_VARBINARY($size), SQLSRV_PHPTYPE_STRING(SQLSRV_ENC_BINARY)], ...];` – Robert May 24 '18 at 18:05

1 Answers1

0

if u want compress and upload

function compressImage($filename){

    $details = getimagesize($filename);
    $content = file_get_contents($filename);
    $srcImg = imagecreatefromstring($content);

    
    //get content
    ob_start();
    imagejpeg($srcImg, null,10);
    $contents = ob_get_contents();
    ob_end_clean(); 

    imagedestroy($srcImg);
    return $contents;
}


            $CompressedImage = compressImage($FileTempName);

            $status = sqlsrv_query($conn,'insert into Post (P_image) values (?)', array(  
            array($CompressedImage, SQLSRV_PARAM_IN,SQLSRV_PHPTYPE_STREAM(SQLSRV_ENC_BINARY), SQLSRV_SQLTYPE_VARBINARY('max'))
            ));