You can make outbound web requests (i.e. RESTful API calls) from your PL/SQL code to any JSON based API.
To call a RESTful API simply insert a row into the parasql_http_request table with the appropriate values. The system will then make the HTTP request asynchronously and write the response back into the same table. Finally, if you specified a callback_procedure in your request, it will be called to process the response.
There are a wide variety of functions available to easily parse and generate JSON as needed.
To accept an inbound web request, please see the Inbound Web Request documentation.
To start a request/response cycle:
|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','PUT','DELETE') 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_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; charset=utf-8 is the most common alternative.|
|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.|
|auth_user||VARCHAR(128)||DEPRECATED. For http basic authorization set the request_headers field to: JSON_OBJECT('Authorization',CONCAT('Basic ',TO_BASE64(CONCAT('user',':','pass'))))|
The following code makes a request:
If you specified a callback_procedure in your request, that procedure will be called to process the response. See the PDF Generation documentation for a complete request/response example using a callback_procedure.
Your outbound web 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.
If you run into problems, try issuing a command like the following via Tools > SQL Console... to diagnose what went wrong: