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.

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

Minimal

Note This example shows automatic rollback on error.

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

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

 

Request properties ("params")

Property Description Default Type Limits (inclusive)

transactionId

 The "transactionId" property identifies a transaction in which the specified action will be included. Optional with default of "" string 0 to 255 bytes

databaseName

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

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

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

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

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

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

ownerName

The "ownerName" property specifies the account that owns an object, such as a table or code package. See "createSession" and the "defaultOwnerName" property for more details. 

You specify this property when you want to use a different account instead of the default. Your session's account must have the appropriate privileges to access the code package. 

This property is useful because objects, such as tables and code packages, can have the same name in the same database as long as different accounts own each object. This feature allows you to create duplicate objects for different users on the same server and reuse the same JSON actions on those objects. For example, an administrator can copy objects from a production environment to her account so she can troubleshoot an issue using the same JSON actions, JavaScript, and SQL code.

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

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

 Optional with default of the session's "defaultOwnerName" property string 1 to 64 bytes

onError

The "onError" property determines when to stop or continue the execution of all SQL statements. For more details, see "onError".

 Optional with default of "continue" string

"stop"

"continue"

atEnd

 The "atEnd" property defines how the action commits or rolls back the statement it runs. For more details, see “atEnd”.  Optional with default of "rollbackOnError" string

"commit"

"rollbackOnError"

"rollback"

sqlStatements

The "sqlStatements" property specifies SQL statements that the server will execute.

There is one SQL statement per string.

Required - No default value

 array of strings 1 or more sql statements

inParams

The "inParams" property specifies the values of input parameters. 

  • 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.
{
  "params": {
    "inParams": [
      {
        "name": "inIntParam1",
        "value": 3
      },
      {
        "name": "inoutBinaryParam3",
        "value": "54657374"
      },
      {
        "name": "mySqlNamedParam4",
        "value": "Ray"
      }
    ]
  }
}

[]

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

"name"

"value"

inParams

.name

The "name" property is the name of a label or field. 

The "group" and "name" properties combined uniquely identify each label. 

The "id" property also uniquely identifies each label, so you can rename a label's group and name without breaking "id" references to the label.

 

 Required - No default value string 1 to 64 bytes

inParams

.value

The "value" property is used by the server to compare the value assigned to "value" to the appropriate field data in records.

 

In Key-Value actions, the required "value" property contains a JSON value, which may be up to 2 gigabytes in length. It can be any JSON value, such as an object, array, string, number, true, false, or null.

 Required - No default value string

"string"

"integer"

"number"

"boolean"

"null"

 

Response properties ("result")

Property Description Type Limits (inclusive)

reactions

 The "reactions" property contains the data returned by a SQL SELECT statement. array of objects  

reactions

.affectedRows

 The "affectedRows" property specifies the number of records that were affected by the SQL statement. integer number of records affected by the statement.

reactions

.errorCode

 The "errorCode" property indicates an error when set to a non-zero integer or success when 0. integer -2147483648 to 2147483647

reactions

.errorMessage

 The "errorMessage" property displays a human-readable error message. string 0 to 256 bytes

reactions

.outParams

 The "outParams" property specifies the output values of a stored procedure. array of objects

"binaryFormat"

"length"

"name"

"scale"

"type"

"value"

reactions

.rows

 The "rows" property specifies the rows returned by a stored procedure of a SELECT statement and metadata about the results. object The rows returned by a stored procedure.

reactions

.rows

.binaryFormat

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

reactions

.rows

.data

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

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

reactions

.rows

.data

.name

The "name" property is the name of a label or field. 

The "group" and "name" properties combined uniquely identify each label. 

The "id" property also uniquely identifies each label so you can rename a label's group and name without breaking "id" references to the label.

 

 string 1 to 64 bytes

reactions

.rows

.dataformat

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

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

"autoDetect"

"arrays"

"objects"

reactions

.rows

.fields

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

 

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

"autoTimestamp"

"autoValue"

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

"primaryKey"

“autoValue”

reactions

.rows

.fields

.length

 Identifies the length of the field. integer 1 to 65500 

reactions

.rows

.fields

.name

The "name" property is the name of a label or field. 

The "group" and "name" properties combined uniquely identify each label. 

The "id" property also uniquely identifies each label so you can rename a label's group and name without breaking "id" references to the label.

 

 string 1 to 64 bytes

reactions

.rows

.fields

.type

 Identifies the type of the field. See Data types. string

"bit"

"tinyint"

"smallint"

"integer"

"bigint"

"real"

"float"

"number"

"money"

"date"

"time"

"timestamp"

"char"

"varchar"

"lvarchar"

"binary"

"varbinary"

"lvarbinary"

"json"

reactions

.numberFormat

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

 

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

"number"

"string"

reactions

.requestedRecordCount

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

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

0 to 2147483647

reactions

.returnedRecordCount

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

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

0 to 2147483647

sql

 The "sql" property specifies the original SQL statement that was executed. string The original SQL statement

Was this article helpful?

Give feedback about this article

Related Articles