ExecuteStatement

Updated at:

Executes an SQL statement on a specified database by using OpenAPI and returns the result.

Operation description

This API is used to securely execute SQL statements on a Hologres instance by using OpenAPI.

Before you begin, meet the following conditions:

  1. On the instance details page in the console, turn on the Allow SQL Execution by Using OpenAPI switch in the Data Security section.

  2. The RAM user used to invoke the API has been granted the hologram:ExecuteStatement permission.

Statements such as SELECT, DDL, and DML are supported. The parameterized query feature with placeholders such as $1 and $2 is supported to prevent SQL injection. By default, a query returns a maximum of 200 rows (up to 1,000 rows) and 10 MB of data. Excess data is truncated, and the truncated field indicates the truncation. The timeout period for a single execution is 30 seconds.

Try it now

Try this API in OpenAPI Explorer, no manual signing needed. Successful calls auto-generate SDK code matching your parameters. Download it with built-in credential security for local usage.

Test

RAM authorization

The table below describes the authorization required to call this API. You can define it in a Resource Access Management (RAM) policy. The table's columns are detailed below:

  • Action: The actions can be used in the Action element of RAM permission policy statements to grant permissions to perform the operation.

  • API: The API that you can call to perform the action.

  • Access level: The predefined level of access granted for each API. Valid values: create, list, get, update, and delete.

  • Resource type: The type of the resource that supports authorization to perform the action. It indicates if the action supports resource-level permission. The specified resource must be compatible with the action. Otherwise, the policy will be ineffective.

    • For APIs with resource-level permissions, required resource types are marked with an asterisk (*). Specify the corresponding Alibaba Cloud Resource Name (ARN) in the Resource element of the policy.

    • For APIs without resource-level permissions, it is shown as All Resources. Use an asterisk (*) in the Resource element of the policy.

  • Condition key: The condition keys defined by the service. The key allows for granular control, applying to either actions alone or actions associated with specific resources. In addition to service-specific condition keys, Alibaba Cloud provides a set of common condition keys applicable across all RAM-supported services.

  • Dependent action: The dependent actions required to run the action. To complete the action, the RAM user or the RAM role must have the permissions to perform all dependent actions.

Action

Access level

Resource type

Condition key

Dependent action

hologram:ExecuteStatement

none

*Instance

acs:hologram:{#regionId}:{#accountId}:instance/{#InstanceId}

None None

Request syntax

POST /api/v1/instances/{instanceId}/executeStatement HTTP/1.1

Path Parameters

Parameter

Type

Required

Description

Example

instanceId

string

No

The instance ID.

hgprecn-cn-i7m2ucpyu005

Request parameters

Parameter

Type

Required

Description

Example

body

object

No

The request body.

dbName

string

No

The database.

test_db

sql

string

No

The SQL statement to be executed. The maximum length is 16,384 characters. Multiple SQL statements separated by semicolons are supported. If multiple statements are specified, the result of the last statement is returned.

select * from test_table limit 10;

parameters

array

No

The list of parameters to attach to the SQL parameterized query, which correspond in sequence to placeholders such as $1 and $2 in the SQL statement. Supported value types include String, Long, Double, Boolean, and null, which represents NULL. Using a parameterized query effectively prevents SQL injection and improves the cache reuse rate of precompiled statements. The parameter list applies to each SQL statement in Statements.

any

No

The content of the parameter set.

test_val

maxRows

integer

No

The maximum number of rows to return. Default value: 200. Maximum value: 1000. Excess rows are truncated and indicated by the Truncated field in the response.

300

maxBytes

integer

No

The maximum amount of data to return in a single request, in bytes. Default value: 10485760 (10 MB). Excess data is truncated and indicated by setting truncated to true.

1024

queryTimeout

integer

No

The timeout period for SQL execution, in seconds. Default value: 30. Maximum value: 30. Minimum value: 1. If a timeout occurs, the server actively cancels the query.

5

Response elements

Element

Type

Description

Example

object

The result data.

requestId

string

Id of the request

819A7F0F-2951-540F-BD94-6A41ECF0281F

success

string

Indicates whether the request is successful.

True

errorCode

string

The error code. This field is empty if the request is successful.

InvalidParameterValue

errorMessage

string

The error message. This field is empty if the request is successful.

Invalid parameter value, such as an empty or excessively long SQL statement

httpStatusCode

string

The HTTP status code.

200

data

object

Specifies whether OpenAPI SQL execution is allowed. Valid values: true: Allowed. false: Not allowed.

success

boolean

Indicates whether the request is successful.

errorCode

string

The error code. This field is empty if the request is successful.

InvalidParameterValue

errorMessage

string

The error message. This field is empty if the request is successful.

Invalid parameter value, such as an empty or excessively long SQL statement

results

array<object>

The list of execution results. The length is currently fixed to 1. If multiple SELECT queries are executed, only the result of the last SELECT query is returned.

array<object>

The result object.

success

boolean

Indicates whether the request is successful.

True

sql

string

The SQL statement that is actually executed.

select * from test_table limit 10;

count

integer

The number of rows returned.

25

updateCount

integer

The number of rows affected by the INSERT, UPDATE, or DELETE statement.

10

truncated

boolean

Indicates whether the result is truncated. The value is true if the result exceeds MaxResults.

queryId

string

Query ID

E3F4B2A7-1234-5678-9ABC-DEF012345678

errorMessage

string

The error message for the SQL statement.

ERROR: relation \"non_existent_table\" does not exist\n Position: 15

errorCode

string

The error code for the SQL statement.

SQL_ERROR

columnMetadata

array<object>

The column metadata.

object

name

string

The column name.

id

type

string

The data type, such as int4, text, or bool.

int4

nullable

boolean

Indicates whether the column is nullable.

records

array

The set of query result rows. Each row is an array of strings. All values are serialized as strings, and NULL values are represented as \N.

array

The result list for each row.

string

The field value in string format.

["1", "Alice"]

Examples

Success response

JSON format

{
  "requestId": "819A7F0F-2951-540F-BD94-6A41ECF0281F",
  "success": "True",
  "errorCode": "InvalidParameterValue",
  "errorMessage": "Invalid parameter value, such as an empty or excessively long SQL statement",
  "httpStatusCode": "200",
  "data": {
    "success": false,
    "errorCode": "InvalidParameterValue",
    "errorMessage": "Invalid parameter value, such as an empty or excessively long SQL statement",
    "results": [
      {
        "success": true,
        "sql": "select * from test_table limit 10;",
        "count": 25,
        "updateCount": 10,
        "truncated": false,
        "queryId": "E3F4B2A7-1234-5678-9ABC-DEF012345678",
        "errorMessage": "ERROR: relation \\\"non_existent_table\\\" does not exist\\n  Position: 15",
        "errorCode": "SQL_ERROR",
        "columnMetadata": [
          {
            "name": "id",
            "type": "int4",
            "nullable": false
          }
        ],
        "records": [
          [
            " [\"1\", \"Alice\"]"
          ]
        ]
      }
    ]
  }
}

Error codes

HTTP status code

Error code

Error message

Description

403 NoPermission RAM user permission is insufficient, please grant AliyunHologresReadOnlyAccess permission.

See Error Codes for a complete list.

Release notes

See Release Notes for a complete list.