Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
EranRaz
Contributor
Contributor

Acquiring data dynamically  from a REST webservice

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)

EranRaz_0-1609852292743.png

will appreciate any help or guidance on that

thanks

Eran

 

Labels (1)
10 Replies
stevedark
Partner Ambassador/MVP
Partner Ambassador/MVP

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
Contributor
Contributor
Author

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
Partner Ambassador/MVP
Partner Ambassador/MVP

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
Contributor
Contributor
Author

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
Partner Ambassador/MVP
Partner Ambassador/MVP

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
Contributor
Contributor
Author

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
Partner Ambassador/MVP
Partner Ambassador/MVP

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

chrisbrain
Partner - Specialist II
Partner - Specialist II

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.

beeido.com - BI | Software | Qlik Integration Services
GitFirst - A CI/CD solution for Qlik Sense
EranRaz
Contributor
Contributor
Author

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