With this feature you can make RESTful API calls directly from your SQL code (triggers, stored procedures, scheduled events). You make a request by inserting a row into the parasql_http_request table with the parameters needed for the request (typically a URL, optional headers, an optional body and the name of your callback procedure). After your transaction completes the HTTP(s) request is made and its response is written to the parasql_http_request table. Then your callback procedure is called so that it can process the response. There are a wide variety of functions to easily parse XML and JSON response data.
The example below calls ParaSQL's own RESTful API (to ask it to convert some HTML into a PDF), but you can call any RESTful API in a similar fashion.
This feature is configurable and must be enabled for your account.
To start a request-response cycle, simply insert a row into the parasql_http_request table with the appropriate parameters. After the current transaction completes, the request will be made and its response stored in the response_status, response_headers, and response_body columns. Then, if you specified a callback_procedure in the request, that stored procedure will be called and passed the id of the row in the parasql_http_request table that needs processing.
Columns in the parasql_http_request table:
|id||BIGINT NOT NULL AUTO_INCREMENT||Primary Key|
|request_timestamp||DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP||Time of request|
|request_method||ENUM ('GET','POST','DELETE','HEAD','PUT') NOT NULL DEFAULT 'GET'||Request method|
|request_url||VARCHAR(2083) NOT NULL||The request url|
|request_headers||TEXT||Optional. HTTP request headers as key:value new line|
|post_data||MEDIUMTEXT||Optional. Should be called request_body. If this value is NOT NULL then 1) a Content-Length header will be computed and added automatically and 2) a Content-Type header will be added based upon the post_data_encoding column value.|
|post_data_encoding||VARCHAR(255)||Optional. The Content-Type header if post_data is NOT NULL. Default is application/x-www-form-urlencoded if not specified. application/json is the most common alternative.|
|auth_user||VARCHAR(128)||Optional user name for http basic auth.|
|auth_password||VARCHAR(128)||Optional password for http basic auth.|
|allow_redirects||BOOLEAN NOT NULL DEFAULT FALSE||Allow http redirects|
|request_type||VARCHAR(255)||DEPRECATED. Optional. place to store app specific info|
|request_tag||VARCHAR(255)||DEPRECATED. Optional. place to store app specific info|
|response_status||INT||HTTP response status code or -1 if exception thrown|
|response_headers||TEXT||HTTP response headers as key:value new line|
|response_body||MEDIUMTEXT||HTTP response body or the exception text if status = -1|
|callback_procedure||VARCHAR(128)||Optional. The name of a stored procedure to call to process the response; the procedure should take a single parameter of type BIGINT which will be the value of the parasql_http_request.id of the request.|
|callback_status||VARCHAR(255)||Result of callback execution: either COMMIT or ROLLBACK with error message.|
The following is the complete code needed for a request procedure. This example calls an API to generate a PDF from some simple HTML text:
Note in the last line of the above code, the callback_procedure is set to MyCallbackProcedure to process the response.
The following is the complete code needed for a callback procedure to process a response. All callback procedures take a single parameter of type BIGINT; its value will match the parasql_http_request.id column of the request-response (the request_id variable in the code below):
The above example processes a response in JSON format and saves the result as a new row in the Item table, and then sends it as an email attachment.
Your SQL/REST requests are run asynchronously in a task queue. A task will be fired to process the queue if a save operation is performed by a user (a transaction initiated by a user involving an INSERT, UPDATE or DELETE operation) and at 15 minute intervals.