SaveToDB Framework 7 for PostgreSQL

SaveToDB Framework 7 for PostgreSQL

SaveToDB Framework for PostgreSQL

SaveToDB Framework for PostgreSQL contains ready-to-use solutions for application development using Microsoft Excel and PostgreSQL.

The framework can be easily installed and uninstalled. The framework uses the savetodb_dev and savetodb_xls schemas.

The framework can be installed from the SaveToDB add-in in a couple of minutes. Also, you can generate an Excel workbook to customize SaveToDB features using Microsoft Excel.

See SaveToDB Framework Installer and Configuration Workbook Generator.

 

After installation you can customize required features:

Install and Uninstall

Installation Methods

SaveToDB Framework supports four methods to install or remove the framework:

  1. Installation using the SaveToDB Framework Installer integrated into the SaveToDB add-in.
  2. Installation using the standalone SaveToDB Framework Installer.
  3. Installation using source codes.
  4. Installation using batch files.

The first two methods allow installing or removing the framework in databases on all supported platforms:
Microsoft SQL Server, Microsoft SQL Server Compact, Oracle Database, IBM DB2, MySQL, MariaDB, NuoDB, PostgreSQL, and SQLite.

See SaveToDB Framework Installer.

The source code is located in the "SaveToDB Framework for PostgreSQL" folder:

  • framework-install-en.sql
  • framework-remove-en.sql

The installation using batch files is described below.

Installation Remarks

Database administrator permission is required to install or uninstall the framework.

Grant permissions on the savetodb_xls objects to Excel application end users and on the savetodb_dev objects to Excel application developers after the framework setup.

Only framework objects are removed while framework uninstall. However, this operation removes all configuration data. Be careful.

Install and Uninstall Using Batch Files

The source code and batch files are located in the "SaveToDB Framework for PostgreSQL" folder.

Installing SaveToDB Framework

  1. Run 1-edit-config.cmd and edit the "setup" connection.
  2. Run 2-install-savetodb-framework.cmd.
  3. Run 3-clear-credentials.cmd and clear the "setup" connection password.

The "setup" connection user must have root permissions.

Instead of clearing the setup password on step 3, you may protect gsqlcmd.exe.config:

select the "User protected" option in gConnectionManager.

Uninstalling SaveToDB Framework

  1. Run 1-edit-config.cmd and edit the "setup" connection.
  2. Run framework-remove.cmd.
  3. Run 3-clear-credentials.cmd and clear the "setup" connection password.

Verifying Framework Objects

  1. Run 1-edit-config.cmd and edit the "setup" connection.
  2. Run framework-list.cmd.
  3. Run 3-clear-credentials.cmd and clear the "setup" connection password.

Schemas and Roles

SaveToDB Framework for PostgreSQL has the following schemas:

SchemaDescription
savetodb_xlsThe schema contains the objects that should be available for Excel application end users.
savetodb_devThe schema contains the objects that hold data and should be available for Excel application developers.

Excel application end users must have select permissions on savetodb_xls objects.

Excel application developers, in addition, must have read and write permissions on savetodb_dev objects.

SaveToDB Framework for PostgreSQL creates the following roles:

RoleDescription
savetodb_xls_roleThe role is for Excel application end users.
savetodb_dev_roleThe role is for Excel application developers.

The savetodb_xls_role role permissions are enough to read the SaveToDB configurations.

The savetodb_dev_role role permissions are enough to read and update the SaveToDB configurations.

Query Lists

Preface

SaveToDB allows changing queries using the ribbon Query List.

It is a useful feature as application workbooks can contain fewer worksheets. Also, users can easily connect to new database objects after query list refresh.

The query lists can contain database objects, custom SQL codes, HTTP and text file queries.

The query list configuration views are also used to configure base tables, custom functions, or custom SQL codes to save changes of data loaded from views, functions, SQL codes, HTTP and text file queries.

SaveToDB Framework for PostgreSQL creates database objects to configure these advanced features.

See Configuring Query List, Configuring Saving Changes.

Configuration Data Table

The savetodb_dev.query_list table is used as an additional data source for the savetodb_dev.view_query_list configuration view.

CREATE TABLE IF NOT EXISTS savetodb_dev.query_list (
    id serial NOT NULL,
    table_schema varchar(63) NOT NULL,
    table_name varchar(63) NOT NULL,
    table_type varchar(63) NOT NULL,
    table_code text,
    insert_procedure text,
    update_procedure text,
    delete_procedure text,
    procedure_type varchar(63),
    PRIMARY KEY (id)
);

