Contents Diagrams Roles Schemas Tables Views Procedures Functions

SaveToDB Framework
for Microsoft SQL Server

Tables

TableDescription
xls.formatsThis SaveToDB Framework table contains Excel table formats.
xls.handlersThis SaveToDB Framework table contains the configuration of Excel event handlers.
xls.objectsThis SaveToDB Framework table contains the configuration of saving changes.
xls.translationsThis SaveToDB Framework table contains translations of database objects.
xls.workbooksThis SaveToDB Framework table contains workbook configuration for SaveToDB Application Workbooks wizard.

xls.formats

This SaveToDB Framework table contains Excel table formats.

ColumnDataTypeNullComment
IDintIdentity
TABLE_SCHEMAnvarchar(128)Object schema
TABLE_NAMEnvarchar(128)Object name
TABLE_EXCEL_FORMAT_XMLxmlExcel table format in XML.

The SaveToDB add-in packs and unpacks this format internally.

The SaveToDB add-in loads Excel table formats for connected objects from this table.

Users may save and load Excel table formats using the Table Format Wizard for selected tables or using the Save Table Format and Load Table Format menu items for the active table.

To allow a user to save table formats, grant INSERT, UPDATE, and DELETE permissions on this table or add the user to the xls_formats role.

See also Configuring Table Formats.

xls.handlers

This SaveToDB Framework table contains the configuration of Excel event handlers.

ColumnDataTypeNullComment
IDintIdentity
TABLE_SCHEMAnvarchar(20)The schema of the object that is being configured.
TABLE_NAMEnvarchar(128)The name of the object that is being configured.
COLUMN_NAMEnvarchar(128)Database object column or parameter name.

Specify the column to apply the handler for the specified column only.
EVENT_NAMEvarchar(25)Event name.

See details in the table description.

This field has a validation list in Microsoft Excel.
HANDLER_SCHEMAnvarchar(20)Handler schema.

Use the database object schema for existing database objects and any schema for non-database handlers.
HANDLER_NAMEnvarchar(128)Handler name.

Use the database object name for existing database objects and any name for non-database handlers.
HANDLER_TYPEvarchar(25)Handler type.

See details in the table description.

This field has a validation list in Microsoft Excel.
HANDLER_CODEnvarchar(max)The field must contain the following values depending on the handler type:

CODE - SQL code
HTTP - HTTP or HTTPs link
TEXT - source text file name
RANGE - Excel range name or address
VALUES - Comma or semicolon-separated list of values

The field can contain a comma or semicolon-separated list of fields for tables and views
used to specify selected columns and aliases for validation lists of the COLUMN_NAME columns.
Examples:
- State
- StateID, State
- [State ID] AS id;[State] AS name
- StateID, State, CountryID
- StateID, State, Country
- State, Country

In the last three cases, the CountryID or Country can be used as a filter column of the dynamic validation list.
In this case, the column CountryID or Country must exist in the selected columns of the configured object.
TARGET_WORKSHEETnvarchar(128)The field allows specifying the handler output target or its behavior.

See complete description in the documentation.

Here is specification:
<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 worksheet name to output the drill-down query to the target worksheet.
Use _popup to output the query to the popup window.
Use _taskpane to output the query to the task pane.
Use _browser to output the query into the default web browser.
Use _reload to reload data and configuration after finishing the handler query.
MENU_ORDERintHandler order in the Actions menu or in the Excel context menu.
EDIT_PARAMETERSbitNULL - default (Actions = 1, ContextMenu = 0);
1 - launch the Edit Parameters dialog box before the query;
0 - do not launch the Edit Parameters dialog box before the query.

This table allows customizing the SaveToDB add-in features.

TABLE_SCHEMA and TABLE_NAME define the object that is being configured.

COLUMN_NAME defines a column or parameter name of the object. If the value is NULL, then the configuration is applied to the entire table.

EVENT_NAME defines the configured feature. See the description below.

