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?