Web Server Database Access

The web server includes database access functionality that can be used to allow authenticated users to read and write the data stored on one or more private database servers/stores in a safe and secure manner.

Databases

A database is a virtual object that represents a database connection to a target database. Each database is defined with a database engine type along with a set of engine-specific connection properties that specify how to succesfully connect to the target database.

The following types of database connections are supported:

ElevateDB

DBISAM

ADO/ODBC

Each database can be assigned an access privilege that determines if a database is accessible for a given user. Please see the Web Server Security topic for more information on roles and privileges.

DataSets

Within each database are datasets, which are virtual objects that represent a set of rows. DataSets are purely virtual and are only containers for a set of dataset commands. Each dataset can be configured so that all date/time column values are localized when read/written from/to the underlying database table(s).

DataSet Commands

Within each dataset are dataset commands, which are containers for engine-specific SQL statements/calls that determine which commands can be executed for the dataset. These SQL statements/calls use named input parameters (:ParamName) to control which rows are selected, updated, or deleted, and named input-output or output parameters (:ParamName) to retrieve autoinc/identity/generated column values after rows are inserted or updated during a transaction. There are two types of dataset commands:

Command Type

Description

Dataset	These commands execute SQL statements that perform row operations such as SELECT, INSERT, UPDATE, and DELETE DML statements.
BLOB Column	These commands execute SQL statements that perform BLOB column operations such as SELECT and UPDATE DML statements, as well as retrieving the MIME content type of BLOB columns, and are named according to the BLOB column that is being operated on. This architecture optimizes BLOB column access performance and allows for the selective updating of BLOB columns.

There are four supported dataset commands:

Command Name

Description

Select	Contains an SQL statement that returns a result set. If the user cannot execute the Select command, then the dataset is effectively "invisible" to the user unless the dataset is being accessed in a server application. In addition to this command, you will also need to add column-level commands for returning the data from any BLOB columns that are present in the result set, as specified below
Insert	Contains an SQL statement that inserts a new row into the dataset. If the user cannot execute the Insert command, then the user cannot insert any rows into the dataset unless the dataset is being updated in a server application. You should not include BLOB columns in the SQL statement. Instead, specify any BLOB columns as individual column updates as specified below.
Update	Contains an SQL statement that updates an existing row in the dataset. If the user cannot execute the Update command, then the user cannot update any rows in the dataset unless the rows are being updated in a server application. You should not include BLOB columns in the SQL statement. Instead, specify any BLOB columns as individual column updates as specified below.
Delete	Contains an SQL statement that deletes an existing row in the dataset. If the user cannot execute the Delete command, then the user cannot delete any rows in the dataset unless the rows are being deleted in a server application.

There are two supported BLOB column commands, with a variation for the Update command that is used to update the content type for the BLOB column in a given row:

Command Name

Description

Select<Column Name>	Contains an SQL statement that returns a result set containing the specified BLOB column and, optionally, the MIME content type column for the BLOB column data, in a single row. If the user cannot execute the column command, then the user cannot read the BLOB column unless the BLOB column is being read in a server application. The MIME content type column is used by the web server to assign the Content-Type response header for any access to the BLOB column. The name of the MIME content type column should be set to <Column Name>_ContentType in order for the MIME content type column to be used correctly by the database access in the web server. The BLOB MIME content type column is optional. However, if it does not exist, then the user agent or server application retrieving the data will need to be able to determine the format of the data through other means.
Update<Column Name>	Contains an SQL statement that updates the specified BLOB column in an existing row in the dataset. This type of command only applies to server applications. It is currently not possible to update BLOB columns using client applications.
Update<Column Name>_ContentType	Contains an SQL statement that updates the MIME content type for the specified BLOB column in an existing row. This content type is used by the web server to assign the Content-Type response header for any access to the BLOB column. This type of command is optional. If you do not wish to allow an update to the MIME content type for a BLOB column, then you do not need this type of command for the BLOB column.

The dataset command names must match (case-insensitive) the above command names in order for them to be executed correctly for the dataset.

The dataset commands can be generated for a given dataset using the administration API. Please see the Web Server Administration API - Databases topic for more information.

Each dataset command can be assigned an execution privilege that determines if a dataset is accessible for a given user, or whether the user can execute a given dataset command. Please see the Web Server Security topic for more information on roles and privileges.

Database Operations

The database operations are universal in the web server, meaning that they function very similarly for both client and server applications. The primary differences between the client and server application database operations are:

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. 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.

It is always recommended that you implement additional privileges for accessing functionality in server applications and use them to restrict access to any personal information or other types of sensitive data. It is important to remember that users can authenticate against the web server as the built-in Anonymous user. Please see the Web Server Security topic for more information.

BLOB columns can only be updated using server applications. Client applications can only read BLOB data and display it using a properly-constructed BLOB column database access API URL. These URLs are constructed automatically in client applications by the database access components. Please see the Web Server Database Access API topic for more information on how the database access API URLs are structured.

There are five types of database operations used in the web server:

Operation

Description

Get DataSet Columns	This operation executes the Select dataset command (without any input parameters) and returns the columns for the dataset.
Get DataSet Parameters	This operation executes the Select dataset command (without any input parameters) and returns the parameters for the command.
Get DataSet Rows	This operation executes the Select dataset command and returns the rows for the dataset.
Get DataSet BLOB Column	This operation executes the Select<Column Name> dataset command and returns the data in the specified BLOB column along with an optional <Column Name>_ContentType column that specifies the MIME content type for the BLOB column data.
Commit Transaction	This operation commits a transaction that was previously started automatically or manually by the client or server application. Each transaction contains a series of row inserts, updates, and deletes that are executed in the order in which they occurred using the Insert, Update, Update<Column Name>, and Delete commands. For server applications, a transaction commit will update any BLOB columns that were modified after each insert or update. Each transaction commit is bracketed with an actual database transaction for the underlying database connection, making all transactions atomic.

Transactions can be nested in both client and server applications, and can be started automatically or manually. If automatic transactions are enabled (the default), then any insert, update, or delete operation in the dataset will cause a transaction to be started. After the insert, update, or delete operation completes successfully, the automatic transaction is then committed. Please see the Transactions topic for more information.

More Support Options

Support Forums

Frequently Asked Questions

Technical Bulletins

Technical Articles

Incident Reports

Roadmap