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

** The add-in also also supports the configuration in the previous format without this field.

TABLE_SCHEMA and TABLE_NAME define objects.

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

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

** The add-in also also supports the configuration in the previous format without this field.

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

** The add-in also supports the configuration in the previous format without the HANDLER_CODE field. SaveToDB 8 does not support previous format 2.x.

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
  • Formula, FormulaValue
  • AddStateColumn
  • 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.

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().

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

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

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.

Output Targets

The TARGET_WORKSHEET field may contain the following values:

  • <Sheet name> | _new [ _TopWindow | _LeftWindow | _RightWindow | _BottomWindow ]
  • _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.

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.

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.

Parameter Values

The SaveToDB add-in places parameters of stored procedures, SQL codes, HTTP queries, and text file queries on the ribbon.

Users can change parameter values, and the add-in executes the queries with the new values.

By default, a user can specify any value of appropriate data type. User's values are stored in a history.

Also, users can select fields of tables and views to use in the WHERE clause of the query. Such fields are placed on the ribbon also.

By default, the field parameters have value lists as unique column values, and users can select the existing values only.

Database developers may define tables, views, stored procedures, functions, or SQL codes used to populate parameter values.

In this case, users may select values from the list only.

This feature can be used to prevent non-indexing queries from big data tables and views.

The database objects used to select values can have parameters also. So, the second parameter value list can depend on the first parameter value and so on.

You may use this for working with hierarchy data, for example.

To configure this feature, just install SaveToDB Framework to your database using the Application Installer.

Configuration Specification

SaveToDB 8 supports configuration versions 3, 5, and 8.

Version 3

SaveToDB reads the configuration from tables and views that contain the following fields:

  1. Optional primary key column like ID
  2. SPECIFIC_SCHEMA *
  3. SPECIFIC_NAME
  4. PARAMETER_NAME
  5. SELECT_SCHEMA *
  6. SELECT_NAME
  7. SELECT_TYPE

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

We recommend you to migrate to SaveToDB Framework 7.2 if you use this version.

You may use the Application Installer to upgrade the framework.

Version 5

SaveToDB reads the configuration from tables and views that contain the following fields:

  1. Optional primary key column like ID
  2. TABLE_SCHEMA *
  3. TABLE_NAME
  4. PARAMETER_NAME
  5. SELECT_SCHEMA *
  6. SELECT_NAME
  7. SELECT_TYPE
  8. SELECT_CODE

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

This version has renamed SPECIFIC_SCHEMA and SPECIFIC_NAME fields and includes the new SELECT_CODE field.

This is the latest version of SaveToDB 7. You may continue using it with existing applications.

Version 8

SaveToDB 8 allows using the event handler configuration to configure parameter value lists:

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

This configuration table has all the required fields with other prefixes: COLUMN_ instead of PARAMETER_ and HANDLER_ instead of SELECT_.

So, we decided to unify event handler and parameter configurations and use the single table in SaveToDB 8.

Specify the ParameterValues value in the HANDLER_TYPE field for parameter value queries.

Field Description

TABLE_SCHEMA and TABLE_NAME define a customizable database or code-based object.

PARAMETER_NAME defines a parameter or a field of the customizable object.

SELECT_SCHEMA, SELECT_NAME, SELECT_TYPE, and SELECT_CODE define the object used to select parameter values.

The SELECT_TYPE column may contain the following types:

  • PROCEDURE
  • FUNCTION
  • TABLE
  • VIEW
  • CODE
  • RANGE
  • VALUES

Use the first four for existing database objects (stored procedures, functions, tables, and views).

Specify schemas and names for such objects in the SELECT_SCHEMA and SELECT_NAME fields.

Other types (CODE, RANGE, and VALUES) are described below.

Specify schemas and names that do not conflict with the existing database objects.

Using SQL Code

Place an SQL code into the SELECT_CODE field.

The SQL code 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, and PostgreSQL.

This is an example of the SQL code:

SELECT DISTINCT CategoryID, CategoryName FROM dbo.Category c WHERE c.ParentCategoryID = @CategoryID

In this example, the add-in loads parameter values every time when the user changes the CategoryID parameter.

Using Ranges

You may specify a range as a parameter value source.

Specify a range or a named cell name in the SELECT_CODE field.

Using Values

This is a simple case.

Just specify parameter values separated by commas or semicolons in the SELECT_CODE field.

Add an empty value in the first position, if you need to have it in the ribbon parameter list.

Dependent Parameters

For example, we have a procedure with the following parameters: CategoryID, SubcategoryID, and BrandID.

We may create three procedures to populate ribbon parameter values:

CREATE PROCEDURE [xls12].[uspParameterValues_CategoryID]
CREATE PROCEDURE [xls12].[uspParameterValues_SubcategoryID]
    @CategoryID int = NULL
CREATE PROCEDURE [xls12].[uspParameterValues_BrandID]
    @CategoryID int = NULL
    , @SubcategoryID int = NULL

In this example, when a user changes the CategoryID parameter, then the add-in executes stored procedures to get the SubcategoryID and BrandID parameter values dependent on the CategoryID value.

Output Values

Value list queries may return one, two, or more than two columns.

Single-Column Output

The add-in uses values as is.

Two-Column Output

The add-in uses the first column for parameter values and the second column for value names.

This is a common case when the parameter requires id values while users work with meaningful names.

Multi-Column Output

You may try to use this feature also.

The add-in detects columns to use as ids and names.

However, you may return two columns to solve the task easily.

Empty Values

The add-in adds an empty value (NULL) to dependent parameter value lists only (SubcategoryID and BrandID in the example above).

For independent parameters, it uses values returned by the query only.

You may use the code like below as the first line of your code to add the empty value:

SELECT NULL AS ID, NULL AS Name UNION

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.

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

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