{Update May 2024 – Read the newer blog entry that shows the updated development experience with ORDS in VB}
Many of our customers are looking to expose data from existing Oracle databases in Visual Builder applications. In order to do this, you'll need to expose a layer of REST services on top of the database so Visual Builder could access and manipulate the data. One approach for doing this is to use ORDS (Oracle REST Data Services) – which provide a declarative way to achieve such exposure. In this blog we'll show you some of the basics of creating this connection.
{Update June 2020 – You can improve the integration between ORDS and VB with the new OpenAPI support covered in this blog}
In the video below we cover a simple flow from creating the table to exposing it with ORDS and consuming in Visual Builder.
The first step is to expose a table through REST services, SQL Developer provides a nice wizard to help you achieve this. Simply right click a table and choose to REST enable it. When you do this you'll also choose the endpoint name for this specific table.
Once you have that end point, you'll usually append it to a URL that has your DB Server name followed by ords/schemaName – so something like: https://yourDBServer/ords/yourschema/yourtable/
In Visual Builder you can add this endpoint in the service connection wizard – you can then define multiple endpoints that will map to each one of the REST operations such as GET, POST, DELETE etc.
Once you have the GET operation you can hook it up to your pages and for example base a table on it – as shown in the video at : 2:00.
Transform Functions
The ORDS services have built in capabilities to do advance functionality including operations such as query by example, sorting, and pagination (fetching x amount of records at a time). You'll need to tell Visual Builder how to use those – and the way to achieve this is through the transforms tab in your service definition.
Transform functions let VB know how to transform the info generated from filterCriterion, sort and pagination to the format expected by the REST service you are calling. When you click the checkbox, we'll create a sample piece of code that you'll need to adapt for your specific REST service. Once you define such a transformation VB will know how to do more advance things with your service – for example filter a serviceDataProvider variable using the filterCriterion approach and sort and paginate a table based on this ServiceDataProvider.
The transform functions are generic for ORDS and don't depend on a specific table or service. Below I'm including the transform function I used in the video (which was developed by David Konecny from our VB team and enhanced by Amit Singh from our GSC Tech Cloud Digital). Note that it doesn't cover all the operations available in filterCriterion – so you might need to add some code in there to handle other operators.
define(['urijs/URI'], function (URI) {
'use strict';
const addQParam = (url, toAdd) => {
const query = URI(url).search(true);
if (query.q) {
toAdd = Object.assign(JSON.parse(query.q), toAdd);
}
query.q = JSON.stringify(toAdd);
return URI(url).search(query).toString();
};
class Request {
/**
* @typedef {Object} Configuration
* @property {Object} fetchConfiguration configuration for the current fetch call
* @property {Object} endpointDefinition metadata for the endpoint
* @property {function} readOnlyParameters: Path and query parameters. These are not writable.
* @property {Object} initConfig map of other configuration passed into the request. The
* 'initConfig' exactly matches the 'init' parameter of the request.
* @property {string} url full url of the request.
*/
/**
* @typedef {Object} TransformsContext a transforms context object that can be used by authors of transform
* functions to store contextual information for the duration of the request.
*/
/**
* filter builds filter expression query parameter using either the deprecated
* filterCriteria array or filterCriterion object set on the options.
* @param {Configuration} configuration
* @param {Object} options the JSON payload that defines the filterCriterion
* @param {TransformsContext} transformscontext a transforms context object that can be used by authors of transform
* functions to store contextual information for the duration of the request.
* @returns {Configuration} configuration object, the url looks like ?filter=foo eq bar
*/
filter(configuration, options, transformsContext) {
let filterCriterion = options;
if (filterCriterion === undefined) {
return configuration;
}
// following code is just an example and it assumes that
// the SDP does not have any preconfigured filter criterion and
// that there is only single "vb-textFilterAttributes" attribute set
if (transformsContext['vb-textFilterAttributes'] !== undefined) {
// in case the filtering comes from single select ui component
// the search criteria details come in transformsContext
const searchText = options && (options.text || options.value);
const textFilterAttributes = transformsContext && transformsContext['vb-textFilterAttributes'];
filterCriterion = textFilterAttributes.map(el => ({attribute: el, op: "$co", value: searchText }));
}
let operation;
let criteria = filterCriterion.criteria;
if (criteria === undefined || criteria.length === 0) {
if (filterCriterion instanceof Array && filterCriterion.every(el => el.attribute && el.op && el.value)) { // If conditon to handle if there are more than one "vb-textFilterAttributes" attribute
criteria = filterCriterion;
operation = "$or";
} else if (filterCriterion instanceof Object){
criteria = [filterCriterion];
operation = "$and";
}
else {
return configuration;
}
}
function transformOperator(fop) {
switch (fop) {
case '$co':
return '$instr';
case '$le':
return '$lte';
case '$ge':
return '$gte';
default:
return fop;
}
}
function isEmpty(val) {
return (val === undefined || val === null || val === '');
}
if (filterCriterion && criteria && criteria.length > 0) {
const q = [];
criteria.forEach(function (item) {
if (item.value === undefined || item.value === '%%' || item.value.length === 0) {
return;
}
const queryItem = {};
//Below function and If condition for date
function isDate(date) {
const regex = /\d{4}-\d{2}-\d{2}/;
const validDate = regex.test(date) && !isNaN((new Date(item.value)));
return validDate;
}
if(isDate(item.value)) {
const date = new Date(item.value).toISOString();
item.value = {
$date: date
};
}
queryItem[transformOperator(item.op)] = item.value;
const queryJSON = {};
queryJSON[item.attribute] = queryItem;
q.push(queryJSON);
});
if (q.length > 0) {
const query = {};
if (operation === undefined) {
operation = filterCriterion.op;
}
if (operation === undefined) {
operation = "$and";
}
query[operation] = q;
// ORDS query URL is for example:
// .../ords/hr/emp?q={"$or":[{"ename":{"$instr":"martin"}},{"job":{"$like":"%developer%"}}]}
configuration.url = addQParam(configuration.url, query);
}
}
return configuration;
};
/**
* @typedef {Object} PaginateOptions
* @property {number} iterationLimit
* @property {number} offset which item the response should begin from
* @property {String} pagingState
* @property {number} size how many items should be returned
*/
/**
* pagination function appends limit and offset parameters to the url
* @param {Configuration} configuration
* @param {PaginateOptions} options
* @param {TransformsContext} transformscontext
* @returns {Configuration} configuration object.
*/
paginate(configuration, options, transformscontext) {
let newUrl = configuration.url;
if (options && options.size) {
newUrl = URI(newUrl).addSearch({limit: options.size, offset: options.offset}).toString();
}
configuration.url = newUrl;
return configuration;
}
/**
* sort the 'uriParameters' property is passed in as options. Normally uriParameters are appended
* to the URL automatically, but there may be cases where the user would want to adjust the query parameters.
* @param {Configuration} configuration
* @param {Array} options
* @param {TransformsContext} transformscontext
* @returns {Configuration} configuration object, the url looks like ?orderBy=foo:asc
*/
sort(configuration, options, transformscontext) {
if (Array.isArray(options) && options.length > 0) {
const firstItem = options[0];
if (firstItem.attribute) {
const dir = firstItem.direction === 'descending' ? 'DESC' : 'ASC';
let newUrl = configuration.url;
const sort = '"$orderby":{"'+firstItem.attribute+'":"'+dir+'"}';
let query = URI(newUrl).search(true);
if (query.q) {
query.q = '{'+sort+','+query.q.substr(1);
} else {
query.q = '{'+sort+'}';
}
// ORDS sort URL is for example:
// ...ords/hr/emp?q={"$orderby":{"sal":"ASC"}}
// BUT: sorting is applied after filter() method above so sorting
// needs to be inserted into existing q param if filtering is on
newUrl = URI(newUrl).search(query).toString();
configuration.url = newUrl;
}
}
return configuration;
}
/**
* query function
* @param {Configuration} configuration
* @param {object} options
* @param {TransformsContext} transformscontext
* @returns {Configuration} configuration object
*/
/*query(configuration, options, transformscontext) {
const c = configuration;
if (options && options.search) {
let newUrl = c.url;
newUrl = URI(newUrl).addSearch( options.search, 'faq' ).toString(); // appends 'faq' to the search term
c.url = newUrl;
}
return c;
}*/
/**
* select typically uses the 'responseType' to construct a query parameter to select and expand
* the fields returned from the service
* Example:
*
* Employee
* - firstName
* - lastName
* - department
* - items[]
* - departmentName
* - location
* - items[]
* - locationName
*
* would result in this 'fields' query parameter:
*
* fields=firstName,lastName;department:departmentName;department.location:locationName
*
* @param {Configuration} configuration
* @param {object} options
* @param {TransformsContext} transformscontext
*/
/*select(configuration, options, context) {
const queryParamExists = (url, name) => {
const q = url.indexOf('?');
if (q >= 0) {
return (url.indexOf(`?${name}`) === q) || (url.indexOf(`&${name}`) > q);
}
return false;
};
// the options should contain a 'type' object, to override
const c = configuration;
// do nothing if it's not a GET
if (c.endpointDefinition && c.endpointDefinition.method !== 'GET') {
return c;
}
// do nothing if there's already a '?fields='
if(queryParamExists(c.url, 'fields')) {
return c;
}
// if there's an 'items', use its type; otherwise, use the whole type
const typeToInspect = (options && options.type && (options.type.items || options.type));
if(typeToInspect && typeToInspect === Object) {
const fields = 'TODO: query parameters'; // just an example; query parameter construction is left to the developer
if(fields) {
c.url = URI(c.url).addSearch('fields', fields).toString();
}
}
return c;
}*/
/**
* fetchByKeys allows the page author to take a key or Set of keys passed in via the options and
* tweak the URL, to fetch the data for the requested keys.
* @param {Configuration} configuration
* @param {object} options
* @param {TransformsContext} transformscontext
*/
fetchByKeys(configuration, transformOptions) {
const c = configuration;
const to = transformOptions || {};
const fetchByKeys = !!(c && c.capability === 'fetchByKeys'); // this tells us that the current fetch call is a fetchByKeys
if (fetchByKeys) {
const keysArr = Array.from(c.fetchParameters.keys);
const key = keysArr[0]; // grab the key provided by caller
if (key) {
c.url = URI(c.url).addQuery({ id: key }).toString();
}
}
return c;
}
/**
* body is used to build or tweak the body for the fetch request. With some endpoints the search is made with a
* complex search criteria set on the body that can be tweaked here.
* This transform function is the only function that is guaranteed to be called after all other request
* transform functions, (filter, sort, paginate, and so on). The reason is that any of the other transform
* functions can set info into the 'transformsContext' parameter, as a way to update the body.
* @param {Configuration} configuration
* @param {object} options
* @param {TransformsContext} transformscontext
*/
/*body(configuration, options, transformsContext) {
const c = configuration;
if (options && Object.keys(options).length > 0) {
c.initConfig.body = c.initConfig.body || {};
// update body
}
return c;
}*/
};
class Response {
/**
* @typedef {Object} PaginateResponse
* @property {number} totalSize optional what the totalSize of the result is (the total count of the records in
* the service endpoint).
* @property {boolean} hasMore usually required, the paginate response transform function is relied upon to
* inform the ServiceDataProvider when to stop requesting to fetch more data. Indicates whether there are more
* records to fetch
* @property {String} pagingState optional. This can be used to store any paging state specific to the paging
* capability supported by the endpoint. This property can be used in the response paginate transform function
* to set an additional paging state. This will then be passed as is to the request paginate transform function
* for the next fetch call.
*/
/**
* paginate is called with the response so this function can process it and return an object with
* properties set.
* @param {object} result
* @param {TransformsContext} transformscontext
* @return {PaginateResponse}
*/
paginate(result, transformscontext) {
const tr = {};
if (result && result.body) {
const cb = result.body;
// ORDS does not support "totalCount" but only "hasMore"
tr.hasMore = cb.hasMore;
}
return tr;
}
/**
* body is called last, after all the other response transforms have been called. It is a hook for authors
* to transform the response body or build an entirely new one.
* @param {object} result
* @param {TransformsContext} transformscontext
* @return {object}
*/
/*body(result) {
let tr = {};
if (result.body) {
tr = result.body;
}
// as a example store some random aggregation data
tr.aggregation = { example: 4 };
return tr;
}*/
}
return {
request: Request,
response: Response
};
});
Other option:
define(['urijs/URI'], function (URI) {
'use strict';
var Request = function () {
};
/**
* Filter transform function
* @param configuration
* @param options the JSON payload that defines the filterCriteria
* @returns {object} configuration object the url looks like ?filter=foo eq 'bar'.
*/
Request.prototype.filter = function(configuration, options) {
var filterCriterion = options;
if (filterCriterion === undefined) {
return configuration;
}
var operation;
var criteria = filterCriterion.criteria;
if (criteria === undefined || criteria.length === 0) {
if (filterCriterion.attribute && filterCriterion.op && filterCriterion.value) {
criteria = [filterCriterion];
operation = "$and";
} else {
return configuration;
}
}
function jetFilterOpToScim(fop) {
switch (fop) {
case '$co':
return '$like';
case '$le':
return '$lte';
case '$ge':
return '$gte';
default:
return fop;
}
}
function isEmpty(val) {
return (val === undefined || val === null || val === '');
}
if (filterCriterion && criteria && criteria.length > 0) {
var q = [];
criteria.forEach(function(crit) {
if (crit.value === undefined || crit.value === '%%' || crit.value.length === 0) {
return;
}
var v = {};
if (crit.op === '$co') {
v[jetFilterOpToScim(crit.op)] = '%'+crit.value+'%';
} else {
v[jetFilterOpToScim(crit.op)] = crit.value;
}
var vv = {};
vv[crit.attribute] = v;
q.push(vv);
});
if (q.length > 0) {
var query = {};
if (operation === undefined) {
operation = filterCriterion.op;
}
if (operation === undefined) {
operation = "$and";
}
query[operation] = q;
// ORDS query URL is for example:
// ...ords/hr/emp?q={"$or":[{"ename":{"$like":"%david%"}},{"job":{"$like":"%developer%"}}]}
configuration.url = URI(configuration.url).addQuery({ q : JSON.stringify(query)}).toString();
}
}
return configuration;
};
/**
* Pagination function appends limit and offset parameters to the url
* @param configuration
* @param options the JSON payload that defines the pagination criteria
* @returns {object} configuration object.
*/
Request.prototype.paginate = function(configuration, options) {
var newUrl = configuration.url;
if (options && options.size) {
newUrl = URI(newUrl).addSearch({limit: options.size, offset: options.offset}).toString();
}
configuration.url = newUrl;
return configuration;
};
/**
* Sort transform function
* @param configuration
* @param options the JSON payload that defines the sort criteria
* @returns {object} configuration object. the url looks like ?orderBy=foo:asc
*/
Request.prototype.sort = function(configuration, options) {
if (Array.isArray(options) && options.length > 0) {
var firstItem = options[0];
if (firstItem.attribute) {
var dir = firstItem.direction === 'descending' ? 'DESC' : 'ASC';
var newUrl = configuration.url;
var sort = '"$orderby":{"'+firstItem.attribute+'":"'+dir+'"}';
var query = URI(newUrl).search(true);
if (query.q) {
query.q = '{'+sort+','+query.q.substr(1);
} else {
query.q = '{'+sort+'}';
}
// ORDS sort URL is for example:
// ...ords/hr/emp?q={"$orderby":{"sal":"ASC"}}
// BUT: sorting is applied after filter() method above so sorting
// needs to be inserted into existing q param if filtering is on
newUrl = URI(newUrl).search(query).toString();
configuration.url = newUrl;
}
}
return configuration;
};
var Response = function() {
};
/**
* Paginate Response Transform Function Implementation
*/
Response.prototype.paginate = function(result) {
var tr = {};
if (result && result.body) {
var cb = result.body;
// ORDS does not support "totalCount" but only "hasMore"
tr.hasMore = cb.hasMore;
}
return tr;
};
return {
request: Request,
response: Response
};
});
–>
Some other things you might need to take care off are – having a valid SSL certificate for your ORDS – we have a blog that covers this here.
You will also need to decide how are you securing your ORDS and providing authentication – for example using OAuth2.
Note that there is also a sample of working with ORDS in the VB Cookbook that you can download.