SQL Evaluation API - Developers Wiki

This is the SQL Evaluation API resource documentation. If you face any issues or have any requests, please contact support.

The SQL evaluation API provides endpoints for executing SQL queries against various database servers. It can be accessed using an API key-based authentication and authorization process.

Registration

You must register your web application in order to receive a client-specific client-secret that must be provided while communicating with the API.

Currently, there is a limit on the number of:

Clients that a user can register.
Free requests that a user can make.

Authentication

The client authentication is done using your unique client-secret API key which you generated. You need to pass this client secret in the request header with the key as client-secret and value as the Client Secret Key which you receive after registering your client (as described above).

Making requests

Request Headers

You have to pass the following two mandatory headers:

content-type: This represents the MIME type of the request. The value has to be application/json.
client-secret: This is the client secret you receive after registration.

ENDPOINTS

Submit Evaluation API

POST request for submitting SQL evaluations

https://api.hackerearth.com/sql-evaluation/v1/submissions/

Request body

{
  "dbType": "MYSQL",
  "problemId": "5dcb19fbd3f1481a9f79a0ad04bbea8b",
  "sourceCode": "select count(*) from RetailOutlet;",
  "timeLimit": 10,
  "requestContext": "{'customId': 213121}",
  "callbackUrl": "https://client.com/callback/"
}

dbType
- Datatype: String
- Required: True
- This represents the type of database server against which the source/SQL code needs to be executed. For more information about all the database servers that are supported, check Supported Databases section at the bottom.
problemId
- Datatype: String
- Required: True
- This is the UUID of a SQL problem available in the My Library section of your HackerEarth account. The given source/SQL code will be executed against the Sample test case of the problem.
- Alternatively, you can use UUID of the publicly available default SQL problem.
  - Problem UUID: 29b80486eabb45618ccd228a597ed7e0
  - Problem Details: Please find here.
sourceCode
- Datatype: String
- Required: True
- This is the actual SQL query that needs to be executed. Only the SELECT DML statement is allowed.
timeLimit
- Datatype: Integer
- Required: False
- This is the maximum time (in seconds) for which the source code is allowed to run. The maximum value of the time limit can be 10 seconds. Any value greater than 10 seconds in the API request will be capped to 10 seconds. Exceeding the time limit returns the result as TLE in the response.
requestContext
- Datatype: String
- Required: False
- Max length: 1024 chars
- This attribute is provided for the client to pass any context which is related to this request. If this is passed in the Submit Evaluation API request, then it is sent back, as it is, along with the execution results sent at the callback URL or in the response for the Get Evaluation Status API. It can be used to pass any identifier that is relevant to client’s request or any contextual information that the client wants to attach with this evaluation request for self reference.
callbackUrl
- Datatype: String
- Required: False
- This is the URL where the client can receive a POST request that provides the status of the submission for SQL evaluation. For each submission request made by the client, if a callback URL is present in the request, then it is called immediately after the execution has been completed. If you use a callback URL, you don’t need to poll the Get Evaluation Status API to get the execution results. Please note that we retry a maximum of 3 times in case of failure and a maximum of 5 redirects are supported.
- This is the payload that is posted to the callbackUrl after the execution has been completed:
```
{
  "requestId": "e885e329-beb8-407d-a2a6-d31c7c919407",
  "requestStatus": {
    "code": "REQUEST_COMPLETED",
    "message": "Your request has been completed successfully"
  },
  "result": {
    "status": "AC",
    "output": "https://he-s3.s3.ap-southeast-1.amazonaws.com/media/userdata/AnonymousUser/code/a7e4f31",
    "timeUsed": 0.000794172286987305,
    "stderr": null
  },
  "requestContext": "{'customId': 213121}",
  "getStatusUrl": "https://api.hackerearth.com/sql-evaluation/v1/submissions/request/e885e329-beb8-407d-a2a6-d31c7c919407/",
  "errors": {}
}
```

Response

This is the payload that is returned as an immediate response for the API call:

{
    "requestId": "e885e329-beb8-407d-a2a6-d31c7c919406",
    "requestStatus": {
        "code": "REQUEST_INITIATED",
        "message": "Your request has been recorded and initiated"
    },
    "result": {
        "status": "NA",
        "output": null,
        "timeUsed": null,
        "stderr": null
    },
    "requestContext": "{'customId': 213121}",
    "getStatusUrl": "https://api.hackerearth.com/sql-evaluation/v1/submissions/request/e885e329-beb8-407d-a2a6-d31c7c919407/",
    "errors": {}
}

requestId: This is a UUID4 string which uniquely identifies every SQL evaluation request made by the client. This is used in the Get Evaluation Status API call to fetch the current status/result of the SQL evaluation request identified by this value.
requestStatus: This represents the current status of the evaluation request. Possible values for requestStatus["code"] are:
- REQUEST_INITIATED: The request has been recorded and processing has been initiated.
- REQUEST_QUEUED: The request has been queued in the evaluation pipeline.
- REQUEST_COMPLETED: The request has been executed.
- REQUEST_FAILED: The request could not be processed due to an internal issue.

result: This contains the execution results of the SQL evaluation request.

The result json contains "status", "output", "timeUsed" and "stderr" attributes.

The "status" represents the execution result with respect to whether the SQL query executed successfully, or there were any errors. The expected values of status attribute have been listed below.

