Icon Web Server Database Access API

The database API requests use a REST-ful style that is structured as follows:

<Origin>/databases/<Database>[/<DataSet>[/Data]]?<Parameters>

<Origin> = <Protocol>://<Domain>[:<Port>]

<Database> = Database name

<DataSet> = DataSet name

<Parameters> = URL parameters in key=value form and an
               ampersand (&) between parameters

Information The "databases" resource name is the default resource name for database requests. The database resource name is configurable in the web server. Please make sure that the resource name that you are using matches the proper resource name on the target web server.

Any content included with database API requests, or returned as a response to the request, should/will be formatted as JSON content. The date/time format used in the JSON content is equivalent to a raw JavaScript Date value: an integer value representing the number of milliseconds since 1 January 1970 UTC.

Information Any forward slashes present in any JSON content returned as a response by the database API will be escaped so that the JSON content can be included in HTML without issues.


Get DataSet Columns Request
The get dataset columns request enumerates all of the columns in the dataset and database specified in the URL. The column information returned includes the name, type, length (if applicable), and scale (if applicable). The various supported column types are:

Column TypeDescription
0Unknown type - will cause an error when the columns are loaded
1String - requires a column length for fixed-length columns, null for variable-length columns
2Boolean
3Integer
4Float - can have a column scale specified
5Date
6Time
7Date/Time
8BLOB
9CLOB (non-browser clients only)

For browser client applications, BLOB columns in datasets are defined like variable-length String columns with a null length, and contain a URL that is used to dynamically load the BLOB column from the client application. However, CLOB columns (Character Large Object) in datasets are defined as actual String columns with a null length. For an example of a URL for a BLOB column request, please see the Get DataSet BLOB Column request below.

For non-browser client applications, BLOB columns in datasets are also defined like variable-length String columns with a null length, and contain a URL that is used to dynamically load the BLOB column from the client application. However, CLOB columns (Character Large Object) in datasets are defined with a distinct CLOB type, and are handled like a String column with a null length.

Information BLOB columns cannot be updated from browser client applications, whereas CLOB columns can be updated.

Also, an optional additional String column can be defined for a BLOB column that indicates the MIME type of the BLOB column data in each row. Such a column should be named:

<Column Name>_ContentType

If the web server finds a column with this name, it will use the contents of the column as the Content-Type header when returning the BLOB data in a BLOB column request.

If there is no active session or the session is not authenticated, the request will result in a 403 Forbidden HTTP response.

HTTP Method: GET

HTTP Response Content Type: application/json; charset=utf-8

Example Request:

https://localhost/example/albums

Example Response Content:

{
   "Columns": [{ "Name": "ID",
                 "Type": 1,
                 "Length": 38,
                 "Scale": null },
               { "Name": "Title",
                 "Type": 1,
                 "Length": 60,
                 "Scale": null },
               { "Name": "Artist",
                 "Type": 1,
                 "Length": 40,
                 "Scale": null },
               { "Name": "SortArtist",
                 "Type": 1,
                 "Length": 40,
                 "Scale": null },
               { "Name": "Year",
                 "Type": 3,
                 "Length": null,
                 "Scale": null },
               { "Name": "Label",
                 "Type": 1,
                 "Length": 40,
                 "Scale": null },
               { "Name": "CoverArt",
                 "Type": 8,
                 "Length": null,
                 "Scale": null },
               { "Name": "CoverArt_ContentType",
                 "Type": 1,
                 "Length": 40,
                 "Scale": null }]
}

HTTP Response: 200 on success or 500 on error

Get DataSet Parmeters Request
The get dataset parameters request enumerates all of the parameters in the dataset and database specified in the URL. The column information returned includes the name and type. The parameter types that can be returned are:

Column TypeDescription
0Unknown type
1String
2Boolean
3Integer
4Float
5Date
6Time
7Date/Time
8BLOB
9CLOB (non-browser clients only)

If there is no active session or the session is not authenticated, the request will result in a 403 Forbidden HTTP response.

HTTP Method: GET

HTTP Response Content Type: application/json; charset=utf-8

Example Request:

https://localhost/example/tracks/params

Example Response Content:

{
   "Params": [{ "Name": "AlbumID",
                "Type": 1 }]
}

HTTP Response: 200 on success or 500 on error

Get DataSet Rows Request
The get dataset rows request returns the rows in the dataset and database specified in the URL. Any input parameters to be used with the Select command for the dataset should be specified as URL parameters. Each column type in the JSON response content should be formatted according to the following rules:

