Como importar e exportar imagens entre o SQL Server e o File System ? – Parte I

Bom Dia Pessoal,

Em projetos que envolvam o armazenamento de imagens, é natural que haja uma discussão muito comum entre desenvolvedores, DBAs, arquitetos, etc sobre o melhor local para armazenamento de imagens. É sem dúvida uma discussão muito pertinente, pois, não há uma solução ideal para todos os casos. Armazenar imagens no banco de dados como um BLOB, administrá-la no File System ou ainda utilizar um banco de dados não relacional são as alternativas mais comuns para essa atividade. Todas possuem vantagens, desvantagens e alguns contratempos associados. Em determinadas situações, a escolha de colocar as imagens no FileSystem pode parecer mais interessante que uma coluna VarBinary (ou Image), mas pode ser que com o tempo a melhor escolha se reverta. É factível que um projeto inicie o armazenamento em colunas do tipo Blob (Binary Large Objects) e posteriormente seja necessário revertê-las para o File System. O tipo de dados FILESTREAM do SQL Server 2008 torna esse processo muito transparente, mas como fazer para converter imagens armazenadas no banco de dados para o File System e vice versa quando esse tipo de dados não estiver sendo utilizado ? Como fazer para converter os diversos registros em imagens ou ainda carregar diversas imagens para o banco de dados ?

Para demonstrar como importar e exportar as imagens entre o SQL Server e o File System, utilizarei as imagens das bandeiras de alguns países (Alemanha, Brasil, China, Espanha, Estados Unidos e Inglaterra). As imagens podem ser obtidas facilmente na Internet, mas se necessário, disponibilizei as mesmas no link abaixo:

http://cid-f4f5c630410b9865.skydrive.live.com/self.aspx/ProjetosSQLServer/20090610%7C_Imagens.zip

Antes de prosseguir, também criarei um banco de dados, um usuário e uma tabela para armazenar as imagens conforme o script abaixo:

— Cria um banco de dados
CREATE DATABASE Imagens

— Cria um login para inserir as imagens
EXEC sp_addlogin ‘UsrImg’,‘pwdImg’

— Concede privilégios para o usuário
USE Imagens
GO
EXEC
sp_grantdbaccess ‘UsrImg’,‘UsrImg’
GO

— Cria uma tabela para armazenar as bandeiras
CREATE TABLE Bandeiras (Pais VARCHAR(50),
    Arquivo VARCHAR(100), Imagem IMAGE NOT NULL)

— Adiciona um valor Default (necessário para o TextCopy)
ALTER TABLE Bandeiras ADD CONSTRAINT
    DF_Imagem DEFAULT 0x0 FOR Imagem

— Concede as devidas permissões ao usuário UsrImg
GRANT SELECT, INSERT, UPDATE ON Bandeiras TO UsrImg

Esse script cria uma coluna do tipo IMAGE com o valor padrão 0x0 que é um valor binário. O tipo de dados IMAGE foi utilizado porque no SQL Server 2000 não está disponível o VARBINARY(MAX). As stored procedures sp_addlogin e sp_grantdbaccess também foram utilizadas, pois, no SQL Server 2000 os comandos CREATE LOGIN e CREATE USER não estão disponíveis. Em alguns aplicativos e APIs não é suportado um Blob com valor nulo e para evitar problemas é utilizado o valor default 0x0. Após a criação da tabela é chegada a hora de inserir os registros (as imagens serão inseridas em etapa posterior). Está sendo admitido que as imagens estão no diretório G:\Imagens.

INSERT INTO Bandeiras (Pais, Arquivo) VALUES (‘Alemanha’,‘G:\Imagens\Alemanha.jpg’)
INSERT INTO Bandeiras (Pais, Arquivo) VALUES (‘Brasil’,‘G:\Imagens\Brasil.jpg’)
INSERT INTO Bandeiras (Pais, Arquivo) VALUES (‘China’,‘G:\Imagens\China.jpg’)
INSERT INTO Bandeiras (Pais, Arquivo) VALUES (‘Espanha’,‘G:\Imagens\Espanha.jpg’)
INSERT INTO Bandeiras (Pais, Arquivo) VALUES (‘Estados Unidos’,‘G:\Imagens\EstadosUnidos.jpg’)
INSERT INTO Bandeiras (Pais, Arquivo) VALUES (‘Inglaterra’,‘G:\Imagens\Inglaterra.jpg’)

Importando imagens com o TextCopy

