runSqlStatements

JSON DB "runSqlStatements" action runs SQL statements as a batch operation

The "runSqlStatements" action runs one or more SQL statements as a batch operation, such as drop, create, alter, call, insert, update, and delete statements.

 

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.

  • The "runSqlStatements" action cannot run in an existing transaction. It creates a new transaction.
  • FairCom does not recommend including SELECT statements in the "runSqlStatements" action because the server returns all records selected by each query.
    • It is easy to return too many records, which results in query timeouts or clients receiving too much data.
    • It is much better to use the "getRecordsUsingSQL" action to run SQL queries because it can return a cursor, which is faster and much more efficient at paginating data.
    • If you want to use the "runSqlStatements" to run queries, be sure to add the SQL TOP clause to your SQL statement to limit the number of records returned.
  • The server returns a "data" object in the response that contains one object for each SQL statement in the request.
  • The "errorCode" property for each individual SQL statement indicates the success or failure of the statement during runtime.
    • You must check the "errorCode" property of each individual SQL statement to determine if it succeeded or failed. This is intentional because there are use cases where you expect some SQL statements to fail, such as dropping a table before creating it.
    • Do not assume a zero value in the "errorCode" property for the "runSqlStatements" action means all SQL statements succeeded.
    • The only time the server sets a non-zero value in the "errorCode" property for the "runSqlStatements" action is when it evaluates the request and detects invalid JSON syntax, missing required properties, and invalid property values.
  • Before the server runs SQL statements, it creates a new transaction, then runs all specified SQL statements in that transaction.
    • It can be efficient to run hundreds of statements in one "runSQLStatements" action.
  • By default, the server automatically rolls back the transaction at the end. You must set the "atEnd" and "onError" properties to control how the server commits each SQL statement.
  • You may call a stored procedure as long as it does not have out or "in"/"out" parameters and you pass a constant value to each "in" parameter.
  • A SQL statement may optionally include a semicolon at its end, but it is unnecessary and is ignored.
  • A SQL statement may contain one or more line feed characters. These are encoded in JSON strings as \n.
  • Use "runSQLStatements" action to do things that are not available in other JSON DB API actions.
  • Use parameters in the SELECT and CALL statements to prevent SQL injection attacks.
    • A parameter name in the SQL statement must begin with a colon, immediately followed by the name of the parameter. When defining input and output parameters in JSON, parameter names do not include the colon to be more friendly to programming languages.
  • Use native JSON DB API actions when possible. They run faster than SQL. It is also easier and safer to dynamically generate JSON in your code than to generate SQL code.
    • Use the "getRecordsUsingSQL" action to run queries because it can return a cursor.
    • Use the "createTable" action to create a table because it follows best practices and provides more settings.
    • Use the "alterTable" action to modify a table because it is easier to use.
    • Use the "insertRecords" action to insert large numbers of records because it is more efficient.

 

Request examples

For a list and detailed description of the common properties in an action request message, see JSON Action request.

 

Minimal

Note This example shows automatic rollback on error.

{
  "api": "db",
  "action": "runSQLStatements",
  "params": {
    "sqlStatements": [
      "CREATE TABLE employee (id BIGINT, name VARCHAR(50), job VARCHAR(50) )",
      "CREATE UNIQUE INDEX employee_pk ON employee (id)",
      "INSERT INTO employee \n VALUES (7369, 'John Smith', 'Clerk')"
    ]
  },
  "authToken": "replaceWithAuthTokenFromCreateSession"
}

 

Unconditional rollback and immediate stop on first error

{
  "api": "db",
  "action": "runSQLStatements",
  "params": {
    "onError": "stop",
    "atEnd": "rollback",
    "sqlStatements": [
      "CREATE TABLE employee (id BIGINT, name VARCHAR(50), job VARCHAR(50) )",
      "CREATE UNIQUE INDEX employee_pk ON employee (id)",
      "INSERT INTO employee \n VALUES (7369, 'John Smith', 'Clerk')"
    ]
  },
  "debug": "none",
  "authToken": "replaceWithAuthTokenFromCreateSession"
}

 

