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.
On this page
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. |
Content-Type |
Represents the media type (MIME) of the body of the request and must be set to |
Request Body
The request body is a JSON object that specifies the SQL statement to execute and the statement context.
Field |
Type |
Description |
---|---|---|
sql |
String |
The SQL statement to execute. |
args |
Array |
(Optional) A list of arguments to be used in the SQL statement.
|
args[x] |
Any |
(Optional) An argument to be used in the command. |
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 |
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.
Examples (Shell)
Create a database
Request Data
{"sql": "CREATE DATABASE publications"}
HTTP Request
curl -H "Content-Type: application/json" --data '{"sql": "CREATE DATABASE publications"}' https://root:password@localhost:8080/api/v2/exec
HTTP Response
{"lastInsertId": 0,"rowsAffected": 1}
Create a table
Request Data
{"sql": "CREATE TABLE authors(author_id INT AUTO_INCREMENT PRIMARY KEY, author_name VARCHAR(50) NOT NULL, bio VARCHAR(255))", "database": "publications"}
HTTP Request
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
{"lastInsertId": 0,"rowsAffected": 0}
Insert values into a table
Request Data
{"sql": "INSERT INTO authors (author_name) VALUES (?), (?), (?)", "args": ["Kegan Roslyn", "Toby Lynsey", "Lacey Desmond"], "database": "publications"}
HTTP Request
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
{"lastInsertId": 1,"rowsAffected": 3}
Call a stored procedure
Request Data
{"sql": "CALL sample_procedure(?)", "args": ["Crime Thriller"], "database": "publications"}
HTTP Request
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
{"lastInsertId": 0,"rowsAffected": 0}
Set an engine variable
Request Data
{"sql": "SET GLOBAL data_conversion_compatibility_level = '8.0'"}
HTTP Request
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
{"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
{"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
#/* The JSON payload for the HTTP request is supplied from the exec.jsonfilecurl -H "Content-Type: application/json" --data '@./exec.json' https://root:password@localhost:8080/api/v2/exec
HTTP response
{"lastInsertId": 0,"rowsAffected": 0}
Last modified: August 8, 2023