ODataDB Sample 02 - Advanced Features

This sample shows advanced ODataDB features used to configure applications.

You can choose a platform:

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 the SaveToDB add-in for Microsoft Excel, the DBEdit desktop application, and ODataDB and DBGate web applications.

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

You can read more about the SaveToDB Framework. We will highlight the used features in the following sections. Below are the complete tables.

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

Drop-down Lists and Parameter Values

ODataDB reads foreign key constraints 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 ODataDB has no access to the source SQL code.

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

You can 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

ODataDB allows saving table changes by default.

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

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

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

Saving Changes using Stored Procedures

ODataDB 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 use the same procedure for several objects, use the xls.objects table.
Here is a sample.

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

Note that you can generate such procedures using the SaveToDB Developer Framework for SQL Server.
You can 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 save changes:

Saving Changes using Cell Change Handlers

ODataDB 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, ODataDB links it automatically.

In other cases, you can define the change handler 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 can use tables and views as change handlers.
In this case, ODataDB 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.
It is enough 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

ODataDB supports using SQL codes like stored procedures.

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

You can 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

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

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

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

So, applications can 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 can edit data in the report directly.

Using Formulas

ODataDB supports a subset of Excel formulas to calculate values on the client side.

You can 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 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, ODataDB sets cell values in the row attributes like data-row_format, data-section, etc.

Also, you can 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

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

ODataDB analyzes SQL definitions of views and stored procedures and configures features automatically,
and the resulting models 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 the available features.

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

ODataDB shows a table of contents for a service document. You can try the following link.

Conclusion

We target ODataDB to database developers.

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

You can install it on Windows and Linux.

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

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

Code Browser



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

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

© 2019-2024 Gartle LLC | www.savetodb.com | ODataDB Guide | Developer Guide | Privacy Policy | Cookie Policy