Skip to main content

"getRecordsFromCursor"

The JSON DB API "getRecordsFromCursor" action retrieves records from a cursor created by a "getRecords..." action

Abstract

The "getRecordsFromCursor" action efficiently retrieves records from the recordset of a previously opened cursor. A cursor can jump to the beginning or end of the recordset, skip forward or backward, and retrieve records forward or backward. This action works for all FairCom products that include the FairCom database engine.

The "getRecordsFromCursor" action efficiently retrieves records from the recordset of a previously opened cursor. A cursor can jump to the beginning or end of the recordset, skip forward or backward, and retrieve records forward or backward. This action works for all FairCom products that include the FairCom database engine.

A cursor always remembers its position; thus, you can repeatedly call "getRecordsFromCursor" to retrieve additional records starting where the cursor left off. This approach makes pagination easy to implement.

A cursor is created by a call to a "getRecords..." action. All "getRecords..." actions (except for "getRecordsByIds" ) let you set a "returnCursor" property to true, which causes them to create a cursor and return a reference to it in the "cursorId" property. You must use this "cursorId" property in the "getRecordsFromCursor" action to retrieve records from the cursor.

To free resources on the server, you must call "closeCursor" when you no longer need a cursor.

The server automatically closes a cursor when you do not retrieve records within the number of seconds specified by the "idleCursorTimeoutSeconds" property in the "createSession" and "alterSession" actions. When you call the "getRecordsFromCursor" action, the server resets the timeout. You can set "idleCursorTimeoutSeconds" to -1 to prevent a session's cursors from timing out, but you must not fail to call "closeCursor" when you no longer need it.

How to use a cursor

Each call to the "getRecordsFromCursor" can use the "fetchRecords", "skipRecords", and "startFrom", properties to reset the cursor position, skip records, fetch one or more records, or do all of the above.

  • To fetch forwards, assign a positive integer number to "fetchRecords".

  • To fetch backward, assign a negative integer number to "fetchRecords".

  • To skip forwards, assign a positive integer number to "skipRecords".

  • To skip backward, assign a negative integer number to "skipRecords".

  • To reset a cursor to the beginning of the recordset, set the "startFrom" property to "beforeFirstRecord".

  • To reset a cursor to the end of the recordset, set the "startFrom" property to "afterLastRecord".

Cursor recipes

Note

Some cursors are forward only, such as cursors returned from the "getRecordsFromTable" action when querying a table containing variable-length records.

If you attempt to use a negative number for "fetchRecords" and "skipRecords" on a forward-only cursor, the "getRecordsFromCursor" returns error 48.

If you need to fetch records backward, create a cursor based on an index, such as "getRecordsByIndex", "getRecordsByPartialKeyRange", "getRecordsInKeyRange", and "getRecordsStartingAtKey".

Troubleshooting

When you receive a cursor error that stops the cursor from working, reset the cursor position by calling "getRecordsFromCursor" with the "startFrom" property set to "beforeFirstRecord" or "afterLastRecord".

Error examples that require you to reset the cursor position:

719: Can't move to previous record in table

14703 - JSONNAV_INTERNAL_ERROR, JSONNAV_NOTYET_PARAM - A jsonAction internal error was encountered. Report this to FairCom.

