Skip to main content
Solved

Example REST API Multipart Form using msxml2.xmlhttp

Randolph Beekman
Vanguard
Forum|alt.badge.img+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;

 

Best answer by Randolph Beekman

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

 

View original

R
Superhero
Forum|alt.badge.img+15
  • René WSuperhero
  • Superhero
  • June 23, 2021

Maybe use a proces flow with a http connector instead?

Mark Jongeling
1 person likes this
  • Mark Jongeling
    Mark Jongeling
Randolph Beekman
Vanguard
Forum|alt.badge.img+2

Hello René,

 

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

 

Mark Jongeling
Administrator
Forum|alt.badge.img+23
Randolph Beekman wrote:

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

"Wake me up before you low-code"
Randolph Beekman
Vanguard
Forum|alt.badge.img+2

Hey Mark,

Thanks I will try that. Thanks for the tip.

Regards,

Randolph

Randolph Beekman
Vanguard
Forum|alt.badge.img+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

Mark Jongeling
Administrator
Forum|alt.badge.img+23

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

"Wake me up before you low-code"
Randolph Beekman
Vanguard
Forum|alt.badge.img+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

 

Mark Jongeling
1 person likes this
  • Mark Jongeling
    Mark Jongeling
Randolph Beekman
Vanguard
Forum|alt.badge.img+2

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

Robbert van Tongeren
Thinkwise blogger

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


Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings