What is Post Query?

Post Query Script lets you to alter the data being fetched from the database, before it's presented to the User on the browser. You can choose between Java and/or PL/SQL to do the data manipulation.

Java Example

Let's say you have an attribute "Status" that contains values I for Inactive & A for Active. But on the UI instead of display I or A, we want to display "Inactive" or "Active". Though you can achive this by defining a ComboBox, you can also use Post Query to convert the values.

String status = data.get("Status");
// Here data is like a Map that contains all the attribute values of a row fetched from the database
if ("I".equals(status)) {
  data.set("Status", "Inactive");
} else if ("A".equals(status)) {
  data.set("status", "Active");  // Note: The Attribute name (Key) is NOT case sensitive.
}

The above is just a simple example, you could do more complex stuff with it. e.g. dynamically set the CSS class of a UI Component base on the values in that row.

Note: The above java snippet will be invoked once for each row ("data") of the result set that's displayed in the current pagination data

In order to modify the data exported to excel, check for config.getParam("ExcelExport") == 'Y' as shown below

if ("Y".equals(config.getParam("ExcelExport"))) {
  String status = data.get("Status");
  if ("I".equals(status)) {
    data.set("Status", "Inactive");
  } else if ("A".equals(status)) {
    data.set("status", "Active");
  }
}

You can also pass values from Pre-Query script to Post-Query thru' user's server session

db.setSessionData("Session Variable Name", data.get("Attribute To Be Carried Over From Pre-Query to Post-Query"));
data.set("Attribute To Be Carried Over From Pre-Query to Post-Query", db.getSessionData("Session Variable Name"));

Bulk Processing in Post-Query

If you want process through all the rows that are displayed within the current page of the search results, you can loop through the rows variable that would contain an array list of RPCModelData as shown in the below example
if (data == rows.get(rows.size()-1)) { // last row
	List<RPCModelData> list = new ArrayList<RPCModelData>(1);
	for (RPCModelData row : rows) {
		RPCModelData model = new RPCModelData();
		model.set("Cust Account Id", row.get("custaccountid"));
		model.set("Context", "Order");
		model.set("Type", "S");
		model.set("Context Id", row.get("Header Id"));
		model.set("Context Text", ((Double)row.get("Order Number")).longValue() + "");
		model.set("comments", db.getSessionData("SearchAuditComments"));
		list.add(model);    
	}
	db.postObjectsToDB("RecentActivities", list);
	db.getApplicationConnection().commit();
}

PL/SQL Example

To use PL/SQL you must check the "Use API" & "Call Post Query" checkbox on the Data Source. When checked, POST_QUERY procedure in the given PL/SQL Package will be invoked by the platform once for each row fetched from the database as a result of a query performed by the user.
PROCEDURE POST_QUERY (P_STATUS IN OUT VARCHAR2) IS
BEGIN
IF (P_STATUS = 'I') THEN
P_STATUS = 'Inactive';
ELSE
P_STATUS = 'Active';
END IF;
END; 

Variables available for Post Query

data (RPCModelData)
an RPCModelData instance that contains a row's data fetched from the database
config (RPCModelData)
Config data used for the current query. This is same as the data variable available in the pre-query
db
an instance of ScriptUtil class
dateUtils
an instance of DateUtils class
user
sign'ed in user's username
userid (Double)
sign'ed in user's userid
subDomain
current application's subdomain
dsName
Alias Name of the current Datasource
 rows (List<RPCModelData>)
All the rows of the current result set

Location

101 California Street, Suite 2710
San Francisco, CA 94111


440 N. Wolfe Rd.
Sunnyvale, CA 94085


Office 11, 5th Floor, Building 9,
Mindspace IT Park
Hyderabad 500081

p: +1 (844) AT CloudIO (844-282-5683)
f: +1 (650) 300-5247 | e: sales@cloudio.io
© 2009, 2017 CloudIO Inc. | Terms of Use | Privacy Policy