Things to know

  • The following actions create forward-only cursors:

    • "getRecordsFromTable" creates a cursor that moves forward through every record in the table.

    • "getRecordsUsingSQL" creates a cursor that moves forward through every record returned by the SQL query.

    Note

    If you receive error 48 when using negative numbers for skip or fetch, it indicates the cursor is forward-only. A table cursor cannot skip or fetch backward when the table contains variable-length records. If you need to fetch records backward, create a cursor based on an index, such as "getRecordsByIndex", "getRecordsByPartialKeyRange", "getRecordsInKeyRange", and "getRecordsStartingAtKey".

  • The following actions create bidirectional cursors:

    • "getRecordsByIndex" creates a cursor that moves forward or backward through every record in the index.

    • "getRecordsStartingAtKey" creates a cursor that moves forward or backward through every record in the index starting with the closest match to the key.

    • "getRecordsByPartialKeyRange" creates a cursor that moves forward or backward through every record in the index that matches the partial key.

    • "getRecordsInKeyRange" creates a cursor that moves forward or backward through every record in the index within the specified key range.

  • A cursor is always positioned before or after a record, and the fetch direction determines the cursor position.

    • When fetching forward, the cursor is positioned before a record to retrieve the record on its way forward.

    • When fetching backward, the cursor is positioned after the current record to retrieve the record on its way backward.

    • When a cursor is positioned before the first record, it cannot be moved backward.

    • When a cursor is positioned after the last record, it cannot be moved forward.

  • Calling "getRecordsFromCursor" with an expired or closed cursor, returns an error response.

Request examples

Minimal request example

{
  "api": "db",
  "authToken": "replaceWithAuthtokenFromCreateSession",
  "action": "getRecordsFromCursor",
  "params": {
    "cursorId": "replaceWithCursorIdFromGetRecords",
    "fetchRecords": 1
  }
}

Maximal request example

{
  "api": "db",
  "apiVersion": "1.0",
  "requestId": "2",
  "authToken": "replaceWithAuthtokenFromCreateSession",
  "action": "getRecordsFromCursor",
  "params": {
    "cursorId": "replaceWithCursorIdFromGetRecords",
    "startFrom": "beforeFirstRecord",
    "skipRecords": 3,
    "fetchRecords": 2
  },
  "responseOptions": {
    "binaryFormat": "hex",
    "dataFormat": "objects",
    "numberFormat": "string",
    "includeFields": [],
    "excludeFields": []
  },
  "debug": "max"
}

Response examples

Minimal response example

{
  "authToken": "authToken",
  "result": {
    "dataFormat": "objects",
    "binaryFormat": "hex",
    "fields": [
      {
        "name": "id",
        "type": "bigint",
        "length": null,
        "scale": null,
        "defaultValue": null,
        "nullable": false,
        "primaryKey": 1,
        "autoValue": "incrementOnInsert"
      },
      {
        "name": "changeId",
        "type": "bigint",
        "length": null,
        "scale": null,
        "defaultValue": null,
        "nullable": true,
        "primaryKey": 0,
        "autoValue": "changeId"
      },
      {
        "name": "name",
        "type": "varchar",
        "length": 30,
        "scale": null,
        "defaultValue": null,
        "nullable": true,
        "primaryKey": 0,
        "autoValue": "none"
      },
      {
        "name": "ranking",
        "type": "smallint",
        "length": null,
        "scale": null,
        "defaultValue": null,
        "nullable": false,
        "primaryKey": 0,
        "autoValue": "none"
      },
      {
        "name": "birthDate",
        "type": "date",
        "length": null,
        "scale": null,
        "defaultValue": null,
        "nullable": true,
        "primaryKey": 0,
        "autoValue": "none"
      },
      {
        "name": "playerNumber",
        "type": "number",
        "length": 32,
        "scale": 6,
        "defaultValue": null,
        "nullable": true,
        "primaryKey": 0,
        "autoValue": "none"
      },
      {
        "name": "livedPast2000",
        "type": "bit",
        "length": null,
        "scale": null,
        "defaultValue": null,
        "nullable": true,
        "primaryKey": 0,
        "autoValue": "none"
      },
      {
        "name": "earnings",
        "type": "money",
        "length": 32,
        "scale": 4,
        "defaultValue": null,
        "nullable": true,
        "primaryKey": 0,
        "autoValue": "none"
      },
      {
        "name": "favoriteSaying",
        "type": "varchar",
        "length": 500,
        "scale": null,
        "defaultValue": null,
        "nullable": true,
        "primaryKey": 0,
        "autoValue": "none"
      }
    ],
    "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
      }
    ],
    "primaryKeyFields": [
      "id"
    ],
    "changeIdField": "changeId",
    "moreRecords": true,
    "requestedRecordCount": 1,
    "returnedRecordCount": 1
  },
  "errorCode": 0,
  "errorMessage": ""
}

