exec

Warning

SingleStore 9.0 gives you the opportunity to preview, evaluate, and provide feedback on new and upcoming features prior to their general availability. In the interim, SingleStore 8.9 is recommended for production workloads, which can later be upgraded to SingleStore 9.0.

Endpoint URL

/api/v2/exec

HTTP Method

POST

Description

Executes a SQL statement without returning result sets; typically used for executing DDL and DML statements for which result sets are not expected, such as CREATE TABLE and INSERT statements.

Request Headers

Header	Description
Authorization	Specifies the authentication information of the user agent. For more information, see Data API Authentication.
Content-Type	Represents the media type (MIME) of the body of the request and must be set to `application/json`.

Request Body

The request body is a JSON object that specifies the SQL statement to execute and the statement context. The request body size must not exceed 1MB.

Field	Type	Description
sql	String	The SQL statement to execute.
args	Array	(Optional) A list of arguments to be used in the SQL statement. The command uses question marks (?) for placeholders, which will be replaced by the specified arguments during execution. The command must have exactly as many placeholders as arguments, or the request will fail. Example: JSON {"sql": "INSERT INTO tbl (col1, col2) VALUES (?, ?)", "args": ["Hello", 2]}
args[x]	Any	(Optional) An argument to be used in the command. This argument will be parsed into a Golang type based on the default JSON parsing rules and then passed to the go sql driver for safe interpolation.
database	String	(Optional) The database on which the SQL statement must be executed.

Response

For successful requests, the response will be a JSON object in the message body along with the HTTP status code 200 OK.

Field	Type	Description
lastInsertId	Integer	The last value inserted into an `AUTO_INCREMENT` column if applicable to the SQL statement.
rowsAffected	Integer	The number of rows affected by the SQL statement.

On request failure, an HTTP status code corresponding to the error will be returned. See Data API Error Handling for more information.

Examples (Shell)

Create a database

Request Data

JSON

{"sql": "CREATE DATABASE publications"}

HTTP Request

Shell

curl -H "Content-Type: application/json" --data '{"sql": "CREATE DATABASE publications"}' https://root:password@localhost:8080/api/v2/exec

HTTP Response

JSON

{
  "lastInsertId": 0,
  "rowsAffected": 1
}

Create a table

Request Data

JSON

{"sql": "CREATE TABLE authors(author_id INT AUTO_INCREMENT PRIMARY KEY, author_name VARCHAR(50) NOT NULL, bio VARCHAR(255))", "database": "publications"}

HTTP Request

Shell

curl -H "Content-Type: application/json" --data '{"sql": "CREATE TABLE authors(author_id INT AUTO_INCREMENT PRIMARY KEY, author_name VARCHAR(50) NOT NULL, bio VARCHAR(255))", "database": "publications"}' https://root:password@localhost:8080/api/v2/exec

HTTP Response

JSON

{
  "lastInsertId": 0,
  "rowsAffected": 0
}

Insert values into a table

Request Data

JSON

{"sql": "INSERT INTO authors (author_name) VALUES (?), (?), (?)", "args": ["Kegan Roslyn", "Toby Lynsey", "Lacey Desmond"], "database": "publications"}

HTTP Request

Shell

curl -H "Content-Type: application/json" --data '{"sql": "INSERT INTO authors (author_name) VALUES (?), (?), (?)", "args": ["Kegan Roslyn", "Toby Lynsey", "Lacey Desmond"], "database": "publications"}' https://root:password@localhost:8080/api/v2/exec

HTTP Response

JSON

{
  "lastInsertId": 1,
  "rowsAffected": 3
}

Call a stored procedure

Request Data

JSON

{"sql": "CALL sample_procedure(?)", "args": ["Crime Thriller"], "database": "publications"}

HTTP Request

Shell

curl -H "Content-Type: application/json" --data '{"sql": "CALL sample_procedure(?)", "args": ["Crime Thriller"], "database": "publications"}' https://root:password@localhost:8080/api/v2/exec

HTTP Response

JSON

{
  "lastInsertId": 0,
  "rowsAffected": 0
}

Set an engine variable

Request Data

JSON

{"sql": "SET GLOBAL data_conversion_compatibility_level = '8.0'"}

HTTP Request

Shell

curl -H "Content-Type: application/json" --data '{"sql": "SET GLOBAL data_conversion_compatibility_level = '\''8.0'\''"}' https://root:pa55w0rd@localhost:8080/api/v2/exec

HTTP Response

JSON

{
  "lastInsertId": 0,
  "rowsAffected": 0
}

Supplementary Reference

When using cURL to invoke SingleStore’s Data API, you can issue the JSON payload of the HTTP POST request from a file instead of providing it directly in the request, as shown below.

Create a table by issuing HTTP POST request’s JSON payload from a file

Request data saved in a JSON file

JSON

{
    "sql": "CREATE TABLE reviews(book_title VARCHAR(255) NOT NULL, author_name VARCHAR(50), review JSON NOT NULL)", 
    "database": "publications"
}

HTTP request with the JSON payload issued from the JSON file

Shell

#/* The JSON payload for the HTTP request is supplied from the exec.jsonfile

curl -H "Content-Type: application/json" --data '@./exec.json' https://root:password@localhost:8080/api/v2/exec

HTTP response

JSON

{
  "lastInsertId": 0,
  "rowsAffected": 0
}

exec

On this page

Request Headers

Request Body

Response

Examples (Shell)

Create a database

Request Data

HTTP Request

HTTP Response

Create a table

Request Data

HTTP Request

HTTP Response

Insert values into a table

Request Data

HTTP Request

HTTP Response

Call a stored procedure

Request Data

HTTP Request

HTTP Response

Set an engine variable

Request Data

HTTP Request

HTTP Response

Supplementary Reference

Create a table by issuing HTTP POST request’s JSON payload from a file

Request data saved in a JSON file

HTTP request with the JSON payload issued from the JSON file

HTTP response

Was this article helpful?

On this page

Was this article helpful?