Unconditional commit

{
  "requestId": "2",
  "api": "db",
  "action": "runSQLStatements",
  "params": {
    "transactionId": "NO SUPPORT FOR TRANSACTIONS AT THIS TIME",
    "databaseName": "ctreeSQL",
    "ownerName": "admin",
    "onError": "continue",
    "atEnd": "commit",
    "sqlStatements": [
      "DROP TABLE employee;",
      "CREATE TABLE employee (id BIGINT, name VARCHAR(50), job VARCHAR(50) )",
      "CREATE UNIQUE INDEX employee_pk ON employee (id)",
      "INSERT INTO employee VALUES (7369, 'John Smith', 'Clerk');",
      "INSERT INTO employee VALUES (1, 'Emma Smith', 'Boss')",
      "CALL my_stored_proc(:inIntParam1, :outDoubleParam2, :inOutBinaryParam3)",
      "CALL c2f(:input_celsius, :output_fahrenheit) ",
      "SELECT TOP 20 SKIP 0 name \nFROM employee \nWHERE name <= :mySqlNamedParam4",
      "CREATE TABLE employee ( id BIGINT )"
    ],
    "inParams": [
      {
        "name": "inIntParam1",
        "value": 3
      },
      {
        "name": "inoutBinaryParam3",
        "value": "54657374"
      },
      {
        "name": "mySqlNamedParam4",
        "value": "J"
      },
      {
        "name": "input_celsius",
        "value": 22
      }
    ]
  },
  "responseOptions": {
    "binaryFormat": "hex",
    "dataFormat": "objects",
    "numberFormat": "string",
    "includeFields": [],
    "excludeFields": []
  },
  "apiVersion": "1.0",
  "debug": "max",
  "authToken": "replaceWithAuthTokenFromCreateSession"
}

 

Response examples

For a list and detailed description of the common properties in an action response message, see JSON Action response.

 

Minimal

Note This example shows automatic rollback on error.

{
  "result": {
    "reactions": [
      {
        "affectedRows": 0,
        "errorCode": 0,
        "errorMessage": "",
        "outParams": [],
        "rows": {},
        "sql": "CREATE TABLE employee (id BIGINT, name VARCHAR(50), job VARCHAR(50) )"
      },
      {
        "affectedRows": 0,
        "errorCode": 0,
        "errorMessage": "",
        "outParams": [],
        "rows": {},
        "sql": "CREATE UNIQUE INDEX employee_pk ON employee (id)"
      },
      {
        "affectedRows": 1,
        "errorCode": 0,
        "errorMessage": "",
        "outParams": [],
        "rows": {},
        "sql": "INSERT INTO employee \n VALUES (7369, 'John Smith', 'Clerk')"
      }
    ]
  },
  "debugInfo": {
    "request": {
      "api": "db",
      "action": "runSQLStatements",
      "params": {
        "sqlStatements": [
          "CREATE TABLE employee (id BIGINT, name VARCHAR(50), job VARCHAR(50) )",
          "CREATE UNIQUE INDEX employee_pk ON employee (id)",
          "INSERT INTO employee \n VALUES (7369, 'John Smith', 'Clerk')"
        ]
      },
      "debug": "max",
      "authToken": "replaceWithAuthTokenFromCreateSession"
    },
    "serverSuppliedValues": {
      "databaseName": "faircom",
      "ownerName": null
    },
    "errorData": {
      "errorData": null
    },
    "warnings": []
  },
  "errorCode": 0,
  "errorMessage": "",
  "authToken": "replaceWithAuthTokenFromCreateSession"
}

 

Rollback on error

Note This result occurs when the minimal request example is run a second time.

