DBGate Sample 02 - Advanced Features

This sample shows advanced DBGate features used to configure applications.

The sample database has the following tables:

Database Diagram of the Cashbook Sample

SaveToDB Framework

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

You may download the framework for any supported database platform with the SaveToDB SDK.

You may read more here. We will highlight the used features in the following sections. Below are the complete tables.

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

Drop-down Lists and Parameter Values

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

Also, it creates drop-down lists and parameter values for views and procedures if it can parse SQL definitions of objects.

However, usually, end-users have no VIEW DEFINITION permission, and DBGate has no access to the source SQL code.

In the last case, developers can configure the lists the xls.handlers table.

You may try the following samples and change cell and parameter values:

Especially, try the lists in the third example. The list of companies shows only companies related to the selected item.

Below 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 table changes by default.

Moreover, it analyzes SQL definitions of views and stored procedures and detects target tables automatically.

You may try these samples that support saving changes to a table:

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

Saving Changes using Stored Procedures

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

The most simple way is to create such procedures with the _insert, _update, and _delete suffixes.
In this case, it links such procedures to the base object automatically.

If you prefer another convention or want to use the same procedure for other objects, use the xls.objects table.
Here is a sample.

You may take a look at the edit procedures in the code browser below.

Note that you may generate such procedures using the SaveToDB Developer Framework for SQL Server.
You may download it with the SaveToDB SDK. We recommend using the SaveToDB add-in to run procedures.

Here is a sample of the procedure that uses stored procedures to saved changes:

Saving Changes using Cell Change Handlers

DBGate allows using stored procedures and SQL codes to process cell changes to check or save changes.

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

In other cases, you may define the change hander in the xls.handlers table. Here is a sample.

Pay attention to the _Commit word in the TARGET_WORKSHEET column. It commits the changes in the browser table.

Note that you may use tables and views as change handlers.
In this case, DBGate updates the changed cell in the target table or view immediately. Here is a sample.

Try these samples:

We recommend taking a look at the SQL code of the usp_cashbook_change procedure.

It has parameters: @column_name, @cell_value, @cell_number_value, @cell_datetime_value, and @id.
This is enough to make changes to the underlying table. And the code is self-explanatory.

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

Using SQL Codes

DBGate supports using SQL codes like stored procedures.

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

You may use this technique to avoid adding objects into a database that you do not control.
Just ask to install the SaveToDB Framework into your database, and then configure the app features yourself.

Creating Multilingual Applications

DBGate detects a user culture from a URL or browser settings.

DBGate translates UI controls using strings from the odatadb-languages.js file.
You may easily add or edit the desired language yourself.

Also, DBGate passes the language to stored procedures in the @DataLanguage or @data_language parameters.

So, applications may return data in the user's language. We recommend using the xls.translations table for this.

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

Note that the report has a cell change handler. So, you may edit data in the report directly.

Using Formulas

DBGate supports a subset of Excel formulas to calculate values at the client-side.

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

For example, try this sample with running totals.

Conditional Formatting

You have two ways to define conditional formatting rules:

For the first case, try the s02_usp_cash_by_months report. It has rules in the page.css file.

Also, the HTML page contains a list of columns used in the conditional formatting:

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

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

Also, you may use cell classes gt0, eq0, and lt0 to format numbers.

For the second case, try budget reports of 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 such formats.
Use Wizards, Developer Tools, Show Table Format.

Metadata and Service Documents

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

So, a user can see objects, select data, and execute procedures depending on actual permissions.

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

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

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

DBGate shows a table of contents for a service document. You may try the following link.

Conclusion

We target DBGate to database developers.

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

You can install it on Windows and Linux.

And you may create feature-rich corporate web applications using database development skills only.

Home | Sample 01 | Sample 04 | Sample 16 | Sample 17 | Sample 20 | Budgeting | Planning

Code Browser

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

You may download samples for any supported database platform with the SaveToDB SDK.

© 2020 Gartle LLC | www.savetodb.com | Privacy Policy | Cookie Policy