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

Algoritmos, Caixa Eletrônico em SQL SERVER

Anteriormente em setembro/2010, havia escrito um script para caixa eletrônico em linguagem PL/PgSQL do Postgres, agora foi desenvolvido o mesmo algoritmo de caixa eletrônico para a linguagem Transact do SQL SERVER.

O parâmetro é o valor em inteiro no qual retorna as cédulas das notas em Real.

Com poucas adaptações, pode se remover a nota de 1 Real, para ficar com o novo padrão de cédulas da moeda Real do Brasil.

Mais uma vez, espero ter ajudado.

--Retornando notas do caixa eletrônico
--Notas de 1, 2, 5, 10, 20, 50 e 100

IF EXISTS (
            SELECT * 
              FROM sys.objects 
             WHERE object_id = OBJECT_ID(N'[dbo].[usf_caixa_eletro]') 
               AND type IN (N'FN')
           )
 DROP FUNCTION usf_caixa_eletro;

CREATE FUNCTION usf_caixa_eletro (@pvalor int) RETURNS varchar(max) AS 
--
-- Nome Artefato/Programa..: usf_caixa_eletro_sql_server.sql
-- Autor(es)...............: O Peregrino (emersonhermann at gmail.com) 
-- Data Inicio ............: 02/08/2012
-- Data Atual..............: 03/08/2012
-- Versao..................: 0.01
-- Linguagem...............: TRANSACT 
-- Compilador/Interpretador: T-SQL 
-- Sistemas Operacionais...: Windows
-- SGBD....................: SQL SERVER 2005/2008/2012
-- Kernel..................: Nao informado!
-- Finalidade..............: Caixa Eletronico
-- OBS1....................: Caixa Eletronico
 
--
/*
Algoritmo Caixa Eletronico
notasSaída = []                         #guardar as notas que saírão do caixa eletrônico
notas = [100, 50, 20, 10, 5, 1]         #notas que podem ser sacadas
valor = 375                             #valor a ser sacado
 
restante = valor                        #faz uma cópia do valor em "restante"
inota = 0                               #índice da nota: 0 é 100, 1 é 50, 2 é 20, 3 é 10, ...
enquanto restante>0:                    #enquanto restante for maior que 0
  resultado = restante-notas[inota]     #calcula o resultado da subtração entre o valor e a nota
  se resultado<0:                       #se for negativo:
    inota++                             #incrementa o índice para a próxima nota
  senão:                                #se for positivo ou zero:
    restante = resultado                #deixa restante com o novo resultado
    notasSaída.adicionar(notas[inota])  #adiciona a nota utilizada nas que devem sair do caixa
 
para nota em notasSaída:                # escreve as notas que devem sair
  escreva nota
 
*/
BEGIN
 DECLARE
     @sretorno   varchar(max)
    ,@qnota1     integer
    ,@qnota2     integer
    ,@qnota5     integer
    ,@qnota10    integer
    ,@qnota20    integer
    ,@qnota50    integer
    ,@qnota100   integer
    ,@pvalorx    integer
    ,@residual   integer
    ,@restante   integer
    ,@vet_notas1 integer
    ,@vet_notas2 integer
    ,@vet_notas3 integer
    ,@vet_notas4 integer
    ,@vet_notas5 integer
    ,@vet_notas6 integer
    ,@vet_notas7 integer
    ,@i          integer
    ,@resultado  integer

    SET @vet_notas1=100
    SET @vet_notas2=50
    SET @vet_notas3=20
    SET @vet_notas4=10
    SET @vet_notas5=5
    SET @vet_notas6=2
    SET @vet_notas7=1
     
    SET @i         = 1
    SET @qnota1    = 0
    SET @qnota2    = 0
    SET @qnota5    = 0
    SET @qnota10   = 0
    SET @qnota20   = 0
    SET @qnota50   = 0
    SET @qnota100  = 0
    SET @pvalorx   = 0
    SET @resultado = 0
    SET @sretorno  = ''
    SET @restante  = @pvalor
 
    WHILE (@i <= 7) BEGIN
     
    
        IF @i = 1 BEGIN 
            SET @resultado = @restante - @vet_notas1
        END ELSE IF @i = 2 BEGIN 
            SET @resultado = @restante - @vet_notas2 
        END ELSE IF @i = 3 BEGIN 
            SET @resultado = @restante - @vet_notas3
        END ELSE IF @i = 4 BEGIN 
            SET @resultado = @restante - @vet_notas4
        END ELSE IF @i = 5 BEGIN 
            SET @resultado = @restante - @vet_notas5
        END ELSE IF @i = 6 BEGIN 
            SET @resultado = @restante - @vet_notas6
        END ELSE IF @i = 7 BEGIN 
            SET @resultado = @restante - @vet_notas7
        END 

     
        IF (@resultado < 0) BEGIN
          
           SET @i = @i + 1
               
        END ELSE BEGIN -- senao
 
           SET @restante = @resultado                             

           IF @i = 1 BEGIN
               SET @qnota100 = @qnota100 + 1
           END ELSE IF @i = 2 BEGIN
               SET @qnota50 = @qnota50 + 1
           END ELSE IF @i = 3 BEGIN
               SET @qnota20 = @qnota20 + 1
           END ELSE IF @i = 4 BEGIN
               SET @qnota10 = @qnota10 + 1
           END ELSE IF @i = 5 BEGIN
               SET @qnota5  = @qnota5 + 1
           END ELSE IF @i = 6 BEGIN
               SET @qnota2  = @qnota2 + 1
           END ELSE IF @i = 7 BEGIN
               SET @qnota1  = @qnota1 + 1
           END --fim_se 
              
        END -- fim_se 
  
    END -- fim_enquanto
     
     
    SET @sretorno = 'Total: '
                 + cast (@pvalor as varchar(max))
                 + ' ' --chr(10)
                 + 'Notas de 100:'
                 + cast (@qnota100 as varchar(max))
                 + ' ' --chr(10)
                 + 'Notas de 50:'
                 + cast (@qnota50 as varchar(max))
                 + ' ' --chr(10)
                 + 'Notas de 20:'
                 + cast (@qnota20 as varchar(max))
                 + ' ' --chr(10)
                 + 'Notas de 10:'
                 + cast (@qnota10 as varchar(max))
                 + ' ' --chr(10)
                 + 'Notas de 5:'
                 + cast (@qnota5 as varchar(max)) 
                 + ' ' --chr(10)
                 + 'Notas de 2:'
                 + cast (@qnota2 as varchar(max))
                 + ' ' --chr(10)
                 + 'Notas de 1:'
                 + cast (@qnota1 as varchar(max))
                 
    RETURN (@sretorno) -- Retorna as linhas
END;
GO
 
--alguns testes, chamada da function
 
--SELECT dbo.usf_caixa_eletro(2678);
--SELECT dbo.usf_caixa_eletro(1078);