{
  "result": {
    "data": [
      {
        "affectedRows": 0,
        "errorCode": -20041,
        "errorMessage": "Table/View/Synonym employee already exists",
        "sql": "CREATE TABLE employee (id BIGINT, name VARCHAR(50), job VARCHAR(50) )"
      },
      {
        "affectedRows": 0,
        "errorCode": -20028,
        "errorMessage": "Index with the same name employee_pk already exists",
        "sql": "CREATE UNIQUE INDEX employee_pk ON employee (id)"
      },
      {
        "affectedRows": 0,
        "errorCode": -17002,
        "errorMessage": "CT - Key value already exists in index employee_pk",
        "output": {},
        "sql": "INSERT INTO employee \n VALUES (7369, 'John Smith', 'Clerk')"
      }
    ]
  },
  "errorCode": 0,
  "errorMessage": "",
  "authToken": "replaceWithAuthTokenFromCreateSession"
}

 

Unconditional rollback and immediate stop on first error

{
  "result": {
    "reactions": [
      {
        "affectedRows": 0,
        "errorCode": -20041,
        "errorMessage": "Table/View/Synonym employee already exists",
        "outParams": [],
        "rows": {},
        "sql": "CREATE TABLE employee (id BIGINT, name VARCHAR(50), job VARCHAR(50) )"
      }
    ]
  },
  "errorCode": 0,
  "errorMessage": "",
  "authToken": "replaceWithAuthTokenFromCreateSession"
}

 

Unconditional commit

{
  "result": {
    "reactions": [
      {
        "affectedRows": 0,
        "errorCode": -20005,
        "errorMessage": "Table/View/Synonym employee not found",
        "outParams": [],
        "rows": {},
        "sql": "DROP TABLE employee"
      },
      {
        "affectedRows": 0,
        "errorCode": 0,
        "errorMessage": "",
        "outParams": [],
        "rows": {},
        "sql": "CREATE TABLE employee (id BIGINT, name VARCHAR(50), job VARCHAR(50) )"
      },
      {
        "affectedRows": 0,
        "errorCode": 0,
        "errorMessage": "",
        "outParams": [],
        "rows": {},
        "sql": "CREATE UNIQUE INDEX employee_pk ON employee (id)"
      },
      {
        "affectedRows": 1,
        "errorCode": 0,
        "errorMessage": "",
        "outParams": [],
        "rows": {},
        "sql": "INSERT INTO employee VALUES (7369, 'John Smith', 'Clerk')"
      },
      {
        "affectedRows": 1,
        "errorCode": 0,
        "errorMessage": "",
        "outParams": [],
        "rows": {},
        "sql": "INSERT INTO employee VALUES (1, 'Emma Smith', 'Boss')"
      },
      {
        "affectedRows": 0,
        "errorCode": -20122,
        "errorMessage": "procedure my_stored_proc not found",
        "outParams": [],
        "rows": {},
        "sql": "CALL my_stored_proc(:inIntParam1, :outDoubleParam2, :inOutBinaryParam3)"
      },
      {
        "affectedRows": 0,
        "errorCode": -20122,
        "errorMessage": "procedure c2f not found",
        "outParams": [],
        "rows": {},
        "sql": "CALL c2f(:input_celsius, :output_fahrenheit)"
      },
      {
        "affectedRows": 0,
        "errorCode": 0,
        "errorMessage": "",
        "outParams": [],
        "rows": {
          "binaryFormat": "hex",
          "data": [
            {
              "name": "Emma Smith"
            }
          ],
          "dataFormat": "objects",
          "fields": [
            {
              "length": 50,
              "name": "name",
              "type": "varchar"
            }
          ],
          "moreRecords": false,
          "numberFormat": "string",
          "requestedRecordCount": 1,
          "returnedRecordCount": 1
        },
        "sql": "SELECT TOP 20 SKIP 0 name \nFROM employee \nWHERE name <= :mySqlNamedParam4"
      },
      {
        "affectedRows": 0,
        "errorCode": -20041,
        "errorMessage": "Table/View/Synonym employee already exists",
        "outParams": [],
        "rows": {},
        "sql": "CREATE TABLE employee ( id BIGINT )"
      }
    ]
  },
  "requestId": "2",
  "debugInfo": {
    "request": {
      "api": "db",
      "action": "runSQLStatements",
      "params": {
        "databaseName": "ctreeSQL",
        "ownerName": "admin",
        "onError": "continue",
        "atEnd": "commit",
        "sqlStatements": [
          "DROP TABLE employee;",
          "CREATE TABLE employee (id BIGINT, name VARCHAR(50), job VARCHAR(50) )",
          "CREATE UNIQUE INDEX employee_pk ON employee (id)",
          "INSERT INTO employee VALUES (7369, 'John Smith', 'Clerk');",
          "INSERT INTO employee VALUES (1, 'Emma Smith', 'Boss')",
          "CALL my_stored_proc(:inIntParam1, :outDoubleParam2, :inOutBinaryParam3)",
          "CALL c2f(:input_celsius, :output_fahrenheit) ",
          "SELECT TOP 20 SKIP 0 name \nFROM employee \nWHERE name <= :mySqlNamedParam4",
          "CREATE TABLE employee ( id BIGINT )"
        ],
        "inParams": [
          {
            "name": "inIntParam1",
            "value": 3
          },
          {
            "name": "inoutBinaryParam3",
            "value": "54657374"
          },
          {
            "name": "mySqlNamedParam4",
            "value": "J"
          },
          {
            "name": "input_celsius",
            "value": 22
          }
        ]
      },
      "apiVersion": "1.0",
      "requestId": "2",
      "responseOptions": {
        "binaryFormat": "hex",
        "dataFormat": "objects",
        "numberFormat": "string",
        "includeFields": [],
        "excludeFields": []
      },
      "debug": "max",
      "authToken": "replaceWithAuthTokenFromCreateSession"
    },
    "serverSuppliedValues": {
      "databaseName": "ctreeSQL",
      "ownerName": null
    },
    "errorData": {
      "errorData": null
    },
    "warnings": [
      {
        "code": 100,
        "message": "SQL execution produced errors"
      }
    ]
  },
  "errorCode": 0,
  "errorMessage": "",
  "authToken": "replaceWithAuthTokenFromCreateSession"
}

 

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

