Why is "endpoint" is defined as "select" statement? That will not work. Some thing like below (note below not validated)
CREATE VIRTUAL PROCEDURE request (requestURL varchar, cluserid string, params string) RETURNS (message varchar(100), jobids varchar(100), success varchar(10)) AS BEGIN DECLARE STRING VARIABLES.qp = QUERYSTRING('requestURL', command.request.clusterid AS clusterid, command.request.params AS params); select request.* from (call restBDM.invokeHttp(action => 'POST', endpoint => qp)) AS ws, XMLTABLE('Request' PASSING jsonToXml('Request', ws.result) columns Message string PATH './message', JobIDs string PATH './jobids', Success string PATH './success' ) AS request END
> That will not work.
Why won't that work? A scalar subquery is a valid expression.
> I need some help with how to put that into this VDB definition.
That is simply up to the format that you need. Ramesh is showing above how this would look as url query string parameters (although the input parameters should not be qualified with command). Otherwise if what you are looking for is as the message body, then yes simply pass them as the request value:
request=>('clusterid:' || clusterid || '
params:' || params)
Thanks Ramesh and Steven.
We actually do need to pass those as part of the message body. The snippet you wrote doesn't have a separator for the two parameters, we tried putting a comma right before params: like below:
request=>('clusterid:' || clusterid || ',params:' || params)
But that didnt help much, any suggestions?
> But that didnt help much, any suggestions?
As I don't know what exact format you need or what remote error you are seeing, I can't say for sure. Do you need a specific encoding or have anything specific from the remote side to indicate what might be amiss?
May be a full representation of your request how you need to send would be helpful rather than we guessing?
Sorry about missing details. So we need the contentType to be set to 'application/x-www-form-urlencoded'
And the actual request body will be something like
So we make our virtual procedure definition to be like below:
CREATE VIRTUAL PROCEDURE request
(requestURL string, requestParams string)
RETURNS (message varchar(100), jobids varchar(100), success varchar(10))
select request.* from
(call restBDM.invokeHttp(action => 'POST', endpoint => (select restURL || requestURL FROM config.base_url where TenantId = 1), request => requestParams)) AS ws,
XMLTABLE('Request' PASSING jsonToXml('Request', ws.result) columns
Message string PATH './message',
JobIDs string PATH './jobids',
Success string PATH './success'
) AS request
So we would pass the url as we did earlier and also pass the fully encoded request message body("clusterid=3¶ms=%7Bkey1%3A%22value1%22%7D") to this stored procedure. The only missing piece we have now is how do we set the contentType. From looking at the source code and documentation for WS translator the contentType parameter is an output parameter so it doesn't sound like that would help us. Thanks again for your help.
Currently I do not see a way to set ContentType or any custom headers from this interface. I see there is "contentType" as OUT parameter. You could log this as enhancement request. I am not sure if there are any other alternatives?
The trouble with map style arguments is that they generally require a custom construct in SQL, like the xml namespaces used in SQL/XML. We can either simply add an optional content type parameter or we could have a vararg argument that expects the header values to be specified in pairs.
For simplicity sake, how about adding a extra parameter like
call restBDM.invokeHttp(action => 'POST', endpoint => qp, headers=>'ContentType=application/json;Accepts=application/text')
That way one can add more than single header, just need to be able escape the contents for the delimiter