Using data sources for advanced functions

Invoking a data source using Pre Query

When your application requires custom search, you can write a pre query script on the data source. The platform invokes the pre-query script before performing a search, allowing the developer to control the query being executed to obtain appropriate results.

Invoking a data source in pre-query is mainly used to record an entry before querying for any database object.

E.g.: To know finance related query information, you can invoke a data source to create record entry in an audit table by capturing the user details from the session

When at times, the query gets complex:

1. Pre-process the query in PL/SQL.

2. Dump the query results into a temp table.

3. Create a data source.

   1. To invoke the required table, select the Use API checkbox, while creating the data source.

   2. Inherit the temp table.

4. Invoke a PL/SQL API based data source using the pre query.

5. Return an ID from the PL/SQL to the pre query.

6. Fetch the data and show the results in the UI of the temp table.

7. Use the code snippet below to invoke the datasource:

The platform maps the inherited result internally into the RPCModelData (a custom cloudIO java class object), which is rendered/read by the component on the UI. Every component works on the RPCModelData object.

• Change the parameter values as per your application specifications.

// Get the required parameter values from the current
// search criteria provided by the user i.e. user
// entered values in the search panel
String partyName = data.get("partyName");
String accountNumber = data.get("accountNumber");
String contactName = data.get("contactName");
String emailAddress = data.get("emailAddress");
String postalCode = data.get("postalCode");

// Prepare the model to be used invoke the
// Pre-Query PL/SQL Procedure
RPCModelData model = new RPCModelData();
 model.set("partyName", partyName);
 model.set("customerNumber", accountNumber);
 model.set("contactName", contactName);
 model.set("contactEmail", emailAddress);
 model.set("postalCode", postalCode);00000000000000000000000000000000

// Invoke the Pre-Query PL/SQL Procedure. This will call
// PACKAGE.INSERT_ROW Procedure
 db.postObjectToDB("CustomerPreQueryAPI", model);

// Get the return value
 Double batchId = model.get("batchId");

// Use the return value in the where clause
 if (batchId != null) {
     if (whereClause.length() > 0) {
         whereClause.append(" AND ");
     }
     whereClause.append("x.batch_id = ? ");
     params.add(batchId);
}