Solved

Example REST API Multipart Form using msxml2.xmlhttp

  • 23 June 2021
  • 9 replies
  • 1826 views

Userlevel 2
Badge +2

Hello all,

Does anyone have an example of an t-sql request against a REST API with a Multidata/Form? We already call several REST APIs, but not with Multidata/form. Below is a setup (which does not work). The API converts a PDF document into fields in json format. It contains 2 fields that must be included:

  • name (name of the document)
  • document (base64 encoded string)

Below is a first attempt, but it does not work 🙂. Thanks in advance for the reactions.

declare @response table (txt nvarchar(max));

declare @object int ,
@url_endpoint nvarchar(200) ,
@response_format nvarchar(5) ,
@request nvarchar(250) ,
@json nvarchar(max) ,
@address address ,
@body varchar(max) ,
@status int ,
@document_file_name nvarchar(200) ,
@base64_document varchar(max) ,
@content_type varchar(100) ,
@status_text nvarchar(32)

set textsize -1
set @url_endpoint = 'endpoint'
set @document_file_name = 'document.pdf'
set @base64_document = -- the base64 content
set @content_type = 'multipart/form-data';
set @body = concat('name=',@document_file_name,'&','document=',@base64_document)

exec sp_oacreate 'msxml2.xmlhttp', @object out
exec sp_oamethod @object, 'open', null, 'post', @url_endpoint, 'false'
exec sp_oamethod @object, 'AddHeader', null, 'Content-Type', @content_type
exec sp_oamethod @object, 'send', null, @body

exec sp_oagetproperty @object, 'status', @status out;
exec sp_oagetproperty @object, 'statustext', @status_text out;

insert into @response (txt)
exec sp_oamethod @object, 'responsetext'
exec sp_oadestroy @object

select @json = txt from @response;

 

icon

Best answer by Randolph Beekman 29 June 2021, 10:10

View original

9 replies

Userlevel 5
Badge +15

Maybe use a proces flow with a http connector instead?

Userlevel 2
Badge +2

Hello René,

 

Thanks for the reply. I want use this procedure as a scheduled SQL job, so there will be no user intervention.

 

Userlevel 7
Badge +19

Hello René,

 

Thanks for the reply. I want use this procedure as a scheduled SQL job, so there will be no user intervention.

 

Hi,

Could a System flow instead be sufficient? That way the process flow runs without user interaction and run on a specific schedule (e.g. every night at 03:00). System flows do require Indicium (Universal) to be deployed

Userlevel 2
Badge +2

Hey Mark,

Thanks I will try that. Thanks for the tip.

Regards,

Randolph

Userlevel 2
Badge +2

Hey

The wish has changed somewhat. If a new document is added to the table, the above stored procedure must be fired. So not by means of a schedule, but an on insert trigger. So I am curious how this stored procedure should look like.

I am also curious (in case of a process flow) how the input parameters should look like in the http connector in case of a multipart/form-data, like in the screenshot below:

Many thanks advance.

Randolph

Userlevel 7
Badge +19

Hi Randolph,

Although I don't exactly know what the URL endpoints expects, I do suggest using this part of your initial try: 

set @body = concat('name=',@document_file_name,'&','document=',@base64_document)

 

This body (or Content) is what you then can POST to the URL. The HTTP connector will also get information back from the URL endpoint most likely; be sure to capture that in other process variables.

I used this as reference: html - Example of multipart/form-data - Stack Overflow

Userlevel 2
Badge +2

Hi All,

I managed to use both a process flow and a sql statement to call the OCR api. Below is the code for those interested:
 

declare @token          int;
declare @ret int;
declare @url nvarchar(max);
declare @authheader nvarchar(64);
declare @contenttype nvarchar(64);
declare @apikey nvarchar(32);
declare @body nvarchar(max);
declare @json table(json_table nvarchar(max));

set @url = [endpoint]
set @body = [base64 content]

exec @ret = sp_oacreate 'msxml2.xmlhttp', @token out;
if @ret <> 0 raiserror('unable to open http connection.', 10, 1);
exec @ret = sp_oamethod @token, 'open', null, 'post', @url, 'false';
exec @ret = sp_oamethod @token, 'setrequestheader', null, 'content-type', 'application/json';
exec @ret = sp_oamethod @token, 'send', null, @body

insert into @json (json_table) exec sp_oagetproperty @token, 'responsetext'

select * from @json

 

Userlevel 2
Badge +2

I must add that the post changed from multipart/form to application/json.

Userlevel 5

Maybe a bit late, but I have found that this is working perfectly for me:

declare @content_type varchar(100) = 'multipart/form-data; boundary=12345'

declare @line_break varbinary(2) = 0x0D0A;
declare @separator varbinary(100) = convert(varbinary(100), '--12345');
declare @final_separator varbinary(100) = convert(varbinary(100), '--12345--');

set @api_content_varbinary =
@separator + -- Start each block with the multipart separator
@line_break + -- Follow each separator with a line break
convert(varbinary(max), 'Content-Disposition: form-data; name="file"; filename="' + cast(@xml_outbound_message_id as varchar) + '.xml"') +
@line_break +
convert(varbinary(max),'Content-Type: application/xml') +
@line_break +
@line_break +
convert(varbinary(max),convert(varchar(max),@xml_file)) +
@line_break +
@final_separator -- The final separator should the the separator plus two additional dashes at the end

 

Reply