Maximal response example

{
  "authToken": "authToken",
  "result": {
    "dataFormat": "objects",
    "binaryFormat": "hex",
    "fields": [
      {
        "name": "id",
        "type": "bigint",
        "length": null,
        "scale": null,
        "defaultValue": null,
        "nullable": false,
        "primaryKey": 1,
        "autoValue": "incrementOnInsert"
      },
      {
        "name": "changeId",
        "type": "bigint",
        "length": null,
        "scale": null,
        "defaultValue": null,
        "nullable": true,
        "primaryKey": 0,
        "autoValue": "changeId"
      },
      {
        "name": "name",
        "type": "varchar",
        "length": 30,
        "scale": null,
        "defaultValue": null,
        "nullable": true,
        "primaryKey": 0,
        "autoValue": "none"
      },
      {
        "name": "ranking",
        "type": "smallint",
        "length": null,
        "scale": null,
        "defaultValue": null,
        "nullable": false,
        "primaryKey": 0,
        "autoValue": "none"
      },
      {
        "name": "birthDate",
        "type": "date",
        "length": null,
        "scale": null,
        "defaultValue": null,
        "nullable": true,
        "primaryKey": 0,
        "autoValue": "none"
      },
      {
        "name": "playerNumber",
        "type": "number",
        "length": 32,
        "scale": 6,
        "defaultValue": null,
        "nullable": true,
        "primaryKey": 0,
        "autoValue": "none"
      },
      {
        "name": "livedPast2000",
        "type": "bit",
        "length": null,
        "scale": null,
        "defaultValue": null,
        "nullable": true,
        "primaryKey": 0,
        "autoValue": "none"
      },
      {
        "name": "earnings",
        "type": "money",
        "length": 32,
        "scale": 4,
        "defaultValue": null,
        "nullable": true,
        "primaryKey": 0,
        "autoValue": "none"
      },
      {
        "name": "favoriteSaying",
        "type": "varchar",
        "length": 500,
        "scale": null,
        "defaultValue": null,
        "nullable": true,
        "primaryKey": 0,
        "autoValue": "none"
      }
    ],
    "data": [
      {
        "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"
      }
    ],
    "primaryKeyFields": [
      "id"
    ],
    "changeIdField": "changeId",
    "moreRecords": true,
    "requestedRecordCount": 2,
    "returnedRecordCount": 2
  },
  "requestId": "2",
  "debugInfo": {
    "request": {
      "authToken": "authToken",
      "api": "db",
      "action": "getRecordsFromCursor",
      "params": {
        "cursorId": "cursorId",
        "startFrom": "beforeFirstRecord",
        "skipRecords": 3,
        "fetchRecords": 2
      },
      "apiVersion": "1.0",
      "requestId": "2",
      "responseOptions": {
        "binaryFormat": "hex",
        "dataFormat": "objects",
        "numberFormat": "string",
        "includeFields": [],
        "excludeFields": []
      },
      "debug": "max"
    },
    "serverSuppliedValues": {
      "databaseName": null,
      "ownerName": null
    },
    "errorData": {
      "errorData": null
    },
    "warnings": []
  },
  "errorCode": 0,
  "errorMessage": ""
}

"params"

The "params" property is an object that contains an action's parameters. Each action defines its own required and optional properties.

Property summary

Table 1. "params" property summaries

Property

Description

Default

Type

Limits (inclusive)

"cursorId"

contains a unique identifier returned by the server

Required - No default value

string

Minimum length: 0
Maximum length: 255

"fetchRecords"

contains a positive or negative a positive integer fetches that number of records forward, a negative integer fetches that number of records backward

null

integer

"skipRecords"

specifies the number of records to skip over in the results before returning records

0

integer

0 - 9,223,372,036,854,775,807

"startFrom"

sets the cursor to a known position

"currentPosition"

string