runSqlStatements "params" property summaries
Property Description Default Type Limits (inclusive)
atEnd (optional) specifies how the action commits or rolls back the statements it runs "rollbackOnError" string
"commit"
"rollbackOnError"
"rollback"
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
inParams (optional) specifies values for input parameters

[]

The is the default when no SQL statements have any named parameters. When one or more SQL statements have named parameters, this is required.

 array of objects  
inParams
.name
 specifies the name of an input parameter Required - No default value string  
inParams
.value
 specifies the value of the parameter Required - No default value
number
string
true
false
null
  
onError (optional) specifies when to stop or continue the execution of SQL statements "continue" string
"stop"
"continue"
ownerName

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

 "" string 1 to 64 bytes
sqlStatements

specifies SQL statements that the server will execute

There is one SQL statement per string

Required - No default value

 array of strings  

 

"atEnd"

The "atEnd" property is an optional string that defines how the action commits or rolls back the statement it runs. It defaults to "rollbackOnError".

  • Possible values:
    • "commit"
      • Setting "atEnd" to "commit" causes the "runSqlStatements" action to always commit the results of all successful SQL statements that you specify.
    • "rollbackOnError"
      • Setting "atEnd" to "rollbackOnError" causes the "runSqlStatements" action to commit the results of all SQL statements as long as all are completed successfully.  If one SQL statement returns an error, the "runSqlStatements" action rolls back all changes.
    • "rollback"
      • Setting "atEnd" to "rollback" causes the "runSqlStatements" action to always roll back the results of all SQL statements. It does not matter if some complete successfully and some fail.
  • There are use cases in which use "onError" and "atEnd" together.
    • When prototyping code.
      • "onError": "continue ", "atEnd": "rollback"
      • Use this sample when you want all statements to run and report errors, but you do not want to commit any changes. This is useful in development when you want to try out a number of SQL statements to check their syntax, performance, and proper execution and you do not yet want to commit changes because you are still developing the code. It is convenient since it eliminates the need to drop newly created objects and delete newly inserted records.
    • When developing code.
      • "onError": "continue", "atEnd": "rollbackOnError"
      • Use this sample when you want all statements to run so you can see and troubleshoot all errors and you want to commit all changes, but only when all SQL statements run successfully. This is the default setting because it is good for development and is still safe when these settings are accidentally deployed to production.
    • When running in production and test environments.
      • "onError": "stop", "atEnd": "rollbackOnError"
      • Use this sample when you want to immediately stop running SQL statements and rollback all changes when there is an error, but you want to commit all changes when all the SQL statements run successfully. This is useful in production because you want to commit a set of successfully executed SQL statements, but when a failure occurs, you want the server to immediately stop running the remaining SQL statements and roll back all changes. Immediately stopping execution and rolling back changes prevents server resources from being consumed unnecessarily.
    • When deploying database changes.
      • "onError": "continue", "atEnd": "commit"
      • Use this sample when you want to unconditionally commit all SQL statements even when an error occurs on one or more statements. This is useful for deploying database changes because it is common to ignore errors during a deployment process — for example, the DROP table command returns an error when dropping a table that does not exist. This error does not prevent a subsequent CREATE table from running successfully.

 

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

 

