> For clean Markdown of any page, append .md to the page URL.
> For a complete documentation index, see https://ocient.ferndocs.com/llms.txt.
> For AI client integration (Claude Code, Cursor, etc.), connect to the MCP server at https://ocient.ferndocs.com/_mcp/server.

# Ocient HTTP Query API

> The Ocient Hyperscale Data Warehouse™ has the HTTP Query API that enables you to execute SQL statements. The API follows OpenAPI Specification (Swagger®) guidelines.

The Ocient Hyperscale Data Warehouse™ provides an HTTP Query API that enables the execution of SQL statements. This page uses command-line examples using `curl`.

The HTTP Query API returns data in JSON format by default. The default header value is `Accept: application/json`.

## API Documentation

The API follows OpenAPI Specification (Swagger®) guidelines, which you can access in these formats from any SQL Node address `{sql_node_address}`:

* YAML format: `/openapi.yaml` endpoint
* JSON format: `/openapi.json` endpoint

**YAML Download Example**

```curl
curl https://{sql_node_address}/openapi.yaml
```

**JSON Download Example**

```curl
curl https://{sql_node_address}/openapi.json
```

You can use these specifications with various OpenAPI tools for code generation, testing, and documentation.

## Connection Setup

Before using the API, you must establish an open port for a SQL Node.

The connectivity pool defines connection settings for SQL Nodes, including their open port and address. For details, see [**CONNECTIVITY POOL**](/guides/sql-reference/data-definition-language-ddl-statement-reference/cluster-and-node-management).

> **Note**
>
> All the examples assume that the `openapi_port` is the standard SSL port `443`.  If your connectivity pool `openapi_port` has a different port setting, you must specify it in the request URL. For example, this URL specifies using port `8443`:  `https://sql_node_address:8443/v1/execute/`.

**Enable the OpenAPI Port**

To open a port, execute this [**ALTER CONNECTIVITY POOL**](/guides/sql-reference/data-definition-language-ddl-statement-reference/cluster-and-node-management) SQL statement on a connectivity pool in your system. Change the connectivity pool and node values in the example according to your system.

```sql
ALTER CONNECTIVITY_POOL benchmark_sql_connectivity_pool
    ALTER PARTICIPANT "benchmark-sql0" SET openapi_port 443;
```

### Check Port Configuration

Verify your port configuration by executing this query.

```sql
SELECT c.name, n.name, openapi_port
    FROM sys.connectivity_pool_participants AS cp
    LEFT JOIN sys.connectivity_pools AS c ON cp.id = c.id
    LEFT JOIN sys.nodes AS n ON cp.node_id = n.id;
```

The output displays all configured connectivity pools with their associated nodes and OpenAPI ports.

### SSL Certificate Setup

The Ocient® HTTP Query API supports two options for handling SSL certificates.

**Secure Connection Using TLS**

You can use your own certificates for TLS-secured connections by configuring certificate files in your Ocient install. For details, see [**Secure Connections Using TLS**](/guides/system-administration/secure-connections-using-tls).

**Unsecured Connection**

For quick testing or in non-production environments, you can bypass certificate validation by adding the `-k` or `--insecure` flag to your curl command.

**Example**

```sql
curl -k -u 'admin@system:admin' https://<sql-node-address>/v1/execute/system \
    -d '{"statement":"SELECT * FROM sys.dummy2;","format":"collection"}'
```

### Authentication Methods

The Ocient HTTP Query API accepts user credentials in various formats designed for different use cases.

**Simple API Requests**

For simple queries, you can include user credentials in the request.

In this example, the request includes credentials after the `-u` flag.

```curl
curl -u 'admin@system:admin' https://sql_node/v1/execute/system \
    -d '{"statement":"SELECT * FROM sys.dummy2;", "format": "collection"}'
```

**Bearer Token Authentication**

For continuous access, you can obtain an authentication token by making a request to the `login` endpoint. Replace the text in this example with the address of your SQL Node, your username, your password, and the name of your database.

