Qlik Community

Integration, Extension & APIs

Discussion board where members can learn more about Integration, Extensions and API’s for Qlik Sense.

Announcements
Now Live: Qlik Sense SaaS Simplified Authoring – Analytics Creation for Everyone: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
twanqlik
Creator
Creator

Setup bulk API connection

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...{ %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...: %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):
Eloqua Bulk API Error.PNG

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. 

 

 

1 Reply
Levi_Turner
Employee
Employee

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