"inParams"

The "inParams" property is both an optional and required array of objects that specifies the values of input parameters. When optional it defaults to an empty array.

  • It must include one value for each input parameter in each SQL statement in the "sqlStatements" property.
  • It is optional when no SQL statements have named parameters.
    • A named parameter can be in SELECT and CALL statements.
  • It is required when one or more SQL statements have named parameters.

 

Example

{
  "params": {
    "inParams": [
      {
        "name": "inIntParam1",
        "value": 3
      },
      {
        "name": "inoutBinaryParam3",
        "value": "54657374"
      },
      {
        "name": "mySqlNamedParam4",
        "value": "Ray"
      }
    ]
  }
}

 

"name"

The "name" property is a required string that specifies the name of an input parameter.

  • The "name" property must be unique across all parameters in all SQL statements in the "sqlStatements" property.
  • Inside SQL, the parameter name starts with a colon, but in JSON the parameter name does not — for example, in JSON a parameter named "param1" is named ":param1" in SQL, such as "CALL my_proc(:param1)".

 

"value"

The "value" property can be a number, string, true, false, null, object, or array and is required.

  • Before the server runs a SQL statement, it replaces the parameter name in a SQL statement with this value.
  • The server converts the value into the type expected by the SQL statement.

 

"onError"

The "onError" property is an optional string that determines when to stop or continue the execution of all SQL statements. It defaults to "continue".

  • Possible values:
    • "stop"
      • Setting "onError" to "stop" causes the "runSqlStatements" action to stop executing SQL statements when it encounters an error. Stopping "runSqlStatements" on an error is useful when running subsequent steps that would cause problems or would consume server resources unnecessarily.
    • "continue"
      • Setting "onError" to "continue" causes the "runSqlStatements" action to continue executing SQL statements when it encounters an error. Continuing "runSqlStatements" on even when an error occurs is useful when you want to verify the viability of each specified statement, such as ensuring it uses the correct syntax and executes properly.
  • There are use cases in which use "onError" and "atEnd" together.
    • When prototyping code.
      • "onError": "continue ", "atEnd": "rollback" 
      • Use this sample when you want all statements to run and report errors, but you do not want to commit any changes. This is useful in development when you want to try out a number of SQL statements to check their syntax, performance, and proper execution and you do not yet want to commit changes because you are still developing the code. It is convenient since it eliminates the need to drop newly created objects and delete newly inserted records.
    • When developing code.
      • "onError": "continue", "atEnd": "rollbackOnError"
      • Use this sample when you want all statements to run so you can see and troubleshoot all errors and you want to commit all changes, but only when all SQL statements run successfully. This is the default setting because it is good for development and is still safe when these settings are accidentally deployed to production.
    • When running in production and test environments.
      • "onError": "stop", "atEnd": "rollbackOnError"
      • Use this sample when you want to immediately stop running SQL statements and rollback all changes when there is an error, but you want to commit all changes when all the SQL statements run successfully. This is useful in production because you want to commit a set of successfully executed SQL statements, but when a failure occurs, you want the server to immediately stop running the remaining SQL statements and roll back all changes. Immediately stopping execution and rolling back changes prevents server resources from being consumed unnecessarily.
    • When deploying database changes.
      • "onError": "continue", "atEnd": "commit"
      • Use this sample when you want to unconditionally commit all SQL statements even when an error occurs on one or more statements. This is useful for deploying database changes because it is common to ignore errors during a deployment process — for example, the DROP table command returns an error when dropping a table that does not exist. This error does not prevent a subsequent CREATE table from running successfully.

 

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

 

