Acquiring data dynamically from a REST webservice

[EranRaz]

Hi, 

The webservice expecting a JSON with parameters in the body

But I need the body to be dynamically changed according to the user selection, 

something like the pic attached  (the connector works when the  body is hardcoded)

will appreciate any help or guidance on that

thanks

Eran

[stevedark]

Hi @EranRaz 

You will want to look at the WITH CONNECTION property of the connection:

https://help.qlik.com/en-US/connectors/Subsystems/REST_connector_help/Content/Connectors_REST/Load-R...

Whether or not you will be able to pass in a whole chunk of JSON like that or not I am not sure, but this is definitely the approach to try first.

If not a JSON block you may be able to specify each of the parts as separate parameters?

Hope that helps.

Steve

[EranRaz]

Hi ,

I did try that :

RestConnectorMasterTable:

SQL SELECT

"EXISTS_LASER_DRILL",

"False2",

"JMP_Formula",

"JMP_Result",

"REQ_ID"

FROM JSON (wrap on) "root"

WITH CONNECTION(BODY '{

"REQ_ID":"W888888",

"FINISH_CS" : "YES",

"NUM_DESIGN_LAYERS" : 4,

"EXIST_LASER" : "NO",

"EXIST_CU_PASTE" : "NO",

"HATS_COUPON_TYPE" : "NONE",

"FAI_TYPE" : "NONE",

"SEL_PLT_CS" : "NONE",

"FINISH_PS" : "CHM_GOLD",

"CAM_PNL_SIZE_X" : 12,

"CAM_PNL_SIZE_Y" : 18,

"TEST_PARAM": "DEMO"

}' ;

[root]:

LOAD [EXISTS_LASER_DRILL],

[False2],

[JMP_Formula],

[JMP_Result],

[REQ_ID]

RESIDENT RestConnectorMasterTable;

DROP TABLE RestConnectorMasterTable;

But got the error :
The following error occurred:
Connector reply error: Unknown substring, string, or value at (8,20): ''{'

[stevedark]

Hi @EranRaz 

The WITH CONNECTION requires double quotes to be used around the body, which then means that these can't (I imagine) be used in the body that is sent.

I guess that if you remove all of the double quotes from the body it will not work?

Steve

[EranRaz]

Hi Steve,

I have tried a variety of options to get that JSON to work,

Most of the time got the error :

The following error occurred:

Connector reply error: Unexpected lexem 'Option' ('REQ_ID') at (8,33). Expected 'CloseBracket'.

Check SELECT STATEMENT syntax with documentation.

Options like :

let vRequestBody =

'{

["REQ_ID": "W444444"],

["FINISH_CS" : "YES"],

["NUM_DESIGN_LAYERS" : 4],

["EXIST_LASER" : "NO"],

["EXIST_CU_PASTE" : "NO"],

["HATS_COUPON_TYPE" : "NONE"],

["FAI_TYPE" : "NONE"],

["SEL_PLT_CS" : "NONE"],

["FINISH_PS" : "CHM_GOLD"],

["CAM_PNL_SIZE_X" : 12],

["CAM_PNL_SIZE_Y" : 18],

["TEST_PARAM" : "DEMO"]

}';

// and taking the var into the body as : FROM JSON (wrap on) "root" WITH CONNECTION(BODY "$(vRequestBody)");

// any usage of double quotes inside the JSON , gives the following errors type :

// Connector reply error: Unexpected lexem 'Option' ('REQ_ID') at (8,36). Expected 'CloseBracket'. Check SELECT STATEMENT syntax with documentation.



When using the following construction (without quotes ) :

let vRequestBody =

'{

[REQ_ID]: [W444444],

[FINISH_CS] : [YES],

[NUM_DESIGN_LAYERS] : 4,

[EXIST_LASER] : [NO],

[EXIST_CU_PASTE] : [NO],

[HATS_COUPON_TYPE] : [NONE],

[FAI_TYPE] : [NONE],

[SEL_PLT_CS] : [NONE],

[FINISH_PS] : [CHM_GOLD],

[CAM_PNL_SIZE_X] : 12,

[CAM_PNL_SIZE_Y] : 18,

[TEST_PARAM]: [DEMO]

}';



//QS load the JSON but get error from the WS due to syntax issue :



The following error occurred:

HTTP protocol error 400 (BAD REQUEST):





400 Bad Request

Bad Request

The browser (or proxy) sent a request that this server could not understand.

// As it expect to get the following structure:

{

"REQ_ID":"T999999",

"FINISH_CS" : "YES",

"NUM_DESIGN_LAYERS" : 4,

… }



Any idea ? its frustrating

[stevedark]

Hi @EranRaz 

Have you tried without double quotes or the square brackets? None of the parameter names and values have spaces, so this may work.

It may be that passing a BODY tag in like that will not work.

Can you try setting up each of the values as a parameter, first in a connection using the dialog and then if that works replacing it with WITH CONNECTION, listing each parameter separately.

Steve

[EranRaz]

Hi,

Tried that, didn’t get much far with that, the parameters still get parsed as double quote – and the load results with error

Taking off all the double quote - get pass the loading process, but rejected by WS as it’s getting not valid json format

[stevedark]

Tagging @chrisbrain as he may have some thoughts. I would have thought this is quite a common requirement/problem.

[chrisbrain]

Hi @EranRaz / @stevedark ,

I think I may have this working. I created a connection to this test service:

https://jsonplaceholder.typicode.com/posts

(From https://jsonplaceholder.typicode.com/guide/)

Using:

• HTTP POST
• Content-type header = 'application/json; charset=UTF-8'
• Body: 

{
"title": "foo",
"body": "bar",
"userId": 1
}

I then made a selection from the connection and updated the script to the following, which seems to work:

let vTitle = 'Test title';
let vBody = 'Test body';
let vId = 456;

RestConnectorMasterTable:
SQL SELECT 
	"title",
	"body",
	"userId",
	"id"
FROM JSON (wrap on) "root"
WITH CONNECTION(BODY "{
""title"": ""$(vTitle)"",
""body"": ""$(vBody)"",
""userId"": $(vId)
}");

[root]:
LOAD	[title],
	[body],
	[userId],
	[id]
RESIDENT RestConnectorMasterTable;

DROP TABLE RestConnectorMasterTable;

The trick here was to use double doublequotes inside the single double quotes - this is actually in the docs at:
https://help.qlik.com/en-US/sense/November2020/Subsystems/Hub/Content/Sense_Hub/Scripting/use-quotes...

(The 'Use escape characters' section at the end).

Hopefully this is close to what you need and you can adapt it.

[EranRaz]

Hi Steve,

Tried to do that similar to what you did – and I’ m getting the following error

[cid:image002.png@01D6E7FD.B33E1220]

I have few suspicions, maybe that can give some lead

That is my script – note the interpreter didn’t’ t mark the [root] part



[cid:image006.png@01D6E7FD.B33E1220]



Another thing the connection I’m using :” LIB CONNECT TO 'JMP (pcb-il_eranra)';”

Included JSON in the body

(as it doesn’t let me to save the connection if the WS retunes an error - which it does if no JSON parameters are sent

*I expected that the script parameters will override the connection parameters

[cid:image008.png@01D6E7FD.B33E1220]

And just to verified , using the Postman, I do get answers

[cid:image010.png@01D6E7FD.B1DBEA10]





Thank Steve you for the help and effort you put in

Eran