Wednesday, September 19, 2018

Query Logic Implementation in VBCS for ADF BC REST

Oracle Visual Builder Cloud Service allows to define external REST service connections. In this post I will explain how to implement query logic against such service. Connection is defined for ADF BC REST service.

Wizard provides option to add query parameters, both static and dynamic. I have set one static parameter onlyData=true, to return data only from the service. Also I have created multiple dynamic parameters, the one used in this use case - q parameter. This parameter accepts query expression to filter data. Later in VBCS action chain, I will assign value to this parameter and service will be re-executed to bring filtered data:

Search form elements will be assigned with page scope variables, to hold user query input. On search button click, VBCS action chain will be invoked to read these values and update query parameter. Page scope variables:

Variables firstNameQueryVar and lastNameQueryVar are assigned to search form fields, here is example:

Search button invokes action chain:

Action chain does two things - calls JS function to construct query parameter and then assigns returned value to rest service query parameter to execute search:

JS function is mapped to accept input parameters from search form input fields:

JS function code - parameters are joined into ADF BC REST query string:

JS function result is mapped with page scope variable - result is assigned to this variable:

REST service query parameter q variable is assigned with this value. Once value changes, query is automatically re-executed:

In my next post I will explain how to implement filtering and pagination with transformation function, on top of service connection:

VBCS sample application code is available on GitHub (if you download ZIP from GitHub, make sure to extract it and create new archive including extracted content directly, without top folder).


Naveen said...

Hi Andrejus,

We are trying to develop a VBCS page using service connection (Data Source - Oracle APEX RESTful service). But unable to add filter and paging criteria.

Can you please share a document on how to implement filtering, sorting and pagination transform for service connections.


Andrej Baranovskij said...

This is on my todo list.


Unknown said...

Hi Andrejus,

Using this query, if I have multiple attributes on which I want to query using 'AND' operator , it doesn't work while it works only if I provide 'or'.
For example, I would want to query using criteria: q = Attribute1='XYZ' and Attribute2='ABC'.
Can you please suggest?