Configuring SaveToDB

Configuring SaveToDB

Configuring Features

The SaveToDB add-in adds many features to Microsoft Excel from-the-box.

Database developers may add power features using the server-side configurations:

  • Connecting to database objects, web data sources, and text files using the ribbon query list.
  • Saving data from views, stored procedures, SQL codes, and HTTP and text file queries to a database.
  • Saving data to a database using cell Change event handlers.
  • Drill-down queries to databases and the web on DoubleClick events.
  • Master-detail interfaces using SelectionChange event handlers.
  • Different actions for the Excel context menu and SaveToDB Actions menu including executing procedures, macros, and Windows Shell and CMD commands.
  • Translation of objects, fields, and parameters to a business language.
  • Parameter value lists.
  • Databases to store and distribute Excel table formats of database objects.
  • Excel formulas including DDE and RTD (Real-Time Data) in views and stored procedures.

This section describes SaveToDB add-in requirements for server-side configurations.

You may create the required server-side objects yourself or just install SaveToDB Framework using the Application Installer.

Then use the Application Workbooks wizard to create a workbook to customize application features in Microsoft Excel.

You may try online examples from the wizards menu to find the required techniques.

You may download SaveToDB SDK with source codes of the frameworks and examples at www.savetodb.com.

Configuration topics:

Query Lists

When users connect to a database, they see a complete list of database objects by default.

They may select a specific schema to reduce the list of objects.

Later, users use the same list in the query list at the ribbon to change query objects.

You may configure such object lists using special query list views.

Moreover, you may add new objects based on SQL codes, HTTP queries, and text files to lists and save loaded data to databases.

See Code-based Objects for details.

Query list views also allow configuring saving changes.

Configuration Specification

SaveToDB reads the query list configuration from tables and views with the following fields:

  1. An optional primary key column like ID
  2. TABLE_SCHEMA *
  3. TABLE_NAME
  4. TABLE_TYPE
  5. TABLE_CODE
  6. INSERT_PROCEDURE
  7. UPDATE_PROCEDURE
  8. DELETE_PROCEDURE
  9. PROCEDURE_TYPE

* The add-in does not use the TABLE_SCHEMA field values with Microsoft SQL Server Compact and SQLite.

TABLE_SCHEMA and TABLE_NAME define objects.

TABLE_TYPE defines object types and may contain values: TABLE, VIEW, PROCEDURE, CODE, HTTP, TEXT, and HIDDEN.

You may specify the HIDDEN type to exclude database objects from query lists shown in Excel.

TABLE_CODE defines an SQL code, URL, or a text file query.

INSERT_PROCEDURE, UPDATE_PROCEDURE, and DELETE_PROCEDURE configure how to save changes and can have NULL values.

PROCEDURE_TYPE defines the type of edit procedures and may contain values: TABLE, PROCEDURE, and CODE.

Creating Query List Views

To configure a query list, create a view with the fields described above.

You may select objects from INFORMATION_SCHEMA views directly.

However, it is much simpler to select objects from SaveToDB Framework query list views like xls.queries (SaveToDB Framework 8) or xls01.viewQueryList (prior versions).

Saving Changes

The SaveToDB add-in allows saving data changes back to database tables and updatable views, and OData web services by default.

Just click the Save button to save changes.

This topic shows how to configure saving changes using the Save button for data loaded from views, stored procedures, SQL codes, HTTP queries, and text files.

Note that you may also use other techniques:

Basic Ways to Save Changes

There are two basic ways to save changes of data loaded from any data source:

  • To update a single target ("base") table;
  • To update multiple target tables.

In the first case, it is enough to specify the base table. The add-in generates and uses the required INSERT, UPDATE, and DELETE commands automatically.

In the second case, you have to provide SQL codes or stored procedures for INSERT, UPDATE, and DELETE operations ("edit procedures").

Parameters of Edit Procedures

Edit procedures may contain parameters with the names of selected fields.

For example, the select procedure has the following code:

SELECT ID, [Date], [Sum], CompanyID, ItemID FROM dbo.Payments

We may use the following SQL codes to update the underlying table (INSERT, UPDATE, and DELETE codes):

INSERT INTO dbo.Payments ([Date], [Sum], CompanyID, ItemID) VALUES (@Date, @Sum, @CompanyID, @ItemID)

UPDATE dbo.Payments SET [Date] = @Date, [Sum] = @Sum, CompanyID = @CompanyID, ItemID = @ItemID WHERE ID = @ID

DELETE FROM dbo.Payments WHERE ID = @ID

Use the :ParameterName form like :Date for Oracle, DB2, MySQL, PostrgeSQL, and NuoDB.

This model is simple and effective.

However, it requires that the selected fields must contain no spaces.

You may create columns for parameter values by formulas. This is useful for importing data from the Web and text files.

Using row values of selected fields as parameter values is the main scenario.

However, if the Excel table does not contain the column with the parameter name, the add-in looks for the parameter value in other places in the following order:

  1. A ribbon parameter with a parameter name (the parameter of the select query);
  2. A named cell with a parameter name;
  3. A built-in variable.

Specifying Target Tables and Edit Procedures

The add-in looks for target tables and edit procedures in the following order:

  1. Query list configurations;
  2. Object configurations;
  3. Name considerations;
  4. Automatic detection.

Query List Configurations

When users connect to a database using the Connection Wizard, they may select a configured query list table or view as a list of objects.

Such tables or views must contain the following fields:

  1. An optional primary key column like ID
  2. TABLE_SCHEMA *
  3. TABLE_NAME
  4. TABLE_TYPE
  5. TABLE_CODE
  6. INSERT_PROCEDURE
  7. UPDATE_PROCEDURE
  8. DELETE_PROCEDURE
  9. PROCEDURE_TYPE

* The add-in does not use the TABLE_SCHEMA field values with Microsoft SQL Server Compact and SQLite.