"result"

This section covers the unique properties of the "result" property.

runSqlStatements "result" properties summary
Property Description Type Limits (inclusive)
data

contains objects that the server returns

It is an empty array when there are no results available

 array of objects The action determines its contents.
data
.affectedRows
 specifies the number of records that were affected by the SQL statement integer  
data
.errorCode
 indicates an error when set to a non-zero integer or success when 0 integer -2147483648 to 2147483647
data
.errorMessage
 displays a human-readable error message string 0 to 256 bytes
data
.output
 specifies the results returned by a stored procedure or SELECT statement object  
data
.output
.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
data
.output
.dataFormat
 specifies the format of the data in the "data" property string
"arrays"
"autoDetect"
"objects"
data
.output
.fields
 specifies the settings of a field in a table array  
data
.sql
 specifies the original SQL statement that was executed string  
reactions contains the data returned by a SQL SELECT statement array of objects  
reactions
.affectedRows
 specifies the number of records that were affected by the SQL statement. 0 indicates no records were affected integer  
reactions
.elapsedMilliseconds
 specifies the number of milliseconds it took for the server to execute the SQL statement integer  
reactions
.errorCode
 indicates an error when set to a non-zero integer or success when 0 integer -2147483648 to 2147483647
reactions
.errorMessage
 displays a human-readable error message string 0 to 256 bytes
reactions
.outParams
 specifies the output values of a stored procedure array of objects  
reactions
.outParams
.binaryFormat
 (optional) specifies how binary values are returned. string One of the following: "base64", "hex", or "byteArray".
reactions
.outParams
.length
 specifies the maximum possible length of the output parameter. It is included when the data type of the output parameter is "char", "varchar", "lvarchar", "binary", "varbinary", "lvarbinary", or "json"
integer
string
  
reactions
.outParams
.name
 specifies the name of the output parameter string  
reactions
.outParams
.scale
 specifies the scale of the output parameter. It is included when the data type of the output parameter is "number" integer  
reactions
.outParams
.type
 specifies the JSON DB data type of the output parameter. This is useful when the value is a string containing another data type string  
reactions
.outParams
.value
 specifies the value of the output parameter
array
false
null
number
object
string
true
  
reactions
.rows
 specifies the rows returned by a stored procedure of a SELECT statement and metadata about the results object  
reactions
.startTimestamp
 Specifies the time at which the query was initiated    
reactions
.sql
 indicates the SQL statement that is in the request to help associate each SQL statement in the request with the response. The order of the SQL statement in the request match the order in the response array of objects  

 

"binaryFormat"

The "binaryFormat" property designates the formatof binary values embedded in JSON strings. The default value for "binaryFormat" is the "defaultBinaryFormat" defined in the "createSession" or "alterSession" actions. If it is omitted there, it defaults to the value of the "defaultBinaryFormat" property in the <faircom>/config/services.json file. If it is not there, the FairCom server defaults it to "hex". Prior to version 13.0.4, the server defaulted it to "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", "result", and "mapOfPropertiesToFields".
    • It occurs in "params" when actions create or change values.
    • It occurs in "responseOptions" when actions return values.
  • 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.
  • When "binaryFormat" occurs in "mapOfPropertiesToFields", it tells the server how to encode or decode the binary value in a JSON property.
    • For example, including "binaryFormat" in a "tableFieldsToJson" transform step controls how the server takes a raw binary field value and encodes it as a JSON property.
    • For example, including "binaryFormat" in a "jsonToTableFields" or "jsonToDifferentTableFields" transform step controls how the server decodes a binary value in a JSON property so it can store the raw binary value in a field.
  • 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 characters 0 - 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 - 9 and 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 ]

 

