rbf_selectQuery2()

Warning: Support for using this method with external objects (such as those mapped to external tables or through a HDP connection) is a beta feature. This method is supported in production systems, except for external objects.

Purpose

This function runs a SQL SELECT query on the server and returns results as a 2D array to the callback function. It differs from rbf_selectQuery() in that it takes a rowFrom parameter where you can specify a starting row other than 0. For fields that contain values for different languages, pass a langCode value in the options parameter to get the values in a specific language. If the field does not allow multiple languages, or if there is no value for the specified language, the value is returned in the tenant's base language. To use the options parameter, you must specify a value for useLegacyDateFormat. Throws an error if the specified language code is not configured for the tenant.

The current user must have View permission on the selected object type to run this API.

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

rbf_selectQuery2(query, rowFrom, maxRows, callback, useLegacyDateFormat, options);

Parameters

query

SQL SELECT query. See Query API section below for examples and limitations.

rowFrom

Number of row to start output (0 based)

maxRows

Maximum number of rows to retrieve (1-20,000 range)

callback

A callback function that will receive a 2D JSON array with query results as parameter.

useLegacyDateFormat

This optional parameter only applies to JSON output and, specifically, to Date and Date/Time fields. If true, or if not specified, a date value is returned as a Date object. If false, a date value is returned as a String.

options

An optional JSON object that sets the values of optional arguments. The properties that this object can take are as follows:

  • langCode to a valid two-letter ISO language code, for example, {"langCode":"es"}
  • useISODateFormat to get the date or date/time values in ISO format, for example, {"useISODateFormat":true}.
    • If the date and time value is Mon Jul 30 00:01:00 IST 2018 its equivalent ISO formatted value 2018-07-29T18:31:00Z- is returned.
    • If the date value is Mon Jul 30 2018, its equivalent ISO formatted value 2018-07-30 is returned.

Note:
  • This API is supported in portals as well.
  • If both useLegacyDateFormat and useISODateFormat are set to true, useISODateFormat is applied.

Example

The following example returns the query result in French:

function my_callback(values) {
      var amount = values[0][0];
      var price  = values[0][1];
      // Do something...
      }
    
rbf_selectQuery2("select amount, price from order where id={!id}", 1, 10, my_callback, true, {"langCode":"fr"});