ExecuteStatement
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:
On the instance details page in the console, turn on the Allow SQL Execution by Using OpenAPI switch in the Data Security section.
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
Test
RAM authorization
|
Action |
Access level |
Resource type |
Condition key |
Dependent action |
|
hologram:ExecuteStatement |
none |
*Instance
|
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.