DBGate Sample 02 - Advanced Features

This sample demonstrates advanced DBGate features for configuring applications.

You can choose a platform:

The sample database includes the following tables:

Database Diagram of the Cashbook Sample

SaveToDB Framework

The SaveToDB Framework consists of tables used to configure the SaveToDB add-in for Microsoft Excel, the DBEdit desktop application, and DBGate web applications.

You can download the framework for any supported database platform using the SaveToDB SDK.

For more details, refer to the SaveToDB Framework documentation. We will highlight the features used in the following sections. Below are the complete tables.

You can save formats only with the SaveToDB add-in. DBGate does not utilize the xls.workbooks table.

Drop-down Lists and Parameter Values

DBGate automatically reads foreign key constraints and creates drop-down lists for tables.

It also generates drop-down lists and parameter values for views and procedures if it can parse their SQL definitions.

However, end-users typically lack VIEW DEFINITION permission, so DBGate may not access the source SQL code.

In such cases, developers can configure the lists in the xls.handlers table.

Try the following samples and modify cell and parameter values:

Pay special attention to the lists in the third example. The company list displays only those related to the selected item.

Here are the settings for parameters and drop-down lists:

To define the lists, developers can use tables, views, stored procedures, SQL codes, and fixed value lists.

Saving Changes to Tables

DBGate allows saving changes to tables by default.

It analyzes SQL definitions of views and stored procedures to automatically detect target tables.

Try these samples that support saving changes to a table:

Developers can manually define the target table in the xls.objects table. Here is a sample.

Saving Changes using Stored Procedures

DBGate supports saving changes via stored procedures defined for insert, update, and delete operations.

The simplest approach is to create procedures with the _insert, _update, and _delete suffixes.
In this case, DBGate links these procedures to the base object automatically.

If you prefer a different naming convention or use the same procedure for multiple objects, use the xls.objects table.
Here is a sample.

You can review the edit procedures in the code browser below.

Note that you can generate these procedures using the SaveToDB Developer Framework for SQL Server.
Download it with the SaveToDB SDK. We recommend using the SaveToDB add-in to execute procedures.

Here’s a sample of a procedure that uses stored procedures to save changes:

Saving Changes using Cell Change Handlers

DBGate allows using stored procedures and SQL codes to process cell changes for validation or saving.

The easiest way is to create a procedure with the _change suffix.
In this case, DBGate links it automatically.

For other scenarios, you can define the change handler in the xls.handlers table. Here is a sample.

Notice the _Commit keyword in the TARGET_WORKSHEET column. It commits changes in the browser table.

You can also use tables and views as change handlers.
In this case, DBGate immediately updates the changed cell in the target table or view. Here is a sample.

Try these samples:

We recommend checking the SQL code of the usp_cashbook_change procedure.

This procedure has parameters: @column_name, @cell_value, @cell_number_value, @cell_datetime_value, and @id.
These are sufficient to make changes to the underlying table, and the code is self-explanatory.

Note that you can generate handlers using the SaveToDB Developer Framework for SQL Server.

Using SQL Codes

DBGate supports using SQL codes similar to stored procedures.

You can define SQL-code objects in the xls.objects and xls.handlers tables. Here are the samples:

This technique allows you to avoid adding objects to a database that you do not control.
Simply request the installation of the SaveToDB Framework in your database, and then configure the app features yourself.

Creating Multilingual Applications

DBGate detects user culture from the URL or browser settings.

It translates UI controls using strings from the db-languages.js file.
You can easily add or edit the desired language yourself.

Additionally, DBGate passes the language to stored procedures using the @DataLanguage or @data_language parameters.

This allows applications to return data in the user's language. We recommend using the xls.translations table for this purpose.

For example, select a language and run the following samples.

Note that the report includes a cell change handler, allowing you to edit data directly in the report.

Using Formulas

DBGate supports a subset of Excel formulas for client-side calculations.

You can define formula columns in views, stored procedures, and SQL codes.

For example, try this sample with running totals.

Conditional Formatting

You can define conditional formatting rules in two ways:

For the first approach, try the usp_cash_by_months report, which has rules in the page.css file.

The HTML page includes a list of columns used in the conditional formatting:

<script type="application/odatadb+json">
    {"data_row_fields":["row_format","section","level","row_bold"]}
</script>

DBGate sets cell values in row attributes like data-row_format, data-section, etc.

You can also use cell classes gt0, eq0, and lt0 to format numbers.

For the second approach, check the budget reports from the Gartle Budgeting application.

It loads formatting rules from the xls.handlers table.

You can use a free version of the SaveToDB add-in to convert Excel conditional formatting to these formats.
Utilize Wizards, Developer Tools, and Show Table Format.

Metadata and Service Documents

DBGate creates data service models by reading database metadata under the user's credentials.

This allows users to see objects, select data, and execute procedures based on their actual permissions.

DBGate analyzes SQL definitions of views and stored procedures to configure features automatically,
resulting in models that differ depending on the user's VIEW DEFINITION permission.

Developers can configure application features in the SaveToDB Framework and add users to the xls_users role.
In this case, the model will include all available features.

Try these models generated for users with different permissions and xls_users membership.

DBGate displays a table of contents for a service document. You can try the following link.

Conclusion

DBGate is designed for database developers.

You can use DBGate with SQL Server, Oracle Database, MySQL, and PostgreSQL.

It can be installed on both Windows and Linux.

With DBGate, you can create feature-rich corporate web applications using only your database development skills.

Home | Sample 01 | Sample 04 | Sample 13 | Sample 16 | Sample 17 | Sample 20 | API Samples | Tab Framework | Budgeting | Planning

Code Browser



DBGate automatically creates web forms based on underlying database objects.
For example, it generates the cashbook form using only the s02.cashbook declaration.

You can download samples for any supported database platform using the SaveToDB SDK.

© 2025 Gartle LLC | www.dbgate.net | DBGate Guide | Developer Guide | Privacy Policy | Cookie Policy