db  is an instance of ScriptUtil object and is available as a variable in all server side scripts - Server Validation, Pre-Query , Post-Query, HTML Request & Java Snippets. db provides various utility methods to interact with CloudIO Platform Datasource and other APIs. We have segregated the available methods under the following headings along with some sample code illustrating the various usage of the APIs.

Methods to Fetch Data

The following are the various methods available to fetch data from any data source using the db object.

Using RPCModelData

Use this method to fetch data using RPCModelData (a map like an object)

Method Signature

List<RPCModelData> getObjects(String dataSource, RPCModelData data)

The following example will fetch all employees of age 21 and living in San Fransisco

// Create an instance of RPCModelData for passing the filter criteria
RPCModelData param = new RPCModelData();

// Set the filter criteria
param.set("city", "San Fransisco");
param.set("age", 21);

String ds = "EmployeesDS";

// Invoke getObjects API to fetch data with the above filters
List<RPCModelData> list = db.getObjects(ds, param);

Range Search

Use the following example to fetch all employees at an age between 18 & 21 (inclusive of 18 & 21). This will become age >= 18 AND 

// Set the filter criteria
param.set("@>age", 18);
param.set("@<age", 21);

Complex Usage

Use the following example to fetch the data with complex where clause along with the above map like usage

RPCModelData param = new RPCModelData();

// Create an instance of RPCModelData 
// and set the custom where clause with bind variables
RPCModelData customParam = new RPCModelData();
String whereClause = "NVL(#attributeName#, 'Y') = ? AND 1 = ?";
customParam.set("WHERE_CLAUSE", whereClause);

// Create a list of objects for the bind values
List<Object> custParaList = new ArrayList<Object>();
custParaList.add("Y");
custParaList.add(1);

// set the bind variables to customParam 
customParam.set("params", custParaList);

// Set the customParam to the main param object
param.setCustomParam(customParam);

List<RPCModelData> list = db.getObjects(ds, param);

RPCModelData

You can use the following methods on RPCModelData  to further configure the filter criteria

RPCModelData param = new RPCModelData();

// Performing pagination queries
param.setOffset(0);
param.setLimit(20);

// By default, all the attribute key values will be converted to lower case 
// and any non-alphanumeric characters will be removed

// if you want the attribute keys to be in camel case, then use the following option
// primarily used for JSON clients e.g. mobile applications
param.setRest(true);

// if you want the = condition to be applied for the query parameters 
// instead of LIKE
data.setParam("strict", "Y");

// Setting order by clause
// attribute name has be in camel case when using REST API
// for desktop/RPC APIs it can be of any case
param.setParam("ORDER_BY", "#attributeName1#, #attributeName2# DESC");

Using Where Clause

Use this method to fetch data using a where clause with bind parameters

Method Signature

List getObjects(String dataSource, String whereClause, List params)

// Create a list of parameter values for the bind variables in the where clause
List<Object> custParaList = new ArrayList<Object>();
custParaList.add("Y");
custParaList.add(1);

//Invoke getObjects API to fetch data using the where clause and bind parameters
String wc = "NVL(#attributeName#, 'Y') = ? AND 1 = ?";
List<RPCModelData> list = db.getObjects(ds, wc, custParaList);

Using Primary Key Values

Use this method to fetch a single row using the primary key values

Method Signature

RPCModelData getObjectByPKValues(String ds, Object... params)

String empNo = "101";
RPCModelData result = db.getObjectByPKValues(ds, empNo);

Double headerId = 101d;
Double lineId = 1001d;
RPCModelData result = db.getObjectByPKValues(ds, headerId, lineId);

Single Row Using Name-Value Pair

Use this method to fetch a single row using the one or more attribute name values

Method Signature

RPCModelData getObjectByNameValues(String ds, Object... nameValues)

RPCModelData result = db.getObjectByNameValues(ds, "empNo", "101");

result = db.getObjectByNameValues(ds, "headerId", 101, "lineId", 1);

Multiple Rows Name-Value Pair

Use this method to fetch multiple rows using the one or more attribute name values

Method Signature

List getObjectsByNameValues(String ds, int offset, Object... nameValues)

List<RPCModelData> list = db.getObjectsByNameValues(ds, 0, "empNo", "101");

list = db.getObjectsByNameValues(ds, 0, "headerId", 101, "lineId", 1);

Using Map, Offset & Limit

Use this method to fetch multiple rows using a map with one or more attribute name values

Method Signature

List<RPCModelData> getObjectsByNameValues(ds, Map<String, Object> map, int offset, limit)

// This is a shorthand method for the below
RPCModelData data = new RPCModelData();
data.setOffset(offset);
data.setLimit(limit);
for (String key : map.keySet()) {
	data.set(key, map.get(key));
}
data.setParam("strict", "Y");
return getObjects(ds, data);
Method Signature

List<RPCModelData> getObjectsByNameValues(ds, Map<String, Object> map, int offset)

// This is a shorthand method for the below
return getObjectsByNameValues(ds, map, offset, 200);

Sequence  Next Value

Sequence Next Value

Double getNextVal(String sequenceName)

Profile Value

Profile Value For Current Logged In User

String getProfile(String profileCode)

