Exporting Blob from MySQL Database to File with Only SQL

Exporting Blob from MySQL database to file with only SQL

I don't like the idea ...

drop procedure if exists dump_image;
delimiter //
  create procedure dump_image()
  begin

    declare this_id int;
    declare cur1 cursor for select imageId from image;
    open cur1;
      read_loop: loop
        fetch cur1 into this_id;
        set @query = concat('select blob_field from image where imageId=', 
            this_id, ' into outfile "/tmp/xyz-', this_id,'.jpg"');
        prepare write_file from @query;
        execute write_file;
      end loop;
    close cur1;
  end //
delimiter ;

Despite the error

mysql> call dump_image();
ERROR 1329 (02000): No data - zero rows fetched, selected, or processed

ls -1 /tmp/xyz*

MySQL Writing Image BLOB To Disk

Yes, it is possible. You can use SELECT command with INTO DUMPFILE clause. For example -

SELECT
  data_column
FROM
  table1
WHERE
  id = 1
INTO DUMPFILE 'image.png';

From the reference: If you use INTO DUMPFILE instead of INTO OUTFILE, MySQL writes only one row into the file, without any column or line termination and without performing any escape processing. This is useful if you want to store a BLOB value in a file.

sql query to download BLOB Data from mysql database

This is a very crude example but it should work. There is no graceful error handling or safety checks. It will overwrite existing files so make sure $file_path is writable and empty.

<?php

// Report all PHP errors
error_reporting(E_ALL);

$db_name = '';
$db_host = 'localhost';
$dsn = "mysql:dbname=$db_name;host=$db_host";
$user = '';
$password = '';

// the file path must be writable by the current user
$file_path = 'D:\\tmp\\';

// Creates a PDO instance representing a connection to a database
$PDO = new PDO($dsn, $user, $password);
$PDO->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// Prepares and executes an SQL statement and returns a PDOStatement object or false on failure.
$stmt = $PDO->query('SELECT `id`, `filename`, `content_type`, `content` FROM `faq_attachment`', PDO::FETCH_OBJ);

// iterates through the returned result set
foreach ($stmt as $file) {

    // prepare filename with full path to write to
    $filename = $file_path . str_replace(' ', '-', "{$file->id}_{$file->filename}");

    // writes the content from the BLOB to the given file
    file_put_contents($filename, $file->content);

}

Exported blob data different than DB data

The corrupted data from the new database looks like the result of converting the source data from ISO‑8859‑1 to UTF‑8 (e.g. U+00FF — ÿ — is FF in the former and C3 BF in the latter).

Since BLOBs have no character set, the character encoding is not controlled by server variables; I suspect mysqldump is outputting your BLOB data in a UTF-8–encoded file (which is the default), and it is encoded along the way somehow, through some combination of server settings and options passed to mysqldump.

The best solution may be to use the --hex-blob option when exporting BLOB fields, which would result in something like:

INSERT INTO `table` VALUES (0xFFD8FFE0...);

Basic info on how to export BLOB as files

There are several ways:

