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).
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
GET | POST https://www.appsynergy.com/api?action=WEB_REQUEST&apiKey=YOUR_API_KEY&OptParam=SomeValue
{
"AnyValidJson": "OK"
}
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
{
"AnyValidJson": "OK"
}
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:
BEGIN
-- only respond if resp_code is NULL
IF (NEW.resp_code IS NULL) THEN
-- verify the request is from a valid API user
IF (NEW.req_user_id != 'u1234') THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Permission Denied.';
END IF;
-- parse req_params and/or req_body and generate your response
IF (JSON_VALUE(NEW.req_params,'$.TestMe') = 'true') THEN
SET NEW.resp_code = 200;
SET NEW.resp_body = JSON_OBJECT('MyResponse','Hello World');
ELSE
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid Request. Try this: TestMe=true';
END IF;
END IF;
END
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
POST https://www.appsynergy.com/api?action=EXEC_QUERY&apiKey=YOUR_API_KEY
{
"sqlCmd": "SELECT * FROM MyTable",
"responseFormat": "JSON"
}
- 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:
Customer_ID,Name
1000,"Company A"
1001,"Company B"
If responseFormat was JSON:
{
"status": "OK",
"errorMessage": "",
"errorCode": "",
"data": {
"columns": [
{
"tableName": "Customers",
"columnName": "Customer_ID",
"datatype": "BIGINT"
},
{
"tableName": "Customers",
"columnName": "Name",
"datatype": "VARCHAR"
}
],
"rows": [
{
"values": [
{
"value": "1000"
},
{
"value": "Company A"
}
]
},
{
"values": [
{
"value": "1001"
},
{
"value": "Company B"
}
]
}
]
}
}
- 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
POST https://www.appsynergy.com/api?action=EXEC_DML&apiKey=YOUR_API_KEY
{
"sqlCmd": "UPDATE MyTable SET MyCol = 123 WHERE ID = 100"
}
- 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
{
"status": "OK",
"errorMessage": "",
"errorCode": "",
"data": {
"rowsAffected": 1
}
}
- 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
POST https://www.appsynergy.com/api?action=HTML2PDF&apiKey=YOUR_API_KEY
{
"html": "<html><body><h1>Hello World</h1></body></html>",
"filename": "HelloWorld.pdf",
"makePublic": false
}
- 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
{
"status": "OK",
"errorMessage": "",
"errorCode": "",
"data": {
"documentField": "HelloWorld.pdf;1048;1598992707098;database/files/a655f610-febd-4f6a-b11d-6bcd696f5456.pdf"
}
}
- 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
POST https://www.appsynergy.com/api?action=PDF_AUTOFILL&apiKey=YOUR_API_KEY
{
"sourcePdf": "MyFillableTemplate.pdf;1048;1598992707098;database/files/a655f610-febd-4f6a-b11d-6bcd696f5456.pdf",
"dataFields": {
"field1":"value1",
"field2":"value2"
},
"outputFilename": "MyNewDoc.pdf",
"ignoreMissingFields": true,
"flattenFields": "NONE"
}
- 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
{
"status": "OK",
"errorMessage": "",
"errorCode": "",
"data": {
"filledPdf": "MyNewDoc.pdf;12048;1598992709098;database/files/a655f610-febd-4f6a-b11d-6bcd696f5466.pdf",
"acroFieldNames": [
"FirstName",
"LastName"
]
}
}
- 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
POST https://www.appsynergy.com/api?action=PDF_MERGE&apiKey=YOUR_API_KEY
{
"outputFilename": "MyNewDoc.pdf",
"sourcePdfs": [
"Doc1.pdf;10428;1598992706098;database/files/a655f610-febd-4f6a-b11d-6bcd696f5456.pdf",
"Doc2.pdf;21048;1598992507098;database/files/a655f610-febd-4f6a-b11d-6bcd696f6bcd.pdf",
"Doc3.pdf;90148;1598992737098;database/files/a655f610-febd-4f6a-b11d-6bcd696f4f6a.pdf"
]
}
- 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
{
"status": "OK",
"errorMessage": "",
"errorCode": "",
"data": {
"mergedPdf": "MyMergedDoc.pdf;91048;1598992709098;database/files/a655f610-febd-4f6a-b11d-6bcd696f5466.pdf"
}
}
- The data.mergedPdf property is the resulting merged PDF document (in DocumentField format).