HANDLER_SCHEMA and HANDLER_NAME define the object that is used as a handler.
Use the database object schema and name for existing database objects and any schema and name for non-database handlers.
Note that you may translate handler names using the xls.translations table.

HANDLER_TYPE defines the handler type.
The field must contain TABLE, VIEW, FUNCTION, or PROCEDURE for database objects and special values described below for non-database objects.

The HANDLER_CODE field can contain the code specific to the handler type.
See the field comments.

The TARGET_WORKSHEET field can contain the output target.
See the field comments.

The MENU_ORDER can contain the handler order in the Actions menu at the ribbon or in the context menu.

The EDIT_PARAMETERS field can contain 1 or 0 to show or suppress the Edit Parameters dialog box for the Actions and ContextMenu handlers.

See also Configuring Event Handlers.

Event Names

The EVENT_NAME field has a validation list of available values (use Options, Show List Editor to show this task pane):

The Actions handler adds an item to the Actions menu at the SaveToDB ribbon; the ContextMenu handler adds an item to the Actions menu in the Excel context menu.

You may execute any query, to get or update data, from any data source.
The ContextMenu handlers always have active row values in parameters.
The Actions handlers have no active row values if the user clicks the action with the selection outside of the table.

The DoubleClick handler are executed on the double-click event.
The SelectionChange handlers are executed on the selection change event.
See possible targets in the TARGET_WORKSHEET field.
For example, you may update task pane data on the selection change.

The Change handler is executed on the change event.
You may update the underlying database or raise an exception to prevent changes.
This powerful feature is available in the SaveToDB Enterprise and Personal editions.
Note that you may create change handlers adding the _change suffix, and the add-in adds such handlers to the underlying objects automatically.

The ConvertFormulas handler defines replacing column values to Excel formulas.
So, you may define Excel formulas in the views and stored procedures.
For example, like '=[@Amount]*[@Price]'.
You may add the formula into Excel first, and then use its text in the view or procedure.

The SaveToDB add-in detects such formulas automatically testing the first row.
Add the ConvertFormulas handler if the first row can be empty.

Use the DoNotConvertFormulas handler to prevent converting text to formulas in a column.

Use the DoNotAddValidation handler to prevent automatic adding Excel validation lists detected on foreign key constraints.
You may disable this feature for the entire table or the specific column.
Note that the SaveToDB add-in does not add auto-detected validation lists if the object already has any ValidationList handler.
You may use the Help, Workbook Information dialog box to check the generated handlers and copy the configuration into the xls.handlers table.

Use the DoNotAddChangeHandler to prevent adding the change handlers detected by the _change suffix.

Use the ProtectRows handler to prevent adding or deleting rows in Excel table.
Usually, this handler is used together with the Change handler.

The Formula handler allows specifying a column Excel formula in the HANDLER_CODE field (starting =).
The add-in replaces the column values with the specified formula.
This is especially useful for tables as views or stored procedures can select the column formulas using the code.
The add-in saves initial formula values on the hidden SaveToDB_LoadedID worksheet and updates rows with changed formula values.

The FormulaValue handler allows calculating cell values on row changes using the formula specified in the HANDLER_CODE field (starting =).
Unlike the previous handler, this handler inserts the calculated values, not formulas.
You may use any Excel formula or SaveToDB built-in formulas: =DomainUsername and =Username.

Use the ParameterValues handler to specify a query to select ribbon parameter values.

You may use tables, views, stored procedures, or custom SQL code.
The queries may return one or two columns.
If the query returns two columns, the add-in shows the values of the second column but passes into underlying queries the values of the first column.
For example, a query selects id and name. The add-in shows name values but passes the id values.
If you use tables or views to select values, you may specify output columns separated by commas or semicolon in the HANDLER_CODE format.
You may also use + and - prefixes to sort the selected values by the specified column.

Note that you may use ParameterValues queries with parameters.
In this case, the add-in reloads parameters values if the previous parameter at the ribbon with the same name has been changed.
For example, the first ribbon parameter has the @country name and the second has @state.
The second parameter query has the @country parameter.
So, when a user changes the country at the ribbon, the add-in reloads the values of the start parameter.