O TextCopy é um utilitário de linha de comando disponível no SQL Server 2000 para importar / exportar imagens sendo encontrado na pasta BINN da instância em questão (normalmente C:\Program Files\Microsoft SQL Server\MSSQL\Binn). Embora seja um executável simples, não é possível simplesmente copiá-lo. As instruções abaixo fazem o upload das imagens para o banco de dados. Elas devem ser executadas em um prompt de comando em um servidor onde o SQL Server 2000 esteja instalado (sugiro utilizar uma BAT).

E:\>"C:\Program Files\Microsoft SQL Server\MSSQL\Binn\textcopy.exe" /S .\SQL2005 /I /D Imagens /T Bandeiras /C Imagem /F G:\Imagens\Alemanha.jpg /U UsrImg /P pwdImg /W "WHERE Pais = ‘Alemanha’"
E:\>"C:\Program Files\Microsoft SQL Server\MSSQL\Binn\textcopy.exe" /S .\SQL2005 /I /D Imagens /T Bandeiras /C Imagem /F G:\Imagens\Brasil.jpg /U UsrImg /P pwdImg /W "WHERE Pais = ‘Brasil’"
E:\>"C:\Program Files\Microsoft SQL Server\MSSQL\Binn\textcopy.exe" /S .\SQL2005 /I /D Imagens /T Bandeiras /C Imagem /F G:\Imagens\China.jpg /U UsrImg /P pwdImg /W "WHERE Pais = ‘China’"
E:\>"C:\Program Files\Microsoft SQL Server\MSSQL\Binn\textcopy.exe" /S .\SQL2005 /I /D Imagens /T Bandeiras /C Imagem /F G:\Imagens\Espanha.jpg /U UsrImg /P pwdImg /W "WHERE Pais = ‘Espanha’"
E:\>"C:\Program Files\Microsoft SQL Server\MSSQL\Binn\textcopy.exe" /S .\SQL2005 /I /D Imagens /T Bandeiras /C Imagem /F G:\Imagens\EstadosUnidos.jpg /U UsrImg /P pwdImg /W "WHERE Pais = ‘Estados Unidos’"
E:\>"C:\Program Files\Microsoft SQL Server\MSSQL\Binn\textcopy.exe" /S .\SQL2005 /I /D Imagens /T Bandeiras /C Imagem /F G:\Imagens\Inglaterra.jpg /U UsrImg /P pwdImg /W "WHERE Pais = ‘Inglaterra’"

Alguns parâmetros do batch acima merecem ser explicados.

  • O nome do utilitário "C:\Program Files\Microsoft SQL Server\MSSQL\Binn\textcopy.exe" está entre aspas duplas, pois, o nome do diretório possui espaços
  • O parâmetro /S informa o nome do servidor (no caso uma instância nomeada no servidor local)
  • O parâmetro /I informa o fluxo que trata-se de uma importação (Input) para o SQL Server
  • O parâmetro /D informa o nome do banco de dados (Imagens)
  • O parâmetro /T informa o nome da tabela (Bandeiras)
  • O parâmetro /C informa o nome da coluna (Imagem)
  • O parâmetro /F informa o nome do arquivo a ser carregado para a coluna Blob
  • O parâmetro –U corresponde ao usuário utilizado para conexão e o –P a senha utilizada (não é possível utilizar autenticação integrada)
  • O parâmetro –W estipula uma cláusula WHERE para encontrar o registro onde o arquivo irá ser carregado (apenas um registro por vez)

Esse conjunto de seis linhas fez o upload das imagens para o respectivo pais. Uma consulta à tabela Bandeiras pode mostrar que a coluna Imagem (Blob) foi alterada do valor 0x0 para alguma representação em hexadecimal. O script abaixo gera todas as instruções de exportação:

DECLARE
    @PathExe VARCHAR(80), @Srv VARCHAR(20), @BD VARCHAR(20),
    @Tab VARCHAR(20), @Col VARCHAR(20), @Usr VARCHAR(20),
    @Pwd VARCHAR(20), @Where VARCHAR(50), @cmd VARCHAR(200)

SET @PathExe = ‘"C:\Program Files\Microsoft SQL Server\MSSQL\Binn\textcopy.exe"’
SET @Srv = ‘.\SQL2005’
SET @BD = ‘Imagens’
SET @Tab = ‘Bandeiras’
SET @Col = ‘Imagem’
SET @Usr = ‘UsrImg’
SET @Pwd = ‘pwdImg’
SET @Where = ‘"WHERE Pais = ”?2”"’

SET @cmd = @PathExe + ‘ /S ‘ + @Srv + ‘ /I /D ‘ + @BD + ‘ /T ‘ + @Tab + ‘ /C ‘
SET @cmd = @cmd + @Col + ‘ /F ?1 /U ‘ + @Usr + ‘ /P ‘ + @Pwd + ‘ /W ‘ + @Where

