Storing and Retrieving Images from SQL Server using Store Procedures in Transact-SQL - Armazenando e Recuperando Imagens do SQL Server usando Store Procedures em Transact-SQL

In SQL Server does not have the capability to export a varbinary (BLOB) images directly via SQL, for the Operating System (Windows), just as import, export is done via SSRS or using any programming language, but it was developed a stored procedure called usp_lo_export, below, follows the solution to the problem in question.

No SQL Server não tem o recurso para exportar um varbinary (blob), imagens, diretamente via SQL, para o Sistema Operacional (Windows), apenas como importar, a exportação é feita via SSRS ou usando alguma linguagem de programação, mas para isso foi desenvolvido uma store procedure chamada usp_lo_export, abaixo, segue a solução para o problema em questão.

Configuration for SQL Server 2008/2012

-- 
-- Functionality over safety
-- Funcionalidade em detrimento da segurança

-- Routine Tested in SQL SERVER 2008 Enterprise and SQL SERVER 2012 Enterprise
-- Procedimento Testado no SQL SERVER 2008 Enterprise e SQL SERVER 2012 Enterprise

-- Configuration for SQL Server procedure call xp_cmdshell
-- Configuracao do SQL Server para chamada de procedure xp_cmdshell
USE master; 
GO

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO

-- make dir c:\images
EXEC xp_cmdshell 'mkdir c:\images';

-- list dir c:\images 
EXEC xp_cmdshell 'dir c:\images\*.*';

Store Procedure usp_lo_export

USE master; -- change to your database - mudar para o seu bd
GO
-- Creating Stored Procedure
-- start procedure 
IF EXISTS ( SELECT name FROM sys.objects  WHERE object_id = OBJECT_ID(N'[dbo].[usp_lo_export]') AND type IN (N'P') )
 DROP PROCEDURE usp_lo_export;