You may specify parameters for queries from tables and views using the third field with the @ prefix (in the HANDLER_CODE field).
Example: id, +state, @country_id

Use the ValidationList handler to specify a query to select values for Excel validation lists.
The ValidationList handlers have the same configuration capabilities as the ParameterValues handlers.

The queries may select one, two, or three columns.
If the query returns a single column, the add-in adds a validation list with loaded values.
If the query returns two columns, the add-in replaces underlying column values with names.
Queries that return two columns may have parameters. In this case, the add-in reloads values on ribbon parameter value changes.
If the query returns three columns, the add-in creates dynamic validation lists using named formulas.
For example, the ValidationList query of the state_id query returns the id, state, and country_id columns.
If the underlying table contains the country_id column, the add-in creates a validation formula for the state_id column.
When a user selects a new country in the country_id column, Excel will show only states of the selected country.
It is important that the filter column must exist in the underlying table with the same name.
You may use the AS keyword and the alias to change the name of the validation list column.
For example: id, +state, @country_id AS country

Use server-specific brackets to add fields with spaces or other special characters.
Example: id, +state, @country_id AS [country id]

The SelectionList handlers are similar to the ValidationList handlers.
However, the add-in does not create Excel validation lists.
Users may select values from the List Editor (Options, Show List Editor).

The SyncParameter handlers allow linking parameters of several queries.
For example, a worksheet has two queries: usp_order_header and usp_order_details.
Both queries have the @order_id parameter.
By default, the add-in reloads only the query with a changed parameter.
So, when a user changes @order_id in usp_order_header, the add-in reloads only the header.
To change this behavior, add SyncParameter handlers for both queries with the order_id column name.

Use the DefaultListObject handler to specify the main object on the worksheet.
The add-in shows the ribbon parameters of this object if the active cell is outside of any table.

SaveToDB 8 has a specific mode to edit tables that implement many-to-many relations.
For example, an employee_states table can contain two foreign key fields: employee_id and state_id.
So, you may specify the ManyToMany handler for both fields of the employee_states table (view, or procedure).

See Wizards, Online Examples, Developer Guides, Developer Guide 8.8 - Many-to-many.xlsx for details.

The SaveToDB add-in detects many-to-many relations automatically in most cases.
To disable this special mode, use the DoNotAddManyToMany handler.

Handler Types

The HANDLER_TYPE field has a validation list of available values (use Options, Show List Editor to show this task pane):

Use the TABLE, VIEW, FUNCTION, and PROCEDURE types for database objects.

Use the CODE type for handlers based on SQL codes placed in the HANDLER_CODE field.
Use this type, for example, to execute stored procedures with predefined parameters.

Use the HTTP and TEXT types for HTTP and text file queries.
Place the URLs and file names in the HANDLER_CODE field.

Use the MACRO type to run macros specified in the HANDLER_CODE field.
Here is an example of the HANDLER_CODE code: AppMacros.xlsm!SayHello {FirstName}, {LastName}
The add-in populates parameters in the curly brackets using the active row values.

Use the CMD type to execute Windows and CMD commands specified in the HANDLER_CODE field.

Use the VALUES type for ParameterValues and ValidationList handlers with comma- or semicolon-separated lists in the HANDLER_CODE field.

Use the RANGE type for ParameterValues and ValidationList handlers with range names or addresses in the HANDLER_CODE field.

Use the REFRESH type for the Actions handlers to refresh comma- or semicolon-separated lists of worksheets specified in the HANDLER_CODE field.

Use the MENUSEPARATOR type to add menu separators to the Actions menu at the ribbon or in the context menu.
Specify the handler names like MenuSeparator20 or MenuSeparator30 to satisfy the unique constraint.
Do not forget to specify the MENU_ORDER values.

Use the PDF and REPORT types to generate PDF files or Excel report workbooks using the Actions menu.
You may specify the required worksheets in the HANDLER_CODE field.

