selectQuery()

Purpose

Runs a SQL SELECT query on the server and returns the results as a 2D-array. Either the current user or the API user must have View access for all records of the given type.

The selectQuery() method, as well as selectValue() and selectNumber(), use the same permissions model as the server-side query API (see Adding Logic to an Application for more details).

Examples of valid queries on the User object (you can find more examples in Adding Logic to an Application): 

SELECT id, name, updatedAt, updatedBy FROM USER WHERE name LIKE 'M%' ORDER BY name
    
SELECT count(1) FROM USER WHERE name LIKE 'M%'
    
SELECT count(1) FROM USER WHERE updatedBy>=QUARTER

The SELECT query used in this method consists of the following parts:

  • The SELECT statement expects columns or expressions to be selected (mandatory). Use the field integration names as SQL column names. You can use expressions such as COUNT(1). You cannot use * to retrieve all columns.
  • The FROM clause must consist of exactly one object name (mandatory).
  • The WHERE clause can include a valid SQL expression to narrow the selection (optional). Use field integration names as SQL column names.
  • The ORDER BY clause can include a valid SQL expression to order the selection (optional). Use field integration names as SQL column names.

You can use special tokens in your queries such as:

  • TODAY for current time
  • WEEK for 12PM of last Sunday
  • MONTH for 12PM of 1st day of current month
  • QUARTER for 12PM of 1st day of current quarter
  • YEAR for 12PM of 1st day of current year
  • CURR_USER for id of currently logged in user

Object and Field names are case-sensitive, while other components of the SQL query are not.

Use #code suffix to fetch integration codes for picklist fields rather than IDs. See Adding business logic for more information.

Syntax

selectQuery(string sessionId, string query, int maxRows);

Parameters

sessionId

A string containing the session ID obtained at log in.

query

SQL SELECT query

maxRows

An integer value representing the maximum number of rows to retrieve (can be configured per Platform instance)

Output

SELECT query wrapped in a StringArr2D

Permissions Required

View permission for the requested object type.

Example

StringArr2D values = binding.selectQuery(sessionId, "SELECT loginName, firstName, lastName FROM USER ORDER BY lastLogin DESC", 10);
StringArr[] recs = values.getArr();
for (StringArr rec: recs) {
	String[] data = rec.getArr();
	String loginName = data[0];
	String firstName = data[1];
	String lastName  = data[2];
}