I am trying to download a file and I have been successful if the file is less than 8K. Once the file is larger than 8K, the variable (VARBINARY(MAX)) from the "ResponseBody" returns null. I wonder if I need to use VARBINARY FileStream whatever that is?
If I convert this code to VBScript, then there is no problem at all.
I have included code with two URLs. One works and one does not work.
DECLARE @URL AS NVARCHAR(MAX)
--THIS does not WORKS because it is more than 8K
SET @URL = 'https://img.icons8.com/emoji/2x/chimpanzee-emoji.png'
--THIS WORKS because it is less than 8k
SET @URL = 'https://img.icons8.com/emoji/2x/bear-emoji.png'
----------------------------------------
DECLARE @oREST_API AS INT
DECLARE @Verb AS VARCHAR(25) = 'GET'
DECLARE @ReturnCode AS INT
DECLARE @ReturnMessage AS INT
EXEC @ReturnCode = sp_OACreate 'Msxml2.ServerXMLHTTP.6.0', @oREST_API OUT
EXEC @ReturnCode = sp_OAMethod @oREST_API, 'OPEN', NULL, @Verb, @URL, 'FALSE' --, @UserName, @Password
EXEC @ReturnCode = sp_OAMethod @oREST_API, 'setRequestHeader', NULL, 'Content-Type', 'image/jpeg'
EXEC @ReturnCode= sp_OAMethod @oREST_API, 'SEND'
IF @ReturnCode <> 0 EXEC sp_OAGetErrorInfo @ReturnMessage
DECLARE @Status NVARCHAR(50);
DECLARE @Status_Text NVARCHAR(50);
DECLARE @Get_All_Response_Headers NVARCHAR(4000);
EXEC @ReturnCode = sp_OAGetProperty @oREST_API, 'Status', @Status out;
EXEC @ReturnCode = sp_OAGetProperty @oREST_API, 'StatusText', @Status_Text out;
EXEC @ReturnCode = sp_OAGetProperty @oREST_API, 'GetAllResponseHeaders', @Get_All_Response_Headers out;
SELECT
@Status AS [fStatus]
, @Status_Text AS [fStatus_Text]
, @Get_All_Response_Headers AS [fGet_All_Response_Headers]
----------------------------------------
--PURPOSE: Get URL
DECLARE @Response_URL NVARCHAR(4000);
EXEC @ReturnCode = sp_OAGetProperty @oREST_API, 'getOption(-1)', @Response_URL out; --WORKS Only with Msxml2.ServerXMLHTTP
IF @ReturnCode <> 0
BEGIN
EXEC sp_OAGetErrorInfo @ReturnMessage
END
DECLARE @FileName AS NVARCHAR(MAX) = REPLACE( RIGHT(@Response_URL, CHARINDEX('/', REVERSE(@Response_URL), 0)) , '/', '')
SET @FileName = 'C:\(Delete) ' + @FileName
SELECT
@Response_URL AS Original_URL
, @FileName AS Saved_Location
----------------------------------------
DECLARE @JSONResponseBody AS VARBINARY(MAX) --= CAST(REPLICATE(CAST('' AS NVARCHAR(MAX)) + 'a', 10000) AS VARBINARY(MAX))
DECLARE @TableJSON AS TABLE (MyData VARBINARY(MAX))
INSERT INTO @TableJSON (MyData)
EXEC @ReturnCode = sp_OAGetProperty @oREST_API, 'responseBody'
SELECT @JSONResponseBody = MyData FROM @TableJSON
SELECT *, LEN(MyData) FROM @TableJSON
EXEC sp_OADestroy @oREST_API
----------------------------------------
DECLARE @ADO AS INT
EXEC sp_OACreate 'ADODB.Stream', @ADO OUTPUT
EXEC sp_OASetProperty @ADO, 'Type', 1 --Write
--EXEC sp_OASetProperty @ADO, 'Type', 2 --WriteText
EXEC sp_OAMethod @ADO, 'Open'
EXEC sp_OAMethod @ADO, 'Write', NULL, @JSONResponseBody
EXEC sp_OAMethod @ADO, 'SaveToFile', NULL, @FileName, 2 --jpg txt
--EXEC sp_OAGetErrorInfo @ADO
EXEC sp_OAMethod @ADO, 'Close'
EXEC sp_OADestroy @ADO
----------------------------------------