Sign In

Outbound Web Requests

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.

Request/Response Flow

To start a request/response cycle:

Table: parasql_http_request

Column NameDatatypeComments
idBIGINT NOT NULL AUTO_INCREMENT PRIMARY KEYThis value is passed to the callback_procedure (if any).
request_timestampDATETIME NOT NULL DEFAULT CURRENT_TIMESTAMPTime of request
request_methodENUM ('GET','POST','DELETE','HEAD','PUT') NOT NULL DEFAULT 'GET'Request method
request_urlVARCHAR(2048) NOT NULLThe request URL
request_headersJSONOptional. HTTP request headers in JSON format. Use JSON_OBJECT() to create.
post_dataMEDIUMTEXTOptional. 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_encodingVARCHAR(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_statusINTHTTP response status code or -1 if an exception is thrown before a response is received.
response_headersJSONHTTP response headers in JSON format.
response_bodyMEDIUMTEXTHTTP response body or the exception text if response_status = -1
callback_procedureVARCHAR(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_statusVARCHAR(1024)Result of callback execution: either COMMIT or ROLLBACK with error message.
opt_metadataJSONOptional user defined metadata about the request. Useful for linking certain request/response behavior.
auth_userVARCHAR(128)DEPRECATED. For http basic authorization set the request_headers field to: JSON_OBJECT('Authorization',CONCAT('Basic ',TO_BASE64(CONCAT('user',':','pass'))))
auth_passwordVARCHAR(128)DEPRECATED.

Example – Making a Request

The following code makes a request:

BEGIN DECLARE v_req_url VARCHAR(2048) DEFAULT 'https://api.somecompany.com/api/v1/batch_locations'; DECLARE v_req_body MEDIUMTEXT; -- create the top level JSON object SET v_req_body = JSON_OBJECT('locations', JSON_ARRAY()); -- for each record add a location object to the locations array FOR rec IN (SELECT Shipper_Name, Load_ID, Tractor_ID, Trailer_ID FROM Inventory_Pick_Ticket WHERE Departure_Timestamp IS NOT NULL) DO SET v_req_body = JSON_ARRAY_APPEND(v_req_body, '$.locations', JSON_OBJECT( 'shipper', rec.Shipper_Name, 'billOfLading', rec.Load_ID, 'tractorNumber', rec.Tractor_ID, 'trailerNumber', rec.Trailer_ID ) ); END FOR; -- make the request INSERT INTO parasql_http_request (request_method, request_url, request_headers, post_data) VALUES ('POST', v_req_url, JSON_OBJECT('content-type','application/json'), v_req_body); END

Example – Processing the Response

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.

Other Considerations

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:

SELECT * FROM parasql_http_request ORDER BY id DESC LIMIT 5