```curl
curl -X POST https://sql_node/v1/login \
    -H "Content-Type: application/json" \
    -d '{
        "username": "admin@system",
        "password": "admin",
        "database": "system"
    }'
```

This request returns an authentication token that you can add to subsequent requests.

```curl
curl -X POST https://sql_node/v1/execute \
    -H "Authorization: Bearer TEST_ACCESS_TOKEN" \
    -H "Content-Type: application/json" \
    -d '{
        "statement": "SELECT * FROM sys.dummy2;",
        "database": "system",
        "format": "collection"
    }'
```

**OpenID Connect (SSO)**

For SSO authentication, you can use OpenID Connect by targeting the `sso_authentication` endpoint with a callback path. Replace the text in this example with the name of your instance, the name of your database, and your callback path.

```curl
curl -X POST https://ocient-instance/v1/sso_authentication \
    -H "Content-Type: application/json" \
    -d '{
        "database": "system",
        "sso_callback_path": "/app_callback"
    }'
```

After the redirection and authentication are complete, you can include your token in subsequent requests.

```curl
curl -X POST https://ocient-instance/v1/sso_token \
    -H "Content-Type: application/json" \
    -d '{
        "id_token": "TEST_ID_TOKEN",
        "database": "system"
    }'
```

## API Endpoint Documentation

The Ocient HTTP Query API supports these endpoints.

For API requests, JSON data must use double quotes (`"`) for all keys and string values. Single quotes should enclose the JSON payload, as shown in this example (see the payload section following the `-d` flag).

```curl
curl -X POST \
    https://my_sql_node.com/v1/execute \
    -H "Content-Type: application/json" \
    -d '{ "query": "SELECT * FROM my_table WHERE status = \"active\"", "database": "my_database" }'
```

Single or double quotes are acceptable for parameters not nested in JSON objects.

### execute

**`POST https://{sql_node}/v1/execute/{database}`**

Executes a SQL statement and returns the results. Supports requests for both regular and streaming responses.

With streaming, results return as they become available using the HTTP chunked transfer encoding. Each chunk contains a valid JSON object that you can parse independently.

For details on streaming, including large-response configuration, see the header parameters.

You can specify the database in the request body. If you do not specify the database, the Ocient System defaults to the database specified in the authentication token or system settings. If you specify a database in the request, this value overrides any alternate database value specified as a body parameter.

**Path Parameters**

| Name         | Type   | Required | Description                                                                                                                                                                                                 |
| ------------ | ------ | -------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `{sql_node}` | string | Yes      | The IP address or domain of a SQL Node assigned to a connectivity pool in your system.                                                                                                                      |
| `{database}` | string | No       | The specific database in your system for the API call.   If you do not specify the database, the executed query defaults to the database from the authentication token or system settings.  If you inclu... |

**Query Parameters**

| Name     | Type   | Required | Description                                                                                                                                                                                                 |
| -------- | ------ | -------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `format` | string | No       | Sets the format for the requested data.   Note: This parameter applies only when using Content-Type: text/plain. When using Content-Type: application/json, specify the format in the request body.  Sup... |
| `schema` | string | No       | The schema to use for the SQL statement.  Example: schema=mydata  Note: This parameter applies only when using Content-Type: text/plain. When using Content-Type: application/json, specify the schema i... |

**Header Parameters**