The table is used to:

  • Define SQL codes for query lists.
    Specify an SQL code in the TABLE_CODE field and the CODE type in the TABLE_TYPE field.
  • Define HTTP queries for query lists.
    Specify an HTTP query in the TABLE_CODE field and the HTTP type in the TABLE_TYPE field.
  • Define text file queries for query lists.
    Specify a text file query in the TABLE_CODE field and the TEXT type in the TABLE_TYPE field
  • Define base tables to save changes of data loaded from views, functions, or SQL codes, or to merge data loaded from any data source including HTTP and text file queries.
    To have the possibility to save changes, specify the table in all INSERT_PROCEDURE, UPDATE_PROCEDURE, and DELETE_PROCEDURE fields.
    To have the possibility to merge data, specify the table in the INSERT_PROCEDURE field only.
  • Define custom functions or SQL codes to save changes of data loaded from views, functions, or SQL codes, or to merge data loaded from any data source including HTTP and text file queries.
    All the INSERT_PROCEDURE, UPDATE_PROCEDURE, and DELETE_PROCEDURE fields must be specified for the save mode, and only the INSERT_PROCEDURE field is specified for the merge mode.

See Configuring Query List, Configuring Saving Changes.

Query List View for Developers

The savetodb_dev.view_query_list view selects all objects available to the user, including SaveToDB Framework objects.

This view is suitable for developer query lists as developers can connect to any object.

Query List View for Users

The savetodb_xls.view_query_list view selects all objects available to the user, excluding SaveToDB Framework and specially named objects.

This view can be used for creating derived views for different business areas.

For example:

CREATE VIEW stocks.view_query_list
AS
SELECT
    t.table_schema
    , t.table_name
    , t.table_type
    , t.table_code
    , t.insert_procedure
    , t.update_procedure
    , t.delete_procedure
    , t.procedure_type
FROM
    savetodb_xls.view_query_list t
WHERE
    t.table_schema IN ('stocks', 'savetodb_xls');

The example view selects database objects in the stocks and savetodb_xls schemas.

Event Handlers

Preface

SaveToDB allows using functions, SQL codes, and HTTP queries for handling the following Microsoft Excel events:

  • Change
  • DoubleClick
  • SelectionChange

Using event handling developers can implement tasks like database data changes, drill-down queries, or master-detail interfaces.

Also, developers can customize the Microsoft Excel context menu and SaveToDB Actions menu to run:

  • Views
  • Functions
  • SQL codes
  • HTTP queries
  • Text file queries
  • Macros
  • Windows Shell and CMD commands

See Configuring Event Handlers.

Configuration Data Table

The savetodb_dev.event_handlers table contains configuration data selected via the savetodb_xls.view_event_handlers configuration view.

CREATE TABLE IF NOT EXISTS savetodb_dev.event_handlers (
    id serial NOT NULL,
    table_schema varchar(63) NOT NULL,
    table_name varchar(63) NOT NULL,
    column_name varchar(63),
    event_name varchar(50) NOT NULL,
    handler_schema varchar(63),
    handler_name varchar(63) NOT NULL,
    handler_type varchar(63),
    handler_code text,
    target_worksheet varchar(256),
    menu_order smallint,
    edit_parameters boolean,
    PRIMARY KEY (id)
);

See field description in the Configuring Event Handlers topic.

The HANDLER_CODE field can contain SQL codes, HTTP and text queries, macro codes, or Windows Shell and CMD commands.

The recommended name format for event handling objects:

  • xl_<event>_<schema>_<name>
  • xl_<event>_<schema>_<name>_<column>

where <schema>_<name> is a base object name, <column> is a base object column, and <event> has the following event name codes:

  • act - Actions
  • chg - Change
  • dbl - DoubleClick
  • mnu - ContextMenu
  • sel - SelectionChange

The objects named as xl_% are excluded from the Query List views.

Configuration View

The savetodb_xls.view_event_handlers view is used by the SaveToDB add-in to get the event handler configuration data.

The view selects the data from the savetodb_dev.event_handlers table.

