Skip to main content

"runSqlStatements"

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

Things to know

  • 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 success or failure.

    • A zero value in the "errorCode" property for the "runSqlStatements" action only means the request has no problems, such as incorrect JSON syntax, missing required properties, or invalid property values.

    • It is important to not assume a zero value in the "errorCode" property for the "runSqlStatements" action means all SQL statements succeeded.

    • You must check the "errorCode" property for 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.

  • Before the server runs SQL statements, it creates a new transaction, then runs all specified SQL statements in that transaction.

    Tip

    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.

    Note

    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 request example

Note

This example shows automatic rollback on error.

{
  "api": "db",
  "authToken": "replaceWithAuthtokenFromCreateSession",
  "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')"
    ]
  }
}
{
  "api": "db",
  "authToken": "replaceWithAuthtokenFromCreateSession",
  "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"
}
{
  "requestId": "2",
  "authToken": "replaceWithAuthtokenFromCreateSession",
  "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"
}

Response examples

Note

This example shows automatic rollback on error.

{
  "authToken": "authToken",
  "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": {
      "authToken": "authToken",
      "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"
    },
    "serverSuppliedValues": {
      "databaseName": "faircom",
      "ownerName": null
    },
    "errorData": {
      "errorData": null
    },
    "warnings": []
  },
  "errorCode": 0,
  "errorMessage": ""
}

Note

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

{
  "authToken": "authToken",
  "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": "authToken",
  "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": "authToken",
  "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": {
      "authToken": "authToken",
      "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"
    },
    "serverSuppliedValues": {
      "databaseName": "ctreeSQL",
      "ownerName": null
    },
    "errorData": {
      "errorData": null
    },
    "warnings": [
      {
        "code": 100,
        "message": "SQL execution produced errors"
      }
    ]
  },
  "errorCode": 0,
  "errorMessage": ""
}

"params"

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

Property summary

Table 1. "params" property summaries

Property

Description

Default

Type

Limits (inclusive)

"atEnd"

defines how the action commits or rolls back the statements it runs

"rollbackOnError"

string

"commit"
"rollbackOnError"
"rollback"

"databaseName"

contains the name of a database

Defaults to the "defaultDatabaseName" value that is set during "createSession"

string

Minimum length: 1
Maximum length: 64

"inParams"

specifies values for input parameters

[]

Note

This 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

"onError"

determines when to stop or continue the execution of SQL statements

"continue"

string

"stop"
"continue"

"ownerName"

contains the unique name of a schema in a database

""

string

Minimum length: 1
Maximum length: 64

"sqlStatements"

contains SQL statements that the server will execute

Note

There is one SQL statement per string.

Required - No default value

array of strings



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

Things to know:
  • 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.

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.

Things to know:
  • A zero-length "databaseName" is invalid.

  • Its limits are 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", "defaultDatabaseName" will be set to the "defaultDatabaseName" value that is specified in the services.json file.

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.

Things to know:
  • 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.

    Note

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

Property summary

Table 2. "inParams" property summaries

Property

Description

Default

Type

Limits (inclusive)

"name"

specfies the name of an input parameter

Required - No default value

string

"value"

specifies the value of the parameter

Required - No default value

number
string
true
false
null



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

Things to know:
  • 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.

The "ownerName" property is an optional string from 1 to 64 bytes that specifies the account that owns an object.

Things to know:
  • The "ownerName" property is optional and has a dynamic default value.

  • If the "ownerName" property is omitted or set to null, the server uses the value of the "defaultOwnerName" property supplied during the "createSession" action.

  • If the "defaultOwnerName" property is not defined, the server uses the "admin" as the owner name.

  • The owner of an object has administrative rights over that object.

  • The "ownerName" property is a namespace for an object. You can think of it as a container of objects.

    The "ownerName" allows users to use any name for the objects they create — for example, a QA engineer may copy tables into their owner space to run a set of tests.

    It is common for a user to create their own copies of objects from other accounts for testing, troubleshooting, and fixing data. The copied objects can retain the same name because the "ownerName" distinguishes between them.

  • The fully qualified name of an object is the "databaseName", "ownerName", and the object's name, such as "tableName" meaning a FairCom server may contain many tables with the name "mytable" as long as each one is in a different database or in a different owner space.

    For example, an organization often creates different databases for different phases of the development lifecycle, such as dev, test, stage, ua, and prod. Each of these databases contains the same set of objects with the same names. Applications leave the "databaseName" out of their JSON actions and use the "defaultDatabaseName" property to specify the target database.

  • Queries and scripts are often written without specifying "databaseName" and/or "ownerName", allowing queries and scripts to work properly when run in different databases or in different schemas.

"result"

Property summary

Table 3. "result" property summaries

Property

Description

Type

Limits (inclusive)

"data"

contains objects that the server returns

Note

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

array of objects

Its contents are determined by the action

"reactions"

contains the data returned by a SQL SELECT statement

array of objects



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

Property summary

Table 4. "data" property summaries

Property

Definition

Type

Limits (inclusive)

"affectedRows"

contains the number of records that were affected by the SQL statement

integer

"errorCode"

indicates an error when set to a non-zero integer or success when 0

integer

-2,147,483,648 - 2,147,483,647

"errorMessage"

contains a human-readable error message

string

Minimum length: 0 bytes
Maximum length: 256 bytes

"output"

contains the results returned by a stored procedure or SELECT statement

object

"sql"

contains the original SQL statement that was executed

string



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

Property summary

Table 6. "reactions" property summaries

Property

Description

Type

Limits (inclusive)

"affectedRows"

contains the number of records that were affected by the SQL statement

Note

0 indicate no records were affected.

integer

"elapsedMilliseconds"

contains the number of milliseconds it took for the server to execute the SQL statement

integer

"errorCode"

indicates an error when set to a non-zero integer or success when 0

integer

-2147483648 - 2147483647

"errorMessage"

contains a human-readable error message

"outParams"

contains the output values of a stored procedure

array of objects

"rows"

contains the rows returned by a stored procedure or a SELECT statement and metadata about the results

object

"startTimestamp"

"sql"

contains the SQL statement that is in the request to help associate each SQL statement in the request with the response

Note

The order of the SQL statments in the request match the order in the response.

array of objects