May 7, 2020 at 4:30 pm
Hi there, I'm not sure if this is possible and can't seem to find a clear answer.
I have a table that contains some light HTML, and by light, I mean 1 or 2 pages of simply formatted text and a logo. I have a stored procedure that creates the HTML.
What I'd like to do is convert that HTML into a PDF and store the PDF in the same table that contains the source HTML. The PDF would end up encoded and stored in an image column. I was looking at an API that does an HTML to PDF conversion (I'm using an online service called Pdfcrowd). Basically, in the body of the request, you include the HTML and then call an API, which results in a PDF file. The API call works well when I test it through Postman, but in order to view the resulting PDF, I have to download the response.
I'm hoping to automate this function and was thinking of doing it on the back-end using OLE Automation, namely ServerXMLHTTP. I've use this before to do some simple API calls from within SQL Server so I'm a little familiar with it. However, the few that I have developed all return a response in JSON and not a file that I need to download.
So I am wondering if it is possible to call an API which responds by generating a downloadable file and prompts the user to download the generated file. In other words, can OLE Automation within T-SQL somehow instruct an API to download a resulting file and then stream that into a table column?
Here is some code that I thought I could use:
set nocount on
declare @responseText as table(responseText nvarchar(max))
declare @Object as int
declare@status int,
@requestBody nvarchar(max),
@responseBody nvarchar(max),
@authHeader nvarchar(64)
declare @url as nvarchar(1000) = 'https://api.pdfcrowd.com/convert/'
set @authHeader = 'Basic {inserting my token here}'
set @requestBody = 'text=<html><body>Hello World</body></html>'
exec sp_OACreate 'MSXML2.ServerXMLHTTP', @Object out
exec sp_OAMethod @Object, 'open', null, 'POST', @url, 'false'
exec sp_OAMethod @Object, 'setRequestHeader', null, 'Authorization', @authHeader
exec sp_OAMethod @Object, 'setRequestHeader', null, 'Content-Type', 'application/x-www-form-urlencoded'
declare @len int = len(@requestBody)
exec sp_OAMethod @Object, 'setRequestHeader', null, 'Content-Length', @len
exec sp_OAMethod @Object, 'send', null, @requestBody
exec sp_OAGetProperty @Object, 'status', @status out
select @status
exec sp_OADestroy @Object
When I run the above, the result is a 200 status, just like I would get when running the equivalent in Postman. I just don't know how to capture and consume the response because it is not in the responseBody or responseText property.
I hope this is making sense. Maybe it just isn't possible and I have to manually create and download each PDF into a folder that SQL Server scans and then imports and inserts any PDF into a table.
David
May 8, 2020 at 5:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply