getRecordsUsingSQL

JSON DB "getRecordsUsingSQL" action runs a SQL query and returns the resulting records

The "getRecordsUsingSQL" action runs a SQL query and returns the resulting records. It is particularly useful when joining data from multiple tables, for aggregating data, for running ad hoc queries, and for sorting results by unindexed fields.

It is the most efficient way to return all records from a SQL query. When query results contain too many records to retrieve all at once, use a cursor, which makes it easy, fast, and efficient to paginate results. See Most effective ways to return data from the JSON DB API. Returning a cursor and returning records directly are mutually exclusive operations.

Only the SQL SELECT statement can be used. Any other SQL statements fail and return an error. The only unusual requirement is that JSON requires the double-quote character to be escaped by the backslash character. The semicolon that SQL requires at the end of a SELECT statement is optional.

 

Request examples

Minimal

{
  "action": "getRecordsUsingSQL",
  "params": {
    "sql": "SELECT * from athlete"
  },
  "authToken": "replaceWithAuthTokenFromCreateSession"
}
 
 

Cursor

{
  "api": "db",
  "action": "getRecordsUsingSQL",
  "params": {
    "sql": "SELECT * from athlete",
    "returnCursor": true
  },
  "requestId": "2",
  "authToken": "replaceWithAuthTokenFromCreateSession"
}
 
 

Maximal

{
  "api": "db",
  "apiVersion": "1.0",
  "requestId": "3",
  "action": "getRecordsUsingSQL",
  "params": {
    "databaseName": "ctreeSQL",
    "sql": "SELECT * from athlete WHERE earnings > ?",
    "sqlParams": [
      1000000000
    ],
    "returnCursor": false,
    "forceRecordCount": true,
    "sqlForwardOnly": true
  },
  "responseOptions": {
    "binaryFormat": "hex",
    "dataFormat": "objects",
    "numberFormat": "string",
    "variantFormat": "variantObject",
    "includeFields": [],
    "excludeFields": []
  },
  "debug": "max",
  "authToken": "replaceWithAuthTokenFromCreateSession"
}
 
 

 

Response examples

Minimal

{
  "result": {
    "dataFormat": "objects",
    "numberFormat": "number",
    "binaryFormat": "hex",
    "fields": [
      {
        "name": "id",
        "type": "bigint"
      },
      {
        "name": "changeid",
        "type": "bigint"
      },
      {
        "name": "name",
        "type": "varchar",
        "length": 30
      },
      {
        "name": "ranking",
        "type": "smallint"
      },
      {
        "name": "birthdate",
        "type": "date"
      },
      {
        "name": "playernumber",
        "type": "numeric",
        "length": 32,
        "scale": 6
      },
      {
        "name": "livedpast2000",
        "type": "bit"
      },
      {
        "name": "earnings",
        "type": "money",
        "length": 32,
        "scale": 4
      },
      {
        "name": "favoritesaying",
        "type": "varchar",
        "length": 500
      }
    ],
    "data": [
      {
        "birthdate": "1963-02-17",
        "changeid": 1291366,
        "earnings": 1700000000,
        "favoritesaying": "There is no 'i' in team but there is in win.",
        "id": 1,
        "livedpast2000": true,
        "name": "Michael Jordan",
        "playernumber": 23,
        "ranking": 1
      },
      {
        "birthdate": "1942-01-17",
        "changeid": 1291366,
        "earnings": 60000000,
        "favoritesaying": "Float like a butterfly, sting like a bee.",
        "id": 3,
        "livedpast2000": true,
        "name": "Muhammad Ali",
        "playernumber": 1,
        "ranking": 3
      },
      {
        "birthdate": "1895-02-06",
        "changeid": 1291366,
        "earnings": 800000,
        "favoritesaying": "Every strike brings me closer to the next home run.",
        "id": 2,
        "livedpast2000": false,
        "name": "Babe Ruth",
        "playernumber": 3,
        "ranking": 2
      },
      {
        "birthdate": "1940-10-23",
        "changeid": 1291366,
        "earnings": 115000000,
        "favoritesaying": "Everything is practice.",
        "id": 4,
        "livedpast2000": true,
        "name": "Pele",
        "playernumber": 10,
        "ranking": 4
      },
      {
        "birthdate": "1961-01-26",
        "changeid": 1291366,
        "earnings": 1720000,
        "favoritesaying": "You miss 100 percent of the shots you never take.",
        "id": 5,
        "livedpast2000": true,
        "name": "Wayne Gretzky",
        "playernumber": 99,
        "ranking": 5
      },
      {
        "birthdate": "1969-01-03",
        "changeid": 1291366,
        "earnings": 990000000,
        "favoritesaying": "Once something is a passion, the motivation is there.",
        "id": 6,
        "livedpast2000": true,
        "name": "Michael Schumacher",
        "playernumber": 1,
        "ranking": 6
      }
    ],
    "moreRecords": false,
    "requestedRecordCount": 6,
    "returnedRecordCount": 6,
    "totalRecordCount": 6
  },
  "debugInfo": {
    "request": {
      "action": "getRecordsUsingSQL",
      "params": {
        "sql": "SELECT * from athlete"
      },
      "debug": "max",
      "authToken": "replaceWithAuthTokenFromCreateSession"
    },
    "serverSuppliedValues": {
      "databaseName": "ctreeSQL",
      "ownerName": null
    },
    "errorData": {
      "errorData": null
    },
    "warnings": []
  },
  "errorCode": 0,
  "errorMessage": "",
  "authToken": "replaceWithAuthTokenFromCreateSession"
}
 
 