"currentPosition"
"beforeFirstRecord"
"afterLastRecord"


"cursorId"

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

Things to know:

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

"fetchRecords"

The "fetchRecords" property is a required integer number from -2,147,483,648 to 2,147,483,647. A positive integer fetches that number of records forward. A negative integer fetches that number of records backward. It defaults to null.

Things to know:

  • A value of zero is an error because it does not fetch any records.

  • Fetching around 10,000 records at a time may be the most efficient way to transfer large numbers of records.

  • Larger numbers may cause network delays and slow processing on the client. Smaller numbers require more client-server round trips.

"skipRecords"

The "skipRecords" property is an optional integer from 0 to 9,223,372,036,854,775,807. It is used in query actions to paginate the query results when returning records directly. It defaults to 0.

Things to know:

  • It specifies the number of records to skip over in the results before returning records.

  • It works with "maxRecords" and "reverseOrder".

  • It is ignored when "returnCursor" is true.

    Note

    This does not apply to the actions where "returnCursor" is not present.

"result"

Property summary

Table 2. "result" property summaries

Property

Description

Type

Limits (inclusive)

"binaryFormat"

designates how binary values are returned

string enum

"base64"
"hex"

"data"

contains an array or object that the server returns, such as records returned by a query

Note

It is an empty array when there are no results available.

array

Its contents are determined by the action

"dataFormat"

defines the format of the data in the "data" property

string

"autoDetect"
"arrays"
"objects"

"fields"

shows 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

"primaryKey"

represents the order of the primary keys in the index

Note

When "primaryKey" is 0 it means a field is not part of a primary key.

integer

0 - 255

"requestedRecordCount"

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

integer

0 - 2147483647

"returnedRecordCount"

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

integer

0 - 2147483647



"binaryFormat"

The "binaryFormat" property designates how binary values are formatted in the JSON request and JSON response message. The default value is "base64".

Note

Typically, response options apply only to the server’s response, but the "binaryFormat" property applies to both the request and the response.

Things to know:

  • The "binaryFormat" property may occur inside "params",  "responseOptions", "defaultResponseOptions" , and "result".

  • When "binaryFormat" occurs in "params" it specifies how the sender represents binary values.

    For example, when  "binaryFormat" is set to "hex", the FairCom server expects the binary values of fields and keys to be represented in strings with hexadecimal format.

  • When "binaryFormat" occurs in "responseOptions" or "defaultResponseOptions" it specifies how the FairCom server should represent binary values in responses.

    For example, when "binaryFormat" is set to "hex", the FairCom server represents binary values in strings with hexadecimal format.

  • When "binaryFormat" occurs in "result" it signifies how binary values are represented.

    For example, when "binaryFormat" is set to "base64", the FairCom server represents binary values in the response in base64 format.

  • The following are the possible values for each format.

    • "base64"

      When the server reads and writes from a binary field, it represents the binary value as a base64 string.

      • This is the default

      • "base64" strings contain the following characters:

        • 0-9

        • A-Z

        • a-z

        • +

        • /

        • =

    • "hex"

      When the server reads and writes from a binary field, it represents the binary value as a hexadecimal string.

      • Hexadecimal is easier for people to read and convert to binary.

      • Hexadecimal creates a larger payload than "base64", which makes it less efficient for data transmission.

      • Hexadecimal strings contain the following characters:

        • 0-9

        • A-F

    • "byteArray"

      When the server reads and writes from a binary field, it represents the binary value as an array of bytes.

      • An array of bytes is easiest for a program to manipulate.

      • An array of bytes creates a larger payload than "base64" and "hex", which makes it less efficient for data transmission.

      • An array of bytes returns a JSON array containing one integer number between 0 and 255 for each byte in the binary value:

        • "aBinaryField": [ 255, 0, 255 ]

Examples

Create a "binary_test" table

This example creates a table containing one binary field named "bin" with a fixed length of 5 bytes.

