Save image file on disk from image data in sql table

Question

I have an sql table that stores images in varbinary format. I want to take the image data from sql table and store it as image file on my system.

$getPhotos = "SELECT Name, PhotoContent
              FROM [Photos]";                 
$resultPhotos = odbc_exec($con, $getPhotos);

while($post = odbc_fetch_array($resultPhotos)){
    $fileData = $post['PhotoContent'];
    $fileName = "D:\\Images\\".$post['Name'];
    file_put_contents($fileName,str_replace('data:image/jpeg;base64,','',$fileData));
}

The above code saves the image file on the system but, the saved image is broken.

How do I fix this issue ?

Thanks

I really dont understand. Does the images exist on a file server and you want to copy the files. Or is the data in PhotoContent is the Content of the photo? Can you give me an example of the content? — mvisser, Jun 14 '16 at 16:13
yes.. the data in photocontent is the content of the photo, which is stored in sql table in varbinary(max) format — Sana, Jun 14 '16 at 16:16
If you need to strip `data:image/jpeg;base64` your data is not binary at all. In fact it's not a raw picture but a base64-serialized one. Thus you need to base64-decode it first. — Álvaro González, Jun 15 '16 at 09:20
BTW, I hope that `$post['Name']` is carefully validated before you call `$fileName = "D:\\Images\\".$post['Name'];`. I don't need to explain the huge security hole it'd be otherwise. — Álvaro González, Jun 15 '16 at 09:22

mvisser · Answer 1 · 2016-06-15T09:37:46.577

Ok you can do all of this in MS SQL. Since I know nothing about PHP.

So first this is my table below.

1) Create blob format file, so you can export your blob.

    DECLARE @sql VARCHAR(500)
    SET @sql = 'bcp DatabaseName.dbo.Blob format nul -T -n -f C:\Users\Public\Documents\blob.fmt -S ' + @@SERVERNAME
    SELECT @sql 
    EXEC master.dbo.xp_CmdShell @sql

Format File

2) Edit the Format File. Keep only the Content column.

2.1) Original Format

2.2) Change the format as below

3) Lastly I created a Stored Procedure that will change all varbinary data into .jpb(You can also have it in different format not just .jpg).

CREATE PROCEDURE BLOBImageExport

AS

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON

IF OBJECT_ID('tempdb..#export') IS NOT NULL DROP TABLE #export

DECLARE @imgID INT,
    @imgName VARCHAR(128),
    @sql VARCHAR(8000)

SELECT * 
INTO #export
FROM Blob


WHILE (SELECT COUNT(*) FROM #export) > 0
BEGIN
    SELECT TOP 1 @imgID = ID,
        @imgName = imgName
    FROM #export

    SET @sql = 'BCP "SELECT Content FROM DATABASENAME.dbo.Blob WHERE ID = '  + CONVERT(VARCHAR(10), @imgID) + '" QUERYOUT C:\Users\Public\Documents\' + '' + @imgName + '' + ' -T -f C:\Users\Public\Documents\blob.fmt -S ' + @@SERVERNAME

    EXEC master.dbo.xp_CmdShell @sql

    DELETE #export
    WHERE ID = @imgID
END

4) Now you can see the images below

4.1) Accountant Image

4.2) Accountant2 Image

5) So, you could either in PHP copy all the images to your specified drive or use SSIS or change the location in Stored Procedure where you want to save to disk.

Hope this helps.

score 0 · Answer 2 · edited Mar 17 '17 at 12:03

In MySQL server you can use this code:

using MySql.Data.MySqlClient;
using System.IO;
using System.Globalization;

int i=0; //variable for image name

MySqlConnection conn = new MySqlConnection("SERVER=127.0.0.1;DATABASE=test;UID=root;PASSWORD=1234");
conn.Open();
MySqlCommand cmd = new MySqlCommand("SELECT signature  FROM  imageTble  ", conn);
MySqlDataReader myReader = null;
myReader = cmd.ExecuteReader();

byte[] imgg = (byte[])(myReader["signature"]);

while (myReader.Read())
{
    i = i + 1;
    File.WriteAllBytes(@"C:\Users\Admin\Desktop\New folder\IMAGES\" + i + ".tiff", imgg);
}

If you using MS SQl database you can use:

using System.Data.Sql;

referance and used sqlkeyword instaent of MYSql

Eg----> SqlConnection, SqlCommand,SqlDataReader

Save image file on disk from image data in sql table

2 Answers2