Cursor

{
  "result": {
    "cursorId": "cursorId",
    "totalRecordCount": 6
  },
  "requestId": "2",
  "debugInfo": {
    "request": {
      "api": "db",
      "action": "getRecordsUsingSQL",
      "params": {
        "sql": "SELECT * from athlete",
        "returnCursor": true
      },
      "requestId": "2",
      "debug": "max",
      "authToken": "replaceWithAuthTokenFromCreateSession"
    },
    "serverSuppliedValues": {
      "databaseName": "ctreeSQL",
      "ownerName": null
    },
    "errorData": {
      "errorData": null
    },
    "warnings": []
  },
  "errorCode": 0,
  "errorMessage": "",
  "authToken": "replaceWithAuthTokenFromCreateSession"
}
 
 

Maximal

{
  "result": {
    "dataFormat": "objects",
    "numberFormat": "string",
    "binaryFormat": "hex",
    "fields": [
      {
        "name": "id",
        "type": "bigint"
      },
      {
        "name": "changeid",
        "type": "bigint"
      },
      {
        "name": "name",
        "type": "varchar",
        "length": 30
      },
      {
        "name": "ranking",
        "type": "smallint"
      },
      {
        "name": "birthdate",
        "type": "date"
      },
      {
        "name": "playernumber",
        "type": "numeric",
        "length": 32,
        "scale": 6
      },
      {
        "name": "livedpast2000",
        "type": "bit"
      },
      {
        "name": "earnings",
        "type": "money",
        "length": 32,
        "scale": 4
      },
      {
        "name": "favoritesaying",
        "type": "varchar",
        "length": 500
      }
    ],
    "data": [
      {
        "birthdate": "1963-02-17",
        "changeid": "1291366",
        "earnings": "1700000000",
        "favoritesaying": "There is no 'i' in team but there is in win.",
        "id": "1",
        "livedpast2000": true,
        "name": "Michael Jordan",
        "playernumber": "23",
        "ranking": "1"
      }
    ],
    "moreRecords": false,
    "requestedRecordCount": 1,
    "returnedRecordCount": 1,
    "totalRecordCount": 1
  },
  "requestId": "3",
  "debugInfo": {
    "request": {
      "api": "db",
      "action": "getRecordsUsingSQL",
      "params": {
        "databaseName": "ctreeSQL",
        "sql": "SELECT * from athlete WHERE earnings > ?",
        "sqlParams": [
          1000000000
        ],
        "returnCursor": false,
        "forceRecordCount": true,
        "sqlForwardOnly": true
      },
      "apiVersion": "1.0",
      "requestId": "3",
      "responseOptions": {
        "binaryFormat": "hex",
        "dataFormat": "objects",
        "numberFormat": "string",
        "variantFormat": "variantObject",
        "includeFields": [],
        "excludeFields": []
      },
      "debug": "max",
      "authToken": "replaceWithAuthTokenFromCreateSession"
    },
    "serverSuppliedValues": {
      "databaseName": "ctreeSQL",
      "ownerName": null
    },
    "errorData": {
      "errorData": null
    },
    "warnings": []
  },
  "errorCode": 0,
  "errorMessage": "",
  "authToken": "replaceWithAuthTokenFromCreateSession"
}
 
 

 

Properties

Request properties ("params")

Property Description Default Type Limits (inclusive)

databaseName

The "databaseName" property specifies the database that contains an object, such as a table or code package. If it is set to null or is omitted, it defaults to the default database of the JSON Action session, see "createSession" and the "defaultDatabaseName" property. 