SELECT REPLACE(REPLACE(@cmd,‘?1’,Arquivo),‘?2’,Pais) FROM Bandeiras

Exportando imagens com o TextCopy

Da mesma forma que o TextCopy possui o parâmetro /I para determinar que o fluxo se trata de uma importação (I – Input para o SQL Server) é possível definir o parâmetro /O determinado o fluxo de exportação (O – Output do SQL Server). Os mesmos comandos do batch anterior exportar as imagens para uma outra pasta.

E:\>"C:\Program Files\Microsoft SQL Server\MSSQL\Binn\textcopy.exe" /S .\SQL2005 /O /D Imagens /T Bandeiras /C Imagem /F G:\Exp\Alemanha.jpg /U UsrImg /P pwdImg /W "WHERE Pais = ‘Alemanha’"
E:\>"C:\Program Files\Microsoft SQL Server\MSSQL\Binn\textcopy.exe" /S .\SQL2005 /O /D Imagens /T Bandeiras /C Imagem /F G:\Exp\Brasil.jpg /U UsrImg /P pwdImg /W "WHERE Pais = ‘Brasil’"
E:\>"C:\Program Files\Microsoft SQL Server\MSSQL\Binn\textcopy.exe" /S .\SQL2005 /O /D Imagens /T Bandeiras /C Imagem /F G:\Exp\China.jpg /U UsrImg /P pwdImg /W "WHERE Pais = ‘China’"
E:\>"C:\Program Files\Microsoft SQL Server\MSSQL\Binn\textcopy.exe" /S .\SQL2005 /O /D Imagens /T Bandeiras /C Imagem /F G:\Exp\Espanha.jpg /U UsrImg /P pwdImg /W "WHERE Pais = ‘Espanha’"
E:\>"C:\Program Files\Microsoft SQL Server\MSSQL\Binn\textcopy.exe" /S .\SQL2005 /O /D Imagens /T Bandeiras /C Imagem /F G:\Exp\EstadosUnidos.jpg /U UsrImg /P pwdImg /W "WHERE Pais = ‘Estados Unidos’"
E:\>"C:\Program Files\Microsoft SQL Server\MSSQL\Binn\textcopy.exe" /S .\SQL2005 /O /D Imagens /T Bandeiras /C Imagem /F G:\Exp\Inglaterra.jpg /U UsrImg /P pwdImg /W "WHERE Pais = ‘Inglaterra’"

Esse conjunto de seis linhas fez o download das imagens do banco de dados para o diretório G:\Exp (cada imagem com o nome de seu respectivo país). Opcionalmente pode-se utilizar um script para gerar os comandos (no caso não alterei o destino final para G:\Exp).

DECLARE
    @PathExe VARCHAR(80), @Srv VARCHAR(20), @BD VARCHAR(20),
    @Tab VARCHAR(20), @Col VARCHAR(20), @Usr VARCHAR(20),
    @Pwd VARCHAR(20), @Where VARCHAR(50), @cmd VARCHAR(200)

SET @PathExe = ‘"C:\Program Files\Microsoft SQL Server\MSSQL\Binn\textcopy.exe"’
SET @Srv = ‘.\SQL2005’
SET @BD = ‘Imagens’
SET @Tab = ‘Bandeiras’
SET @Col = ‘Imagem’
SET @Usr = ‘UsrImg’
SET @Pwd = ‘pwdImg’
SET @Where = ‘"WHERE Pais = ”?2”"’

SET @cmd = @PathExe + ‘ /S ‘ + @Srv + ‘ /O /D ‘ + @BD + ‘ /T ‘ + @Tab + ‘ /C ‘
SET @cmd = @cmd + @Col + ‘ /F ?1 /U ‘ + @Usr + ‘ /P ‘ + @Pwd + ‘ /W ‘ + @Where

SELECT REPLACE(REPLACE(@cmd,‘?1’,Arquivo),‘?2’,Pais) FROM Bandeiras

Importando imagens com o comando OPENROWSET

A importação de dados baseada no TextCopy é um pouco rudimentar, pois, depende de um executável externo. Além de representar a necessidade de instalação do SQL Server (não é possível simplesmente copiar o executável e utilizá-lo), o fato de impossibilitar o uso da autenticação integrada é um desincentivo à sua utilização em conjunto com arquivos bat e outros executáveis. A partir do SQL Server 2005 é possível utilizar a instrução OPENROWSET para importar BLOBs para o banco de dados garantindo um pouco mais flexibilidade. O exemplo abaixo mostra como fazer isso:

— Verifica os registros
SELECT Pais, Arquivo, Imagem FROM Bandeiras

— "Zera" as imagens do banco
UPDATE Bandeiras SET Imagem = 0x0

