0

I'm not familiar with Microsoft SQL Server and the varbinary(max) data type but the database which I need to use stores images as that.

My question is what do I need to do to read that data back and convert it back to readable image type?

So far I have done was

SELECT CONVERT(VARCHAR(max), [IMAGE], 2)
  FROM [demo].[dbo].[DOCIMAGES]
  WHERE [TITLE] = 'test.jpg'

but what I have got it was almost the same just removed 0x from the beginning of that data when

SELECT CONVERT(VARCHAR(max), [IMAGE], 0)
  FROM [demo].[dbo].[DOCIMAGES]
  WHERE [TITLE] = 'test.jpg'

returns value as Lead.

Then I have tried to do stored procedure which will save it to a file like:

DECLARE @ImageData VARBINARY (max);
DECLARE @Path2OutFile NVARCHAR (2000);
DECLARE @Obj INT

SET NOCOUNT ON

SELECT @ImageData = ( 
  SELECT CONVERT(VARBINARY(max), [IMAGE], 1)
  FROM [demo].[dbo].[DOCIMAGES]
  WHERE [TITLE] = 'test.jpg'
);

SET @Path2OutFile = CONCAT (
         'C:\Users\MyPC\Downloads'
         ,'\'
         ,'test.jpg'
       );
    BEGIN TRY
     EXEC sp_OACreate 'ADODB.Stream' ,@Obj OUTPUT;
     EXEC sp_OASetProperty @Obj ,'Type',1;
     EXEC sp_OAMethod @Obj,'Open';
     EXEC sp_OAMethod @Obj,'Write', NULL, @ImageData;
     EXEC sp_OAMethod @Obj,'SaveToFile', NULL, @Path2OutFile, 2;
     EXEC sp_OAMethod @Obj,'Close';
     EXEC sp_OADestroy @Obj;
    END TRY

 BEGIN CATCH
  EXEC sp_OADestroy @Obj;
 END CATCH

   SET NOCOUNT OFF

but that fails with an error:

SQL Server blocked access to procedure 'sys.sp_OADestroy' of component 'Ole Automation Procedures' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ole Automation Procedures' by using sp_configure.

Problem is that I'm not the admin of that server.

In that case can I dump content of the [IMAGE] to a file and then use Java or PHP to stream that content and save it as an image file?

JackTheKnife
  • 3,795
  • 8
  • 57
  • 117
  • Where are you going to be displaying the images / using the image data? Will they be show in a web page or a client-side UI or something like that? If so, you should be able to just read the data as bits from the db and use them with whatever objects you need on the client side. If you have a web api method or something similar that returns a JPG result you should be able to return the raw data with a MIME type of image/jpg and be good. – squillman Sep 07 '18 at 18:53
  • @squillman it will be used by a web API and that was my idea to output it as a JSON object which will contains MIME type as well binary part from that `IMAGE` column but when I try to use it with proper header to display as image/jpg is not working as expected. – JackTheKnife Sep 07 '18 at 19:04
  • You would probably be better off, then, posting the api code you have that is not working since that is the real problem at hand. Can you update with that? – squillman Sep 07 '18 at 19:17
  • @squillman I'm trying to use PHP `hex2bin($data)` where `$data` is a result from the first mentioned query in my question. – JackTheKnife Sep 07 '18 at 19:52

0 Answers0