Example requests

 
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": "replaceWithAuthTokenFromCreateSession"
}

 

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": "replaceWithAuthTokenFromCreateSession"
}

 

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": "replaceWithAuthTokenFromCreateSession"
}

 

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": "replaceWithAuthTokenFromCreateSession"
}

 

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": "replaceWithAuthTokenFromCreateSession"
}

 

Response examples

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

{
  "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": "",
  "authToken": "replaceWithAuthTokenFromCreateSession"
}

 

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": "replaceWithAuthTokenFromCreateSession"
}

 

Response
{
  "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": "",
  "authToken": "replaceWithAuthTokenFromCreateSession"
}

 

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": "replaceWithAuthTokenFromCreateSession"
}

 

Response
{
  "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": "",
  "authToken": "replaceWithAuthTokenFromCreateSession"
}

 

"data"

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

 

"output"

The "output" object contains the results returned by a stored procedure or SELECT statement. In both cases, it contains the same information as returned in the "result" property of the "getRecordsUsingSQL" action. When there is no output, this property is set to the empty object.

Example
 "output": {
   "data": [],
   "dataFormat": "objects",
   "fields": []

 

"data"

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

 

Example

arrays
"data":
  [
   ["test1", ".\\test1.dbs\\SQL_SYS", 1003]
  ]
objects
"data":
[
  {
    "databaseName": "test7",
    "path": ".\\test7.dbs\\SQL_SYS",
    "uid": 1015
  }
]

 

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

 

"reactions"

The "reactions" property is an array of objects that contain the data returned by a SQL SELECT statement.

 

Example

"result": {
  "reactions": [
    {
      "sql": "",
      "affectedRows": 0,
      "errorCode": 0,
      "errorMessage": "",
      "outParams": [],
      "rows": {},
      "startTimestamp": "",
      "elapsedMilliseconds": 0
    }
  ]
}

 

"rows"

The "rows" property is an object containing the rows that are returned by the stored procedure or the SELECT statement and metadata about the results.

  • When there is no output, this property is set to the empty object.
  • When it does contain data it will contain the same information returned in the "result" property of the "getRecordsUsingSQL" action.
Example
"result": {
  "reactions": [
    {
      "rows": {
        "binaryFormat": "hex",
        "data": [ { "name": "Emma Smith" } ],
        "dataFormat": "objects",
        "fields": [ { "name": "name", "type": "varchar", "length": 50 } ],
        "outParams": [],
        "numberFormat": "string",
        "requestedRecordCount": 1,
        "returnedRecordCount": 1
      }
    }
  ]
}

 

"outParams"

The "outParams" property is an array of objects that contains the output values of a stored procedure. When a stored procedure has no output parameters "outParams" is an empty array.

Note The "binaryFormat" and "numberFormat" properties specified in the "runSQLStatements" action control the format of the data assigned to "value". These properties are included in the "data" property to notify your code how binary values and numbers are formatted.

Example
"result": {
  "reactions": [
    {
      "outParams": [
        {
          "name": "outDoubleParam2",
          "value": "0.00154",
          "type": "number",
          "length": 32,
          "scale": 5
        },
        {
          "name": "inOutBinaryParam3",
          "value": "2A2A2A2A",
          "type": "varbinary",
          "length": "65500",
          "binaryFormat": "hex"
        }
      ]
    }
  ]
}

 

"name"

The "name" property is a required string that specifies the name of an input parameter.

  • The "name" property must be unique across all parameters in all SQL statements in the "sqlStatements" property.
  • Inside SQL, the parameter name starts with a colon, but in JSON the parameter name does not — for example, in JSON a parameter named "param1" is named ":param1" in SQL, such as "CALL my_proc(:param1)".

 

"value"

The "value" property can be a number, string, true, false, null, object, or array and is required.

  • Before the server runs a SQL statement, it replaces the parameter name in a SQL statement with this value.
  • The server converts the value into the type expected by the SQL statement.

Was this article helpful?

Give feedback about this article

Related Articles