How to use Qlik NPrinting APIs inside a Qlik Sense load script

[Gianluca_Perin]

Hi all,

attached you'll find a step by step tutorial explaining how to use Qlik Sense (and also QlikView) load script to interact with the Qlik NPrinting APIs.

The examples include:

• Logging in into NPrinting
• Collecting data from NPrinting
• Reloading metadata for a NPrinting connection
• Create, update and delete NPrinting users
• Triggering a NPrinting publish task

This document also shows how to use Qlik Sense Tasks to chain these different events directly from QMC.

This tutorial uses standard Qlik REST Connector.

Please read carefully the requirements before to dig into the actual examples.

Enjoy!

[JonnyPoole]

i have a sample of reading tasks from the repository.

i can't attach my QVF but here is the script:

///////////////////////

OnDemandRequests:
LOAD
"id" as "ondemand_id",
"created" as "OnDemand Request Create Time",
"type",
"title" as "OnDemand Report Name",
"downloaded",
"status" as "OnDemand Status",
"output_format" as "OnDemand Format",
"error_code" as "OnDemand Error Code",
"error_message" as "OnDemand Error",
"request_source_type" as "OnDemand Request Source",
"user_id",
"report_id",
"response_file_id";
SQL SELECT *
FROM "nprinting"."public"."on_demand_request";

Reports:
LOAD "id" as "report_id",
"version" as "Report Version",
"created" as "Report Create Date",
"last_update" as "Report Last Update Date",
"report_type_id",
"title" as "Report",
"description" as "Report Description",
"original_ref",
"enabled",
"deleted",
"on_demand_enabled",
"template_format",
"entities_xml",
"template_tree",
"empty_strategy",
"has_dynamic_name",
"has_cycle",
"app_id",
"qlik_view_entity_id",
"import_task_id",
"template_file_id",
"variable_sequence_id";
SQL SELECT *
FROM "nprinting"."public"."report";
//-------- End Multiple Select Statements ------

//-------- Start Multiple Select Statements ------
Users:
LOAD "recipient_id" as "user_id",

"key_id",
"sid",
"salt",
"hashed_password",
"domain_account",
"locale",
"timezone",
"last_login_attempt",
"login_delay",
"folder",
"sub_folder",
"nickname",
"title",
"company",
"job_title",
"department",
"office",
"email_address_id";
SQL SELECT *
FROM "nprinting"."public"."usr";

Apps:
LOAD "id" as "app_id",
"version",
"name" as App;
SQL SELECT *
FROM "nprinting"."public"."app";

Tasks:
LOAD "id" as "task_id",
"version",
"created" as "Task Created Date",
"last_update" as "Task Last Updated Date",
"task_type" as "Task Type",
"name" as "Task",
"description" as "Task Description",
"enabled" as "Task Enabled Flag";
SQL SELECT *
FROM "nprinting"."public"."task";

TaskExecutions:
LOAD "id" as "task_execution_id",
"version" as "TaskExecution Version",
"created" as "TaskExecution Created Date",
"last_update" as "TaskExecution Updated Date",
"task_type" as "task_execution_type",
"status" as "TaskExecution Status",
"priority" as "TaskExecution Priority",
"completed" as "TaskExecution Completed Date",
"progress" as "TaskExecution Progress",
"config" as "TaskExecution Config",
"result" as "TaskExecution Result",
"retry_count" as "TaskExecution Retry Count",
"max_retry" as "TaskExecution MaxRetry",
"wait_before_retry",
"start_after",
"run_timeout",
"timeout_at",
"last_hearth_beat",
"task_id",
"parent_id",
"trigger_id";
SQL SELECT *
FROM "nprinting"."public"."task_execution";
//-------- End Multiple Select Statements ------

TaskExecutionType:
load * inline [
task_execution_type,Task Execution Type
Qlik.NPrinting.Task.GenerateConnectionCacheEngineTask,Metadata Reload
Qlik.NPrinting.Task.PublishReportsEngineTask,Publish Task
];

[anindya_manna]

Hi Jpe,

After executing this script getting an error as below.

FROM "nprinting"."public"."on_demand_request"
Error: There is no open data connection.
Execution Failed

Please let me know if I have to create any connection for  "nprinting"."public"."on_demand_request" part.

[JonnyPoole]

