topic Re: Acquiring data dynamically from a REST web service in Connectivity & Data Prep

Acquiring data dynamically from a REST webservice

EranRaz — Thu, 23 Dec 2021 18:49:12 GMT

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

Re: Acquiring data dynamically from a REST webservice

stevedark — Tue, 05 Jan 2021 13:28:51 GMT

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-REST-data/Load-data.htm#WITH-CONNECTION-keyword

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

Re: Acquiring data dynamically from a REST web service

EranRaz — Tue, 05 Jan 2021 15:30:27 GMT

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): ''{'

Re: Acquiring data dynamically from a REST web service

stevedark — Tue, 05 Jan 2021 16:56:17 GMT

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

Re: Acquiring data dynamically from a REST web service

EranRaz — Wed, 06 Jan 2021 08:52:22 GMT

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

Re: Acquiring data dynamically from a REST web service

stevedark — Wed, 06 Jan 2021 09:03:29 GMT

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

Re: Acquiring data dynamically from a REST web service

EranRaz — Thu, 07 Jan 2021 06:55:22 GMT

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

Re: Acquiring data dynamically from a REST web service

stevedark — Thu, 07 Jan 2021 08:41:24 GMT

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

Re: Acquiring data dynamically from a REST web service

chrisbrain — Thu, 07 Jan 2021 21:14:27 GMT

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-in-script.htm#anchor-7

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

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

Re: Acquiring data dynamically from a REST web service

EranRaz — Mon, 11 Jan 2021 07:40:45 GMT

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

Re: Acquiring data dynamically from a REST web service

stevedark — Mon, 11 Jan 2021 12:26:59 GMT

Hi @EranRaz

Unfortunately your images don't seem to have come through.

@chrisbrain's solution of escaping double quotes by doubling them up looks like it will fix a number of your woes.

With regards to the connection, you will need to have a connection that is successfully created. Your best bet may be to create a connection which points to a dummy location (such as https://jsonplaceholder.typicode.com/posts) so that it exists correctly. You can then connect to that at the start of the script.

To insert the correct endpoint you can use another entry in the WITH CONNECTION section to point the URL to the correct place.

Anything you set using WITH CONNECTION will override anything that is in the connection previously.

Hope that helps.

Steve