"updateRecords"
JSON DB "updateRecords" action updates records in a database table
The "updateRecords" action updates one or more records in a database table using field values that you specify.
You must omit fields from the
"sourceData"property when you do not want to change their values.You must include all primary key fields and values in the
"sourceData"property because they are required to look up the record being updated.The
"updateRecords" action does not allow you to change the value of a field that is part of the primary key.When the table is created by the JSON DB API, its primary key is the
"id"field and its value must be included in the"sourceData"property to look up the record.For tables created by another API, such as SQL or ISAM, you must include all fields and values in the table’s primary key to look up the record.
Each record typically includes the
"changeId"field to control optimistic record locking.Optimistic locking detects a change conflict and protects a record from being overwritten accidentally — for example, process A reads a record and afterwards process B updates the same record to new values so when process A goes to update the record, it is unaware that process B has updated it.
The
"changeId"field must be included in the"sourceData"property when"ignoreChangeIdProtection"isfalseor omitted.The
"changeId"field in the"sourceData"property will be ignored when"ignoreChangeIdProtection"istrue.The
"changeId"field allows the server to identify update conflicts. It prevents one process from unknowingly overwriting changes made by another process. The server detects conflicts by comparing the"changeId"value in the"sourceData"property with the value of the"changeId"field in the record.If they match, there is no update conflict and the server updates the record.
If they do not match, the server returns an error because another process has changed the record.
When
"changeId"is present, the database compares the value of"changeId"to the current transaction number stored in the"changeId"field of the record. This ensures an update does not modify a record that has subsequently been modified by another operation.If they match, the database updates the record.
If they do not match, the database returns an error.
Record locks made by other operations may also cause an update to fail.
When the
"ignoreChangeIdProtection"property is set totrueand"changeId"is omitted from the"sourceData", the server updates the record unconditionally (unless another process, such as ISAM or SQL, has locked the record). Ignoring change protection is useful when you want to apply changes no matter what values are currently in the record.
Request examples
Update test1 table ignoring change protection request
This example ignores change protection by setting "ignoreChangeIdProtection" to true.
{
"requestId": "1",
"authToken": "replaceWithValidAuthtoken",
"api": "db",
"action": "updateRecords",
"params": {
"databaseName": "ctreeSQL",
"ownerName": "admin",
"tableName": "test1",
"dataFormat": "objects",
"ignoreChangeIdProtection": true,
"sourceData": [
{
"id": 1,
"name": "new updated value"
}
]
}
}
To successfully run this example, query the record, copy the latest value for "changeId", and update this code to use it. In this example, the changeId was taken from the first successful response example below.
{
"requestId": "2",
"authToken": "replaceWithValidAuthtoken",
"api": "db",
"action": "updateRecords",
"params": {
"databaseName": "ctreeSQL",
"ownerName": "admin",
"tableName": "test1",
"dataFormat": "objects",
"sourceData": [
{
"id": 1,
"changeId": 1295300,
"name": "updated"
}
]
}
}
{
"requestId": "3",
"authToken": "replaceWithValidAuthtoken",
"api": "db",
"action": "updateRecords",
"params": {
"databaseName": "ctreeSQL",
"ownerName": "admin",
"tableName": "all_types",
"dataFormat": "objects",
"binaryFormat": "hex",
"ignoreChangeIdProtection": true,
"sourceData": [
{
"id": 1,
"nested_json_object_or_array": {
"updated": "record"
},
"variable_string_up_to_max65500bytes": "updated value"
}
]
},
"responseOptions": {
"binaryFormat": "hex",
"dataFormat": "objects",
"numberFormat": "string"
},
"apiVersion": "1.0",
"debug": "max"
}
{
"authToken": "replaceWithValidAuthtoken",
"errorCode": 1192,
"errorMessage": "Cannot write the record while updating record with id 2 because the 'changeId' change Id field does not match"
}
{
"authToken": "replaceWithValidAuthtoken",
"errorCode": 101,
"errorMessage": "record not found updating record with 'id' :999"
}
The "changeId" field returned in the result is important. This value or a more current value must be included in the next update to ensure no other process has changed the record in the interim.
{
"authToken": "replaceWithValidAuthtoken",
"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": 50,
"scale": null,
"defaultValue": null,
"nullable": true,
"primaryKey": 0,
"autoValue": "none"
}
],
"data": [
{
"changeId": 1295300,
"id": 1,
"name": "new updated value"
}
],
"primaryKeyFields": [
"id"
],
"changeIdField": "changeId"
},
"requestId": "1",
"errorCode": 0,
"errorMessage": ""
}
{
"authToken": "replaceWithValidAuthtoken",
"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": "nested_json_object_or_array",
"type": "json",
"length": 65500,
"scale": null,
"defaultValue": null,
"nullable": true,
"primaryKey": 0,
"autoValue": "none"
},
{
"name": "boolean_byte",
"type": "bit",
"length": null,
"scale": null,
"defaultValue": null,
"nullable": true,
"primaryKey": 0,
"autoValue": "none"
},
{
"name": "signed_int8",
"type": "tinyint",
"length": null,
"scale": null,
"defaultValue": null,
"nullable": true,
"primaryKey": 0,
"autoValue": "none"
},
{
"name": "signed_int16",
"type": "smallint",
"length": null,
"scale": null,
"defaultValue": null,
"nullable": true,
"primaryKey": 0,
"autoValue": "none"
},
{
"name": "signed_int32",
"type": "integer",
"length": null,
"scale": null,
"defaultValue": null,
"nullable": true,
"primaryKey": 0,
"autoValue": "none"
},
{
"name": "signed_int64",
"type": "bigint",
"length": null,
"scale": null,
"defaultValue": null,
"nullable": true,
"primaryKey": 0,
"autoValue": "none"
},
{
"name": "ieee_base2float32",
"type": "real",
"length": null,
"scale": null,
"defaultValue": null,
"nullable": true,
"primaryKey": 0,
"autoValue": "none"
},
{
"name": "ieee_base2float64",
"type": "float",
"length": null,
"scale": null,
"defaultValue": null,
"nullable": true,
"primaryKey": 0,
"autoValue": "none"
},
{
"name": "signed32digits_base10_left32right0",
"type": "number",
"length": 32,
"scale": 0,
"defaultValue": null,
"nullable": true,
"primaryKey": 0,
"autoValue": "none"
},
{
"name": "signed32digits_base10_left0right32",
"type": "number",
"length": 32,
"scale": 32,
"defaultValue": null,
"nullable": true,
"primaryKey": 0,
"autoValue": "none"
},
{
"name": "signed32digits_base10_left20right12",
"type": "number",
"length": 32,
"scale": 12,
"defaultValue": null,
"nullable": true,
"primaryKey": 0,
"autoValue": "none"
},
{
"name": "signed32digits_base10_left30right2",
"type": "money",
"length": 32,
"scale": 2,
"defaultValue": null,
"nullable": true,
"primaryKey": 0,
"autoValue": "none"
},
{
"name": "signed32digits_base10_left28right4",
"type": "money",
"length": 32,
"scale": 4,
"defaultValue": null,
"nullable": true,
"primaryKey": 0,
"autoValue": "none"
},
{
"name": "date_yyyymmdd",
"type": "date",
"length": null,
"scale": null,
"defaultValue": null,
"nullable": true,
"primaryKey": 0,
"autoValue": "none"
},
{
"name": "time_hhmmssfff",
"type": "time",
"length": null,
"scale": null,
"defaultValue": null,
"nullable": true,
"primaryKey": 0,
"autoValue": "none"
},
{
"name": "datetime_yyyymmddthhmmssfff",
"type": "timestamp",
"length": null,
"scale": null,
"defaultValue": null,
"nullable": true,
"primaryKey": 0,
"autoValue": "none"
},
{
"name": "fixed_string_10bytes",
"type": "char",
"length": 10,
"scale": null,
"defaultValue": null,
"nullable": true,
"primaryKey": 0,
"autoValue": "none"
},
{
"name": "variable_string_up_to_max65500bytes",
"type": "varchar",
"length": 65500,
"scale": null,
"defaultValue": null,
"nullable": true,
"primaryKey": 0,
"autoValue": "none"
},
{
"name": "variable_string_up_to_2GB",
"type": "lvarchar",
"length": null,
"scale": null,
"defaultValue": null,
"nullable": true,
"primaryKey": 0,
"autoValue": "none"
},
{
"name": "fixed_binary_10bytes",
"type": "binary",
"length": 10,
"scale": null,
"defaultValue": null,
"nullable": true,
"primaryKey": 0,
"autoValue": "none"
},
{
"name": "variable_binary_up_to_max65500bytes",
"type": "varbinary",
"length": 65500,
"scale": null,
"defaultValue": null,
"nullable": true,
"primaryKey": 0,
"autoValue": "none"
},
{
"name": "variable_binary_up_to_2GB",
"type": "lvarbinary",
"length": null,
"scale": null,
"defaultValue": null,
"nullable": true,
"primaryKey": 0,
"autoValue": "none"
}
],
"data": [
{
"boolean_byte": true,
"changeId": "1299331",
"date_yyyymmdd": "2023-04-18",
"datetime_yyyymmddthhmmssfff": "2023-04-18T15:43:59.013",
"fixed_binary_10bytes": "FF00FF00000000000000",
"fixed_string_10bytes": "_ 3456 _",
"id": "1",
"ieee_base2float32": "-1e-06",
"ieee_base2float64": "-9.22337e+18",
"nested_json_object_or_array": {
"updated": "record"
},
"signed32digits_base10_left0right32": "-0.12345678901234567890123456789012",
"signed32digits_base10_left20right12": "-12345678901234567890.123456789012",
"signed32digits_base10_left28right4": "-1234567890123456789012345678.9012",
"signed32digits_base10_left30right2": "-123456789012345678901234567890.12",
"signed32digits_base10_left32right0": "-12345678901234567890123456789012",
"signed_int16": "-32768",
"signed_int32": "-2147483648",
"signed_int64": "-9223372036854775808",
"signed_int8": "-128",
"time_hhmmssfff": "15:43:59.013",
"variable_binary_up_to_2GB": "FF00FF",
"variable_binary_up_to_max65500bytes": "FF00FF",
"variable_string_up_to_2GB": "Variable-length string up to 2GB in length.",
"variable_string_up_to_max65500bytes": "updated value"
}
],
"primaryKeyFields": [
"id"
],
"changeIdField": "changeId"
},
"requestId": "3",
"debugInfo": {
"request": {
"authToken": "replaceWithValidAuthtoken",
"api": "db",
"action": "updateRecords",
"params": {
"databaseName": "ctreeSQL",
"ownerName": "admin",
"tableName": "all_types",
"dataFormat": "objects",
"binaryFormat": "hex",
"ignoreChangeIdProtection": true,
"sourceData": [
{
"id": 1,
"nested_json_object_or_array": {
"updated": "record"
},
"variable_string_up_to_max65500bytes": "updated value"
}
]
},
"apiVersion": "1.0",
"requestId": "3",
"responseOptions": {
"binaryFormat": "hex",
"dataFormat": "objects",
"numberFormat": "string"
},
"debug": "max"
},
"serverSuppliedValues": {
"databaseName": "ctreeSQL",
"ownerName": "admin"
},
"errorData": {
"errorData": null
},
"warnings": []
},
"errorCode": 0,
"errorMessage": ""
}
Use the updateRecords JSON API action to update records in a database table
The "params" property is an object that contains an action's parameters. Each action defines its own required and optional properties.
Property summary
"params" property summariesProperty | Description | Default | Type | Limits (inclusive) | |||
|---|---|---|---|---|---|---|---|
specifies how binary values are returned |
| string enum |
| ||||
specifies the name of a database | Defaults to the | string | 1 to 64 bytes | ||||
specifies the returned results as an array of array, an array of objects, or the default setting defined at login |
| string |
| ||||
determines whether the server ignores the |
| Boolean |
| ||||
specifies the unique name of a schema in a database |
| string | 0 to 256 bytes | ||||
sourceData | contains either an array of objects or an array of arrays (depending on the specified |
| array | ||||
specifies the name of a table | Required - No default value | string | 1 to 64 bytes |
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.
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.
Base64 is harder for people to read and convert to binary.
Base64 creates the smallest payload for the most efficient data transmission in JSON.
"base64"strings contain the characters0-9,A-Z,a-z,+,/, and=.
"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 30% larger payload than
"base64", which makes it less efficient for data transmission.Hexadecimal strings contain the characters
0-9andA-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": ""
}
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 limits are from 0 to 64 bytes.
If the
"databaseName"property is omitted or set tonull, the server will use the default database name specified at login.If no default database is specified during
"createSession","defaultDatabaseName"will be set to the"defaultDatabaseName"value that is specified in theservices.jsonfile.
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".
"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.
The "ignoreChangeIdProtection" property is an optional Boolean that causes the server to update the record regardless of the value of "changeId", when "true". It defaults to false.
Ignoring the
"changeId"field is useful when a process wants to ensure its changes are always applied to a record.However, ignoring the
"changeId"field can cause an update to overwrite changes made by other processes. So, setting"ignoreChangeIdProtection"property totrueis not recommended when an application reads a record so that a user can update it.It is a best practice to read the record, let the user change values, and update the record using the same
"changeId"value that was read.If another process has changed the record in the interim, the server will not update the record and will return error 32602 indicating a missing
"changeId"field or return error 1192 because the"changeId"value that was passed in matches the"changeId"value in the record.
The "ownerName" property is an optional string from 1 to 64 bytes that specifies the account that owns an object.
The "tableName" property is a string containing the name of a table.
A table name may contain up to 64 ASCII characters and must not start with a number.
Properties summary
"result" property summariesProperty | Description | Type | Limits (inclusive) | |||
|---|---|---|---|---|---|---|
determines how binary values are returned | string enum |
| ||||
data | contains 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 | |||
defines the format of the data in the | string |
| ||||
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 |
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.
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.
Base64 is harder for people to read and convert to binary.
Base64 creates the smallest payload for the most efficient data transmission in JSON.
"base64"strings contain the characters0-9,A-Z,a-z,+,/, and=.
"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 30% larger payload than
"base64", which makes it less efficient for data transmission.Hexadecimal strings contain the characters
0-9andA-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": ""
}
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".
"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.
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
}
]