You will need to create a new ODBC data connection to the postgres repository first and insert the connection string above these statements. 

In all Qlik load scripts, you must CONNECT prior to running any LOAD/SQL commands so that Qlik knows which data source you are querying.  

At a high level you need to 
1. Download ODBC driver from postgres and install on the Qlik Server

2. Create an ODBC System DSN using the driver and ensure it test successfully

3. Create a new DAta Connection in Qlik using ODBC referencing the DSN name

<attached are 3 screenshots for reference on how to setup the DSN>

[steverosebrook]

Prakash - We're getting the same error you mentioned above:
Error: QVX_UNEXPECTED_END_OF_DATA: HTTP protocol error 405 (Method Not Allowed):
{"message":"The requested resource does not support http method 'POST'."}
Any chance you've figured out how to fix this issue?

[Frank_S]

This was answered earlier in this thread with the following article.

https://qliksupport.force.com/articles/000057354

You may also wish to reference the following for additional common API issues and resolutions

https://qliksupport.force.com/articles/000057262

[anindya_manna]

Hi,

When I am fetching all the task list from Nprinting server in below code in RestConnectorMasterTable only 50 task is fetching.How to fetch entire task list?

LIB CONNECT TO  $(vAPIConnectionNameForGET);
//exit script;

let vEndpoint=  vNPrintingServer  & '/api/v1/tasks' ;

    RestConnectorMasterTable:
    SQL SELECT 
        "__KEY_data",
        (SELECT 
            "id",
            "name",
            "description",
            "type",
            "created",
            "lastUpdate",
            "lastExecution",
            "enabled",
            "appId",
            "__FK_items"
        FROM "items" FK "__FK_items")
    FROM JSON (wrap off) "data" PK "__KEY_data"
    with Connection (URL "$(vEndpoint)", HTTPHEADER "Cookie" "$(vCookie)");

    [items]:
    LOAD	[id] AS [id],
        [name] AS [name],
        [description] AS [description],
        [type] AS [type],
        [created] AS [created],
        [lastUpdate] AS [lastUpdate],
        [lastExecution] AS [lastExecution],
        [enabled] AS [enabled],
        [appId] AS [appId]
    RESIDENT RestConnectorMasterTable
    //WHERE NOT IsNull([__FK_items]) and [name]= $(vNPrintingPublishTaskName);
    WHERE NOT IsNull([__FK_items]); // and [id]= '$(vNPrintingPublishTaskID)';

HG 

[JonnyPoole]

This has come up a number of times.  I've hit it reading in all the users in my NP repository.  

The solution is the same for all REST calls:   add a query parameter called "Limit" to the connection. 

Here is sample syntax using "WITH CONNECTION" syntax

WITH CONNECTION( URL "$(vEndpoint)", HTTPHEADER "Cookie" "$(vCookie)" ,QUERY "Limit" "1000000");

[JonnyPoole]

This has come up a number of times.  I've hit it reading in all the users in my NP repository.  

The solution is the same for all REST calls:   add a query parameter called "Limit" to the connection. 

Here is sample syntax using "WITH CONNECTION" syntax

WITH CONNECTION( URL "$(vEndpoint)", HTTPHEADER "Cookie" "$(vCookie)" ,QUERY "Limit" "1000000");

[ergustafsson]

Just want to add a warning here.

If you are dependent on conditions and similar in NPrinting, remember that it takes some time for an app to get published.

We added an extra "delay reloads app" in QS chain so that it has enough time to publish the app before NPrinting picks up conditions and other things, which it depends on. A 430 mb app takes 39 seconds in our DEV environment to get published.

Name a sample document to e.g. "Delay reloads in minutes by_2". Change title after _ to set timeout so that you don't have to change the code for each setup.

Code:

let vTimeoutMinutes = subfield(DocumentTitle(), 'minutes by_', '-1') ; // timeout in Minutes
let vTimeoutSeconds = vTimeoutMinutes * 60 * 1000 ; // convert to seconds

sleep vTimeoutSeconds;

[griffinleow]

Thanks for this wonderful guide on consuming APIs.

Just a question, how do I make use of the NPrinting API documentation to consume the APIs? I.e. how do I know what tables contain what fields etc? Do I have to dig into the NP repository database to know these details?

Thanks