{
  "api": "db",
  "action": "createTable",
  "params": {
    "tableName": "binary_test",
    "fields": [
      {
        "name": "bin",
        "type": "binary",
        "length": 5
      }
    ]
  },
  "authToken": "replaceWithValidAuthtoken"
}
Insert a record into the "binary_test" table using an array of bytes format

This example inserts a record with the ASCII characters "123" in the "bin" field. The value of "bin" is represented as an array of bytes.

{
  "api": "db",
  "action": "insertRecords",
  "params": {
    "tableName": "binary_test",
    "dataFormat": "objects",
    "binaryFormat": "byteArray",
    "sourceData": [
      {
        "bin": [49,50,51]
      }
    ]
  },
  "authToken": "replaceWithValidAuthtoken"
}
Insert a record into the "binary_test" table using hexadecimal format

This example inserts a record with the ASCII characters "123" in the "bin" field. The value of "bin" is represented as a string in hexadecimal format.

{
  "api": "db",
  "action": "insertRecords",
  "params": {
    "tableName": "binary_test",
    "dataFormat": "objects",
    "binaryFormat": "hex",
    "sourceData": [
      {
        "bin": "313233"
      }
    ]
  },
  "authToken": "replaceWithValidAuthtoken"
}
Insert a record into the "binary_test" table using base64 format

This example inserts a record with the ASCII characters "123" in the "bin" field. The value of "bin" is represented as a string in base64 format.

{
  "api": "db",
  "action": "insertRecords",
  "params": {
    "tableName": "binary_test",
    "dataFormat": "objects",
    "binaryFormat": "base64",
    "sourceData": [
      {
        "bin": "MTIz"
      }
    ]
  },
  "authToken": "replaceWithValidAuthtoken"
}
Retrieve a record with "binaryFormat" as an array of bytes

This example requests the first record in the "binary_test" table with the value of "bin" represented as an array of bytes.

{
  "api": "db",
  "action": "getRecordsByTable",
  "params": {
    "tableName": "binary_test",
    "maxRecords": 1
  },
  "responseOptions": {
    "binaryFormat": "byteArray",
    "dataFormat": "objects",
    "numberFormat": "number"
  },
  "authToken": "replaceWithValidAuthtoken"
}
Response

Note

Our examples insert only 3 bytes into "bin" . Because the "bin" field has a fixed-length if 5 bytes, the server pads unused bytes with 0x00 and stores the result. When a record is retrieved, the server returns all 5 bytes.

{
  "authToken": "authtokenFromServer",
  "result": {
    "dataFormat": "objects",
    "binaryFormat": "byteArray",
    "fields": [
      { "name": "id",       "type": "bigint", "length": null, "scale": null, "autoTimestamp": "none", "defaultValue": null, "nullable": false, "primaryKey": 1 },
      { "name": "changeId", "type": "bigint", "length": null, "scale": null, "autoTimestamp": "none", "defaultValue": null, "nullable": true,  "primaryKey": 0 },
      { "name": "bin",      "type": "binary", "length": 5,    "scale": null, "autoTimestamp": "none", "defaultValue": null, "nullable": true,  "primaryKey": 0 }
    ],
    "data": [
      {
        "bin": [49,50,51,0,0],
        "changeId": 50217,
        "id": 1
      }
    ],
    "moreRecords": true,
    "requestedRecordCount": 1,
    "returnedRecordCount": 1,
    "totalRecordCount": 3
  },
  "errorCode": 0,
  "errorMessage": ""
}
Retrieve a record with "binaryFormat" as hexadecimal

This example requests the first record in the "binary_test" table with the value of "bin" represented as a hexadecimal string.

