Sign In

SQL/REST – Make RESTful API Calls Directly from SQL

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.

There are a wide variety of functions available to easily work with JSON and XML data.

The parasql_http_request Table

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.

Column NameDatatypeComments
idBIGINT NOT NULL AUTO_INCREMENTPrimary Key. This 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_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_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 is the most common alternative.
auth_userVARCHAR(128)Optional. User name for HTTP basic authentication.
auth_passwordVARCHAR(128)Optional. Password for HTTP basic authentication.
allow_redirectsBOOLEAN NOT NULL DEFAULT FALSEAllow HTTP redirects.
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.

Example – Calling a Third-Party API

The following example calls a third party API (FourKites) to update logistics data.

BEGIN DECLARE v_req_url VARCHAR(2048) DEFAULT 'https://tracking-api.fourkites.com/api/v1/tracking/batch_locations'; DECLARE v_post_data MEDIUMTEXT; DECLARE FourKitesLocations_Cursor CURSOR FOR SELECT Shipper_Name, Tractor_ID, Load_ID, Trailer_ID, Current_Latitude, Current_Longitude, DATE_FORMAT(CONVERT_TZ(Carrier_Departure_Timestamp,'Canada/Mountain','UTC'), '%Y-%m-%dT%H:%i:%sZ') AS `locatedAt` FROM Inventory_Pick_Ticket WHERE Carrier_Departure_Timestamp IS NOT NULL ORDER BY Carrier_Departure_Timestamp DESC LIMIT 50; -- create the top level JSON object SET v_post_data = JSON_OBJECT('locations', JSON_ARRAY()); -- for each record add a location object to the locations array FOR rec IN FourKitesLocations_Cursor DO SET v_post_data = JSON_ARRAY_APPEND(v_post_data, '$.locations', JSON_OBJECT( 'shipper', rec.Shipper_Name, 'billOfLading', rec.Load_ID, 'tractorNumber', rec.Tractor_ID, 'trailerNumber', rec.Trailer_ID, 'driverPhone', '', 'railEquipmentInitials', NULL, -- 4-character rail equipment prefix 'railEquipmentNumber', NULL, -- 6-digit rail equipment number 'latitude', Current_Latitude, -- decimal number 'longitude', Current_Longitude, -- decimal number 'country', 'CA', 'locatedAt', rec.locatedAt, -- YYYY-MM-DDTHH:MM:SSZ 'delivered', false -- boolean true or false )); END FOR; -- make the request INSERT INTO parasql_http_request (request_method, request_url, post_data, post_data_encoding, auth_user, auth_password) VALUES ('POST', v_req_url, v_post_data, 'application/json', 'someUsername', 'somePassword'); END

The callback_procedure – Processing the Response

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.

Asynchronous Execution

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.