Icon JSON Reference

Elevate Web Builder uses the JSON (JavaScript Object Notation) format for handling database operations between an application and the web server. Both the internal web server in the IDE and the included external Elevate Web Builder Web Server include support for providing JSON column and row data for any datasets in databases defined in the Database Manager, as well as accepting transactional JSON data for inserts, updates, and deletes. However, for other web servers the JSON must be generated and consumed via a layer in the web server application, whether it is coded using PHP, Ruby, ASP.NET, or any other type of web server language or scripting environment. This reference will assist you in building such a layer in your web server application.

For more general information on JSON, please see the following link:

JSON Reference

Elevate Web Builder uses three types of JSON formats for the database functionality:
  • DataSet columns

  • DataSet rows

  • Transactions
DataSet Columns
Dataset columns are requested from the web server using an HTTP GET request when the TDataSet LoadColumns method is called from the application. The JSON returned by the web server should have the following format:

{ columns: [ <Column>, <Column>, <Column>, ... ] }

(... denotes more columns)

<Column> = { name: <Name>, type: <Type>, length: <Length>, scale: <Scale>}

<Name> = String (Example: "Customer No")

<Type> = Integer with a value of 0 through 8 (see below)

<Length> = Integer or null (Example: 20)

<Scale> = Integer or null (Example: 2)

Column Types

The following details the various column types and how they should be specified:

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

BLOB Column Types

In Elevate Web Builder datasets, BLOB columns are handled as String columns with a null length, and usually contain a URL that is used to dynamically load the BLOB data into a TImage, TAudio, or TVideo control. However, if a BLOB column is actually a CLOB (Character Large Object) column, then it will/should be defined and handled as an actual string, and not a URL.

Elevate Web Builder also supports the use of an additional String column for BLOB columns that indicates the MIME type of the BLOB column data. Such a column should be named:

<BLOB Column Name>_ContentType

If Elevate Web Builder finds a column with this name, it will use the contents of the column as the response Content-Type header when returning the BLOB data for BLOB column load requests. This is especially necessary for binary formats that cannot be detected by the browser automatically.

See the BLOB Column Data section below for more information on handling BLOB column data.

Example JSON

The following is an example of the JSON for a products table:

{ "columns": [
{ "name": "ProductID","type": 1,"length": 30,"scale": null },
{ "name": "Description","type": 1,"length": 60,"scale": null },
{ "name": "ListPrice","type": 4,"length": null,"scale": 2 },
{ "name": "Shipping","type": 4,"length": null,"scale": 2 }
] }

DataSet Rows
Dataset rows are requested from the web server using an HTTP GET request when the TDataSet LoadRows method or the TDatabase LoadRows method is called from the application. The JSON returned by the web server should have the following format:

{ rows: [ <Row>, <Row>, <Row>, ... ] }

(... denotes more rows)

<Row> = { <Column Name>: <Column Data>, <Column Name>: <Column Data>, ... }

(... denotes more column data)

<Column Name> = String (Example: "Customer No")

<Column Data> = Valid column data or null (see below)

Column Data

The following details the various column types and how the column data should be formatted for each:

Column TypeDescription
String
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, and can be negative for time values

BLOB Column Data

As mentioned above in the BLOB Column Types section, BLOB columns that are actually binary and not CLOB columns will/should be sent to the Elevate Web Builder application from the web server application as URLs that provide a link to the BLOB data. These links will be passed back to the web server application from the Elevate Web Builder application unchanged, so they can also contain information such as authentication. By default, the internal web server in the IDE, as well as the Elevate Web Builder Web Server, generate the URLs in the following format:

?method=load&database=<Database Name>&dataset=<DataSet Name>&column=<Column Name>&row=<Primary Key Values>[&user=<User Name>&password=<Password>]

The user and password parameters are only included when the original dataset rows request was authenticated. Except for public data, one should always use authentication for database requests. For more information, please see the Creating and Loading DataSets topic.

Warning Elevate Web Builder uses the AJAX functionality in browsers to perform database requests, and this functionality is limited in its ability to perform authentication via native browser methods. Therefore, you should always use secure connections (https) to the web server with any database requests. This is especially true if using BLOB columns that will require authentication information in their URL parameters.

Example JSON

The following is an example of the JSON for a products table:

