Sign In
Call Us: 888-829-7576

SQL/REST

What Is It?

With this feature you can make RESTful API calls directly from your SQL code (triggers, stored procedures, scheduled events). You make a request by inserting a row into the parasql_http_request table with the parameters needed for the request (typically a URL, optional headers, an optional body and the name of your callback procedure). After your transaction completes the HTTP(s) request is made and its response is written to the parasql_http_request table. Then your callback procedure is called so that it can process the response. There are a wide variety of functions to easily parse XML and JSON response data.

The example below calls ParaSQL's own RESTful API (to ask it to convert some HTML into a PDF), but you can call any RESTful API in a similar fashion.

This feature is configurable and must be enabled for your account.

The Request-Response Cycle

To start a request-response cycle, simply insert a row into the parasql_http_request table with the appropriate parameters. After the current transaction completes, 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 in the request, that stored procedure will be called and passed the id of the row in the parasql_http_request table that needs processing.

Columns in the parasql_http_request table:

Column NameDatatypeComments
idBIGINT NOT NULL AUTO_INCREMENTPrimary Key
request_timestampDATETIME NOT NULL DEFAULT CURRENT_TIMESTAMPTime of request
request_methodENUM ('GET','POST','DELETE','HEAD','PUT') NOT NULL DEFAULT 'GET'Request method
request_urlVARCHAR(2083) NOT NULLThe request url
request_headersTEXTOptional HTTP request headers as key:value new line
post_dataMEDIUMTEXTOptional. Required for POST.
post_data_encodingVARCHAR(255)Optional. Encoded as application/x-www-form-urlencoded if not specified.
auth_userVARCHAR(128)Optional user name for http basic auth
auth_passwordVARCHAR(128)Optional password for http basic auth
allow_redirectsBOOLEAN NOT NULL DEFAULT FALSEAllow http redirects
request_typeVARCHAR(255)Optional place to store app specific info
request_tagVARCHAR(255)Optional place to store app specific info
response_statusINTHTTP response status code or -1 if exception thrown
response_headersTEXTHTTP response headers as key:value new line
response_bodyMEDIUMTEXTHTTP response body or the exception text if status = -1
callback_procedureVARCHAR(128)Optional. Name of stored procedure to call to process the response.
callback_statusVARCHAR(255)Result of callback, either COMMIT or ROLLBACK with message

Request Procedure Example

The following is the complete code needed for a request procedure. This example calls an API to generate a PDF from some simple HTML text:

BEGIN
    DECLARE html TEXT DEFAULT '<html><body><div style="border:1px solid green">Hello World</div></body></html>';
    DECLARE url VARCHAR(2048) DEFAULT 'https://www.parasql.com/api?apiKey=YOUR-API-KEY&action=convertHtmlToPdf&filenameWithoutExt=MyHelloWorld';
    DECLARE postData TEXT;

    SET postData = concat('html=', parasql_encode_uri_component(html) );
    INSERT INTO parasql_http_request (request_method, request_url, post_data, callback_procedure)
       VALUES ('POST', url, postData, 'MyCallbackProcedure');
END

Note in the last line of the above code, the callback_procedure is set to MyCallbackProcedure to process the response.

Response Procedure Example

The following is the complete code needed for a callback procedure to process a response. All callback procedures take a single parameter of type BIGINT; its value will match the parasql_http_request.id column of the request-response (the request_id variable in the code below):

BEGIN

    DECLARE v_email_out_id BIGINT;
    DECLARE jsonResp TEXT;

    SELECT response_body INTO jsonResp FROM parasql_http_request WHERE id = request_id;

    INSERT INTO Item (Item_ID, MyDocumentField)
        VALUES (parasql_next_counter_value('Item','Item_ID'), json_value(jsonResp, '$.data') );

    -- send email
    INSERT INTO parasql_email_out (to_address, replyto_address, subject, body)
       VALUES ('joe@acme.com','billing@mycompany.com','test subject','body of test message');

    SELECT last_insert_id() INTO v_email_out_id;

    -- add pdf doc as email attachment
    INSERT INTO parasql_email_out_attachment (email_out_id, attachment)
       VALUES (v_email_out_id, json_value(jsonResp, '$.data') );

END

The above example processes a response in JSON format and saves the result as a new row in the Item table, and then sends it as an email attachment.

Execution Schedule

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.