REST Service

You can also access your database via our restful API.

Query

Execute a SQL query and return the results in either JSON or CSV format. The Security Role assigned to the API Key determines what is accessible.

Request

	POST https://www.appsynergy.com/api?action=EXEC_QUERY&apiKey=YOUR_API_KEY
	
	{
	  "sqlCmd": "SELECT * FROM MyTable",
	  "responseFormat": "JSON"  -- enum: JSON, CSV
	}
	

The sqlCmd can be any valid SQL query.

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.

Data Modification

Execute a SQL DML statement (e.g. INSERT, UPDATE, DELETE) and return the number of rows affected. The Security Role assigned to the API Key determines what can be modified.

Request

	POST https://www.appsynergy.com/api?action=EXEC_DML&apiKey=YOUR_API_KEY
	
	{
	  "sqlCmd": "UPDATE MyTable SET MyCol = 123 WHERE ID = 100"
	}
	

sqlCmd - any valid SQL DML statement.

Response

	{
	  "status": "OK",
	  "errorMessage": "",
	  "errorCode": "",
	  "data": {
	      "rowsAffected": 1
	    }
	}
	

data.rowsAffected - number of rows affected by the SQL statement.

API Limitations

Note that all API requests:

Must provide their API Key via the URL (as shown) or via an HTTP header in Bearer Authentication format.
Must complete in < 10 minutes to avoid a timeout.
Must be < 16MB in size.
Are rate limited to 500 connections per hour to prevent runaway usage. This is configurable via the API user's MAX_CONNECTIONS_PER_HOUR resource option.

Database Connectors

REST Service

Query

Request

Response

Data Modification

Request

Response

API Limitations