Sign In
Call Us: 888-829-7576


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
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. 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 is the most common alternative.
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)DEPRECATED. Optional. place to store app specific info
request_tagVARCHAR(255)DEPRECATED. 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. 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 the value of the of the request.
callback_statusVARCHAR(255)Result of callback execution: either COMMIT or ROLLBACK with error 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 MEDIUMTEXT DEFAULT '<html><body><div style="border:1px solid green">Hello World</div></body></html>'; DECLARE req_url VARCHAR(2048) DEFAULT ''; DECLARE req_body MEDIUMTEXT; SET req_body = concat('html=', parasql_encode_uri_component(html) ); INSERT INTO parasql_http_request (request_method, request_url, post_data, callback_procedure) VALUES ('POST', req_url, req_body, '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 column of the request-response (the request_id variable in the code below):


    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_val('Item'), json_value(jsonResp, '$.data') );

    -- send email
    INSERT INTO parasql_email_out (to_address, replyto_address, subject, body)
       VALUES ('','','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') );


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.