You may use the INSERT_PROCEDURE, UPDATE_PROCEDURE, and DELETE_PROCEDURE fields to specify:

  • A base table (the same table name in all fields);
  • Stored procedure names (including schemas) for INSERT, UPDATE, and DELETE operations accordingly;
  • SQL codes for INSERT, UPDATE, and DELETE operations accordingly.

Also, you may specify the value in the INSERT_PROCEDURE field only:

  • A target table to merge data;
  • A stored procedure name (including a schema) for the MERGE operation;
  • An SQL code for the MERGE operation.

In the merge mode, the add-in executes operations for all rows in an Excel table on the Save button click.

The merge mode is useful for importing data from external data sources.

You may use the MergeDate or merge_date built-in variables as parameters to detect deleted rows in external data sources.

Using query list views is a main method to configure saving changes in the previous SaveToDB versions.

It has the highest priority in SaveToDB 8 also.

It is a good method.

However, user must select the query list view explicitly. It does not work by default.

Object Configurations

SaveToDB 8 introduces a new type of configuration objects.

It reads object configrations from tables and views with the following fields:

  1. An optional primary key column like ID
  2. TABLE_SCHEMA *
  3. TABLE_NAME
  4. TABLE_TYPE
  5. TABLE_CODE
  6. INSERT_OBJECT
  7. UPDATE_OBJECT
  8. DELETE_OBJECT

* The add-in does not use the TABLE_SCHEMA field values with Microsoft SQL Server Compact and SQLite.

Contrary to query list views, the add-in applies this configuration to any object.

So, users may select a default query list or a specific schema, and they will have configured objects.

SaveToDB Framework 8 creates the xls.objects table that implements this specification.

The new configuration object has renamed fields (INSERT_OBJECT, UPDATE_OBJECT, and DELETE_OBJECT) to avoid mixing with query list configurations.

The fields have the same meaning and features.

Name Considerations

The SaveToDB add-in links edit procudures with the _INSERT, _UPDATE, and _DELETE suffixes to objects with the name without suffixes or with the _SELECT suffix.

For example, you may have the following objects in a database:

  • dbo.uspPayments_select
  • dbo.uspPayments_insert
  • dbo.uspPayments_update
  • dbo.uspPayments_delete

In this case, the add-in links the edit procedures to the dbo.uspPayments_SELECT object automatically.

Automatic Detection

SaveToDB 8 loads and caches complete database metadata.

It analyzes object definitions (if a user has the VIEW DEFINITION permission) and may configure saving changes automatically.

For example, a stored procedure has the code like this

SELECT * FROM dbo.Payments WHERE COALESCE(CompanyID, 0) = COALESCE(@CompanyID, CompanyID, 0)

The add-in enables the Save button and saves data changes to the dbo.Payments table.

To enable this feature, grant VIEW DEFINITION permissions on underlying tables, views, and procedures to users.

Technical Remarks

Checking Configuration

Use the Workbook Information dialog box to check the loaded configurations.

Use the Check SQL item of the Save menu to check the generated SQL code used to save data changes.

Row Numbers

To be saveable, the Excel table must have row numbers turned on.

To turn on row numbers, run the Excel Data, Connections, Properties dialog box and check Include row numbers.

Alternatively, run the Reload, Configure Connection... dialog box, click Next, then Finish.

Hidden Data Sheets

SaveToDB saves the loaded data required to save changes on hidden workbook sheets.

Due this, users may close and reopen workbooks multiple times before saving changes to a database.

The add-in inserts such worksheets during the connection using the Connection Wizard.

You may manage worksheets using the Options dialog box.

Transaction Mode

SaveToDB saves the changes in one transaction under the Read Committed isolation level.

If any row cannot be saved, the database server rollbacks the entire transaction.

Event Handlers

The SaveToDB add-in allows processing Excel events like CellChange, SelectionChange, or DoubleClick on the server-side.

So, you may update database data on cell changes or implement master-detail interfaces and drill-down queries.

The SaveToDB add-in also allows customizing application behavior on the server-side.

You configure these features using event handler configurations.

You may install SaveToDB Framework into your database and use the xls.handlers table, or to create your own as described below.

Configuration Specification

SaveToDB reads the event handler configuration from tables and views with the following fields:

  1. An optional primary key column like ID
  2. TABLE_SCHEMA *
  3. TABLE_NAME
  4. COLUMN_NAME
  5. EVENT_NAME
  6. HANDLER_SCHEMA *
  7. HANDLER_NAME
  8. HANDLER_TYPE
  9. HANDLER_CODE
  10. TARGET_WORKSHEET
  11. MENU_ORDER
  12. EDIT_PARAMETERS

* The add-in does not use the TABLE_SCHEMA and HANDLER_SCHEMA field values with Microsoft SQL Server Compact and SQLite.

TABLE_SCHEMA and TABLE_NAME define schemas and names of configured database or code-based objects.

COLUMN_NAME specifies a column name. Set the NULL value to apply the handler to the entire table.

EVENT_NAME specifies an event name. See details below.

HANDLER_SCHEMA and HANDLER_NAME define schemas and names of database or code-based objects used to process events.

HANDLER_TYPE defines a handler object type. See details below.

HANDLER_CODE contains a code-based object code or additional parameters like a field list to select from tables and views.

TARGET_WORKSHEET defines an output target. See details below.

MENU_ORDER allows sorting menu items. Use integer values.

EDIT_PARAMETERS allows changing the default behavior for launching menu items: 1 - confirm parameters, 0 - run without confirmation.

Event Types