{
  "api": "db",
  "action": "getRecordsByTable",
  "params": {
    "tableName": "binary_test",
    "maxRecords": 1
  },
  "responseOptions": {
    "binaryFormat": "hex",
    "dataFormat": "objects",
    "numberFormat": "number"
  },
  "authToken": "replaceWithValidAuthtoken"
}
Response
{
  "authToken": "authtokenFromServer",
  "result": {
    "dataFormat": "objects",
    "binaryFormat": "byteArray",
    "fields": [
      { "name": "id",       "type": "bigint", "length": null, "scale": null, "autoTimestamp": "none", "defaultValue": null, "nullable": false, "primaryKey": 1 },
      { "name": "changeId", "type": "bigint", "length": null, "scale": null, "autoTimestamp": "none", "defaultValue": null, "nullable": true,  "primaryKey": 0 },
      { "name": "bin",      "type": "binary", "length": 5,    "scale": null, "autoTimestamp": "none", "defaultValue": null, "nullable": true,  "primaryKey": 0 }
    ],
    "data": [
      {
        "bin": "3132330000",
        "changeId": 50217,
        "id": 1
      }
    ],
    "moreRecords": true,
    "requestedRecordCount": 1,
    "returnedRecordCount": 1,
    "totalRecordCount": 3
  },
  "errorCode": 0,
  "errorMessage": ""
}
Retrieve a record with "binaryFormat" as base64

This example requests the first record in the "binary_test" table with the value of "bin" represented as a base64 string.

{
  "api": "db",
  "action": "getRecordsByTable",
  "params": {
    "tableName": "binary_test",
    "maxRecords": 1
  },
  "responseOptions": {
    "binaryFormat": "base64",
    "dataFormat": "objects",
    "numberFormat": "number"
  },
  "authToken": "replaceWithValidAuthtoken"
}
Response
{
  "authToken": "authtokenFromServer",
  "result": {
    "dataFormat": "objects",
    "binaryFormat": "byteArray",
    "fields": [
      { "name": "id",       "type": "bigint", "length": null, "scale": null, "autoTimestamp": "none", "defaultValue": null, "nullable": false, "primaryKey": 1 },
      { "name": "changeId", "type": "bigint", "length": null, "scale": null, "autoTimestamp": "none", "defaultValue": null, "nullable": true,  "primaryKey": 0 },
      { "name": "bin",      "type": "binary", "length": 5,    "scale": null, "autoTimestamp": "none", "defaultValue": null, "nullable": true,  "primaryKey": 0 }
    ],
    "data": [
      {
        "bin": "MTIzAAA=",
        "changeId": 50217,
        "id": 1
      }
    ],
    "moreRecords": true,
    "requestedRecordCount": 1,
    "returnedRecordCount": 1,
    "totalRecordCount": 3
  },
  "errorCode": 0,
  "errorMessage": ""
}

"dataFormat"

The "dataFormat" property is an optional, case-insensitive string enum that defines the format of the response in 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 inside "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 causes the server to automatically detect 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.

"fields"

The "fields" property is a required array of field-type objects. There is one object for each top-level field in the array. Each object identifies the actual data type of the field as stored in the database.

A field-type object is used when creating a table. It contains one object for each field definition returned in the data.

Full request example

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

Full response example

"fields":
[
  {
      "name": "id",
      "type": "bigint",
      "length": null,
      "scale": null,
      "autoValue": "none",
      "defaultValue": null,
      "nullable": false,
      "primaryKey": 1
  }
]

Property summary

Table 3. "fields" property summaries

Property

Description

Default

Type

Limits (inclusive)

"autoValue"

controls when and how the server automatically sets the field value

"none"

string

"none"
"incrementOnInsert"
"timestampOnInsert"
"timestampOnUpdate"
"timestampOnUpdateAndInsert"
"changeid"

"defaultValue"

specifies the default value of a field

""

string

Minimum length: 0
Maximum length: 65500

"length"

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

null

integer

1 - 65500

"name"

defines a new name of the field

required

string

Minimum length: 0
Maximum length: 64

"nullable"

allows a field to contain a NULL value when true

true

Boolean

true
false

"primaryKey"

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

null

integer

0 - 32

"scale"

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

null

integer

0 - 32

"type"

defines the type of field

required

string

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


"autoValue"

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

Specify only one of these values per field.

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

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

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

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

"requestedRecordCount"

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

Things to know:

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

Things to know:

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