AppSynergy REST API

The AppSynergy REST API can be broken down into three different categories of services.

Inbound Web Requests

Allow your database to accept custom inbound web requests (i.e. publish APIs for others to use).

• WEB_REQUEST – Accept an inbound web request.

PDF Document Generation

Used to dynamically generate PDF documents from database data. This service is typically called from your PL/SQL code via an Outbound Web Request.

• HTML2PDF – Create a PDF from dynamically generated HTML.
• PDF_AUTOFILL – Auto fill a template PDF to generate a filled PDF.
• PDF_MERGE – Merge multiple PDFs into a single PDF.

Executing SQL

Used to allow other applications to issue SQL commands against your database via a REST interface. You can also allow other systems to connect securely to your database via ODBC or JDBC.

• EXEC_QUERY – Execute an SQL Query (SELECT).
• EXEC_DML – Execute an SQL DML Statement (INSERT, UPDATE, DELETE).

API Keys

In the examples below YOUR_API_KEY and its assigned Security Role determine access rights (see Tools > API Keys...). YOUR_API_KEY can be provided as a URL parameter (as shown below) or alternatively as an HTTP header in Bearer Authentication format.

Note that all actions:

• Must complete in less than 60 seconds.
• Have a request/response size limit of ~16MB.
• Are rate limited by default to 500 connections per hour to prevent runaway systems. This is configurable via the API user's MAX_CONNECTIONS_PER_HOUR resource option.

WEB_REQUEST – Accept an inbound web request

Each http request is inserted into the WebRequest table in your database. Your code then sets the resp_code and resp_body fields to appropriate values when your trigger runs; an example is shown below.

Request

Your trigger will see the request data as follows:

• The NEW.req_user_id field contains the user id associated with the API Key that made the request.
• The NEW.req_method field contains the request method; only GET and POST are supported.
• The NEW.req_params field contains any optional URL parameters represented as a single JSON object with name:value pairs.
• The NEW.req_headers field contains the request headers as a JSON object.
• The NEW.req_body field contains the request body; this must be a JSON object if specified.

Response

Your trigger should SET the response fields as follows:

• Set the NEW.resp_code field to an appropriate value; use 200 for success and 400 for error.
• Set the NEW.resp_body field to an appropriate value; the response can be any valid JSON object.
• Optionally set the NEW.resp_headers field to a valid JSON object containing any additional response headers you may wish to include. The default response header is {"content-type":"application/json; charset=utf-8"}. To append additional response headers use: SET NEW.resp_headers = JSON_MERGE(NEW.resp_headers, JSON_OBJECT('x-parasql-test','testvalue'));

Trigger Example - BEFORE UPDATE on WebRequest

Your BEFORE UPDATE trigger on the WebRequest table should look something like this:

Your trigger will be fired once (and possibly twice) for each web request received. The first update will have NEW.resp_code set to NULL; your code should test for this NULL value and respond appropriately by setting the NEW.resp_code and NEW.resp_body fields to appropriate values.

If, during the first update, your code encounters an error (or raises a SIGNAL to indicate an error) the transaction will automatically be rolled back in its entirety. The system will then update the row a second time, in a new transaction, with a non-null resp_code and resp_body to log the error.

EXEC_QUERY – Execute an SQL Query

Executes an SQL query and returns the results in either JSON or CSV format. You can execute an SQL command like SHOW TABLES to see which tables are available.

Request

• The sqlCmd property can be set to any valid SQL query. Since each API Key is assigned to a specific Security Role, only the SQL commands allowed by that Security Role will be permitted.
• The responseFormat property must be set to either JSON or CSV.

Response

If responseFormat was CSV:

If responseFormat was JSON:

• The data.rows property is an array of row objects. Each row object has an array of value objects. Each value object has a value property. Therefore data.rows[0].values[0].value refers to the value in the first column of the first row.

EXEC_DML – Execute an SQL DML Statement

Executes an SQL DML statement (e.g. INSERT, UPDATE, DELETE) and returns the number of rows affected.

Request

• The sqlCmd property can be set to any valid SQL DML statement. Since each API Key is assigned to a specific Security Role, only the SQL commands allowed by that Security Role will be permitted.

Response

• The data.rowsAffected property is an integer representing the number of rows affected by the SQL DML statement.

HTML2PDF – Generate a PDF doc from HTML

Generates a PDF document from the provided HTML and stores it in your database storage bucket. The response contains a reference to that document in AppSynergy Document Field format. Often used to generate invoices, purchase orders, etc.

Request

• The html property must be in XHTML format (i.e. all elements must be closed; for example <br> is not valid but <br/> is valid). Most of CSS 2.1 is supported.
• The filename property provides the human readable filename for the generated PDF document.
• The makePublic property makes the generated PDF file accessible to the public if true. The default is false.

Response

• The data.documentField property is in AppSynergy Document Field format; it is a reference to the PDF file created in your database storage bucket. This value can be stored directly into any column in the database defined as a Document Field.

Example

See the PDF Generation Example for details on how to call this service from your AppSynergy code.

PDF_AUTOFILL – Auto fill a template PDF doc

Given a fillable PDF document as a template, and a list of field names and values, this method fills in the PDF fields creating a new filled PDF document.

Request

• The sourcePdf property must be in DocumentField format (as shown).
• The dataFields property is an object with any number of key-value pairs; the values must be strings.
• The outputFilename property provides the human readable filename for the generated PDF document.
• The ignoreMissingFields property (default true) determines if an error is generated if any of the specified dataFields are missing in the sourcePdf. Set to false if you want to generate an error on any mismatch.
• The flattenFields property determines if editable fields in the source PDF are made non-editable in the filled PDF. The default is NONE meaning that any fields that were editable in the source PDF will remain editable in the filled PDF. If set to FILLED only those fields that were filled will be made non-editable; non-filled fields will remain editable. If set to ALL then all editable fields in the source PDF will be made non-editable in the filled PDF.

Response

• The data.filledPdf property is in DocumentField format; it is a reference to the filled PDF file created in your database storage bucket. This value can be stored in any database field of type DocumentField.
• The data.acroFieldNames property is an array containing the list of all AcroFields found in the sourcePdf. This can be useful for diagnosing mismatached field names.

PDF_MERGE – Merge multiple PDF docs into a single PDF

Merges multiple PDFs into a single document.

Request

• The outputFilename property provides the human readable filename for the generated PDF document.
• The sourcePdfs property is the array of DocumentField values to be merged.

Response

• The data.mergedPdf property is the resulting merged PDF document (in DocumentField format).