{ "rows": [
{ "ProductID": "9V-BATTERY-12PK",
  "Description": "12-pack of 9-volt batteries",
  "ListPrice": 20, "Shipping": 2 },
{ "ProductID": "9V-BATTERY-4PK",
  "Description": "4-pack of 9-volt batteries",
  "ListPrice": 4.5, "Shipping": 1.5 },
{ "ProductID": "CALCULATOR-BUSINESS",
  "Description": "Business calculator",
  "ListPrice": 10, "Shipping": 1 },
{ "ProductID": "CASH-REGISTER",
  "Description": "Cash register with thermal printer",
  "ListPrice": 170, "Shipping": 10 },
{ "ProductID": "FLASH-USB-16GB",
  "Description": "16GB USB flash drive",
  "ListPrice": 15, "Shipping": 0.5 },
{ "ProductID": "FLASH-USB-32GB",
  "Description": "32GB USB flash drive",
  "ListPrice": 25, "Shipping": 0.5 },
{ "ProductID": "FLASH-USB-8GB",
  "Description": "8GB USB flash drive",
  "ListPrice": 10, "Shipping": 0.5 },
{ "ProductID": "LABEL-MAKER",
  "Description": "Label maker - plastic labels",
  "ListPrice": 35, "Shipping": 2 },
{ "ProductID": "PEN-BP-12PK",
  "Description": "12-pack of ballpoint pens",
  "ListPrice": 12, "Shipping": 0.6 },
{ "ProductID": "PHONE-HEADSET",
  "Description": "Hands-free phone headset",
  "ListPrice": 15, "Shipping": 2 },
{ "ProductID": "PHONE-SYSTEM-4HS",
  "Description": "4-handset phone system with main base",
  "ListPrice": 120, "Shipping": 4 },
{ "ProductID": "PROJECTOR-HD",
  "Description": "1080p HD Projector",
  "ListPrice": 850, "Shipping": 56 },
{ "ProductID": "SCANNER-SF",
  "Description": "Sheet-feed paper scanner",
  "ListPrice": 150, "Shipping": 7 },
{ "ProductID": "SHREDDER-SF-CC",
  "Description": "Sheet-feed, cross-cut shredder with bin",
  "ListPrice": 8, "Shipping": 10 },
{ "ProductID": "USB-CARD-READER",
  "Description": "USB magnetic strip card reader",
  "ListPrice": 25, "Shipping": 2 }
] }

Transactions
Transaction operations are sent to the web server using an HTTP POST request when the TDatabase Commit method is called from the application, and the current TransactionLevel is 0. The JSON sent to the web server will have the following format:

{ operations: [ <Operation>, <Operation>, <Operation>, ... ] }

(... denotes more operations)

<Operation> = { dataset: <DataSet Name>, operation: <Operation Type>,
                beforerow: <Row>, afterrow: <Row> }

<DataSet Name> = String (Example: "Customers")

<Operation Type> = Integer with a value of 0 through 3 (see below)

<Row> = null or { <Column Name>: <Column Data>,
                  <Column Name>: <Column Data>, ... }

(... denotes more column data)

<Column Name> = String (Example: "Customer No")

<Column Data> = Valid column data or null (see above)

Operation Types

The following details the various operation types and how the row data will be formatted for each:

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 (modified values only).
3Delete - the beforerow value will contain the row data for the row before the deletion, and the afterrow value will be null.

Example JSON

The following is an example of the transactional JSON for order and items tables:

{ "operations": [
{ "dataset": "CustomerOrders",
  "operation": 1,
  "beforerow": null,
  "afterrow": { "CustomerID": "DM", "OrderID": "DM-201275-134324404",
                "OrderDate": 1341460800000,
                "PONumber": null, "Terms": "Net 30",
                "ShippingTotal": 0.00, "PurchaseTotal": 0.00,
                "OrderTotal": 0.00, "AmountPaid": 0.00, "BalanceDue": 0.00,
                "SpecialInstructions": null }
},
{ "dataset": "CustomerItems",
  "operation": 1,
  "beforerow": null,
  "afterrow": { "OrderID": "DM-201275-134324404", "LineNo": 1,
                "ProductID": "SCANNER-SF", "Quantity": 1,
                "PurchasePrice": 150.00, "Shipping": 7.00,
                "PurchaseTotal": 150.00, "ShippingTotal": 7.00 }
},
{ "dataset": "CustomerItems",
  "operation": 1,
  "beforerow": null,
  "afterrow": { "OrderID": "DM-201275-134324404", "LineNo": 2,
                "ProductID": "FLASH-USB-32GB", "Quantity": 10,
                "PurchasePrice": 25.00, "Shipping": 0.50,
                "PurchaseTotal": 250.00, "ShippingTotal": 5.00 }
},
{ "dataset": "CustomerOrders",
  "operation": 2,
  "beforerow": { "CustomerID": "DM", "OrderID": "DM-201275-134324404",
                 "OrderDate": 1341460800000,
                 "PONumber": null, "Terms": "Net 30",
                 "ShippingTotal": 0.00, "PurchaseTotal": 0.00,
                 "OrderTotal": 0.00, "AmountPaid": 0.00, "BalanceDue": 0.00,
                 "SpecialInstructions": null },
  "afterrow": { "PONumber": "210054", "ShippingTotal": 12.00,
                "PurchaseTotal": 400.00,"OrderTotal": 412.00,
                "BalanceDue": 412.00 }
}
] }
Image