Profile Value For a Specific User

String getProfile(Double userId, String profileCode)

Others

Current User's OrgId

Double getOrgId()

Current User's UserId

Double getUserId()

Current User's Username

String getUserName()

UserId from Username

Double getUserId(String userName)

Display Name from UserId

String getUserDisplayName(Double userId)

Email address from UserId

String getUserEmailAddress(Double userId)

Shorthand for

RPCModelData row = getUserRowFromCache(userId); return row.getString("emailaddress");

Email address from UserId

String getUserEmail(Double userId)

Shorthand for getUserDisplayName(userId) + "<" + getUserEmailAddress(userId) + ">"

Current User Session Object

UserSession getUserSession()

Cached User Row from UserId

RPCModelData getUserRowFromCache(Double userId)

Request URL

String getURL()

Bookmarkable URL for a given Page Code

String getURL(boolean embeded, String pageCode, String otherParams)

String getURL(String pageCode, String otherParams)

Session Value

Object getSessionValue(String fieldName)

Where fieldName could be any of the following

userid orgid username displayname email resourceid sessionid subdomain

clienttimezone sysdate companyid servertimezone uploadrequestid uploadfilename

rolecode profile.PROFILE_CODE

Get an Object from a Cache

Object getData(String key)

Methods to Post Data

The following are the various methods available to post data using the db object.

Posting Multiple Rows

Use this method to post an array of rows

Method Signature

List<RPCModelData> postObjectsToDB(String ds, List<RPCModelData> rows)

Posting Single Row

Use this method to post a single row

Method Signature

RPCModelData postObjectToDB(String ds, RPCModelData row)

This is a shortcut method for the below

List<RPCModelData> list = new ArrayList<RPCModelData>(1);
list.add(row);
list = postObjectsToDB(ds, list);
return list.get(0);

Insert, Update & Delete in a Single Post

You can set the status on each of the row to instruct which operation to perform

To Insert

row.setStatus(RPCModelData.Status.Insert);

To Update

row.setStatus(RPCModelData.Status.Update);

To Delete

row.setStatus(RPCModelData.Status.Delete);

Set & Get Profile Value

To Set User Level Profile Value

void setProfileValue(Double userId, String code, String value)

To Get User Level Profile Value

String getProfile(Double userId, String profileCode)

Posting Notifications

To Publish on Behalf of the Current User

void publishUserNotification(String subject, String body, String roleUids, String userIds)

Where  roleUids & userIds are comma sepeated values.

To Publish as System Administrator

void publishSystemNotification(String subject, String body, String roleUids, String userIds)

Sending Email

Method Signature

void sendEmail(String toEmails, String ccEmails, subject, body, custTemplate)

Where toEmails & ccEmails are comma seperated email addresses, Subject & Body are String values, Body can be an HTML String. Set custTemplate to "Y" to send an email without CloudIO logo in it.

To Schedule an Email to be Sent at a Future Date & Time

void sendEmail(toEmails, ccEmails, bccEmails, subject, body, Date startDate, custTemplate)

With BCC

void sendEmail(toEmails, ccEmails, bccEmails, subject, body, custTemplate)

With Multiple Attachments

void sendEmail(to, cc, bcc, subject, body, custTemplate, List<Long> attachments)

With Single File Attachment

void sendEmail(to, cc, bcc, subject, body, custTemplate, Long fileId)

// This is a shortcut for the following
List<Long> attachments = new ArrayList<Long>();
attachments.add(fileId);
sendEmail(toEmails, ccEmails, bccEmails, es, eb, custTemplate, attachments);

Debug Log

To Check if Debug is Enabled for the Current Datasource

boolean isDebug()

Server-Side Log

void log(String message)

Execute JDBC Statements

To Fetch a Single Column Value

Object executeQueryObject(String sql, Object[] params)

To Fetch a Double Value

Double executeQueryDouble(String sql, Object[] params)

String sql = "select count(1) from my_table where some_column = ?";
Object[] params = new Object[]{data.get("attributeName")};
Double count = db.executeQueryDouble(sql, params);
Fetch Multiple Rows

List<RPCModelData> executeQuery(String sql, Object[] params)

To Execute an Update Statement

int executeUpdate(String sql, Object[] params)

Programatic User Creation

Method Signatures

RPCModelData createUser(String userName, String email, String DisplayName)

RPCModelData createUser(userName, email, displayName, String managerName)

Create User with Roles Assigned

RPCModelData createUserWithRoles(userName, email, displayName, managerName, String[] roleCodes)

Assign Roles to an existing User

List assignRoles(Double userId, String[] roleCodes)

Assign Roles by Username

List<RPCModelData> assignRoles(String userName, String[] roleCodes)

// This is shortcut method for the following
Double userId = getUserId(userName);
return assignRoles(userId, roleCodes);

Utility Methods

The following are the various utility methods available on db

Checks if both objects are equal

boolean equalWithNull(Object obj1, Object obj2)

Returns false if either one is null

Check there is change in the object values

boolean hasChanged(Object obj1, Object obj2)

Returns false if both are null

To encode a String to Base64

String encode(String s)

To decode a Base64 encoded String

String decode(String s)