| Name                          | Type    | Required | Description                                                                                                                                                                                                 |
| ----------------------------- | ------- | -------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `authorization`               | string  | Yes      | An access token to connect to your Ocient System.   To receive an access token, you must provide your username and password using the login endpoint.   The system passes the credentials using the Auth... |
| `content-type`                | string  | Yes      | Specifies the request body format. Options include:   application/json  Description: The standard option for submitting queries and receiving responses as a single JSON object or array. Use when submi... |
| `accept-encoding`             | string  | No       | Supported compression algorithms for responses. You can specify multiple algorithms as a comma-separated list.   Supported values are:  "gzip"  "deflate"  "be" (Brotli) or combinations like: "gzip, be... |
| `accept`                      | string  | No       | Supported response media types. Regardless of the Accept header, the API returns large query results using chunked encoding. Also, see the note about compression in the accept-encoding parameter.  Sup... |
| `preferred-encoding`          | string  | No       | Indicates the preferred encoding type from those specified in the accept-encoding parameter. This header provides fine-grained control when the accept-encoding parameter contains multiple values, allo... |
| `preferred-compression-level` | integer | No       | Specifies the chosen compression level for the selected encoding algorithm. Values range from 1 (fastest, least compression) to 9 (slowest, maximum compression). The exact behavior depends on the spec... |

**Body Parameters**

| Name         | Type    | Required | Description                                                                                                                                                                                                 |
| ------------ | ------- | -------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `statement`  | string  | Yes      | A valid SQL statement to execute.   Example: statement=SELECT \* FROM customers LIMIT 10;  You can include parameters to substitute in the SQL statement by including them in the params body parameter.    |
| `database`   | string  | No       | A specific database in your system for the API call.   If you do not specify this parameter, the executed query defaults to the database from the authentication token or system settings.                  |
| `format`     | string  | No       | Sets the output format of the query result data.   Supported values are:   "array" — Returns the schema and data as arrays (more efficient for large data sets). (default) "collection" — Returns data a... |
| `params`     | object  | No       | Parameters to substitute in the SQL statement for values enclosed in braces. The keys should match the parameter names in the statement (without the enclosing braces). Wrap string values in single quo... |
| `fetch_size` | integer | No       | The number of rows to return in each chunk for streaming responses. This value must be 0 or greater. A value of 0 (default) means the database determines how many rows to return.    Use smaller values... |
| `max_rows`   | integer | No       | The maximum number of rows to return. The default value is 9223372036854776000.  Note: Adding a LIMIT clause to a SQL query is generally a better method to restrict the number of rows, as the query en... |

**Example Request**

```curl
curl -u 'admin@system:admin' https://my_sql_node.com/v1/execute/system \
    -d '{"statement":"SELECT * FROM sys.dummy2", "format": "collection"}'
```

**Example Response**

**`200`**

```json title="200"
{
    "query_id": "ac4dd039-e385-400b-a428-fd1fdcd02e30",
    "status": {
        "reason": "The operation completed successfully",
        "sql_state": "00000",
        "vendor_code": 0
    },
    "data": [
        {
            "c1": 1
        },
        {
            "c1": 2
        }
    ]
}
```

### execute

**`GET https://{sql_node}/v1/execute/{database}`**

Alternative GET method for executing SQL statements. This method passes parameters as URL query parameters. This method does not support the params body parameter.

Specify which database to access in the query parameters. If you do not specify a database, the connection defaults to the database from the authentication token or system settings.

This method is most suitable for simple, read-only queries where the statement can be safely included in a URL. For complex queries or those with parameters, use the POST method instead.

**Path Parameters**

| Name         | Type   | Required | Description                                                                                                                                                                                                 |
| ------------ | ------ | -------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `{sql_node}` | string | Yes      | The IP address or domain of a SQL Node in your system.                                                                                                                                                      |
| `{database}` | string | No       | The specific database in your system for the API call.   If you do not specify the database, the executed query defaults to the database from the authentication token or system settings.  If you inclu... |

**Query Parameters**

