Not able to show a pdf / image from binary data in MSSQL using PDO LOB

Question

I go MSSQL database which contains attachments - most of them are PDFs. I want to create simple website, that will display attachment after clicking a button. I got main website prepared (there are records with attachments' titles and a link fo the other page, that is supposed to just show the attachment in the new tab).

I've been using PDO as I heard that is secure and pretty simple.

My query '$sql' for sure returns one row (I have checked that by simply doing var_dump() of the FetchAll()).

 $stmt = $pdo->prepare($sql);
    $stmt->execute([$id]);

    $stmt->bindColumn(10,$lob, PDO::PARAM_LOB);
    $stmt->bindColumn(11, $attachment, PDO::PARAM_STR, 256); 
    $stmt->fetch(PDO::FETCH_BOUND);
    $lobContent = stream_get_contents($lob);


    $ext = substr(strrchr($attachment, '.'),1); 



      
       header("Content-Type: Application/pdf");
     
        // fpassthru($lob) shows some weird stuff that looks like memory leak
         echo $lobContent;

Any ideas what Am I doing wrong here? If I try to use fpassthru() it show me some garbage, I mean it looks like memory leak or something, there are some parts of the code, that is not on my website and is not in the database...

I have checked if the query result isn't empty, tried to just fetchAll() the data I got but as told above, got very unexpected behaviour.

This post provides a solution to similar problem using SQLSRV driver. Which driver (and version) do you use to connect to SQL Server? — Zhorov
– Zhorov, Commented Jul 2, 2024 at 5:56
@Zhorov SELECT Microsoft SQL Server 2017 (RTM-GDR) (KB4583456) - 14.0.2037.2 (X64) Nov 2 2020 19:19:59 Copyright (C) 2017 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2016 Standard 10.0 <X64> (Build 14393: ) — nequ2k
– nequ2k, Commented Jul 2, 2024 at 6:07
This is the SQL Server version. How exactly do you create a connection ($pdo = new PDO(...);)? — Zhorov
– Zhorov, Commented Jul 2, 2024 at 6:14
'$dsn = "sqlsrv:server=_address_; database=_name of db_;Encrypt=0"; $username = "username"; $password = "password";' And then: '$pdo = new PDO($dsn, $username, $password);' — nequ2k
– nequ2k, Commented Jul 2, 2024 at 6:50
I managed to get the data in the browser. Now I'm failing to convert the code into the pdf. The code: $attachment = sqlsrv_get_field( $stmt, 9, SQLSRV_PHPTYPE_STREAM(SQLSRV_ENC_CHAR)); //header("Content-Type: Application/pdf"); fpassthru($attachment); Once im uncommenting the Content-Type header, it says "Failed to open document". Ther beggining of the output looks like this: 3C3F7068700A2F2A0A20202020436F7079726.... — nequ2k
– nequ2k, Commented Jul 2, 2024 at 7:57

nequ2k · Accepted Answer · 2024-07-04 06:20:20Z

0

I have managed to solve my problem. It turned out that weird output isn'tany memory leak - it was just the contnent of the document that I was getting the binary from. After finding it out I have tried few things and the milestone was changing the PDO connection to

$conn = sqlsrv_connect($server, $conninfo); 
$stmt = sqlsrv_query($conn, $sql, $params); 
if ( sqlsrv_fetch( $stmt ) )  
{ 
$attachment = sqlsrv_get_field( $stmt, 10,   
SQLSRV_PHPTYPE_STREAM(SQLSRV_ENC_BINARY));  

header("Content-Type: Application/pdf");  

fpassthru($attachment);
  
}

I was able to open the pdf and images from databse in my browser, using binaries saved in the db!

Hope this helps anyone in the future tho I know, it is a workaround.

answered Jul 4, 2024 at 6:20

nequ2k

174 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Not able to show a pdf / image from binary data in MSSQL using PDO LOB

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related