ODataDB Sample 02 - Advanced Features

This sample demonstrates advanced ODataDB features for configuring applications.

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 ODataDB and DBGate web applications.

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

For more information, check out the SaveToDB Framework. We will highlight the features used in the following sections. Below are the complete tables.

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

Drop-down Lists and Parameter Values

ODataDB 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, preventing ODataDB from accessing 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:

Be sure to check the lists in the third example. The list of companies only shows those related to the selected item.

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

Developers can define lists using tables, views, stored procedures, SQL codes, and fixed value lists.

Saving Changes to Tables

ODataDB 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

ODataDB supports saving changes through 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, ODataDB automatically links these procedures to the base object.

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

ODataDB allows the use of 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, ODataDB links it automatically.

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

Note 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, ODataDB immediately updates the changed cell in the target table or view. Here is a sample.

Try these samples:

We recommend reviewing 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 parameters 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

ODataDB 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 you do not control.
Just request the installation of the SaveToDB Framework in your database, and then configure the app features yourself.

Creating Multilingual Applications

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

ODataDB also passes the language to stored procedures via 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.

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 within the report.

Using Formulas

ODataDB 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 case, check the usp_cash_by_months report, which has rules defined in the page.css file.

The HTML page also 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>

Consequently, ODataDB sets cell values in the 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 case, check the budget reports of the Gartle Budgeting application.

This application 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.
Utilize Wizards, Developer Tools, and Show Table Format.

Metadata and Service Documents

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

This means a user can see objects, select data, and execute procedures based on their actual permissions.

ODataDB analyzes SQL definitions of views and stored procedures to configure features automatically,
resulting in models that differ based 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.

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

Conclusion

ODataDB is designed for database developers.

It supports SQL Server, Oracle Database, MySQL, and PostgreSQL.

You can install it on both Windows and Linux.

With ODataDB, you can create feature-rich corporate web applications using just 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



ODataDB automatically creates web forms based on underlying database objects.
For instance, 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.odatadb.com | ODataDB Guide | Developer Guide | Privacy Policy | Cookie Policy