Representational state transfer (REST) is a software architectural style that defines a set of constraints to be used for creating Web services. In a RESTful Web service, requests made to a resource's URI will elicit a response with a payload formatted in JSON or XML format.
AppSynergy allows you to make RESTful API calls directly from your SQL code (e.g. triggers, stored procedures). You do so by inserting a row into the parasql_http_request table for each API call you want to make. The http(s) request is made asynchronously, the response is written to the parasql_http_request table, and then your callback_procedure is called (if any) to process the response.
To start a request/response cycle insert a row into the parasql_http_request table. 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, that stored procedure will be called and passed the id of the request.
|id||BIGINT NOT NULL AUTO_INCREMENT||Primary Key. This value is passed to the callback_procedure (if any).|
|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(2048) NOT NULL||The request URL|
|request_headers||JSON||Optional. HTTP request headers in JSON format. Use JSON_OBJECT() to create.|
|post_data||MEDIUMTEXT||Optional. Should be called request_payload. 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 authentication.|
|auth_password||VARCHAR(128)||Optional. Password for HTTP basic authentication.|
|allow_redirects||BOOLEAN NOT NULL DEFAULT FALSE||Allow HTTP redirects.|
|response_status||INT||HTTP response status code or -1 if an exception is thrown before a response is received.|
|response_headers||JSON||HTTP response headers in JSON format.|
|response_body||MEDIUMTEXT||HTTP response body or the exception text if response_status = -1|
|callback_procedure||VARCHAR(64)||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 passed the value of the id column.|
|callback_status||VARCHAR(1024)||Result of callback execution: either COMMIT or ROLLBACK with error message.|
|opt_metadata||JSON||Optional user defined metadata about the request. Useful for linking certain request/response behavior.|
The following example calls a third party API (FourKites) to update logistics data.
If you specified the name of a callback_procedure in your request, that stored procedure will be called to allow you to process the response. See the PDF Generation documentation for a complete request/response example using a callback_procedure.
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. Requests will be made in the order in which they were added to the queue.