The EVENT_NAME field may contain the following values:

  • Actions, ContextMenu
  • Change
  • DoubleClick
  • SelectionChange
  • ConvertFormulas, DoNotConvertFormulas
  • DoNotSelect, DoNotSave, DoNotChange, ProtectRows, DoNotAddValidation
  • Formula, FormulaValue
  • AddStateColumn
  • DefaultListObject
  • SyncParameter
  • ManyToMany, DoNotAddManyToMany
  • KeepFormulas, DoNotKeepFormulas
  • ParameterValues
  • ValidationList, SelectionList

Action-Based Handlers

The SaveToDB add-in adds items of the Actions type to the Actions menu and items of the ContextMenu type to the Excel context menu.

The add-in executes the handlers of the Change type on cell change, DoubleClick type on double-click, and the SelectionChange type on selection change.

Usually, you implement such handlers as procedures or SQL codes. The add-in passes values of the current table row as parameters.

Declarative Handlers

Handlers of this group configure the add-in behavior only.

ConvertFormulas handlers force converting text formulas from a database to Excel formulas in the specified column. See Using Formulas.

DoNotConvertFormulas handlers prevent converting text formulas from a database to Excel formulas in the specified column.

DoNotSelect handlers prevent selecting the column in the Connection Wizard (however, users may edit the query in Excel manually).

DoNotSave handlers exclude the specified column from the INSERT and UPDATE statements used to save changes or prevent saving changes for the entire table.

The add-in prevents changes in columns marked with the DoNotChange event type.

Also, the add-in prevents adding or deleting rows in tables marked with the ProtectRows event type.

DoNotAddValidation handlers prevent adding automatic validation lists.

The add-in replaces column values with an Excel formula specified in the HANDLER_CODE field for the Formula handlers.

The add-in replaces column values with values calculated by a formula specified in the HANDLER_CODE field for the FormulaValues handlers.

You may use Excel formulas or special built-in formulas: =DomainUserName() and =UserName().

The AddStateColumn handler defines adding the _State_ column to an Excel table. Users may set 0 in this column to delete rows.

The DefaultListObject handler defines the default table on a worksheet. The add-in shows ribbon elements for such tables even the active cell is outside of the table.

The SyncParameter handler links parameters for two objects like an @order_id parameter of order_header and header_details objects.
Specify opposite handlers for both objects.

Use the ManyToMany handler to mark fields of foreign keys in tables with many-to-many relationships. The add-in activates a special mode to edit such relations.

The add-in adds ManyToMany handlers automatically when it detects many-to-many relationships. Use the DoNotAddManyToMany handler to disable this feature for the specified table.

Use the KeepFormulas handler to specify a COLUMN_NAME column used to save and restore user formulas.
Other users get updated formulas after reload too. For example, finance managers can see formulas used to calculate budget values in budget requests.

Use the DoNotKeepFormulas handler to disable saving formulas for the entire table or only for the column specified in the COLUMN_NAME field.
Disable columns, for example, to always have actual values in Excel.

Parameter Values

ParameterValues items configure queries used to populate ribbon parameter value lists.

SaveToDB 8 allows using event handler configuration for this. The previous versions use separate tables.

See details in Parameter Value Lists.

Validation and Selection Lists

The ValidationList handlers define queries used as a source of Excel validation lists.

If the query returns a single column, the add-in just loads values into the hidden SaveToDB_Lists worksheet and create a validation list for the specified column.

If the query returns two or more columns, the add-in additionally converts loaded column values of the underlying table to names.

The SelectionList handlers allow selecting values using the List Editor task pane and do not modify the underlying table.

Handler Types

The HANDLER_TYPE field may contain the following values:

  • PROCEDURE, FUNCTION, TABLE, VIEW
  • CODE, HTTP, TEXT
  • MENUSEPARATOR, MACRO, CMD, REFRESH
  • RANGE, VALUES
  • PDF, REPORT
  • SELECTSHEET
  • SHOWSHEETS, HIDESHEETS

Use the PROCEDURE, FUNCTION, TABLE, and VIEW values for database objects used to process events.

Use the CODE, HTTP, and TEXT values for code-based objects.

Use the MENUSEPARATOR type to define menu separators of the Actions menu and the Excel context menu.

You may use a non-unique name like MenuSeparator for such items in the HANDLER_NAME field. However, specify different values in the MENU_ORDER field.

The MACRO type defines an Excel VBA macro. Place the name of the macro and its optional parameters into the HANDLER_CODE field.

The CMD type defines a Windows Shell or CMD commands. Place the command and its optional parameters into the HANDLER_CODE field.

The REFRESH type defines a command used to refresh data tables on the worksheets specified in the TARGET_WORKSHEET field, separated by commas.

For example, this feature allows refreshing data tables used as sources of validation lists.

The MENUSEPARATOR, MACRO, CMD, and REFRESH types are applicable for Actions and ContextMenu event handlers only.

The RANGE type defines an Excel range used as a data source for the ParameterValues, ValidationList, and SelectionList handlers.
Specify a range or a named cell name in the HANDLER_CODE field.

The VALUES type defines values used as a data source for the ParameterValues, ValidationList, and SelectionList handlers.
Specify values separated by comma or semicolon in the HANDLER_CODE field.

Use the PDF type to create a PDF document. You may define target sheets separated by a comma in the TARGET_WORKSHEET column.

Use the REPORT type to create an Excel report workbook. You may define target sheets separated by a comma in the TARGET_WORKSHEET column.

Use the SELECTSHEET type to select a sheet specified in the TARGET_WORKSHEET field.
The field can contain a sheet name or a name of a target database object.

Use the SHOWSHEETS type to show sheets and the HIDESHEETS type to hide sheets specified in the HANDLER_CODE field.
The field can contain a comma- or semicolon-separated list of sheets or names of database objects.
Also, the TARGET_WORKSHEET field can contain a sheet to select.

.

Output Targets

