Skip to main content

"getRecordsUsingSQL" (JSON Action)

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 Best ways to return data. 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.

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.

Request examples

Minimal

{
  "api": "db",
  "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": {
      "api": "db",
      "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"
}

Use the getRecordsUsingSQL JSON API action to run a SQL query and return the resulting records

API actionsJSON DB APIJSON Actionquery actionsget records using sqlgetRecordsUsingSQL

"params"

The "params" property is an object that contains an action's request parameters as defined by a set of properties. Each action defines its own required and optional properties. See System limits for a comprehensive overview of property requirements and limitations.

Table 1. "params" property summaries

Property

Description

Default

Type

Limits (inclusive)

databaseName

(optional) specifies the name of a database.

Defaults to the "defaultDatabaseName" value that is set during "createSession". If no default is set during "createSession", then "faircom" is used.

string

1 to 64 bytes

forceRecordCount

(optional) forces a query to return a record count when true

false

Boolean

true
false

ownerName

(optional) specifies the unique name of a schema in a database.

""

string

1 to 64 bytes

returnCursor

(optional) configures the server to return a customized response

false

Boolean

true
false

sql

specifies an SQL statement

Required - No default value

string

1 to 32,000,000 bytes

sqlForwardOnly

(optional) returns records in forward-only order

false

Boolean

true
false

sqlParams

(optional) specifies optional values where each value becomes a parameter in the SQL statement

[]

array

transformCodeName

(optional) specifies the name of a "getRecordsTransform" code package to transform the output of the "getRecords..." action

""

string

The string must contain the name of an existing code package with a "codeType" of "getRecordsTransform"



"databaseName"

The "databaseName" property is an optional string that specifies the database that contains the tables. It defaults to the database name supplied at login.

Note

In the API Explorer, "defaultDatabaseName" is set to "ctreeSQL" in the "createSession" action that happens at login.

  • A zero-length "databaseName" is invalid.

  • Its length limit is from 0 to 64 bytes.

  • If the "databaseName" property is omitted or set to null, the server will use the default database name specified at login.

  • If no default database is specified during "createSession", "databaseName" will be set to the "defaultDatabaseName" value that is specified in the services.json file.

"params": {
  "databaseName": "mainDatabase"
  }

"ownerName"

The "ownerName" property is an optional string from 1 to 64 bytes that identifies the user who owns an object (see Object owner). If it is omitted or set to "" or null, the server uses the default owner name supplied during the "createSession" action or uses the account's "username" as the owner name.

"params": {
  "ownerName": "SuperUser"
}

"returnCursor"

The "returnCursor" property is an optional Boolean. When true, an action returns a cursor instead of directly returning records. It defaults to "false".

  • 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.

    Note

    "returnCursor" is mutually exclusive with the "skipRecords", "maxRecords", and "reverseOrder" properties.

"sql"

The "sql" property is a required string from 1 to 32,000,000 bytes. It is a SQL statement. A zero-length SQL statement is invalid.

"sqlParams"

The "sqlParams" is an optional array of values. Each value becomes a parameter 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.

"transformCodeName"

The "transformCodeName" property is an optional string that specifies the name of an existing code package with a "codeType" of "getRecordsTransform".

When this property is present in a "getRecords..." transform, the server passes the query results to the code package as an array of objects. The code transforms the records and returns a valid JSON value. The server places the value in the "data" property of the response to the "getRecords..." action.

"responseOptions"

Table 2. "responseOptions" property summaries

Property

Description

Default

Type

Limits (inclusive)

binaryFormat

specifies how binary values are returned

"base64"

string

One of the following: "base64", "hex", or "byteArray".

dataFormat

specifies what format the results will be returned in

"arrays"

string

"default"
"arrays"
"objects"

excludeFields

(optional) specifies which fields are excluded in the response message

[]

When the array is empty or the property is not specified, the includeFields behavior applies

array

excludePaths

specifies which paths are excluded in the response message

[]

When the array is empty or the property is not specified, the includePaths behavior applies

array

includeFields

(optional) specifies which fields are returned in the response message

[]

When the array is empty or the property is not specified, all fields are returned

array

includePaths

specifies which paths are included in the response message

[]

When the array is empty or the property is not specified, all paths are returned

array

numberFormat

specifies how numbers are formatted in the JSON response message

"number"

string

"number"
"string"

omit

specifies which properties are excluded in the response message

{}

object

variantFormat

(optional) specifies how the server will represent the values of variant fields in its response to your request

"json"

string

"json"
"binary"
"string"
"variantObject"


"binaryFormat"

"dataFormat"

"numberFormat"

The "numberFormat" property is an optional, case-insensitive string enum. It defines the format of JSON numbers. The default value for "numberFormat" is the "defaultNumberFormat" defined in the "createSession" or "alterSession" actions. If it is omitted there, it defaults to the value of the "defaultNumberFormat" property in the <faircom>/config/services.json file.

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.

  • Omitted or set to null

    This defaults to "number".

Example request

{
  "action": "someAction",
  "responseOptions":
  {
    "numberFormat": "string"
  },
  "authToken": "replaceWithAuthTokenFromCreateSession"
}

"variantFormat"

The "variantFormat" property tells the server how to format the values of variant fields in its response to your request.

The "variantFormat" property has one of the following values: "binary", "json", "string", and "variantObject". It tells the server how to store and return values stored in variant fields. The default value is "json".

Things to know

  • The server applies the "variantFormat" property to all variant fields affected by a JSON action.

  • If you want to control the variant format of each field, set the "variantFormat" property to "variantObject" and use variant objects to set the variant type and encoding of each variant field.

The following sections describe each value of the "variantFormat" property.

"variantFormat": "binary"

  • Use the "variantFormat": "binary" setting to encode a variant field's value as a JSON string.

  • The "binaryFormat" property specifies how to encode the binary value.

    • The "binaryFormat": "hex" setting uses hexadecimal to encode a binary value in a JSON string.

    • The "binaryFormat": "base64" setting uses Base64 to encode a binary value in a JSON string.

    • The "binaryFormat": "byteArray" setting uses an array of integer numbers to encode each byte in the binary value.

  • When "variantFormat" is "binary", the server sets the variant's type to "binary" and stores raw bytes in the variant field.

  • The "binary" variant type ensures maximum compatibility and performance across all FairCom APIs, including JSON DB, SQL, CTDB, and ISAM.

  • The server does not validate the binary value, which can be any sequence of bytes.

"variantFormat": "json"

  • Use the "variantFormat": "json" setting to encode a variant field's value as a JSON value.

    • A JSON value can be an object, array, string, number, true, false, or null.

    • A binary field value is returned in a JSON string.

    • The "binaryFormat" property specifies the format of a binary variant value embedded in a JSON string.

    • The "numberFormat" property causes the server to return numbers as JSON numbers or JSON strings.

  • When "variantFormat" is "json", the server sets the variant's type to "json" and stores a JSON value in the variant field.

    • The server validates JSON before it assigns it to a variant field.

    • The server converts a variant field to valid JSON before it returns the value of a variant field.

  • When "variantFormat" is set to "json" in "responseOptions" and the "type" of variant object is "binary", the JSON API returns the binary variant value embedded in a string or as a byte array depending on the "binaryFormat" property in "responseOptions". In other words, when "variantFormat" is set to "json" in "responseOptions", the API returns a JSON value. All variant values can be returned as native JSON values except for binary values because JSON cannot represent them. Thus, when a variant has a binary value and the response option is JSON, the API embeds the binary value in a JSON string according to the value of "binaryFormat".

  • The server converts variant values to and from JSON as follows:

    • JSON null: The server does not store a JSON null; instead it marks the variant field as NULL. If the field is not nullable, the API returns an error.

    • JSON string: The server stores a JSON string in the variant field as is. It includes the double quotes before and after the string's value and stores escaped characters without decoding them. For example, the server stores the JSON string, "my // string",  as "my // string". The server returns a JSON string exactly as it stores it.

    • JSON number: The server stores a JSON number in the variant field as is. A number consists of the following ASCII characters 1234567890+-.eE. For example, the server stores the JSON number -123.45  as the ASCII characters -123.45. The server returns a JSON string exactly as it stores it.

    • JSON true & false: The server stores a JSON true and false in the variant field as is. It stores and returns the ASCII characters true or false.

    • JSON object & array: The server stores a JSON object and array as is. When it validates the JSON, it minimizes whitespace while preserving the UTF-8 characters representing the actual object or array. For example, the server stores the array [1,2,3]  as the UTF-8 characters [1,2,3].

"variantFormat": "variantObject"

  • Use the "variantFormat": "variantObject" setting to encode a variant field's value as a variant object.

  • When the "insertRecords" and "updateRecords" actions have "variantFormat": "variantObject" in "params", you must assign a variant object to each variant field.

  • A JSON action returns a variant object for a variant field value when you put "variantFormat": "variantObject" in "responseOptions".

"variantFormat": "string"

  • Use the "variantFormat": "string" setting to encode a variant field's value as a JSON string.

  • This setting in the "responseOptions" property causes all "getRecords..." actions to return string values from variant fields.

    • Binary values are returned as a JSON string containing an embedded binary value. The "binaryFormat" property specifies how to encode the binary value.

    • Number values are returned as a JSON number embedded in a string, such as "123.45". The server ignores the "numberFormat" property in the "responseOptions" object.

    • Boolean values are returned as a "true" or "false" string.

    • String values are returned as a JSON string, which may contain JSON escaped characters.

    • JSON values are returned embedded in a string.

      • A JSON object is escaped, encoded, and embedded in a string, such as "{\"key\": \"value\"}".

      • A JSON array is escaped, encoded, and embedded in a string, such as "[1, \"my string\", true ]".

      • A JSON string is returned as a JSON string.

      • A JSON number is embedded in a JSON string, such as "-123.456".

      • A JSON true is returned as "true".

      • A JSON false is returned as "false".

      • A JSON null is returned as "null".

  • This setting in the "params" property for "insertRecords" and "updateRecords" actions requires a string value to be assigned to each variant field.

    • FairCom does not recommend this setting because it can only store strings in variant fields. Consider using the other "variantFormat" options to store many more types of data, such as binary, JSON objects, JSON arrays, numbers, etc.

"result"

Table 3. "result" property summaries

Property

Description

Type

Limits (inclusive)

cursorId

specifies a unique identifier returned by the server

data

specifies an array or object that the server returns, such as records returned by a query. It is an empty array when there are no results available

array

Its contents are determined by the action

dataFormat

specifies the format of the data in the "data" property

string

"autoDetect"
"arrays"
"objects"

fields

specifies an array of objects set by the server, where each object is the definition of a field in a table defining the details of each field returned by a query

array

fields
.autoValue

specifies when and how the server automatically sets the field value

string

"none"
"incrementOnInsert"
"timestampOnInsert"
"timestampOnUpdate"
"timestampOnUpdateAndInsert"
"changeid"
fields
.defaultValue

specifies the default value of a field

string

0 to 65,500 bytes

fields
.length

specifies the length of a field's value in a record

integer

1 to 65500 

fields
.name

specifies the new name of the field

string

0 to 64 bytes

fields
.nullable

allows a field to contain a NULL value when true

Boolean

true
false
fields
.primarykey

adds a field to the specified ordinal position of the table's primary key when > 0

integer

0 to 32

fields
.scale

specifies the number of places to the right of the decimal point

integer

0 to 32

fields
.type

specifies the type of field

string

"bit"
"tinyint"
"smallint"
"integer"
"bigint"
"real"
"float"
"number"
"money"
"date"
"time"
"timestamp"
"char"
"varchar"
"lvarchar"
"binary"
"varbinary"
"lvarbinary"
"json"

requestedRecordCount

details how many records were requested in the "getRecordsFromCursor" action to determine if fewer records were returned than were requested

integer

0 to 2147483647

returnedRecordCount

details how many records were returned from the "getRecordsFromCursor" action to determine if fewer records were returned than were requested

integer

0 to 2147483647

totalRecordCount

specifies an integer, the server sets its value to the number of records returned by a query

integer

-1 to 99999999999999999999999999999999



"autoValue"

This optional property indicates when and how the server automatically sets the field value.

Only one of these values is allowed per field.

  • "none" indicates that the server does not automatically set the field's value.

  • "incrementOnInsert" indicates that the server automatically increments a field’s value each time the server inserts a new record. It applies to fields that are of the type of decimal or one of the integer types, such as "bigint". Only one field per table can have this attribute. The server returns an error when assigning this attribute to multiple fields. The JSON DB API automatically creates the "id" field as an "incrementOnInsert" field. If you apply this attribute to another field, it becomes the only automatically incremented field in the table. If you want that field to be the primary key, assign  "primaryKey": 1 to it.

  • "timestampOnInsert" indicates that the server automatically sets a field’s value to the current date and time of an insert. It applies only to fields with a type of "timestamp".

    Note

    If you attempt to insert a record and specify a timestamp for a field that has "autoValue" set to "timestampOnInsert", the timestamp you specified is ignored, and the current date and time are assigned to the field.

  • "timestampOnUpdate" indicates that the server automatically sets a field’s value to the current date and time of an update. This setting applies only to timestamp fields.

  • "timestampOnUpdateAndInsert" indicates that the server automatically sets a field’s value to the current date and time of an insert and an update. It applies only to fields with a type of "timestamp".

  • "changeId" indicates the server uses the field for optimistic locking. The server automatically sets the field's value to the internal transaction number used during the last update of the record. This value changes each time the server updates the record. A table may only have one change tracking field. The field type must be "bigint".

    The JSON DB API automatically creates a "changeid" field with change-tracking functionality.

    Change tracking is optional in the CTDB and ISAM APIs. The application must create a 64-bit integer field and assign change-tracking functionality to it.

Request Example

"fields": [
  {
    "name": "signed_int32",
    "type": "integer",
    "autoValue": "incrementOnInsert"
  }
]

Response Example

{
  "result": {
    "dataFormat": "objects",
    "data": [
      {
        "changeIdField": "changeId",
        "createRecByteIndex": false,
        "databaseName": "ctreeSQL",
        "fieldDelimiterValue": 0,
        "fields": [
          {
            "autoValue": "incrementOnInsert",
            "defaultValue": null,
            "length": null,
            "name": "signed_int32",
            "nullable": true,
            "primaryKey": 0,
            "scale": null,
            "type": "integer"
          }
        ]
      }
    ]
  },
  "errorCode": 0,
  "errorMessage": "",
  "authToken": "replaceWithAuthTokenFromCreateSession"
}

"cursorId"

The "cursorId" property is a required string from 0 to 255 bytes. It is a unique identifier returned by the server.

  • The "getRecordsFromCursor" action uses it to quickly and efficiently retrieve paginated records.

  • Setting a zero-length "cursorId" in the request is invalid.

  • It is not returned when "returnCursor" is false.

Important

Do not assume the "cursorId" is a number embedded in a string.

"dataFormat"

The "dataFormat" property is a case-insensitive string enum that defines the format of the "data" property. The default format is an array of arrays. The alternative is an array of objects. The default for "dataFormat" can be changed during a "createSession" action by assigning a different value to the "dataFormat" property in "defaultResponseOptions".

There are three different (but similar) versions of the "dataFormat" property:

Two of those versions occur in a request, and another occurs in a response. They all indicate how data is formatted.

  • "dataFormat" in the request in "responseOptions" determines how the "data" property in the response is formatted.

    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.

  • "dataFormat" in the request in the "params" object notifies the server how the "sourceData" property is formatted in the request. This version is rarely used because of the default "autoDetect" behavior.

    Possible values include:

    • "arrays"

      This 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.

    • "autoDetect"

      This is the default, and the server automatically detects the format of the data in the "sourceData" property.

  • "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.

Example response

{
  "result": {
    "dataFormat": "objects"
  }
}

"fields"

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

Example

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

"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.

"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.

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.

    Note

    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.

Structured Query Language (SQL) is a standardized programming language that is used to manage relational databases and perform various operations on the data in them.

FairCom's SQL engine is ANSI compliant and has almost all SQL features up through the SQL:2003 standard.

A database is a set of data that is managed by a database server. A database contains data that is organized into tables, records, and fields. A database is a huge composite data type that represents a group of interconnected objects. A database allows records to be joined together for querying, searching, and processing by users, applications, and services.

A database is a security container that controls which accounts have permissions to access specific data.