Setup bulk API connection

twanqlik — Tue, 18 Jan 2022 16:20:36 GMT

I could use some support with setting up a bulk API connection in Qlik Sense .

Input for creating the API connection:

---------------------------------------------------------------------------------------

URL=https://secure.p01.eloqua.com/api/bulk/2.0/activities/exports
Method =Post
Request body=
{
"name": "Clickthrough",
"fields": {
"ActivityId": "{{Activity.Id}}"
},
"filter": "'{{Activity.Type}}' = 'EmailClickthrough'",
}
Key generation strategy= Sequence ID
Query headers (Name=Content-Type, Value=application/json)

---------------------------------------------------------------------------------------

This data connection works, however, i would like to use parameters that i want to control in the application, and not in the 'Request body', else i have to create every day a new data connection.

Hence, i want to use the option: with connection, to override the data connection.

First i checked how the 'connection string' is build in Qlik Sense:

CUSTOM CONNECT TO "provider=QvRestConnector.exe;url=https://secure.p01.eloqua.com/api/bulk/2.0/activities/exports;timeout=30;readwritetimeout=300;method=POST;requestBody={ %3name%3: %3Clickthrough%3, %3fields%3: { %3ActivityId%3: %3{{Activity.Id}}%3 }, %3filter%3: %3'{{Activity.Type}}' %2 'EmailClickthrough'%3,};httpProtocol=1.1;isKeepAlive=true;bodyEncoding=UTF-8;sendExpect100Continue=true;autoDetectResponseType=true;checkResponseTypeOnTestConnection=true;keyGenerationStrategy=0;XMLDTD=0;authSchema=basic;serverCertificateValidation=UseTrust;useCertificate=No;certificateStoreLocation=LocalMachine;certificateStoreName=My;addMissingQueryParametersToFinalRequest=false;queryHeaders=Content-Type%2application/json%1;PaginationType=None;allowResponseHeaders=false;allowHttpsOnly=false;isCookieContainerEnabled=false;useProxy=false;proxyBypassOnLocal=false;proxyUseDefaultCredentials=true;"

-------------------------------------------------------------
I used this connection string in the script.

LIB CONNECT TO 'Eloqua_Bulk_v2';

RestConnectorMasterTable:
SQL SELECT
"name",
"filter",
"maxRecords",
"dataRetentionDuration",
"uri",
"createdBy",
"createdAt",
"updatedBy",
"updatedAt",
"__KEY_root",
(SELECT
"ActivityId",
"__FK_fields"
FROM "fields" FK "__FK_fields")
FROM JSON (wrap on) "root" PK "__KEY_root"
WITH CONNECTION
(url "https://secure.p01.eloqua.com/api/bulk/2.0/activities/exports;timeout=30;readwritetimeout=300;method=POST;requestBody={%3name%3: %3Clickthrough%3,%3fields%3: {%3ActivityId%3: %3{{Activity.Id}}%3},%3filter%3: %3'{{Activity.Type}}' %2 'EmailClickthrough'%3,};httpProtocol=1.1;isKeepAlive=true;bodyEncoding=UTF-8;sendExpect100Continue=true;autoDetectResponseType=true;checkResponseTypeOnTestConnection=true;keyGenerationStrategy=0;XMLDTD=0;authSchema=basic;serverCertificateValidation=UseTrust;useCertificate=No;certificateStoreLocation=LocalMachine;certificateStoreName=My;addMissingQueryParametersToFinalRequest=false;queryHeaders=Content-Type%2application/json%1;PaginationType=None;allowResponseHeaders=false;allowHttpsOnly=false;isCookieContainerEnabled=false;useProxy=false;proxyBypassOnLocal=false;proxyUseDefaultCredentials=true;");

This results in a HTTP protocol error 400 (Bad Request):

This means that i did do something wrong in the url part, but as there are so many variables, i have no idea what this could be. Any idea what i could try?

When this works, i can control the Bulk connection by changing parameters in the URL.

Re: Setup bulk API connection

Levi_Turner — Wed, 19 Jan 2022 21:18:28 GMT

This is very similar to the use case that I outlined here on calling webhooks from Qlik Sense Enterprise Client Managed. I'd give a try to the approach that I did to change the special characters into their chr representations, like so:

// Replace the " characters with the chr representations LET vBody = Replace(vBody,'"',chr(34) & chr(34)); // Replace the / characters with the chr representations LET vBody = Replace(vBody,'/',chr(47)); // Replace the \ characters with the chr representations LET vBody = Replace(vBody,'\',chr(92)); // Replace the * characters with the chr representations LET vBody = Replace(vBody,'*',chr(42));

Judging from the exception, it looks like you're trying to send the structure of the data connection inside of Qlik Sense, which definitely won't work. You'll want to use a similar approach as the Webhook example and use WITH CONNECTION to adjust the body of the POST request.

Hope that helps

topic Setup bulk API connection in Integration, Extension & APIs

Setup bulk API connection

Re: Setup bulk API connection