• Use isql command BLOBDUMP to write a blob to file,
• Use a client library (eg Jaybird for Java, Firebird .net provider for C#) to retrieve the data,
• With PHP you can use ibase_blob_get in a loop to get bytes from the blob, and write those to a file.

I don't use nor know EMS SQL Manager, so I don't know if (and how) you can export a blob with that.

The example you link to, and almost all tools you mention are for Microsoft SQL Server, not for Firebird; so it is no wonder those don't work.

Example in Java

A basic example to save blobs to disk using Java 8 (might also work on Java 7) would be:

/**
 * Example to save images to disk from a Firebird database.
 * <p>
 * Code assumes a table with the following structure:
 * <pre>
 * CREATE TABLE imagestorage (
 *     filename VARCHAR(255),
 *     filedata BLOB SUB_TYPE BINARY
 * );
 * </pre>
 * </p>
 */
public class StoreImages {
    // Replace testdatabase with alias or path of database
    private static final String URL = "jdbc:firebirdsql://localhost/testdatabase?charSet=utf-8";
    private static final String USER = "sysdba";
    private static final String PASSWORD = "masterkey";
    private static final String DEFAULT_FOLDER = "D:\\Temp\\target";

    private final Path targetFolder;

    public StoreImages(String targetFolder) {
        this.targetFolder = Paths.get(targetFolder);
    }

    public static void main(String[] args) throws IOException, SQLException {
        final String targetFolder = args.length == 0 ? DEFAULT_FOLDER : args[0];
        final StoreImages storeImages = new StoreImages(targetFolder);
        storeImages.store();
    }

    private void store() throws IOException, SQLException {
        if (!Files.isDirectory(targetFolder)) {
            throw new FileNotFoundException(String.format("The folder %s does not exist", targetFolder));
        }
        try (
            Connection connection = DriverManager.getConnection(URL, USER, PASSWORD);
            Statement stmt = connection.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT filename, filedata FROM imagestorage")
        ) {
            while (rs.next()) {
                final Path targetFile = targetFolder.resolve(rs.getString("FILENAME"));
                if (Files.exists(targetFile)) {
                    System.out.printf("File %s already exists%n", targetFile);
                    continue;
                }
                try (InputStream data = rs.getBinaryStream("FILEDATA")) {
                    Files.copy(data, targetFile);
                }
            }
        }
    }
}

Example in C#

Below is an example in C#, it is similar to the code above.

class StoreImages
{
    private const string DEFAULT_FOLDER = @"D:\Temp\target";
    private const string DATABASE = @"D:\Data\db\fb3\fb3testdatabase.fdb";
    private const string USER = "sysdba";
    private const string PASSWORD = "masterkey";

    private readonly string targetFolder;
    private readonly string connectionString;

    public StoreImages(string targetFolder)
    {
        this.targetFolder = targetFolder;
        connectionString = new FbConnectionStringBuilder
        {
            Database = DATABASE,
            UserID = USER,
            Password = PASSWORD
        }.ToString();
    }

    static void Main(string[] args)
    {
        string targetFolder = args.Length == 0 ? DEFAULT_FOLDER : args[0];
        var storeImages = new StoreImages(targetFolder);
        storeImages.store();
    }

    private void store()
    {
        if (!Directory.Exists(targetFolder))
        {
            throw new FileNotFoundException(string.Format("The folder {0} does not exist", targetFolder), targetFolder);
        }
        using (var connection = new FbConnection(connectionString))
        {
            connection.Open();
            using (var command = new FbCommand("SELECT filename, filedata FROM imagestorage", connection))
            using (var reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    string targetFile = Path.Combine(targetFolder, reader["FILENAME"].ToString());
                    if (File.Exists(targetFile))
                    {
                        Console.WriteLine("File {0} already exists", targetFile);
                        continue;
                    }

                    using (var fs = new FileStream(targetFile, FileMode.Create))
                    {
                        byte[] filedata = (byte[]) reader["FILEDATA"];
                        fs.Write(filedata, 0, filedata.Length);
                    }
                }
            }
        }
    }
}

fastest way to export blobs from table into individual files

I tried using a CLR function and it was more than twice as fast as BCP. Here's my code.

Original Method:

SET @bcpCommand = 'bcp "SELECT blobcolumn FROM blobtable WHERE ID = ' + CAST(@FileID AS VARCHAR(20)) + '" queryout "' + @FileName + '" -T -c'
EXEC master..xp_cmdshell @bcpCommand

CLR Method:

declare @file varbinary(max) = (select blobcolumn from blobtable WHERE ID = @fileid)
declare @filepath nvarchar(4000) = N'c:\temp\' + @FileName
SELECT Master.dbo.WriteToFile(@file, @filepath, 0)

C# Code for the CLR function

using System;
using System.Data;
using System.Data.SqlTypes;
using System.IO;
using Microsoft.SqlServer.Server;

namespace BlobExport
{
    public class Functions
    {
      [SqlFunction]
      public static SqlString WriteToFile(SqlBytes binary, SqlString path, SqlBoolean append)
      {        
        try
        {
          if (!binary.IsNull && !path.IsNull && !append.IsNull)
          {         
            var dir = Path.GetDirectoryName(path.Value);           
            if (!Directory.Exists(dir))              
              Directory.CreateDirectory(dir);            
              using (var fs = new FileStream(path.Value, append ? FileMode.Append : FileMode.OpenOrCreate))
            {
                byte[] byteArr = binary.Value;
                for (int i = 0; i < byteArr.Length; i++)
                {
                    fs.WriteByte(byteArr[i]);
                };
            }
            return "SUCCESS";
          }
          else
             "NULL INPUT";
        }
        catch (Exception ex)
        {          
          return ex.Message;
        }
      }
    }
}

---

Related Topics