CREATE OR REPLACE VIEW savetodb_xls.view_event_handlers
AS
SELECT
    t.id,
    t.table_schema,
    t.table_name,
    t.column_name,
    t.event_name,
    t.handler_schema,
    t.handler_name,
    t.handler_type,
    t.handler_code,
    t.target_worksheet,
    t.menu_order,
    t.edit_parameters
FROM
    savetodb_dev.event_handlers t;

The ID field is not required by the SaveToDB add-in. It supports the possibility to save data changes from the view and can be used in OData web services.

Data Translation

Preface

SaveToDB allows translating names of database objects, object fields, and function parameters within Microsoft Excel.

It is a useful feature as the names can be changed in configuration tables without changes of underlying database objects.

In addition, the translation data are used in ScreenTips and table header comments.

See Configuring Data Translation.

Object Translation Table

The savetodb_dev.object_translation table contains database object translation data selected via the configuration view.

CREATE TABLE IF NOT EXISTS savetodb_dev.object_translation (
    id serial NOT NULL,
    table_schema varchar(63) NOT NULL,
    table_name varchar(63) NOT NULL,
    language_name character(2) NOT NULL,
    translated_name varchar(63),
    translated_desc varchar(256),
    translated_comment varchar(2000),
    PRIMARY KEY (id)
);

CREATE UNIQUE INDEX ix_schema_name_language ON savetodb_dev.object_translation
    USING BTREE (
        table_schema,
        table_name,
        language_name
    );

See field description in the Configuring Data Translation topic.

The size of the TRANSLATED_* fields can be changed.

Object Translation Configuration View

The savetodb_xls.view_object_translation view is used by the SaveToDB add-in to get the object translation data.

The view selects the data from the savetodb_dev.object_translation table.

CREATE VIEW savetodb_xls.view_object_translation
AS
SELECT
    t.id,
    t.table_schema,
    t.table_name,
    t.language_name,
    t.translated_name,
    t.translated_desc,
    t.translated_comment
FROM
    savetodb_dev.object_translation t;

The ID field is not required by the SaveToDB add-in. It supports the possibility to save data changes from the view and can be used in OData web services.

Column Translation Table

The savetodb_dev.column_translation table contains database object field and parameter translation data selected via the configuration view.

CREATE TABLE IF NOT EXISTS savetodb_dev.column_translation (
    id serial NOT NULL,
    table_schema varchar(63),
    table_name varchar(63),
    column_name varchar(63) NOT NULL,
    language_name character(2) NOT NULL,
    translated_name varchar(63),
    translated_desc varchar(1024),
    PRIMARY KEY (id)
);

CREATE UNIQUE INDEX ix_schema_name_column_language ON savetodb_dev.column_translation
    USING BTREE (
        table_schema,
        table_name,
        column_name,
        language_name
    );

See field description in the Configuring Data Translation topic.

Column Translation Configuration View

The savetodb_xls.view_column_translation view is used by the SaveToDB add-in to get the column translation data.

The view selects the data from the savetodb_dev.column_translation table.

CREATE VIEW savetodb_xls.view_column_translation
AS
SELECT
    t.id,
    t.table_schema,
    t.table_name,
    t.column_name,
    t.language_name,
    t.translated_name,
    t.translated_desc
FROM
    savetodb_dev.column_translation t;

The ID field is not required by the SaveToDB add-in. It supports the possibility to save data changes from the view and can be used in OData web services.

Object Description View

The savetodb_xls.view_object_description view selects translation data from the savetodb_dev.object_translation table for objects available for connection.

This view can be used as an Excel application home page as a user can read object descriptions and connect to any object using the ribbon Query List.

Parameter Values

Preface

SaveToDB allows using views, functions, or SQL codes to populate ribbon parameter values.

The ribbon parameters are used to change parameters of functions, SQL codes, HTTP and text file queries, or WHERE fields for tables and views.

See Configuring Parameter Values.

Configuration Data Table

The savetodb_dev.parameter_values table contains configuration data selected via the savetodb_xls.view_parameter_values configuration view.

CREATE TABLE IF NOT EXISTS savetodb_dev.parameter_values
(
    id serial NOT NULL,
    table_schema varchar(63) NOT NULL,
    table_name varchar(63) NOT NULL,
    parameter_name varchar(63) NOT NULL,
    select_schema varchar(63),
    select_name varchar(63) NOT NULL,
    select_type varchar(63),
    select_code text,
    PRIMARY KEY (id)
);