GO
CREATE PROCEDURE usp_lo_export (@bdx varchar(65), @schemax varchar(65), @tablex varchar(65), @col_bin_imgx varchar(65), @pk_tablex varchar(65), @idx bigint, @file_namex varchar(255))
AS
-- Artifact Name/Program...: usp_lo_exportx.sql
-- Author..................: Emerson Hermann (emersonhermann [at] gmail.com) O Peregrino (http://www.emersonhermann.blogspot.com)
-- Date Start..............: 28/08/2012
-- Date Update.............: 08/12/2014
-- Version.................: 0.03
-- Compiler/Interpreter....: T-SQL (Transact SQL) 
-- OS......................: Windows
-- RDBMS...................: MS SQL Server 2008/2012
-- Kernel..................: Not informed! 
-- Porpuse - Finalidade....: Stored Procedure to export images (varbinary) to binary file database for SO - SP para exportar imagens (varbinary) para arquivo binário do banco de dados para o SO 
-- Notice .................: The parameters are: db, schema, table name, column type binary, primary key, primary key id and name of the binary file -
-- ........................: Os parametros são: bd, esquema, nome da tabela, coluna do tipo binaria, chave primária, id da chave primaria e nome do arquivo binario
--  
BEGIN
 SET NOCOUNT ON
 BEGIN TRY
   
  DECLARE @sql_stmt varchar(4000)
        , @sql_query  varchar(4000) 
        , @v_sql      varchar(4)
        , @echo_v_sql varchar(255)
        , @dir_bcp varchar(255) 
 
  -- example of using bcp into SQL Server 2008 to form Bcp.fmt file (set in Y last option)
  -- exemplo de uso de bcp em SQL Server 2008, para formar archive bcp.fmt (setar Y na ultima opcao) 
  -- bcp testdb.dbo.uvw_img out c:\images\test1.png -T 
  -- bcp.fmt created - ja criado 
  -- bcp testdb.dbo.uvw_img out c:\images\test1.png -T -f c:\images\bcp.fmt     
 
  -- model fmt file bcp (bcp.fmt) to import binary, SQL SERVER 2008 
  -- modelo de arquivo fmt do bcp (bcp.fmt) para importação binaria, SQL SERVER 2008 
  --10.0
  --1
  --1       SQLBINARY           0       0       ""   1     archive            ""
     
 
  -- generating configuration file
  -- gerando arquivo de configuracao 
  SET @v_sql      = SUBSTRING(CAST (SERVERPROPERTY('productversion') AS VARCHAR(max)),1,4) -- version sql server 
  SET @echo_v_sql = 'ECHO ' + @v_sql + ' > c:\images\conf_blob.fmt'
    
  EXEC xp_cmdshell @echo_v_sql, no_output
  EXEC xp_cmdshell 'ECHO 1 >> c:\images\conf_blob.fmt', no_output
  EXEC xp_cmdshell 'ECHO 1       SQLBINARY           0       0       ""   1     archive            "" >> c:\images\conf_blob.fmt', no_output
 
  IF object_id('tempdb.dbo.##blobx') IS NOT NULL BEGIN
       DROP TABLE ##blobx
  END
  CREATE TABLE ##blobx
  (
       file_binx varbinary(max)
  )
   
  -- populating global temporary table with the table data, where the blob
  -- povoando tabela temporária global com os dados da tabela, onde se encontra o blob
  SET @sql_query = 'INSERT INTO tempdb.dbo.##blobx
                    SELECT ' + @col_bin_imgx  + '
                      FROM ' + @bdx + '.' + @schemax + '.' + @tablex + '
                     WHERE ' + @pk_tablex + ' = ' + ltrim(str(@idx)) 
  PRINT @sql_query
  EXEC (@sql_query)

            
  -- for use with dynamic sql xp_cmdshell SP - sql dinamico para uso com SP xp_cmdshell
  -- bcp tempdb.dbo.##blobx out c:\images\test1.png -T -f c:\images\bcp.fmt
  IF @v_sql = '10.0' BEGIN
       SET @dir_bcp = '"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\bcp"'
  END IF @v_sql = '11.0' BEGIN
       SET @dir_bcp = '"C:\Program Files\Microsoft SQL Server\110\Tools\Binn\bcp"'
  END ELSE BEGIN
       SET @dir_bcp = 'bcp'
  END
   
  SET @sql_stmt = @dir_bcp + ' tempdb.dbo.##blobx out c:\images\' + ltrim(str(@idx)) + '_' + @file_namex + ' -T -f c:\images\conf_blob.fmt'
 
  PRINT 'Exporting binary ...'
  EXEC xp_cmdshell @sql_stmt, no_output
       
 END TRY
 
 BEGIN CATCH
  SELECT ERROR_NUMBER()  AS error_number
       , ERROR_SEVERITY()  AS error_severity
       , ERROR_STATE()  AS error_state
       , ERROR_PROCEDURE() AS error_procedure
       , ERROR_LINE()   AS error_line
       , ERROR_MESSAGE()  AS error_message;    
 END CATCH;
  
 -- cleaning dirt, eliminating temporary table - limpando sujeira, eliminando tabela temporaria
 IF object_id('tempdb.dbo.##blobx') IS NOT NULL BEGIN
      DROP TABLE ##blobx
 END 
 -- cleaning, eliminating the bcp format file - limpando sugeira, eliminando arquivo de formatacao do bcp 
 EXEC xp_cmdshell 'DEL c:\images\conf_blob.fmt', no_output
 
 SET NOCOUNT ON
END; 
GO 
-- end procedure 

Working with stored procedure usp_lo_export

USE testdb; -- change to your database - mudar para o seu bd
GO

-- Creating Table Test
IF EXISTS ( SELECT name FROM sys.objects  WHERE object_id = OBJECT_ID(N'[dbo].[image]') AND type IN (N'U') )
 DROP TABLE image;
GO
CREATE TABLE image
(
   id_image int identity(1,1) NOT NULL  PRIMARY KEY
 , file_name nvarchar(1000) NOT NULL
 , archive varbinary(max) -- binary type, implemented in sql2005 sp2
);
GO

-- Only test - Apenas para teste
IF EXISTS ( SELECT name FROM sys.objects  WHERE object_id = OBJECT_ID(N'[dbo].[uvw_img]') AND type IN (N'V') )
 DROP VIEW uvw_img;
GO
CREATE VIEW uvw_img AS
SELECT TOP 1 archive FROM image
GO

-- If you need to test again - Caso precise fazer teste novamente
--TRUNCATE TABLE image;


-- Populating table with images - Povoando tabela com imagens
INSERT INTO image (file_name, archive) 
SELECT 'koala.jpg', BULKColumn FROM OPENROWSET(BULK N'C:\Users\Public\Pictures\Sample Pictures\koala.jpg', SINGLE_BLOB) AS image;

INSERT INTO image (file_name, archive) 
SELECT 'desert.jpg', BULKColumn FROM OPENROWSET(BULK N'C:\Users\Public\Pictures\Sample Pictures\desert.jpg', SINGLE_BLOB) AS image;

INSERT INTO image (file_name, archive) 
SELECT 'penguins.jpg', BULKColumn FROM OPENROWSET(BULK N'C:\Users\Public\Pictures\Sample Pictures\penguins.jpg', SINGLE_BLOB) AS image;

INSERT INTO image (file_name, archive) 
SELECT 'plan_test.xlsx', BULKColumn FROM OPENROWSET(BULK N'C:\Users\Public\Documents\plan_test.xlsx', SINGLE_BLOB) AS Image;
GO

-- Listing images
SELECT * FROM image;


-- Call the Store Procedure - Examples
-- Ex 01 - correct data reported
EXEC master.dbo.usp_lo_export 'testdb','dbo','image','archive','id_image',1,'koala.jpg';
EXEC master.dbo.usp_lo_export 'testdb','dbo','image','archive','id_image',2,'desert.jpg';
EXEC master.dbo.usp_lo_export 'testdb','dbo','image','archive','id_image',3,'penguins.jpg';

SELECT * FROM image;

-- Ex 02 - Table wrongly informed - Tabela informada errada 
EXEC master.dbo.usp_lo_export 'testdb','dbo', 'imagex','archive','id_image',3,'penguins.jpg';

-- Ex 03 - Correct data reported, electronically excel spreadsheet - Dados informados corretos, planilha eletronica do excel 
EXEC master.dbo.usp_lo_export 'testdb','dbo','image','archive','id_image',4,'plan_excel.xlsx';

-- Ex 04 - Use with anonymous block - Uso com bloco anônimo 
DECLARE @file_namex varchar(255)
      , @idx bigint 
SET @idx = 1
SELECT @file_namex = file_name FROM image WHERE id_image = @idx
EXEC master.dbo.usp_lo_export 'testdb','dbo','image','archive','id_image', @idx, @file_namex; 

The peace of the Lord Jesus