Elevate Web Builder includes database access functionality for easily loading data and then updating the same data using transactions.

The database access functionality has the following architecture:



The database access architecture uses a disconnected model where all data is cached locally, all database access on the web server is entirely stateless, and all database operations on the web server are performed optimistically as atomic transactions. All database access requests/responses use JSON as the underlying data format. Please see the Web Server Database Access and Web Server Database Access API topics for more information on how the database access works in the web server along with how the various database API requests and responses are structured.

There can be one or more databases (TDatabase instances) in an application, and within each database can be one or more owned datasets (TDataSet instances).

Authentication

---

Database operations in client applications require an authenticated session in order to complete successfully. Database operations in server applications are already operating under an authenticated session because server application execution also requires authentication. However, database access in server applications is unrestricted, and server applications do not use the effective privileges of the authenticated session in order to determine if any given database is accessible. Server applications behave as if they are executing as a "super user" with respect to database access. Please see the Web Server Authentication topic for more information on authentication in the web server.

Core Concepts

---

There are four core concepts in the database access functionality:

• Loading DataSet Columns - Normally the dataset columns are loaded/defined at design-time in the IDE, but it is possible to dynamically load the columns for a dataset at run-time. The returned JSON column information includes the column name, data type, length, and scale.



• Loading DataSet Rows - The dataset rows must be loaded at run-time in the client or server application. When the rows are loaded, you can specify that the rows be appended to the existing rows in the dataset, or completely replace the current rows in the dataset.



• Loading DataSet BLOB Columns - Any BLOB columns in dataset rows are dynamically loaded on-demand. For client applications, BLOB columns behave like string columns and contain a URL link that represents the web server request required to load the BLOB data. For server applications, BLOB columns are actually binary streams represented by a TStream instance that is accessible via the TDataColumn AsBlob property. The stream is loaded when it is first accessed and then cached as part of the in-memory row.



• Transactions - By default, transactions are automatically started and committed/rolled back as rows are inserted/saved, updated/saved, and deleted in any datasets contained within a database. Nested transactions are supported, so only the outermost commit operation actually results in a commit call to the web server. The automatic transaction handling can be turned off (see the TDatabase component below). The update of generated columns such as identity columns are supported in transactions, and any generated column values are echoed back to the client or server application's in-memory rows after a transaction successfully commits. Please see the Web Server Database Access topic for more information on how generated columns are handled in the web server.

Core Components

---

The database functionality contains several core components, all residing in the WebSession and WebData units in the standard component library.

TServerSession
A global TServerSession component instance called Session is auto-created at application startup for client applications. Server applications do not require authentication, and the WebSession unit and TServerSession component are not used with server applications.

In addition to this default singleton session instance, you can add explicit TServerSession instances to a visual client application project by dragging and dropping any server defined in the server manager on to a form or database designer surface. When the session is dropped on to the designer surface, a new TServerSession instance will be created and the relevant property information, such as the resource names for the server, will automatically be populated for the session. Please see the Using the Server Manager topic for more information.


Each TDatabase component instance (see below) has a ServerSession property that refers to a TServerSession component instance and provides a way for the TDatabase instance to authenticate the client application/user before attempting to execute any database access requests. This singleton instance of the TServerSession component is automatically associated with any TDatabase component instances that do not contain an explicit TServerSession component instance reference. You can use the TDatabase ActiveServerSession property to determine the actual TServerSession instance being used by the database for authentication.


TDatabase
A global TDatabase component instance called Database is auto-created at application startup for both client and server projects. This singleton instance of the TDatabase component is used to keep track of all TDataSet (see below) instances that aren't associated with a specific TDatabase instance.

In addition to this default singleton database instance, you can add explicit TDatabase instances to a visual client or server application project by dragging and dropping any database defined in the server manager on to the tab gutter of the work area in the IDE:



When the database is dropped on the tab gutter of the work area, a new TDatabase (or descendant) instance will be created for the project, along with an associated source unit, and all of the defined datasets for the database will automatically be created as TDataSet instances in the new database instance. Please see the Using the Server Manager topic for more information.

The TDatabase AutoTransactions property is used to control whether transactions are automatically handled by the database instances. Please see the Transactions topic for more information on how the AutoTransactions property affects transaction handling.

TDataSet
TDataSet components can either be dropped directly on a form, request handler, or database at design-time in a visual client or server application project, or created at run-time in both visual and non-visual projects.

The Columns property contains the column definitions for the dataset. The column definitions for a dataset can be defined manually at design-time or load at run-time using the TDatabase LoadColumns method (via the TDatabase instance that contains the TDataSet instance) or the TDataSet LoadColumns method. The primary difference between the two lies with client applications: in client applications the TDatabase LoadColumns method transparently handles retrieving the column definitions from the web server, whereas the TDataSet LoadColumns method accepts a JSON string containing the column definitions, and leaves the details of where the JSON string originated up to the caller. With server applications, there exists an overloaded TDataSet LoadColumns method without the JSON string parameter, and both TDatabase and TDataSet LoadColumns methods perform the same function.

Rows must be loaded from the web server application at run-time using the TDatabase LoadRows method (via the TDatabase instance that contains the TDataSet instance) or the TDataSet LoadRows method. The primary difference between the two lies with client applications: in client applications the TDatabase LoadRows method transparently handles retrieving the rows from the web server, whereas the TDataSet LoadRows method accepts a JSON string containing the rows, and leaves the details of where the JSON string originated up to the caller. With server applications, there exists an overloaded TDataSet LoadRows method without the JSON string parameter, and both TDatabase and TDataSet LoadRows methods perform the same function.

You can navigate the rows in a TDataSet component by using the First, Prior, Next, and Last methods.

The TDataSet component also allows you to Insert, Update, and Delete rows, as well as Find and Sort rows.