CREATE UNIQUE INDEX ix_schema_name_parameter ON savetodb_dev.parameter_values
    USING BTREE (
        table_schema,
        table_name,
        parameter_name
    );

See field description in the Configuring Parameter Values topic.

The recommended name format for objects used in the SELECT_NAME field:

  • xl_val_<parameter_name>

Such objects are excluded from the Query List views.

The SELECT_CODE field can contain SQL codes. In this case, the SELECT_TYPE field must contain the CODE type.

Configuration View

The savetodb_xls.view_parameter_values view is used by the SaveToDB add-in to get the configuration data.

The view selects the data from the savetodb_dev.parameter_values table.

CREATE OR REPLACE VIEW savetodb_xls.view_parameter_values
AS
SELECT
    t.id,
    t.table_schema,
    t.table_name,
    t.parameter_name,
    t.select_schema,
    t.select_name,
    t.select_type,
    t.select_code
FROM
    savetodb_dev.parameter_values t;

The ID field is not required by the SaveToDB add-in. It supports the possibility to save data changes from the view and can be used in OData web services.

Table Formats

Preface

SaveToDB allows storing and distributing Microsoft Excel table formats of database objects using a database.

Database objects are formatted in Microsoft Excel when the objects are connected for the first time, or when required using the Table Format Wizard.

Table formats include the following elements:

  • Cell format.
  • Conditional formatting.
  • Formula columns.
  • Table totals.
  • Applied auto-filters.
  • Print page setup.
  • Window properties.
  • Column visibility.
  • Custom table views.

Use the Table Format Wizard to save, clear, or apply table formats.

See also Configuring Table Formats.

Configuration Data Table

The savetodb_dev.table_formats table contains Microsoft Excel table formats of database objects.

CREATE TABLE IF NOT EXISTS savetodb_dev.table_formats (
    id serial NOT NULL,
    table_schema varchar(63) NOT NULL,
    table_name varchar(63) NOT NULL,
    table_excel_format_xml text,
    PRIMARY KEY (id)
);

CREATE UNIQUE INDEX ix_table_formats_schema_name ON savetodb_dev.table_formats
    USING BTREE (
        table_schema,
        table_name
    );

The savetodb_xls.view_table_formats function is used to update the data.

Configuration View

The savetodb_xls.view_table_formats view is used by the SaveToDB add-in to get the table formats.

The view selects table formats from the savetodb_dev.table_formats table.

CREATE OR REPLACE VIEW savetodb_xls.view_table_formats
AS
SELECT
    t.id,
    t.table_schema,
    t.table_name,
    t.table_excel_format_xml
FROM
    savetodb_dev.table_formats t;

The ID field is not required by the SaveToDB add-in. It supports the possibility to save data changes from the view and can be used in OData web services.

Configuration Function to Save Table Formats

The savetodb_xls.view_table_formats function is used by the SaveToDB add-in to update the savetodb_dev.table_formats table.

Function declaration:

CREATE FUNCTION savetodb_dev.table_format_save (
    Schema varchar(63),
    Name varchar(63),
    ExcelFormatXML text
    )

Version History

Version 7.2, March 20, 2017

[+] New types of event handlers: ConvertFormulas and DoNotConvertFormulas.
[*] Added validation lists for the query_list, event_handlers, and parameter_values tables and derived views.
[*] The event_handlers.handler_name field may have NULL values.

Version 7.1, February 3, 2017

[*] New VALUES and RANGE types in the SELECT_TYPE field.
[*] New VALUES and RANGE types in the HANDLER_TYPE field.

Version 7.0, January 17, 2017

[*] Updated batch files and readme.txt.
SQL codes have no changes.

Version 6.9, June 21, 2016

[+] A new event handler type: ProtectRows.

Version 6.7, April 14, 2016

[+] The framework includes updated validation lists of the EVENT_NAME column of the event handler configuration tables.
Use savetodb-framework-62-upgrade-to-67.sql to update the existing frameworks.
Use Table Format Wizard to reload validation lists of existing Excel configuration tables.
Also, you may create new configuration workbooks using Configuration Workbook Generator.

Version 6.2, December 23, 2015

[+] New event handler types: DoNotSelect, DoNotSave, DoNotChange, Formula, FormulaValue, and ValidationList.
[+] The framework supports PostgreSQL 8.x.
[*] savetodb_dev.view_query_list excludes objects like '%_change'.
[*] You may use the code in the SaveToDB Framework Upgrade 5.0 to 6.2 folder to upgrade existing SaveToDB Frameworks 5.0.