Status	Explanation
AC	Accepted. The SQL code executed successfully.
TLE	Time Limit Exceeded. The execution of the SQL code took more time than the passed time limit.
RE	Runtime error occurred during the execution of the SQL code.

In case of successful execution, the "output" field will contain the URL of the output file and the "timeUsed" field would contain the time used (in seconds) for executing the SQL query, while the "stderr" field would be null.
In case of an execution error, the "stderr" field would contain the complete error traceback, while the rest of the fields would be null.

requestContext: This is the same value as sent by the client under requestContext field while making the Submit Evaluation API request.
getStatusUrl: You can make a GET request to this URL to fetch the current status of your SQL evaluation request.

Get Evaluation Status API

GET request for fetching status of a SQL evaluation request

https://api.hackerearth.com/sql-evaluation/v1/submissions/request/{requestId}/

The requestId parameter is the value of the requestId attribute that you receive in the response while submitting the POST request for SQL evaluation. This requestId attribute uniquely identifies every SQL evaluation request made by the client.

Like in the POST request above, it requires you to pass client-secret in the header for authentication purposes.

The response format of this API is the same as that of the Submit Evaluation API.

Error Response

In case of an error with the API requests, the response contains the details of the error as follows:

If a required attribute is missing in the request payload.

{
    "error": {
        "type": "badData",
        "message": "Invalid request data provided.",
        "details": {
            "dbType": [
                "This field is required."
            ]
        }
    }
}

If your consumption exceeds API services quota.

{
    "error": {
        "type": "quotaExceededError",
        "message": "API usage quota exceeded.",
        "details": {
            "quota": 73000000,
            "currentCount": 120189
        }
    }
}

If the client-secret header value is missing in the request.

{
    "error": {
        "type": "authFailed",
        "message": "Authentication Failed.",
        "details": {
            "reason": "No client-secret provided."
        }
    }
}

If the client-secret header contains any invalid characters.

{
    "error": {
        "type": "authFailed",
        "message": "Authentication Failed.",
        "details": {
            "reason": "Client Secret contains invalid characters."
        }
    }
}

If the provided client-secret doesn't match with the registered client.

{
    "error": {
        "type": "authFailed",
        "message": "Authentication Failed.",
        "details": {
            "reason": "Client Secret does not match."
        }
    }
}

If the SQL evaluation request doesn't exist for the requestId provided in the Get Evaluation Status API request.

{
    "error": {
        "type": "notFound",
        "message": "Entity not found.",
        "details": {}
    }
}

If any internal server error occurs while processing the request.

{
    "error": {
        "type": "processingError",
        "message": "Processing Error occurred. Please try again!",
        "details": {}
    }
}

Examples

Post request for submitting SQL evaluations

Python

import json
import requests

SQL_EVALUATION_URL = 'https://api.hackerearth.com/sql-evaluation/v1/submissions/'
CLIENT_SECRET = 'cf078d6f6fa13508b9d374545f48fe142b43b64e'

def execute_sql(db_type, source_code, request_context):
    problem_id = '5dcb19fbdd31481a9f20a0ad04bbea8b'
    time_limit = 5
    callback_url = "https://client.com/callback/"

    data = {
        'dbType': db_type,
        'problemId': problem_id,
        'sourceCode': source_code,
        'timeLimit': time_limit,
        'requestContext': request_context,
        'callbackUrl' : callback_url,
    }
    headers = {"client-secret": CLIENT_SECRET}
    response = requests.post(SQL_EVALUATION_URL, json=data, headers=headers)
    dict = json.loads(response.text)
    return dict

response_dict = execute_sql(db_type="MYSQL", source_code="select count(*) from RetailOutlet;", request_context="{'customId': 213121}")

CURL

curl -X POST \
  https://api.hackerearth.com/sql-evaluation/v1/submissions/ \
  -H 'cache-control: no-cache' \
  -H 'client-secret: cf078d6f6fa13508b9d374545f48fe142b43b64e' \
  -H 'content-type: application/json' \
  -d '{"dbType": "MYSQL", "problemId": "5dcb19fbdd31481a9f20a0ad04bbea8b",
  "sourceCode": "select count(*) from RetailOutlet;", "timeLimit": 5,
  "requestContext": "{'customId': 213121}", "callbackUrl": "https://client.com/execute/result/"}'

Get request for fetching status of a SQL evaluation request

Python

import json
import requests

GET_STATUS_URL = u'https://api.hackerearth.com/sql-evaluation/v1/submissions/request/cf81e6d9-c6b9-4dcb-a3af-90b0a138996b/'
CLIENT_SECRET = 'cf025d6f6fa13560b9d543745f48fe562b43b46e'

def get_sql_eval_status():
    headers = {"client-secret": CLIENT_SECRET}
    resp = requests.get(GET_STATUS_URL, headers=headers)
    dict = json.loads(resp.text)
    return dict

response_dict = get_sql_eval_status()

CURL

curl -X GET \
  https://api.hackerearth.com/sql-evaluation/v1/submissions/request/cf81e6d9-c6b9-4dcb-a3af-90b0a138996b/ \
  -H 'client-secret: cf025d6f6fa13560b9d543745f48fe562b43b46e'

Supported Databases

dbType Argument	Database Server Version
MYSQL	MySQL 5.6
POSTGRESQL	PostgreSQL 9.3
MSSQL	SQL Server 2014
ORACLE_DB	Oracle Database 11g