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:

Insert a row into the parasql_http_request table specifying the request_method and request_url fields. You may optional specify other data such as post_data or a callback_procedure to process the response; see the table description below for details.
After the current transaction completes, a separate transaction will be used to make the request and write the response into the response_status, response_headers and response_body fields.
Finally, if you specified a callback_procedure it will be called to process the response; this final step also occurs within its own transaction.

Table: parasql_http_request

Column Name	Datatype	Comments
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'))))
auth_password	VARCHAR(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