You specify this property when you want to use a different database instead of the default. 

This property is useful because objects, such as tables and code packages, can have the same name in multiple databases. This feature allows you to create multiple environments in the same server and reuse the same JSON actions in each environment. For example, you can create "dev", "test", "stage", and "prod" databases on the same server and use the "defaultDatabaseName" or "databaseName" properties to specify the desired environment.

It is an error to set "databaseName" to the empty string "".

If no default database is specified during "createSession", the server sets the "defaultDatabaseName" to the "defaultDatabaseName" value specified in the services.json file.

 Defaults to the session's "defaultDatabaseName" property string 1 to 64 bytes

forceRecordCount

 The "forceRecordCount" property forces a query to return a record count when true.

Optional with default of false

Boolean

true

false

returnCursor

The "returnCursor" property causes the action to return a cursor instead of directly returning records when set to true.

  • To retrieve records, call the "getRecordsFromCursor" action and pass the "cursorId" value into it.
  • When "returnCursor" is "true" and one of the "skipRecords", "maxRecords", and "reverseOrder" properties is specified, an error is returned.
    • "returnCursor" is mutually exclusive with the "skipRecords", "maxRecords", and "reverseOrder" properties.
 Optional with default of false Boolean

true

false

sql

The "sql" property specifies a SQL statement.

Required - No default value

 string 1 to 32,000,000 bytes

sqlForwardOnly

The "sqlForwardOnly" property returns records in forward-only order.

Optional with default of false

Boolean

true

false

sqlParams

The "sqlParams" property specifies values that become parameters in the SQL statement.

  • Each value may be a string, number, integer, true, false, or null.
  • The same value may be repeated within the array.
  • The order of the values must match the order of the SQL parameters.
  • The type of each value must correspond to the field type of the corresponding SQL parameter.
  • It is an error if the number of SQL parameters do not match the number of items in this array.
  • It is a best practice to use SQL parameters rather than embedding values in a SQL query.
  • It prevents SQL injection attacks.
  • Because the same value may be applied to multiple SQL parameters, items in the "sqlParams" array can have the same value.
 Optional with default of [] array An array of values

 

Response properties ("result")

Property Description Type Limits (inclusive)

binaryFormat

 The "binaryFormat" property designates the format of binary values embedded in JSON strings. See binaryFormat for more details.  string One of the following: "base64", "hex", or "byteArray".

data