— Carrega as imagens para uma tabela a parte
DECLARE @Bandeiras TABLE (Pais VARCHAR(50), Imagem IMAGE)

— Insere a Bandeira da Alemanha
INSERT INTO @Bandeiras
SELECT ‘Alemanha’, * FROM OPENROWSET(BULK N’C:\Imagens\Alemanha.jpg’, SINGLE_BLOB) As Document

— Insere a Bandeira do Brasil
INSERT INTO @Bandeiras
SELECT ‘Brasil’, * FROM OPENROWSET(BULK N’C:\Imagens\Brasil.jpg’, SINGLE_BLOB) As Document

— Insere a Bandeira da China
INSERT INTO @Bandeiras
SELECT ‘China’, * FROM OPENROWSET(BULK N’C:\Imagens\China.jpg’, SINGLE_BLOB) As Document

— Insere a Bandeira da Espanha
INSERT INTO @Bandeiras
SELECT ‘Espanha’, * FROM OPENROWSET(BULK N’C:\Imagens\Espanha.jpg’, SINGLE_BLOB) As Document

— Insere a Bandeira dos Estados Unidos
INSERT INTO @Bandeiras
SELECT ‘Estados Unidos’, * FROM OPENROWSET(BULK N’C:\Imagens\EstadosUnidos.jpg’, SINGLE_BLOB) As Document

— Insere a Bandeira da Inglaterra
INSERT INTO @Bandeiras
SELECT ‘Inglaterra’, * FROM OPENROWSET(BULK N’C:\Imagens\Inglaterra.jpg’, SINGLE_BLOB) As Document

— Atualiza a tabela de Bandeiras com base no País
UPDATE Bandeiras SET Imagem = tmp.Imagem
FROM Bandeiras As B
INNER JOIN @Bandeiras As tmp ON B.Pais = tmp.Pais

— Verifica os registros
SELECT Pais, Arquivo, Imagem FROM Bandeiras

Infelizmente o SQL Server 2005 não disponibiliza uma forma de exportar imagens via TSQL (o OPENROWSET apenas exporta). O TextCopy pode ser utilizado com o SQL Server 2005, mas será necessário que o TextCopy seja executado a partir de um Host que possua o SQL Server 2000, pois, esse utilitário não está no 2005. Embora o 2005 tenha flexibilizado a importação de imagens com o OPENROWSET as imagens tem de ser importadas uma por vez e é necessário conhecer previamente os arquivos a serem importados.

No próximo artigo demonstrarei algumas soluções do SQL Server mais robustas para transferir as imagens do File System para o SQL Server e vice-versa. Aos que pensarem em utilizar o TextCopy com a xp_cmdshell cabe um aviso. Enquanto o TextCopy faz uma importação ou exportação recursos do SQL Server estão sendo consumidos. Além dos recursos que a xp_cmdshell utiliza serem um pouco mais escassos que o Buffer Pool tradicional, acho incoerente pensar que uma consulta está sendo rodada mais lentamente porque o SQL Server (e não o servidor de aplicação) está importando arquivos. Não é interessante utilizar esse tipo de mecanismo para importar / exportar grandes quantidades de imagens. Se esse for o caso, o ideal é procurar as APIs de aplicação para fazê-lo (embora as soluções que apresentarei no próximo artigo também o façam). Alguns links úteis sobre o assunto:

How To Access and Modify SQL Server BLOB Data by Using the ADO Stream Object
http://support.microsoft.com/kb/258038/en-us

How To Read and Write BLOB Data by Using ADO.NET with Visual Basic .NET
http://support.microsoft.com/kb/308042/en-us

[ ]s,

Gustavo

3 Respostas para “Como importar e exportar imagens entre o SQL Server e o File System ? – Parte I

  1. Muito interessante Gustavo.Sou DBA com histórico de trabalho em infra, tipo servidores, redes, storage, etc e artigos mais focados em codificação me interessam muito para conhecer capacidades do SQL Server que não fazem parte do meu dia a dia. Vou testar os código assim que possível.Aliás vou começar a escrever artigos sobre o assunto tb e quando achar interessante colocarei links para seus artigos.AbsAlex M. Bastos

  2. Olá Alex,Por incrível que pareça eu também sou um DBA mais voltado para infraestrutura, mas escrevo mais artigos voltados para o desenvolvimento (que contraditório (rs)). Fico contente pelo feedback e espero que os artigos lhe sejam muito úteis.Abs,

  3. Oi Gustavo,tudo bem?Gostaria de te fazer um convite. Você pode entrar em contato comigo através do e-mail eduspinola@gmail.com?Abraço,Eduardo Spínola

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s