The TARGET_WORKSHEET field may contain the following values:

  • <Sheet name> | _new [ _TopWindow | _LeftWindow | _RightWindow | _BottomWindow ]
  • <Sheet names separated by a comma>
  • _self
  • _none, _browser
  • _popup | _taskpane [ _transpose | _transposeauto]
  • _reload
  • _saveas [<File name>[;CodePage=<Code page>][;Format=CSV][;Separator=;/,/Tab]]

Use a sheet name and an optional window position for drill-down and master-detail queries that load data to worksheets.

Specify sheet names to use with the PDF and REPORT types.

The _new value defines creating a new worksheet.

The _self value defines reloading the configured object with a new query specified by a handler.

The _none value defines executing the query in a silent mode.

The _browser value defines opening the HTTP query in a browser.

The _popup value defines showing query results in a popup window.

The _taskpane value defines showing query results in a task pane window.

Popup windows and task panes have a different behavior.
Popup windows are query independent. So, users must close popup windows explicitly.
Task panes are related to source worksheets, and the add-in shows and hides task panes itself.

The _transpose option defines transposing query results (rows become columns, and columns become rows).

This option is useful to show details selected as a single row in a two-column form.

The _transposeauto option defines transposing single row results only.

Use the _reload option to reload the source table after executing the query.

Use the _saveas option to export handler query data to CSV files in one click from the Actions menu.

Default Behavior

The add-in opens HTTP queries in the default web browser.

The add-in shows single-cell results in the internal web browser and other results in the popup windows.

For example, you may return HTML or styled XML as a single value to show it in the internal web browser.

Handler Parameters

Handlers may have parameters to get values from table cells, named cells, ribbon parameters.

See Using parameters.

Using Parameter Values in Context Menu Item Names

You may include parameter values into handler names of non-database objects using the {ParameterName} form.

For example: Open website - {Website}

Translating Context and Action Menu Items

You may translate handler names to multiple languages using the TRANSLATED_NAME field of the object name translation configuration.

Customizing Queries from Tables and Views

You may customize queries from tables and views using the HANDLER_CODE field.

You may specify comma-separated field names to select.

For example:

id, name

Also, you may use the following modifiers as field name prefixes:

+ORDER BY ASC
-ORDER BY DESC
@WHERE

For example, if you add the following code to the HANDLER_CODE field for the dbo.view_subcategories view:

id,+name,@category

The add-in will generate the following SQL code:

SELECT [id], [name] FROM [dbo].[view_subcategories] WHERE [category] = @category ORDER BY [name]

As you may see, this is a light-way alternative to using SQL codes described below.

Using SQL Code

You may use SQL codes in the HANDLER_CODE field.

For example:

SELECT * FROM dbo.StockTradeHistory th WHERE th.Symbol = @Symbol

See Code-Based Objects.

Using HTTP Queries

You may use HTTP queries in the HANDLER_CODE field.

For example:

http://www.google.com/finance/historical?q={Symbol=AAPL}

Use HTTP query handlers to add documentation links to the Actions menu.

See Code-Based Objects.

Using Text File Queries

You may specify file names and code pages can in the HANDER_CODE field in the following format:

<File name>[;CodePage=<Code page>]
For example:
Contacts.csv;CodePage=65001

See Code-Based Objects.

Running Macros

You may configure the add-in to launch Excel macros. This way gives additional benefits:

  • All possible user actions are located in two menus: the Excel context menu and the SaveToDB Actions menu.
  • Macros are linked to specific queries and are available immediately after connecting data.
  • Macros can have input parameters from different value sources as described above.

Macros can be located in active workbooks or in separate macro workbooks that can be updated independently.

Specify macros in the HANDLER_CODE field in the following format:

<Excel macro> [<Parameter1>[, <Parameter2>[, ...]]

Macros may use context values in the following format: {ParameterName=DefaultValue} or simply {ParameterName}

For example:

SayHello "World"
SayHello {Name=World}
Sheet1.SayHello {FirstName}, {LastName}
MacroWorkbook.xlsm!SayHello {FirstName}, {LastName}

Note that a macro sheet name is an internal Excel name, not a user visible name.
You may check macro names using the Excel dialog box for launching macros (Alt-F8).

Important! SaveToDB does not check macros. Run macros from trusted sources only!

Running Windows Shell and CMD Commands

SaveToDB allows loading and refreshing data from text files in the following formats: XML, JSON, HTML, and CSV.

You may use Windows Shell and CMD commands for loading, preparing, or checking such files.

Specify commands in the HANDLER_CODE field in the following format:

<Command> [<Parameter1>[ <Parameter2>[ ...]]

Commands can use context values in the following format: {ParameterName=DefaultValue} or simply {ParameterName}

Command examples:

{FileName}
notepad.exe {FileName}
dir *.*
cmd /c dir {Mask=*.*}
cmd /k dir {Mask=*.*}
sayhello.cmd {FirstName}
mailto:{Email}&subject=Thanks for the connection&body=Hello {FirstName},%0A

The latest example shows how to launch a new email using the mailto: command.
The mailto: handlers are added to menus for non-empty recipients only.

The cmd /c mode is used to execute a command and to close the window after execution, and the cmd /k mode is used to leave the window opened.

The working directory is the active workbook directory if the workbook has been already saved.

Important! SaveToDB does not check commands. Run commands from trusted sources only!

Refreshing Queries using REFRESH Handlers

Microsoft Excel allows validating user input. See Data, Data Tools, Data Validation.

Values from database queries can be used as validation lists.

For example, a contact category list can be used for the validation of category column values in a contact list.

In addition, such validation lists should be updated regularly and consistently.

You may use the REFRESH handler added to the Actions menu. Specify the worksheets that contain validation lists in the TARGET_WORKSHEET field.

It is a good practice to place the validation lists on one or two hidden sheets.

Checking and Debugging Configuration

Use the Workbook Information dialog box to check the loaded configurations.

Use also logging SQL queries using the Options dialog box.

Creating Database Configuration Objects

You may create the configuration tables or views yourself.

This is useful when your application must create the configuration dynamically.

In other cases, just install SaveToDB Framework to your database using the Application Installer.

@..\source\/savetodb-configuring.htm#configuring-parameter-values

Data Translation

Users want to see business names in Excel instead of database ones.

For example, "Manager Name" is much better then "MGR_NAME" and "Budget Form" is much better then "xls41.uspBudgetForm".

As a result, database developers create (and modify then) a lot of views and procedures to show data with required business names.

The SaveToDB add-in allows solving this task much simpler.

The add-in reads translations from databases and applies the translation within Excel.

Just install SaveToDB Framework to your database and use a configuration workbook to translate names of database objects, fields, and parameters.

Also, you may create own configuration objects as described in the specification below.

Users may select an actual data translation language using the Options dialog box.

To reload changed translation, use the Reload, Reload Data and Configuration button for column and parameter names, and the Reload Query List button for object names.

Also, you may create special named cells to show values from translation tables in worksheets.

Configuration Specification

SaveToDB reads translations from tables and views with the following fields:

  1. An optional primary key column like ID
  2. TABLE_SCHEMA *
  3. TABLE_NAME
  4. COLUMN_NAME **
  5. LANGUAGE_NAME
  6. TRANSLATED_NAME
  7. TRANSLATED_DESC
  8. TRANSLATED_COMMENT ***

* The add-in does not use the TABLE_SCHEMA field values with Microsoft SQL Server Compact and SQLite.

** The add-in also reads object name translations from tables and views in the previous format without the COLUMN_NAME field.

*** The add-in also reads column and parameter translations from tables and views in the previous format without the TRANSLATED_COMMENT field.

The SaveToDB Framework 8 creates the xls.translations table following the specification.

TABLE_SCHEMA and TABLE_NAME define objects.

The COLUMN_NAME field defines a column or a parameter.

The LANGUAGE_NAME field must contain the first two characters of a language code like "en" or "fr".

The TRANSLATED_NAME field is a main field. The add-in uses it to translate names in UI elements.

The add-in uses the TRANSLATED_DESC field to show UI element descriptions (if applicable).

The add-in uses the TRANSLATED_COMMENT field to update special named cells.

You may set NULL values in the TABLE_NAME field to define default column and parameter names for all objects in the schema.

Also, you set NULL values in the TABLE_SCHEMA and TABLE_NAME fields to define default column and parameter names for all objects in a database.

Table Formats

When users connect to databases, Excel creates data tables and applies the default formatting.

The default formatting is very simple. And users spend a lot of time formatting tables.

The SaveToDB add-in changes this.

Users may save table formats to databases using the Table Format Wizard, and other users get the same formatting when they connect to a database.

To activate this feature, just install SaveToDB Framework to your database or create own configuration objects as described below.

Use the Table Format Wizard to manage table formats.

Saved Format Properties

The SaveToDB add-in saves and restores the following format properties:

  • Cell formats
  • Conditional formatting
  • Applied auto-filters
  • Table totals
  • Data validation
  • Window properties
  • Page setup properties
  • User's formula columns
  • User's table views

Configuration Specification

The SaveToDB add-in loads and saves table formats from tables and views with the following fields:

  1. An optional primary key column like ID
  2. TABLE_SCHEMA *
  3. TABLE_NAME
  4. TABLE_EXCEL_FORMAT_XML

* The SaveToDB add-in does not use the TABLE_SCHEMA field values with Microsoft SQL Server Compact and SQLite.

TABLE_SCHEMA and TABLE_NAME define objects.

The TABLE_EXCEL_FORMAT_XML field contains table formats as XML and its size must be greater than 32K.

Also, you may deny direct update of underlying tables and create a stored procedure to save table formats with the following parameters:

  1. Schema
  2. Name
  3. ExcelFormatXML

Accordingly, the SaveToDB add-in uses the first found procedure instead of direct updating table format tables.

SaveToDB Framework 8 has the xls.formats table to provide working with table formats.

Workbooks

The SaveToDB add-in allows creating application workbooks using the Application Workbooks wizard.

The wizard contains workbooks for built-in SaveToDB applications that you may install using the Application Installer.

Starting SaveToDB 8.7, you may configure the wizard to allow users to create workbooks for your applications.

This is the cheapest way to distribute workbooks and updates.

Users just run the wizard, specify their connect credentials, and the wizard creates new workbooks with the specified credentials.

If you use the Active Directory authentication, your users specify just a server and a database.

Configuration Specification

The SaveToDB add-in loads custom workbook definitions from tables and views with the following fields:

  1. An optional primary key column like ID
  2. NAME
  3. TEMPLATE
  4. DEFINITION

NAME defines a name shown in the Application Workbooks wizard.

TEMPLATE defines a filename or URL of the workbook used as a template.

DEFINITION defines workbook worksheets and connected tables.

SaveToDB Framework 8.7 includes a ready-to-use table, xls.workbooks, with the following structure:

CREATE TABLE xls.workbooks (
    ID int IDENTITY(1,1) NOT NULL,
    NAME nvarchar(128) NOT NULL,
    TEMPLATE nvarchar(255) NULL,
    DEFINITION nvarchar(max) NOT NULL,
  CONSTRAINT PK_workbooks_xls PRIMARY KEY (ID)
);

CREATE UNIQUE NONCLUSTERED INDEX IX_workbooks_name_xls ON xls.workbooks (NAME);

So, you may install SaveToDB Framework 8.7 to use this feature or just create this table in your database using the framework code.

Workbook Definitions

The workbook definition has the following grammar:

<workbook definition> ::= <sheet definition>
    | <sheet definition> NEWLINE <workbook definition>

<sheet definition> ::= <sheet name> ' = ' <table definition>

<table definition> ::= <query object>
    | <query object> ',' <query list>
    | <query object> ',' <query list> ',' <query list enabled>
    | <query object> ',' <query list> ',' <query list enabled> ',' <address>
    | <query object> ',' <query list> ',' <query list enabled> ',' <address> ',' <query parameters>

<query list> ::= EMPTY | '(Default)' | <query list object>

<query list enabled> ::= EMPTY | 'True' | 'False'

The Application Workbooks wizard shows workbooks that have all the required query objects with the SELECT or EXECUTE permissions.

This feature allows having multiple workbooks in the same database while users see only available workbooks.

You may get the active workbook definition using the Definition tab of the Workbook Information dialog box.

So, you may create the target workbook, point it as a template, and just copy and paste its defininiton using the Workbook Information dialog box.

Below are several examples.

The SaveToDB Framework 8 configuration workbook has the following definition:

objects=xls.objects
handlers=xls.handlers
translations=xls.translations

The SaveToDB Framework 7 configuration workbook has the following definition:

QueryList=dbo01.QueryList,(Default),False
EventHandlers=dbo01.EventHandlers,(Default),False
ParameterValues=dbo01.ParameterValues,(Default),False
ObjectTranslation=dbo01.ObjectTranslation,(Default),False
ColumnTranslation=dbo01.ColumnTranslation,(Default),False

The planning_app_analyst workbook of the Planning Application has the following definition:

data=dbo25.view_data,dbo25.view_query_list,False,$B$3,,{"Parameters":{"id1":null,"id2":53,"id3":17,"id4":"Null","unit_id":null}}
facts=dbo25.view_facts,dbo25.view_query_list,False,$B$3,,{"Parameters":{"id1":"Null","id2":52,"id3":17,"id4":"Null","unit_id":null,"calc_type_id":1}}
dimensions=xls25.usp_dimensions,xls25.view_query_list,False,$B$3,,
accounts=xls25.usp_members,xls25.view_query_list,False,$B$3,,{"Parameters":{"dimension_id":1,"root_id":null}}
times=xls25.usp_members,xls25.view_query_list,False,$B$3,,{"Parameters":{"dimension_id":2,"root_id":null}}
categories=xls25.usp_members,xls25.view_query_list,False,$B$3,,{"Parameters":{"dimension_id":3,"root_id":null}}
entities=xls25.usp_members,xls25.view_query_list,False,$B$3,,{"Parameters":{"dimension_id":4,"root_id":null}}
dim5 (regions)=xls25.usp_members,xls25.view_query_list,False,$B$3,,{"Parameters":{"dimension_id":5,"root_id":null}}
dim6 (products)=xls25.usp_members,xls25.view_query_list,False,$B$3,,{"Parameters":{"dimension_id":6,"root_id":null}}
dim7 (subaccounts)=xls25.usp_members,xls25.view_query_list,False,$B$3,,{"Parameters":{"dimension_id":7,"root_id":null}}

Code-based Objects

The SaveToDB add-in allows using SQL codes, HTTP queries, and text file queries like other database objects.

You may configure these objects in the following configuration fields:

In the first case, users may connect to such objects and select data into Excel tables.

In the second case, for example, you may configure processing Excel events using SQL codes or opening URLs from the context menu.

In the third case, you may use SQL codes to populate ribbon parameter values.

Specify the appopriate object type like CODE, HTTP, or TEXT in the fields like TABLE_TYPE, HANDLER_TYPE, and SELECT_TYPE.

You may use SQL codes in the INSERT_PROCEDURE, UPDATE_PROCEDURE, and DELETE_PROCEDURE fields to save data changes.

SQL Codes

SQL codes can contain parameters in the following formats:

  • @ParameterName for Microsoft SQL Server, Microsoft SQL Server Compact, and SQLite.
  • :ParameterName for Oracle Database, IBM DB2, MySQL, MariaDB, NuoDB, Snowflake, and PostgreSQL.

The SaveToDB add-in shows parameters on the ribbon, and users can change the values.

Here is an example of an SQL code:

SELECT * FROM dbo.StockTradeHistory th WHERE th.Symbol = @Symbol

HTTP Queries

The SaveToDB add-in shows URL parameters on the ribbon and supports the standard scheme of URL parameters: ?ParameterName1=Value1&ParameterName2=Value2...

So, you may paste URLs from a web browser into the fields like TABLE_CODE without any change.

Alternatively, you may redefine query parameters in the following format: {ParameterName=DefaultValue}

Use this to hide technical parameters.

For example, you may define a single parameter, Symbol, instead of URL parameters:

http://query.yahooapis.com/v1/public/yql?q=select%20*%20from%20yahoo.finance.quotes%20where%20symbol%20in%20%28%22{Symbol=YHOO}%22%29&diagnostics=false&env=store%3A%2F%2Fdatatables.org%2Falltableswithkeys

The SaveToDB add-in includes parsers for HTML, XML, JSON, CSV, and plain text data.

So, you will get structured data in Excel.

You may tune parsers adding parser parameters at the end of URL separated by semicolons.

See details in the Connecting to Web Data topic.

Text File Queries

You may specify file names including code pages in the following format:

<File name>[;CodePage=<Code page>][;<Parser parameters>]

For example:

Contacts.csv;CodePage=65001

The file name can contain a relative path to the workbook directory.

In this case, you may move the file with the workbook to another place.

You may define query parameters in the following format: {ParameterName=DefaultValue}

For example:

{FileName=Contacts.csv};CodePage={CodePage=65001}

In this example, the add-in shows the FileName and CodePage parameters on the ribbon, and users can changed them.

The SaveToDB add-in includes parsers for HTML, XML, JSON, CSV, and plain text data.

So, you may connect not only to CSV files.

Parameter Insertions

Below is a summary table of special parameter insertions:

Query TypeType KeywordParameter FormatExamples
SQL code for Microsoft SQL Server,
Microsoft SQL Server Compact, and SQLite
CODE@Parameter[=DefaultValue]SELECT * FROM Sales.Contacts WHERE Name = @Name
SELECT * FROM Sales.Contacts WHERE Name = @Name=ABC
SQL code for Oracle Database, IBM DB2, MySQL,
MariaDB, NuoDB, Snowflake, and PostgreSQL
CODE:Parameter[=DefaultValue]SELECT * FROM SALES.CONTACTS WHERE NAME = :Name
SELECT * FROM SALES.CONTACTS WHERE NAME = :Name=ABC
HTTP queryHTTPStandard HTTP parametershttp://www.google.com/finance/historical?q=GOOG
HTTP queryHTTP{Parameter[=DefaultValue]}http://www.google.com/finance/historical?q={Symbol=GOOG}
https://www.google.com/search?as_q={Query}
Text queryTEXT{Parameter[=DefaultValue]}{FileName};CodePage=65001
{FileName};CodePage={CodePage=65001}
Macro commandMACRO{Parameter[=DefaultValue]}SayHello {Name=World}
Sheet1.SayHello {FirstName}, {LastName}
Windows Shell or CMDCMD{Parameter[=DefaultValue]}{FileName}
notepad.exe {FileName}
dir {Mask=*.*}
cmd /c dir {Mask=*.*}
cmd /k dir *.*
mailto:{Email}&subject=Thanks for the connection&body=Hello {FirstName},%0A

Remark: if the default value is specified, the equal sign "=" must have no leading or trailing spaces.

Using Parameters

Stored procedures and SQL codes may get values from the active table, ribbon parameters, named cells, and the current environment.

Below is a list of sources shown in the priority order from highest to lowest:

Parameter NameScopeValue
A parameter with a table column nameAll typesThe parameter gets a value from the column with the same name.
A parameter with a ribbon parameter nameAll typesThe parameter gets a value from the ribbon parameter with the same name.
Note that the ribbon parameters are being synchronized with Excel named cells.
Parameters with predefined names listed below are not shown on the ribbon.
A parameter with a named cell nameAll typesThe parameter gets a value from the named cell with the same name.
A parameter with a parent query parameter nameEvent handlersFor example, an Excel table has the query: EXEC dbo.uspCustomers @ManagerID=101
Event handlers can use a value of the ManagerID parameter.
@WorkbookName or @workbook_nameAll typesThe active workbook name without the directory
@WorkbookPath or @workbook_pathAll typesThe active workbook directory. The value is empty for new workbooks.
@SheetName or @sheet_nameAll typesThe active sheet name
@DataLanguage or @data_languageAll typesA data language code selected using the SaveToDB Options dialog box
@TableName or @table_nameAll typesA qualified name of the active query object like [schema].[name] or "schema"."name"
@EditAction or @edit_actionEdit proceduresINSERT, UPDATE, DELETE, or MERGE
@JsonColumns or @json_columnsEdit proceduresThe parameter gets a JSON array of column names
@JsonValues or @json_valuesEdit proceduresThe parameter gets a JSON array of current row values
@ChangedRowCount or @changed_row_countEdit proceduresThe total number of changed rows of the current transaction
@ChangedRowIndex or @changed_row_indexEdit proceduresA row index in the changed rowset of the current transaction starting 1
@EventName or @event_nameEvent handlersActions, ContextMenu, Change, DoubleClick, or SelectionChange
@ColumnName or @column_nameEvent handlersThe active column name
@CellValue or @cell_valueEvent handlersThe active cell value. Use the text data type like nvarchar and convert values to the required data type.
@CellNumberValue or @cell_number_valueEvent handlersThe active cell number value. This value is NULL if the cell has a non-number value.
@CellDateTimeValue or @cell_datetime_valueEvent handlersThe active cell datetime value. This value is NULL if the cell has an invalid datetime value.
@CellAddress or @cell_addressEvent handlersThe active cell address
@CellFormula or @cell_formulaEvent handlersThe active cell formula in the A1-style
@ChangedCellCount or @changed_cell_countEvent handlersThe total number of changed cells in the current transaction
@ChangedCellIndex or @changed_cell_indexEvent handlersA cell index in the changed cell set of the current transaction starting 1
@TransactionID or @transaction_idAll typesA unique GUID of the current transaction
@TransactionStartTime or @transaction_start_timeAll typesThe UTC start time of current transaction
@WindowsUserName or @windows_user_nameAll typesThe Windows login of the current user
@SaveToDbVersion or @savetodb_versionAll typesSaveToDB version
@MergeDate or @merge_dateMerge proceduresThe start time of the operation. Use it to detect that the data present in the latest dataset.

Using Named Cells

The add-in allows using named cells in the following scenarios:

  • Setting ribbon parameter values using named cell values.
  • Updating named cells using ribbon parameter values.
  • Updating named cells using query properties.
  • Using named cells to show and edit active row values (aka form fields).

To insert named cells, use the Formulas, Define Name Excel menu.

Use the sheet scope for most cases except the named cells for global parameters.

To insert form fields, you may use the Add Form Fields button of the Form Wizard.

Below is a specification for named cell names:

Named Cell NameDescription
A cell with a parameter nameThe add-in updates the cell with a changed ribbon parameter value and updates query parameters with the changed cell value.
A cell with a parameter name and
the __name suffix (two underscores)
The add-in updates the cell with a changed ribbon parameter value and updates query parameters with the changed cell value.
However, it uses names of values loaded using the ValidationList handlers.
A cell with a parameter name and
the global_ prefix
The add-in updates the cell with a changed ribbon parameter value and updates query parameters with the changed cell value.
The add-in applies these changes to all queries in the active workbook.
A cell with a parameter name and
the global_ prefix and
the __name suffix
The add-in updates the cell with a changed ribbon parameter value and updates query parameters with the changed cell value.
However, it uses names of values loaded using the ValidationList handlers.
The add-in applies these changes to all queries in the active workbook.
field_<ListObjectName>_<ColumnName>The add-in updates the cell with a value of the specified column and updates the cell of the table on the named cell changes.
This behavior allows using named cells as a current row field editor.
Once you have created such cell, the add-in tracks ListObject name changes and renames the cell automatically.
SaveToDB_ElapsedMillisecondsThe add-in updates the cell with the query execution time, in milliseconds.
SaveToDB_ConnectionStringThe add-in updates the cell with the query connection string.
SaveToDB_CommandTextThe add-in updates the cell with the query command text.
SaveToDB_ObjectThe add-in updates the cell with the query object name like <schema>.<name>.
SaveToDB_NameThe add-in updates the cell with the value of the TRANSLATED_NAME field if it has the value; otherwise, it has the same value as SaveToDB_Object*.
SaveToDB_DescThe add-in updates the cell with the value of the TRANSLATED_DESC field*.
SaveToDB_CommentThe add-in updates the cell with the value of the TRANSLATED_COMMENT field*.

* The add-in loads these values from the object translation configuration table or view.
The values depend on the data language selected using the Options dialog box.
You may use these named cells to make report headers and descriptions.

Using Excel Formulas

The add-in has different features depending on formula categories:

  • Formulas selected as a text from views and stored procedures.
  • Column formulas added by users.
  • Cell formulas added by users.

In the first case, the add-in converts text values to Excel formulas, if possible.

For example, a view can contain a column with a formula like '=[@Price]*[@Qty]' to calculate the row sum in Microsoft Excel.

Specify English version formulas and the comma as a parameter separator to use formulas in any localized version of Microsoft Excel.

Developers may disable converting formulas using the DoNotConvertFormulas handler or force converting using the ConvertFormulas handler.

Loading text values into the column may be visible for users. To fix this, use an empty string part in the cell format like "0;-0;;".

In the second case, by default, column formulas exist in the user workbooks only.

Users can use the Table Format Wizard or the Save Table Format and Restore Table Format menu items to save and restore such formula columns from a database.

So, users may share formulas with colleagues themselves.

In the third case, the add-in has a solution starting version 8.15.

The add-in saves and restores cell formulas during the refresh phase automatically.

The option is enabled by default. Users may disable it in the Options dialog box.

Developers may disable it for any database object using the DoNotKeepFormulas handler with the NULL value in the COLUMN_NAME column.

Also, developers may specify the column to hold formulas using the KeepFormulas handler.

In this case, other users that reload data will have the same cell formulas.

Upgrading SaveToDB 7 to SaveToDB 8

This topic describes technical details about upgrading configured applications to SaveToDB 8.

If you have no configured applications, you may upgrade the SaveToDB add-in without worries.

Here are key differences introduced in SaveToDB 8 comparing to the previous versions:

  • SaveToDB 8 loads definitions of all database objects in Reload Data and Configuration.
  • SaveToDB 8 creates automatic configurations of saving changes, event handlers, and parameter value queries.
  • SaveToDB 8 has a changed default query list.
  • SaveToDB 8 has a new SaveToDB Framework with a different structure.
  • SaveToDB 8 applies the xls.objects settings by default.
  • SaveToDB 8 has a new configuration table, xls.workbooks.

SaveToDB 8 is a big step forward as it creates typical configurations based on database object analysis automatically.

However, the first three features may add unwanted features to configured applications.

For example, a view may become editable, or a foreign key column may get a validation list.

These are useful features. However, you may want to disable them.

To disable, use new types of event handlers: DoNotSave and DoNotAddValidation.

To hide an object in the default query list, set the HIDDEN type in the xls.objects table.

The second part is related to SaveToDB Framework 8 discussed below.

Upgrading SaveToDB Framework 7 to SaveToDB Framework 8

We have completely redesigned SaveToDB Framework, to make it simple and easy to learn.

SaveToDB 8 supports and will support previous versions of SaveToDB Framework.

SaveToDB 7 does not support and will never support SaveToDB Framework 8.

So, if you migrate your applications to a new framework, be sure that you have upgraded all the SaveToDB 7 installations.

Here is a mapping of Framework objects and roles:

SaveToDB Framework 7SaveToDB Framework 8
dbo01.QueryListxls.objects
dbo01.EventHandlersxls.handlers
dbo01.ParameterValuesxls.handlers
dbo01.ObjectTranslationxls.translation
dbo01.ColumnTranslationxls.translation
dbo01.TableFormatsxls.formats
xls.workbooks
dbo01.viewQueryListxls.queries (view)
xls01.viewQueryListxls.queries (view)
xls01.viewEventHandlersxls.handlers
xls01.viewParameterValuesxls.handlers
xls01.viewObjectTranslationxls.translation
xls01.viewColumnTranslationxls.translation
xls01.viewTableFormatsxls.formats
dbo01.uspUpdateTableFormatxls.formats
SaveToDB_developersxls_developers
SaveToDB_usersxls_users

Here are key differences:

  • SaveToDB 8 has the single xls.handlers table instead of dbo01.EventHandlers and dbo01.ParameterValues.
  • SaveToDB 8 has the single xls.translation table instead of dbo01.ObjectTranslation and dbo01.ColumnTranslation.
  • SaveToDB 8 has no views and procedures except xls.queries.

SaveToDB Framework 8 has six objects instead of 16.

Also, it has the same object names on any supported database platform.

To migrate from the SaveToDB Framework 5-7 to SaveToDB Framework 8, make the following steps using the Application Installer:

  1. Install SaveToDB Framework 8.
  2. Export current settings to SaveToDB Framework 8 (select SaveToDB Framework 7 and Upgrade 7.x to 8.0).
  3. Remove SaveToDB Framework 7.
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