The "data" property contains a response message. Its contents are defined by the action. It is an empty array when no results are available. The following is an example of the data property from a code package action.

  "result": {
    "data": [
      {
        "codeId": 6,
        "databaseName": "faircom",
        "ownerName": "admin",
        "codeName": "convertAndCategorizeTemperature",
        "codeVersion": 1,
        "clonedCodeId": 1,
        "codeStatus": "active",
        "codeLanguage": "javascript",
        "serviceName": "javascript",
        "codeType": "module",
        "description": "optional new description",
        "metadata": {},
        "createdBy": "ADMIN",
        "createdOn": "2025-08-25T21:48:38.109",
        "updatedBy": "ADMIN",
        "updatedOn": "2025-08-25T21:48:38.109",
        "comment": "Cloned from convertTemperature",
        "codeFormat": "utf8"
      },
    ]
 array of objects The action determines its contents.

dataFormat

The "dataFormat" property (case-insensitive) defines the format of the "data" property. The default for "dataFormat" can be changed during a "createSession" action by assigning a different value to the "dataFormat" property in "defaultResponseOptions".

  • "dataFormat" in the response shows the client how the server formatted the "data" property.
    • Possible values include:
      • "arrays"
        • This is the default and causes the server to return results as an array of arrays, which is the most efficient.
      • "objects"
        • This returns results as an array of objects. This is less efficient but is simpler to generate, read, and troubleshoot.
 string

"autoDetect"

"arrays"

"objects"

fields

The "fields" property is an array of objects. Each object in the array defines a field by specifying its properties.

 

"fields": [
  {
    "autoValue": "none",
    "name": "name",
    "type": "varchar",
    "length": 50,
    "scale": null,
    "defaultValue": null,
    "nullable": false
  }
]
 array

"autoTimestamp"

"autoValue"

"primaryKey"
"name"
"type"
"length"
"scale"
"defaultValue"
"nullable"

moreRecords

 The "moreRecords" property indicates if there are more records that match the filters in the request.  Boolean

true

false

numberFormat

The "numberFormat" property (case-sensitive) defines the format of JSON numbers. 

When "numberFormat" occurs in "mapOfPropertiesToFields", it tells the server how to encode or decode a number assigned to a JSON property.

For example, including "numberFormat" in a "tableFieldsToJson" transform step controls if the server encodes a number in a JSON property as a number or a number embedded in a string.

Possible values:
  • "number"
    • This causes the server to return numeric values as JSON numbers, such as -18446744073709551616.000144722494 .
    • This is most efficient.
    • JSON represents numbers are base-ten numbers that may have any number of digits.
    • Large numbers, such as 18446744073709551616.000144722494 are known to cause problems with JSON parsers and some programming languages, such as JavaScript, which are limited to the smaller range and binary rounding errors of IEEE floating point numbers.
  • "string"
    • This returns the server to embed numeric values in JSON strings, such as "18446744073709551616.000144722494".
    • This is slightly less efficient because it includes two extra double quote characters
    • Returning numbers embedded in strings ensures JSON parsers and programming languages will not automatically convert the number to a numeric representation that loses precision, introduces rounding errors, truncates values, or generates errors. This allows your application to control how numbers are processed.
 string

"number"

"string"

requestedRecordCount

The "requestedRecordCount" property is a signed, 32-bit integer set by the server in response to the "getRecordsFromCursor" method.

  • It makes it easy to know how many records were requested in the last call to "getRecordsFromCursor".
  • An application can use "requestedRecordCount" in conjunction with "returnedRecordCount" to determine if fewer records were returned than requested, which occurs when the cursor reaches the end of the recordset.
 integer

0 to 2147483647

returnedRecordCount

The "returnedRecordCount" is a 32-bit integer set by the server in response to the "getRecordsFromCursor" method.

  • It makes it easy to know how many records were returned from the last call to "getRecordsFromCursor".
  • An application can use "returnedRecordCount" in conjunction with "requestedRecordCount" to determine if fewer records were returned than requested, which occurs when the cursor reaches the end of the recordset.
 integer

0 to 2147483647

totalRecordCount

The "totalRecordCount" property contains the total available number of records that can be returned from a query.

  • The "totalRecordCount" is set to -1, when the server does not know the total record count.
  • A very fast way to get the total number of records in a table is to call the "getRecordsByTable" method without applying a "tableFilter". This immediately returns the count without reading and counting records.
  • For most methods, the server does not calculate "totalRecordCount" because calculating it requires walking all records in the query, which may take a significant amount of time.
  • When the result is returned as a cursor, "totalRecordCount" is the total number of records that the cursor can traverse.
    • This does not apply to cursor responses.
  • When the result returns records directly, "totalRecordCount" is the total number of records that can be retrieved – not necessarily the number of records returned.
 integer

-1 to 99999999999999999999999999999999

 

Essential information

Notice JSON DB API transactions do not support SQL stored procedures, stored functions, and triggers. It also does not support "revertTransactionToSavepoint" when the transaction contains a call to "getRecordsUsingSQL" or "runSqlStatements". Unsupported actions return an error.

  • This and all FairCom JSON actions that run SQL statements use the FairCom "Direct SQL" API and as such access the data at full speed.
  • The "sqlParams" property is an optional array of objects (containing zero or more SQL parameters) embedded in the SQL statement. It specifies the value of each parameter. SQL parameters are used instead of embedded values in the SQL statements as it makes it easier to reuse SQL code. It also prevents SQL injection attacks.
  • Each object in the array represents one SQL parameter.
  • Each object contains the parameter’s name and its value.
  • For SQL and "sqlParams" objects, put a semicolon in front of the parameter name.
  • JSON DB API does not support specifying parameters by ordinal position because changing the parameter position in the SQL statement introduces bugs and makes the code harder to read.
  • If the fields returned by the SQL statement include JSON, you optionally can use the "includePaths" property to filter which JSON properties are returned.
  • If the results can be processed with one call to the operation, use "maxRecords" with a value of -1 to return all the results.
  • If the results are large, set "returnCursor" to true and use "getRecordsFromCursor" to process the results.
Performance considerations:
  • Results return less quickly when:
    • The SQL query processes many records.
    • There are many records in the results.
  • Do not call this action repeatedly to return paginated results; it reruns the SQL query. Instead, you must return a cursor.
  • By default, the "totalRecordCount" property in the response message is -1. To get an exact count, set the "forceRecordCount" to true in the request message. It will count all the records returned by the query. Forcing a record count may be a resource-intensive and time consuming process when the query walks many filtered records.

 

Was this article helpful?

Give feedback about this article

Related Articles