Export image saved as Varbinary in different formats

Asked

Viewed 82 times

1

I have some images in the SQL Server 2017 database that were recorded as varbinary. I already have a script that exports as . jpg, but I found that some images are actually . png or . bmp.

Is there any way to validate in which format the image was saved before exporting?

Follow the function I created:

CREATE OR ALTER FUNCTION EXPORTAIMAGEM(@IDIMAGEM uniqueidentifier)
RETURNS NVARCHAR(MAX)
as
BEGIN
EXEC master.dbo.xp_cmdshell 'md c:\EXPORTA';
DECLARE @pctStr INT
DECLARE @image VARBINARY(MAX)
DECLARE @MENSAGEM NVARCHAR(MAX)
SET @image= (SELECT DATA FROM Document
              WHERE ID=@IDIMAGEM)
DECLARE @filePath NVARCHAR(MAX)
SET @filePath = 'C:\EXPORTA\Imagem_'+ CONVERT(nvarchar(MAX),@IDIMAGEM) +'.jpg'
EXEC sp_OACreate 'ADODB.Stream', @pctStr OUTPUT
EXEC sp_OASetProperty @pctStr, 'Type', 1
EXEC sp_OAMethod @pctStr, 'Open'
EXEC sp_OAMethod @pctStr,  'Write', NULL, @image
EXEC sp_OAMethod @pctStr, 'SaveToFile', NULL,@filePath, 2
EXEC sp_OAMethod @pctStr, 'Close'
EXEC sp_OADestroy @pctStr
SET @MENSAGEM=(SELECT CASE WHEN @IMAGE IS NULL THEN 'Imagem_'+  CONVERT(nvarchar(MAX),@IDIMAGEM) +' ERRO! VERIFIQUE SE A IMAGEM EXISTE CADASTRADA NO BANCO DE DADOS!'
    ELSE CONVERT(nvarchar(MAX),@IDIMAGEM)+' SUCESSO!'
    END)
    RETURN @MENSAGEM
END
  • Identify the format directly in SQL Server, when exporting, I do not know if it is possible. After extracting a png file, if you just change the extension it works? If yes, what you can do is export these images and identify the extension with a script using the mimetype of that file

  • Changing the extension works. But as it ends up having around 10 to 15 thousand images to export is complicated to do one by one. We are migrating from images in the database to folder images in a legacy system.

  • Checking mimetype from a script can help you then

No answers

Browser other questions tagged

You are not signed in. Login or sign up in order to post.