Inserting an image retrieved from a web url into a SQL Server database as a varbinary column

Question

I have a image url link that I need to store in a SQL Server database. I need to write something in SQL Server or SSIS to get that image from the URL.

The issue with the code shown here is the responsetext is nvarchar(max) and it should be VarBinary(MAX).

This is just an idea to call a http request from the database. I think the solution will be to do it in a script component in SSIS, am I correct or does someone have a better idea?

DECLARE @status int
DECLARE @responseText AS TABLE (responseText nvarchar(max))
DECLARE @res AS Int;
DECLARE @url AS nvarchar(1000) = 'https://agriapp.azurewebsites.net/barcode/qr?id=LL-12345'

EXEC sp_OACreate 'MSXML2.ServerXMLHTTP', @res OUT
EXEC sp_OAMethod @res, 'open', NULL, 'GET',@url,'false'
EXEC sp_OAMethod @res, 'send'
EXEC sp_OAGetProperty @res, 'status', @status OUT

INSERT INTO @ResponseText (ResponseText) 
    EXEC sp_OAGetProperty @res, 'responseText'

EXEC sp_OADestroy @res

SELECT 
    @status, 
    CONVERT(VARBINARY(MAX),responseText) responseText 
FROM @responseText

Answer 1

I believe what you are getting in responseText is a base64 encoded, and you need to decode it, or convert it from base64 to varbinary. Check out this blog post:

T-SQL: Easy Base64 Encoding and Decoding

https://medium.com/falafel-software/t-sql-easy-base64-encoding-and-decoding-872ad184bd7e

Answer 2

If the image is under 8K in size, you should be able to use your code but change:

DECLARE @responseText AS TABLE (responseText nvarchar(max)) to DECLARE @responseText AS TABLE (responseText varbinary(max))

and change: EXEC sp_OAGetProperty @res, 'responseText' to EXEC sp_OAGetProperty @res, 'responseBody'