Column TypeDescription
String
CLOB (non-browser clients only)
BLOB
Enclose non-null values in double quotes.
BooleanSpecify true or false literals for non-null values.
IntegerSpecify any valid integer value (positive or negative) for non-null values.
FloatSpecify any valid floating-point value for non-null values. If not null, the incoming value must use the period (.) decimal separator if it contains fractional digits.
Date
Time
Date/Time
Specify any valid integer value (positive or negative) for non-null values. If not null, the incoming value represents the number of milliseconds since midnight on January 1, 1970.

If there is no active session or the session is not authenticated, the request will result in a 403 Forbidden HTTP response.

HTTP Method: GET

HTTP Response Content Type: application/json; charset=utf-8

Example Request:

https://localhost/example/albums/data?ID=0201c645-c8fe-46b0-b785-815a617a1136

Example Response Content:

{
   "Rows": [{ "ID": "0201c645-c8fe-46b0-b785-815a617a1136",
              "Title": "August and Everything After",
              "Artist": "Counting Crows",
              "SortArtist": "Counting Crows",
              "Year": 1993,
              "Label": "BMG Direct Marketing, Inc.",
              "CoverArt": "?column=CoverArt&ID=0201c645-c8fe-46b0-b785-815a617a1136",
              "CoverArt_ContentType": "image\/jpeg" }]
}

HTTP Response: 200 on success or 500 on error


Get DataSet BLOB Column Request
The get dataset BLOB column request returns the data in the column, dataset, and database specified in the URL. Unlike the database and dataset names, the BLOB column name is specified in a column parameter instead of in the URL. If there is no active session or the session is not authenticated, the request will result in a 403 Forbidden HTTP response.

HTTP Method: GET

HTTP Response Content Type: Determined by the <Column Name>_ContentType column, if present, and application/octet-stream if not

Example Request:

https://localhost/example/albums/data?column=CoverArt&ID=0201c645-c8fe-46b0-b785-815a617a1136

HTTP Response: 200 on success or 500 on error


Commit Database Transaction Request
The commit database transaction request commits a transaction for the database specified in the URL using the transaction operations in the included JSON content. The operation rowset IDs in the included JSON content are special internal identifiers used by client applications to update any generated row values using the response content (see below). The following are the operation types supported in the included JSON content, as well as how they affect the structure of the included JSON content:

Operation TypeDescription
1Insert - the beforerow value will be null and the afterrow value will contain the row data for the inserted row.
2Update - the beforerow value will contain the row data for the row before the update, and the afterrow value will contain the row data for the row after the update.
3Delete - the beforerow value will contain the row data for the row before the deletion, and the afterrow value will be null.

The response to the commit database transaction request is a set of rows with a rowset ID, row ID, and row data, and will be used to update any inserted or updated rows in the client application with any newly-generated values that were created during the insert or update operation. For example, if an underlying table in the dataset contains an identity column that is assigned an identifier during row inserts, then the response content will contain the identifier.

Information This functionality depends upon the ability of the database engine to use output parameters to capture generated row values. For example, the ElevateDB database engine supports using output parameters with INSERT statements to capture generated row values, but SQL Server will require that you use a script for your Insert dataset command that assigns the last generated identity value to an output parameter.

If there is no active session or the session is not authenticated, the request will result in a 403 Forbidden HTTP response.

HTTP Method: POST

HTTP Request Content Type: application/json; charset=utf-8
HTTP Response Content Type: application/json; charset=utf-8

Example Request:

https://localhost/example

Example Request Content:

{
   "Operations": [{ "DataSet": "Orders",
                    "RowSet": 2,
                    "Operation": 2,
                    "BeforeRow": { "EWBRowID": 4,
                                   "OrderNo": 1006,
                                   "CustNo": 1380,
                                   "SaleDate": 1497398400000,
                                   "ShipDate": 594907200000,
                                   "EmpNo": 46,
                                   "ShipVIA": "Emery",
                                   "PO": "P101324",
                                   "Terms": "FOB",
                                   "PaymentMethod": "Visa",
                                   "ItemsTotal": 31987,
                                   "TaxRate": 0,
                                   "Freight": 0,
                                   "AmountPaid": 0 },
                    "AfterRow": { "EWBRowID": 4,
                                  "OrderNo": 1006,
                                  "CustNo": 1380,
                                  "SaleDate": 1497398400000,
                                  "ShipDate": 594907200000,
                                  "EmpNo": 110,
                                  "PO": "P101324",
                                  "Terms": "FOB",
                                  "PaymentMethod": "Visa",
                                  "ItemsTotal": 31987,
                                  "TaxRate": 0,
                                  "Freight": 0,
                                  "AmountPaid": 0 } }]
}

Example Response Content:

{
   "Rows": [{ "RowSet": 2,
              "EWBRowID": 4, 
              "Row": {  } }]
}

HTTP Response: 200 on success or 500 on error
Image