xls.objects

This SaveToDB Framework table contains the configuration of saving changes.

ColumnDataTypeNullComment
IDintIdentity
TABLE_SCHEMAnvarchar(128)Object schema
TABLE_NAMEnvarchar(128)Object name
TABLE_TYPEnvarchar(128)Object type: TABLE, VIEW, PROCEDURE, CODE, HTTP, TEXT, or HIDDEN.

Use TABLE, VIEW, and PROCEDURE for existing database objects.
Use CODE for SQL queries.
Use HTTP for HTTP or HTTPs links.
Use TEXT for queries from CSV, JSON, XML, or plain text files.
Use HIDDEN to hide an object from the SaveToDB Query List.

This field has a validation list in Microsoft Excel.
TABLE_CODEnvarchar(max)Object code for non-database objects depending on the TABLE_TYPE value:

CODE - SQL code
HTTP - HTTP or HTTPs link
TEXT - source text file name
INSERT_OBJECTnvarchar(max)Table, procedure, or SQL code used to insert new rows.

If UPDATE_OBJECT and DELETE_OBJECT are empty, the add-in merges data using the INSERT_OBJECT (updates all existing rows first and then inserts all new rows).
UPDATE_OBJECTnvarchar(max)Table, procedure, or SQL code used to update existing rows.
DELETE_OBJECTnvarchar(max)Table, procedure, or SQL code used to delete rows.

Use this table to specify the target table to save changes or the target procedures that perform INSERT, UPDATE, and DELETE operations.

Here is a simple configuration that defines the target table to save changes of the data selected by a stored procedure:

See also Configuring Query Lists.

xls.translations

This SaveToDB Framework table contains translations of database objects.

ColumnDataTypeNullComment
IDintIdentity
TABLE_SCHEMAnvarchar(128)Object schema
TABLE_NAMEnvarchar(128)Object name
COLUMN_NAMEnvarchar(128)Column or parameter name.

The field must contain NULL for translation of database objects.
LANGUAGE_NAMEchar(2)Two characters of a language code like 'en' or 'fr'.

Users can select the data language using the SaveToDB Options dialog box.
TRANSLATED_NAMEnvarchar(128)Translated name of an object, column, or parameter.

The add-in uses these values to translate labels and headers in UI.

You may output the value into an Excel spreadsheet cell using the 'SaveToDB_Name' named cell.
TRANSLATED_DESCnvarchar(1024)Object, column, or parameter description.

The add-in uses these values as extended descriptions and column header comments.

You may output the value into an Excel spreadsheet cell using the 'SaveToDB_Desc' named cell.
TRANSLATED_COMMENTnvarchar(2000)Object comment.

The add-in does not use these values directly.

You may output the value into an Excel spreadsheet cell using the 'SaveToDB_Comment' named cell.

The SaveToDB add-in applies this translation within Microsoft Excel.

It loads the translation automatically depending on the selected Data Language in the SaveToDB Options dialog box.

You may use the xls.usp_translations procedure to edit translation for multiple languages in a pivot table and the xls.view_translations view to check and edit the translation of all database objects.

See also Configuring Data Translation.

xls.workbooks

This SaveToDB Framework table contains workbook configuration for SaveToDB Application Workbooks wizard.

ColumnDataTypeNullComment
IDintIdentity
NAMEnvarchar(128)Workbook name
TEMPLATEnvarchar(255)Template workbook path or URL.

The template workbook is not necessary.
The add-in can create a workbook using the definition only.
However, you may supply the workbook to have special features.

You may use Wizards, Developer Wizard, Prepare Workbook for Distribution to remove sensitive data and passwords from the workbook.
DEFINITIONnvarchar(max)Workbook definition.

Use the Workbook Definition tab in the Help, Workbook Information dialog box to get the definition of any opened workbook.

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

The add-in loads the workbook configuration from this table.

Use the Workbook Definition tab in the Help, Workbook Information dialog box to get the definition of any opened workbook.

See also Configuring Application Workbooks.

Contents

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.