Version 5.2, May 12, 2015

[+]The first version.

System Requirements

Supported Versions of SaveToDB:

  • SaveToDB 5.2 and higher

Supported Versions of PostgreSQL:

  • PostgreSQL 8.0, 8.1, 8.2, 8.3, 8.4
  • PostgreSQL 9.0, 9.1, 9.2, 9.3, 9.4

SaveToDB Examples for PostgreSQL

The SaveToDB SDK setup package contains the Developer Guide for PostgreSQL.

You may deploy the application to your PostgreSQL database using batch files or source codes.

Developer Guide 5.2

This guide shows how to develop Microsoft Excel applications using the SaveToDB add-in with PostgreSQL.

This is a complete guide. Try it.

Installing and Uninstalling Examples

Installing Application Examples for PostgreSQL

Use the following steps to install the application examples for PostgreSQL:

  1. Install example database objects.
  2. Change connection data in Microsoft Excel example workbooks.

Installing Examples

The source codes of examples for PostgreSQL are located in the "source code" subfolders.

To install the example, open the subfolder and do the following:

  1. Run 1-edit-config.cmd and edit the "setup" connection.
  2. Run 2-edit-grants.cmd and edit example users if applicable.
  3. Run 3-install-savetodb-framework.cmd if SaveToDB Framework for PostgreSQL is not installed yet.
  4. Run 4-install-application.cmd.
  5. Run 5-clear-credentials.cmd and clear the "setup" connection password.

You may select in the first step the target server and database, and the setup credentials.

The "setup" connection user must have database administrator permissions (root).

Instead of clearing the setup password on the last step, you may protect gsqlcmd.exe.config by selecting the "User protected" option in gConnectionManager.

Alternatively to batch files, you may use queries from the application-install.sql file to install the examples.

Changing Excel Workbook Connections

Examples contain preconfigured Excel workbooks.

You should change the connection server, database, username and the password in the workbooks if you have changed the default settings in the previous step.

You may use the Change Connection Wizard to modify all connections at once:

  1. Select the SaveToDB tab and run the Change Connection Wizard,
  2. Connect to the target database using the new target username and password.
  3. Select all the connections and click Finish.

Then you should refresh all the data tables. You may use the Reload, Reload All Workbook Tables menu item.

Default usernames and passwords:

ExampleDeveloper loginDeveloper passwordUser loginUser password
Developer Guide for PostgreSQLpayments_devDev_2011#_Xls4168payments_userUsr_2011#_Xls4168

Uninstalling Application Examples for PostgreSQL

To uninstall the application example, open the "source code" folder and do the following:

  1. Run 1-edit-config.cmd and edit the "setup" connection.
  2. Run application-remove.cmd.
  3. Run 5-clear-credentials.cmd and clear the "setup" connection password.

The setup connection user must have database administrator permissions.

Instead of clearing the setup password on the last step, you may protect gsqlcmd.exe.config by selecting the "User protected" option in gConnectionManager.

Alternatively to batch files, you may use queries from the application-remove.sql file.

Command Line Utility gsqlcmd

The batch files to install and remove the examples use the command line utility gsqlcmd.
gsqlcmd allows executing SQL scripts and exporting data against all SaveToDB supported database platforms including PostgreSQL.

SaveToDB installs gsqlcmd and changes the PATH environment variable that allows using gsqlcmd anywhere.

SaveToDB SDK includes the gsqlcmd folder with the executables, and the utility is available from the SaveToDB SDK subfolders only.

Version History

Version 7.2, March 20, 2017

[*] Updated SaveToDB Framework 7.2.

Version 7.0, January 17, 2017

[*] Updated SaveToDB Framework 7.0, batch files, and readme.txt.
[*] Updated example dates.

System Requirements

Supported Versions of SaveToDB:

  • SaveToDB 5.2 and higher

Supported Versions of SaveToDB Framework for PostgreSQL:

  • SaveToDB Framework 5.2 for PostgreSQL and higher

Supported Versions of PostgreSQL:

  • PostgreSQL 9.0, 9.1, 9.2, 9.3, 9.4
This website is using cookies. By continuing to browse, you give us your consent to our use of cookies as explained in our Cookie Policy.OK