| Name         | Type   | Required | Description                                                                                                                                                                                                 |
| ------------ | ------ | -------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `statement`  | string | Yes      | A valid SQL statement to execute. The statement must be URL-encoded, e.g., statement=SELECT%20\*%20FROM%20customers%20LIMIT%2010%3B                                                                         |
| `database`   | string | No       | A specific database in your system for the API call.   If you do not specify the database, the executed query defaults to the database from the authentication token or system settings.                    |
| `format`     | string | No       | Sets the response format.   Supported values are:   "array" — Returns the schema and data as arrays (more efficient for large data sets). (default) "collection" — Returns data as JSON objects (more co... |
| `fetch_size` | string | No       | The number of rows to return in each chunk for streaming responses. This value must be 0 or greater. A value of 0 (default) means the database determines how many rows to return.   Use smaller values ... |

**Header Parameters**

| Name                          | Type   | Required | Description                                                                                                                                                                                                 |
| ----------------------------- | ------ | -------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `Authorization`               | string | Yes      | An access token to connect to your Ocient System.   To receive an access token, you must provide your username and password using the login endpoint.   The system passes the credentials using the Auth... |
| `Content-Type`                | string | Yes      | Specifies the request body format. Options include:   application/json  Description: The standard option for submitting queries and receiving responses as a single JSON object or array. Use when submi... |
| `accept-encoding`             | string | No       | Supported compression algorithms for responses. You can specify multiple algorithms as a comma-separated list.   Supported values are:  'gzip'  'deflate'  'br' (Brotli) or combinations like: 'gzip, br... |
| `accept`                      | string | No       | Supported response media types. Regardless of the Accept header, the API returns large query results using chunked encoding. Also, see the note about compression in the accept-encoding parameter.  Sup... |
| `preferred-encoding`          | string | No       | Indicates the preferred encoding type from those specified in the accept-encoding parameter. This header provides fine-grained control when the accept-encoding parameter contains multiple values, allo... |
| `preferred-compression-level` | string | No       | Specifies the chosen compression level for the selected encoding algorithm. Values typically range from 1 (fastest, least compression) to 9 (slowest, maximum compression). The exact behavior depends o... |

**Example Request**

```curl
curl -u 'admin@system:admin' \
    'https://my_sql_node.com/v1/execute?statement=SELECT%20*%20FROM%20sys.dummy2&database=system&format=collection'
```

**Example Response**

**`200`**

```json title="200"
{
    "query_id": "2d5af900-cb02-44f4-b974-6264d2133e96",
    "status": {
        "reason": "The operation completed successfully",
        "sql_state": "00000",
        "vendor_code": 0
    },
    "data": [
        {
            "c1": 1
        },
        {
            "c1": 2
        }
    ]
}
```

### info

**`GET https://{sql_node}/v1/info`**

Returns basic system version information about the Ocient System and the HTTP Query API server.

You can use this endpoint to verify connectivity and to check compatible versions.

**Path Parameters**

| Name         | Type   | Required | Description                                                                            |
| ------------ | ------ | -------- | -------------------------------------------------------------------------------------- |
| `{sql_node}` | string | No       | The IP address or domain of a SQL Node assigned to a connectivity pool in your system. |

**Example Request**

```curl
curl -u 'admin@system:admin' \
    'https://my_sql_node.com/v1/info'
```

**Example Response**

**`200`**

```json title="200"
{
    "default_database": "system",
    "openapi_version": "1.1.0",
    "status": {
        "vendor_code": 0,
        "sql_state": "00000",
        "reason": "The operation completed successfully"
    }
}
```

### login

**`POST https://{sql_node}/v1/login`**

Authenticates a user with a username and password and then returns a token for use in subsequent API calls. This request also sets a session cookie.

Include the returned access token in the authorization header for subsequent requests in the format:
Authorization: Bearer \{token}.

**Path Parameters**

| Name         | Type   | Required | Description                                                                            |
| ------------ | ------ | -------- | -------------------------------------------------------------------------------------- |
| `{sql_node}` | string | Yes      | The IP address or domain of a SQL Node assigned to a connectivity pool in your system. |

**Body Parameters**

| Name       | Type   | Required | Description                                                                                            |
| ---------- | ------ | -------- | ------------------------------------------------------------------------------------------------------ |
| `username` | string | Yes      | Username.                                                                                              |
| `password` | string | Yes      | The password for the username.                                                                         |
| `database` | string | No       | Target database name. If you do not specify this parameter, the request defaults this value to system. |

**Example Request**

```curl
curl -X POST https://my_sql_node.com/v1/login \
    -H "Content-Type: application/json" \
    -d '{
        "username": "admin",
        "password": "admin",
        "database": "system"
    }'

```

**Example Response**

**`200`**

```json title="200"
{
    "access_token": "eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9...",
    "username": "jdoe",
    "database": "retail_analytics",
    "expires_in": 3600,
    "status": {
        "reason": "Authentication successful",
        "sql_state": "00000",
        "vendor_code": 0
    }
}
```

### logout

**`POST https://{sql_node}/v1/logout`**

Log out from a SQL session. This clears any associated cookies, but does not invalidate any access tokens.

This endpoint terminates only the cookie-based session. Any bearer tokens that were previously issued continue to work until they expire.

You must discard any stored bearer tokens to complete the log out in a client application.

**Path Parameters**

| Name         | Type   | Required | Description                                                                            |
| ------------ | ------ | -------- | -------------------------------------------------------------------------------------- |
| `{sql_node}` | string | No       | The IP address or domain of a SQL Node assigned to a connectivity pool in your system. |

**Body Parameters**

| Name       | Type   | Required | Description                                                                                            |
| ---------- | ------ | -------- | ------------------------------------------------------------------------------------------------------ |
| `database` | string | No       | Target database name. If you do not specify this parameter, the request defaults this value to system. |

**Example Request**

```curl
curl -X POST https://my_sql_node.com/v1/logout
    -d '{
        "database": "retail_analytics"
    }
```

**Example Response**

**`200`**

```json title="200"
{
  "status": {
    "reason": "Logged out successfully",
    "sql_state": "00000",
    "vendor_code": 0
  }
}
```

### sso_authentication

**`POST https://{sql_node}/v1/sso_authentication`**

Initiates the OpenID Connect authentication process by redirecting to the authorization server.

This endpoint begins the standard OpenID Connect authentication process:

1. The client calls this endpoint with a callback path.
2. The server responds with a redirect to the identity provider.
3. The user authenticates with the identity provider.
4. The identity provider redirects back to the callback endpoint.
5. The client can exchange the authorization code for an access token.

**Body Parameters**

| Name                | Type    | Required | Description                                                                                                                                                                                                 |
| ------------------- | ------- | -------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `database`          | string  | No       | Target database name. If you do not specify this parameter, the request defaults this value to system.                                                                                                      |
| `sso_callback_path` | string  | No       | Local path for redirection after successful authentication. This path should be registered with your OpenID provider as a valid redirect URI.                                                               |
| `do_redirect`       | boolean | No       | Determines whether to issue the redirect to the authentication server immediately.  If you set this value to true (default), the API responds with a 302 redirect to the OpenID provider.  If you set th... |

**Example Request**

```curl
curl -X POST https://my_sql_node.com/v1/sso_authentication \
    -H "Content-Type: application/json" \
    -d '{
        "database": "retail_analytics",
        "sso_callback_path": "/auth/callback",
        "do_redirect": true
    }'
```

**Example Response**

**`200`**

```json title="200"
{
  "status": {
    "reason": "Query executed successfully",
    "sql_state": "00000",
    "vendor_code": 0
  }
}
```

**`404`**

```json title="404"
{
  "message": "Ain't no cake like that."
}
```

### callback

**`GET https://{sql_node}/v1/callback`**

Provides the authentication token for the OpenID Connect authentication process. The authorization server redirects to callback path after successful authentication.

This endpoint receives the authorization code from the OpenID provider and exchanges it for a token. The provider redirects the user to the application callback URL specified in the initial authentication request.

The client application should not call this endpoint directly. The OpenID provider automatically calls this endpoint in the authentication process.

**Path Parameters**

| Name         | Type   | Required | Description                                                                            |
| ------------ | ------ | -------- | -------------------------------------------------------------------------------------- |
| `{sql_node}` | string | Yes      | The IP address or domain of a SQL Node assigned to a connectivity pool in your system. |

**Query Parameters**

| Name    | Type   | Required | Description                                                                                                                                                   |
| ------- | ------ | -------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `code`  | string | Yes      | The authorization code from the OpenID provider. This endpoint exchanges the code for a token.  Example: code=4/P7q7W91a-oMsCeLvIaQm6bTrgtp7                  |
| `state` | string | Yes      | Prevents cross-site request forgery attacks. This value should match the state in the initial authentication request.  Example: state=fRJfv29f3v39Jf39dJf93jf |

**Example Request**

```curl
curl -X GET "https://my_sql_node.com/v1/callback?code=4/P7q7W91a-oMsCeLvIaQm6bTrgtp7&state=fRJfv29f3v39Jf39dJf93jf"

```

**Example Response**

**`200`**

```json title="200"
{
  "status": {
    "reason": "Query executed successfully",
    "sql_state": "00000",
    "vendor_code": 0
  }
}
```

### sso_token

**`POST https://{sql_node}/v1/sso_token`**

Exchange an OpenID Connect identifier token or access token for an Ocient access token.

This endpoint allows clients to directly exchange tokens without following the full browser-based authentication process. This exchange is useful for server-to-server scenarios or when the client already has a valid OpenID token from another process.

**Path Parameters**

| Name         | Type   | Required | Description                                                                            |
| ------------ | ------ | -------- | -------------------------------------------------------------------------------------- |
| `{sql_node}` | string | Yes      | The IP address or domain of a SQL Node assigned to a connectivity pool in your system. |

**Body Parameters**

| Name           | Type   | Required | Description                                                                                                                                                             |
| -------------- | ------ | -------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `database`     | string | Yes      | Target database for connection after successful authentication. This database must be accessible to the user identified by the SSO token.                               |
| `id_token`     | string | No       | OpenID identifier token received from the OpenID provider after successful authentication. You must provide the id_token or access_token body parameters, but not both. |
| `access_token` | string | No       | OpenID access token received from the OpenID provider after successful authentication. You must provide the id_token or access_token body parameters, but not both.     |

**Example Request**

```curl
curl -X POST https://my_sql_node.com/v1/sso_token \
    -H "Content-Type: application/json" \
    -d '{
        "id_token": "eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9...",
        "database": "retail_analytics"
    }'
```

**Example Response**

**`200`**

```json title="200"
{
  "id_token": "eyJhbGciOiJSUzI1NiIsInR5cCI6IkpXVCJ9...",
  "database": "retail_analytics"
}
```

### sso_device_grant

**`POST https://{sql_node}/v1/sso_device_grant`**

Retrieve an OpenID device grant code that the Ocient System can verify and use with the sso_device_grant_verify endpoint.

The device grant process is intended for devices with limited input capabilities or no web browser:

1. Call this endpoint to retrieve a user code and verification URI.
2. Display the user code and verification URI to the user.
3. The user visits the verification URI on another device and enters the code.
4. Call the sso_device_grant_verify endpoint to check if the user has completed verification.

**Body Parameters**

| Name       | Type   | Required | Description                                                                                                                                                                                                 |
| ---------- | ------ | -------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `database` | string | No       | Target database name. If you do not specify this parameter, the request defaults this value to system. This value represents the database where the user connects after completing the device grant proc... |

**Example Request**

```curl
curl -X POST https://my_sql_node.com/v1/sso_device_grant \
    -H "Content-Type: application/json" \
    -d '{
        "database": "retail_analytics"
    }'
```

**Example Response**

**`200`**

```json title="200"
{
  "verification_uri_complete": "https://auth.example.com/device?code=BCDFGHJK",
  "verification_uri": "https://auth.example.com/device",
  "user_code": "BCDFGHJK",
  "status": {
    "reason": "Device authorization initiated",
    "sql_state": "00000",
    "vendor_code": 0
  }
}
```

### sso_device_grant_verify

**`POST https://{sql_node}/v1/sso_device_grant_verify`**

Verify a previous device grant request and return an authorization token.

After initiating a device grant process with the sso_device_grant endpoint, use this endpoint to check if the user has completed the verification process. If the verification is successful, the endpoint returns an authorization token that you can use for subsequent API calls.

You can call this endpoint multiple times until one of these outcomes:

The user completes the verification (returns 200 OK with a token).

The timeout is reached (returns error).

The verification is canceled (returns error).

**Path Parameters**

| Name         | Type   | Required | Description                                                                            |
| ------------ | ------ | -------- | -------------------------------------------------------------------------------------- |
| `{sql_node}` | string | Yes      | The IP address or domain of a SQL Node assigned to a connectivity pool in your system. |

**Body Parameters**

| Name        | Type   | Required | Description                                                                                                                                                                                                 |
| ----------- | ------ | -------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `user_code` | string | Yes      | The user code returned from a previous device grant request using the sso_device_grant endpoint.                                                                                                            |
| `database`  | string | No       | Target database name. If you do not specify this parameter, the request defaults this value to system.                                                                                                      |
| `timeout`   | string | No       | The verification timeout, in seconds, indicates how long the server waits for the user to complete the device authorization process before returning a timeout error.  The default value is 30 seconds. ... |

**Example Request**

```curl
curl -X POST https://my_sql_node.com/v1/sso_device_grant_verify \
    -H "Content-Type: application/json" \
    -d '{
        "user_code": "BCDFGHJK",
        "timeout": 60,
        "database": "retail_analytics"
    }'
```

**Example Response**

**`200`**

```json title="200"
{
  "access_token": "eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJzdWIiOiJqZG9lIiwiZGIiOiJyZXRhaWxfYW5hbHl0aWNzIiwiZXhwIjoxNjM1NDYxNzEyfQ.SflKxwRJSMeKKF2QT4fwpMeJf36POk6yJV_adQssw5c",
  "username": "jdoe",
  "database": "retail_analytics",
  "status": {
    "reason": "Authentication successful",
    "sql_state": "00000",
    "vendor_code": 0
  }
}
```

### token_refresh

**`POST https://{sql_node}/v1/token_refresh`**

Refreshes an existing access token, extending its validity period. Call this endpoint before the current token expires to maintain uninterrupted access.

Use the expires_in value from the login or previous refresh response to determine when to refresh the token. A common practice is to refresh when the token has half of its time remaining.

On success, the endpoint returns the 200 response and a JSON object containing a new access token and metadata about the refreshed session. Replace the access token in your client with the new token returned by the endpoint, and use the expires_in value to decide when to refresh again.

**Path Parameters**

| Name         | Type   | Required | Description                                                                            |
| ------------ | ------ | -------- | -------------------------------------------------------------------------------------- |
| `{sql_node}` | string | Yes      | The IP address or domain of a SQL Node assigned to a connectivity pool in your system. |

**Header Parameters**

| Name            | Type   | Required | Description                                                                                                                                                                                   |
| --------------- | ------ | -------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `Authorization` | string | Yes      | The bearer token, which must be a valid access token still within its refresh lifetime configured on the server. To obtain a token, call either the /v1/login or /v1/token_refresh endpoints. |
| `Content-Type`  | string | Yes      | Specifies the request body format. Use the value: application/json.                                                                                                                           |

**Body Parameters**

| Name       | Type   | Required | Description                                                                                            |
| ---------- | ------ | -------- | ------------------------------------------------------------------------------------------------------ |
| `database` | string | No       | Target database name. If you do not specify this parameter, the request defaults this value to system. |

**Example Request**

```curl
curl -X POST https://your-ocient-instance/v1/token_refresh \
    -H "Authorization: Bearer YOUR_CURRENT_TOKEN" \
    -H "Content-Type: application/json" \
    -d '{
        "database": "retail_analytics"
    }'
```

**Example Response**

**`200`**

```json title="200"
{
    "access_token": "eyJhb...",
    "username": "jdoe",
    "database": "retail_analytics",
    "expires_in": 3600,
    "status": {
        "reason": "Token refreshed successfully",
        "sql_state": "00000",
        "vendor_code": 0
    }
}
```

## HTTP API Query Examples

These examples demonstrate how you can sequence API endpoints for common use cases.

**Simple Queries**

This example query format is appropriate for simple read-only queries. The example provides user credentials `admin@system:admin` in the request. The `https://sql_node/v1/execute/system` API call specifies the `execute` endpoint with the SQL Node address `sql_node` and the `system` database. In this example, the SQL statement is `SELECT * FROM sys.dummy2;`. The `collection` format option returns the result in JSON format.

```curl
curl -u 'admin@system:admin' https://sql_node/v1/execute/system \
    -d '{"statement":"SELECT * FROM sys.dummy2;", "format": "collection"}'
```

**Large Result Set Queries**

Specify the content type as JSON data with the `Content-Type` header option. When working with large amounts of data, enable streaming using the `application/stream+json` value with the `Accept` header option. The request also compresses the response using `gzip` compression with the `Accept-Encoding` header option. In this example, the SQL statement is `SELECT * FROM large_table;`. The example specifies the `fetch_size` value to set the number of rows each chunk of streaming data contains to `1000`.

```curl
curl -u 'admin@system:admin' https://sql_node/v1/execute/system \
    -H "Content-Type: application/json" \
    -H "Accept: application/stream+json" \
    -H "Accept-Encoding: gzip" \
    -d '{
        "statement": "SELECT * FROM large_table;",
        "fetch_size": 1000
    }'
```

**Token Authentication Script**

This example uses a bash script to retrieve a token for the subsequent `curl` query. Use the `login` endpoint to retrieve the token. Specify the username, password, and database name. Then, use the `execute` endpoint to execute the `SELECT * FROM large_table;` SQL statement. Specify the token with the `Authorization` header option. The `gunzip` command decompresses the gzip-compressed API response so it is readable in JSON format.

```bash
# Retrieve the token
TOKEN=$(curl -s -X POST https://sql_node/v1/login \
    -H "Content-Type: application/json" \
    -d '{
        "username": "admin@system",
        "password": "admin",
        "database": "system"
    }' | jq -r '.access_token')

# Then use the token for a query with streaming and compression
curl -X POST https://sql_node/v1/execute \
    -H "Authorization: Bearer $TOKEN" \
    -H "Content-Type: application/json" \
    -H "Accept: application/stream+json" \
    -H "Accept-Encoding: gzip" \
    -d '{
        "statement": "SELECT * FROM large_table;",
        "database": "system",
        "fetch_size": 1000
    }' | gunzip
```

**Save Results to a File**

This example saves the query results in a `tables.json` file. The example uses the `SELECT * FROM sys.tables;` SQL statement.

```curl
curl -u 'admin@system:admin' https://sql_node/v1/execute/system \
    -H "Content-Type: application/json" \
    -d '{
        "statement": "SELECT * FROM sys.tables;",
        "format": "collection"
    }' | jq '.data' > tables.json
```

**Use a Wrapper Script**

This example specifies the connection parameters in the `ocient-query.sh` file. To run the file at the command line, you must also include an argument for executing the SQL statement (for example: `./ocient-query.sh "SELECT * FROM my_table;"`.

```bash
#!/bin/bash
# ocient-query.sh

HOST="sql-node.com"
USER="admin"
PASSWORD="admin"
DATABASE="system"

QUERY="$1"

curl -s -u "${USER}@${DATABASE}:${PASSWORD}" "https://${HOST}/v1/execute/${DATABASE}" \
    -H "Content-Type: application/json" \
    -d "{
        \"statement\": \"${QUERY}\",
        \"format\": \"collection\"
    }" | jq '.data'
```

## Related Links

[**Swagger**](https://swagger.io/specification/)

[**General SQL Syntax**](/guides/query-ocient/general-sql-syntax)