Read BLOB file of the bbdd

Question

Read BLOB file of the bbdd

Navigation

#1 by (0 votes)
#2 by (0 votes)

1

The point is that I have inserted a file of type BLOB in a field of my table, inside a bbdd in local.

To read the file, using PDO, I created a file that creates the connection to my database and another file that is responsible for reading the BLOB, obviously requiring the connection file.

When I execute it, on the screen, the ID and the type of the record of the table where the BLOB is located appear, but the content is not shown (in this case, a png image), more specifically the small one appears 'broken' image icon.

This is the connection file:

<?php

try{

$base= new PDO('mysql:host=localhost; dbname=pruebas', 'root', '');

$base->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

//$base->exec('SET CHARACTER SET UTF8');

}catch(Exception $e){
   die ('Error' . $e->getMessage());
   echo "Línea del error: " . $e->getLine();
}

return $base;

?>

And this my reading code:

<?php

echo "<br>";

$Id="";
$contenido="";
$tipo="";

require("Conexion.php");

$sql="SELECT * FROM ARCHIVOS WHERE Id=12";

$resultado=$base->prepare($sql);

$resultado->execute();

while($fila=$resultado->fetch(PDO::FETCH_ASSOC)){
    $Id=$fila["Id"];
    $contenido=$fila["Contenido"];
    $tipo=$fila["Tipo"];
}

?>

<center>

    <h1>Leer archivo BLOB de la BBDD</h1>

    <br>

    <?php

        echo "Id: " . $Id . "<br>";
        echo "Tipo: " . $tipo . "<br>";
        echo "Contenido: " . "<img src='data:image/png; base64," . base64_encode($contenido) . "' '/>";

    ?>

 </center>

mysql php

asked by Mario G. Lucas 29.08.2018 в 16:41

source

2 answers

Send html form to SQL [closed] Symfony3 Form how to specify a br (spacing)

score 0 · Answer 1

Try converting the BLOB data to text as follows:

$sql="SELECT convert(Contenido USING utf8) as png, ARCHIVOS.* FROM ARCHIVOS WHERE Id=12";

$resultado=$base->prepare($sql);

$resultado->execute();

while($fila=$resultado->fetch(PDO::FETCH_ASSOC)){
    $Id=$fila["Id"];
    $contenido=$fila["png"];
    $tipo=$fila["Tipo"];
}

Tell me if it served you. greetings,

score 0 · Answer 2

I know what you ask is for MySQL , I will add this answer, what worked for me at the time, but in SQL , it is likely that it can serve as a reference.

You can create a function to have it available whenever you require it.

IN SQL

USE [pruebas]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[imagenABase64]
(
    @bin varbinary(max)
)
RETURNS varchar(max)
AS
BEGIN
    DECLARE @result varchar(max)

    SELECT @result = (select cast(N'' as xml).value('xs:base64Binary(xs:hexBinary(sql:variable("@bin")))', 'varchar(max)'));

    RETURN @result
END

Then with the function already created, you make the query:

SELECT pruebas.DBO.imagenABase64(Contenido) AS IMG
FROM pruebas.ARCHIVOS
WHERE Id = 12