Configuring SaveToDB 7

Configuring SaveToDB 7

Configuring Features

The SaveToDB Add-In adds many features to Microsoft Excel from-the-box.

Developers can add power features using the server-side configurations:

  • Connecting database objects, SQL codes, HTTP and text file queries 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 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.

These are requirements for signatures of configuration view fields and procedure parameters.
The SaveToDB add-in does not use the predefined object names. Database developers have full freedom of object naming and implementation.

The best way to quickly add the server-side features to a database is to install SaveToDB Framework.

You may use the SaveToDB Framework Installer wizard to install the framework in a couple of minutes.

Also, you may generate a configuration workbook using the Configuration Workbook Generator wizard and use it to customize application features directly in Microsoft Excel.

The frameworks with full source codes and examples are included in the SaveToDB SDK that can be downloaded separately at www.savetodb.com.

Configuration topics:

SaveToDB Frameworks:

SaveToDB Configuration Poster

Table 1 - Query List Configuration View

TABLE_SCHEMATABLE_NAMETABLE_TYPETABLE_CODEINSERT_PROCEDUREUPDATE_PROCEDUREDELETE_PROCEDUREPROCEDURE_TYPE
<Table schema><Table name>TABLE [<Procedure>/<SQL code>][<Procedure>/<SQL code>][<Procedure>/<SQL code>]TABLE
/ PROCEDURE
/ CODE
<View schema><View name>VIEW [<Table>
/<Procedure>
/<SQL code>]
[<Table>
/<Procedure>
/<SQL code>]
[<Table>
/<Procedure>
/<SQL code>]
<Procedure schema><Procedure name>PROCEDURE 
<Object schema><SQL code name>CODE<SQL code>
<Object schema><HTTP query name>HTTP<HTTP query>[<Table>
/<Procedure>
/<SQL code>]
[<Table>][<Table>]
<Object schema><text query name>TEXT<File name>
[;CodePage=<Code page>]

Remark: table data changes can be saved by default; data changes of HTTP and text queries are saved in a merge mode.

Table 2 - Configuration View of Excel Event Handlers

TABLE_SCHEMATABLE_NAMECOLUMN_NAMEEVENT_NAMEHANDLER_SCHEMAHANDLER_NAMEHANDLER_TYPEHANDLER_CODETARGET_WORKSHEETMENU_ORDEREDIT_PARAMETERS
<Object schema>
See Table 2.1
<Object name>
See Table 2.1
[<Column name>]Actions
Change
ContextMenu
DoubleClick
SelectionChange
ConvertFormulas
DoNotConvertFormulas
DoNotSelect
DoNotSave
DoNotChange
ProtectRows
Formula
FormulaValue
ValidationList
SelectionList
<Handler schema>
See Table 2.2
<Handler name>
See Table 2.2
<Handler type>
See Table 2.2
<Handler code>
See Table 2.2
[<Output target>]
See Table 2.3
[<Item order in
ContextMenu or
Actions menu>]
[1 or 0]
Table 2.1
TABLE_SCHEMATABLE_NAME
<Table schema><Table name>
<View schema><View name>
<Procedure schema><Procedure name>
<Object schema><SQL code name>
<Object schema><HTTP query name>
<Object schema><text query name>
Table 2.2
HANDLER_SCHEMAHANDLER_NAMEHANDLER_TYPEHANDLER_CODE
<Procedure schema><Procedure name>[PROCEDURE] 
<Function schema><Function name>FUNCTION 
<Table schema><Table name>TABLE 
<View schema><View name>VIEW 
<Handler object schema><SQL code name>CODE<SQL code>
<Handler object schema><HTTP query name>HTTP<HTTP query>
<Handler object schema><text query name>TEXT<File name>[;CodePage=<Code page>]
<Handler object schema><Menu item name>MACRO<Excel macro> [<Parameters>]
<Handler object schema><Menu item name>CMD<Windows Shell or CMD command>
 <Handler name>RANGE<Range name or address>
 <Handler name>VALUES<Values>
<Menu item name>REFRESH 
MenuSeparator[MENUSEPARATOR] 
Table 2.3
TARGET_WORKSHEET
<Sheet name> [<Window position>]
_new [<Window position>]
_self
_none
_msgbox
_popup
_taskpane
_browser
_reload
_transpose
_transposeauto
_saveas [<File name>[;CodePage=<Code page>]]
<Sheet list for REFRESH>
Table 2.4
Window Position
_TopWindow
_LeftWindow
_RightWindow
_BottomWindow

Remark: a combination of the SelectionChange event and the <Sheet name> <Window position> target is used to implement master-detail interfaces. TABLE_SCHEMA is ignored on Microsoft SQL Server Compact and SQLite.

Table 3 - Configuration View of Field and Parameter Values

TABLE_SCHEMATABLE_NAMEPARAMETER_NAMESELECT_SCHEMASELECT_NAMESELECT_TYPESELECT_CODE
<Object schema>
See Table 3.1
<Object name>
See Table 3.1
[<Parameter name>
or <Column name>]
<Value query schema>
See Table 3.2
<Value query name>
See Table 3.2
<Value query type>
See Table 3.2
<Value query SQL code>
See Table 3.2
Table 3.1
TABLE_SCHEMATABLE_NAME
<Table schema><Table name>
<View schema><View name>
<Procedure schema><Procedure name>
CODE<SQL code name>
HTTP<HTTP query name>
TEXT<text query name>
Table 3.2
SELECT_SCHEMASELECT_NAMESELECT_TYPESELECT_CODE
<Procedure schema><Procedure name>[PROCEDURE] 
<Function schema><Function name>FUNCTION 
<Table schema><Table name>TABLE 
<View schema><View name>VIEW 
<SQL object schema><SQL code name>CODE<SQL code>
<Schema><Name>RANGE<Range name or address>
<Schema><Name>VALUES<Values>

Table 4 - Object Name Translation View

TABLE_SCHEMATABLE_NAMELANGUAGE_NAMETRANSLATED_NAMETRANSLATED_DESCTRANSLATED_COMMENT
<Object schema>
See Tables 1, 2.2
<Object name>
See Tables 1, 2.2
<Language code>[<Translated name>]
[<Description>][<Comment>]

Table 5 - Object Field and Parameter Translation View

TABLE_SCHEMATABLE_NAMECOLUMN_NAMELANGUAGE_NAMETRANSLATED_NAMETRANSLATED_DESC
[<Object schema>]
See Tables 1, 2.2
[<Object name>]
See Tables 1, 2.2
<Column name> or
<Parameter name>
<Language code>[<Translated name>]
[<Description>]

Table 6 - Excel Table Format View

TABLE_SCHEMATABLE_NAMETABLE_EXCEL_FORMAT_XML
<Object schema>
See Table 1
<Object name>
See Table 1
<Excel table format>

Remark: the first procedure with the Schema, Name and ExcelFormatXML parameters is used to save Excel table formats. TABLE_SCHEMA is ignored on Microsoft SQL Server Compact and SQLite.

Table 7 - Parameter Formats

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, 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=1251
{FileName};CodePage={CodePage=1251}
Macro commandMACRO{Parameter[=DefaultValue]}SayHello {Name=World}
Sheet1.SayHello {FirstName}, {LastName}
Windows Shell or CMDCMD{Parameter[=DefaultValue]}notepad.exe {FileName}
dir {Mask=*.*}
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.

Table 8 - Parameter Values

Value SourceScopeComment
A table column of the same nameAll typesSource object column names without translation are used.
A ribbon parameter of the same nameAll typesRibbon parameters are shown for queries inserted into Excel tables.
Ribbon parameters are being synchronized with Excel named cells. Parameters with predefined names (see below) are not shown on the ribbon.
An Excel named cell of the same nameAll typesExcel named cell value
An active query parameter of the same nameAll typesFor example, event handlers of EXEC dbo.uspCustomers @ManagerID=101 can use a value of the ManagerID parameter.
WorkbookName, workbook_nameAll typesActive workbook name without directory
WorkbookPath, workbook_pathAll typesActive workbook directory. The value is empty for new workbooks.
SheetName, sheet_nameAll typesActive sheet name
DataLanguage, data_languageAll typesData language code selected using SaveToDB Options
TableName, table_nameEvent handlersActive query object
EventName, event_nameEvent handlersEvent name as defined in the EVENT_NAME field of table 2
ColumnName, column_nameEvent handlersActive column name
CellValue, cell_valueEvent handlersActive cell value. Use text type for this parameter and convert the value to a required data type in SQL code.
CellAddress, cell_addressEvent handlersActive cell address
CellFormula, cell_formulaEvent handlersActive cell formula in the Range.Formula format
ChangedCellCount, changed_cell_countEvent handlersNumber of changed cells
ChangedCellIndex, changed_cell_indexEvent handlersNumber of command in the group of change cell handler commands
TransactionID, transaction_idAll typesUnique GUID of a group of executed commands.
TransactionStartTime, transaction_start_timeAll typesUTC start time of executing a group of commands.
WindowsUserName, windows_user_nameAll typesUser's Windows login
SaveToDbVersion, savetodb_versionAll typesSaveToDB version
MergeDate or merge_dateData mergeData merge SQL generation date and time. The value allows detecting data presence in the latest data set.

Remark: parameter value sources are shown in the priority order from highest to lowest.

Table 9 - Updatable Excel Named Cells

Cell NameComment
SaveToDB_ObjectAn active query object
SaveToDB_NameA value of the TRANSLATED_NAME field of object translation view for the active object and current data language (Table 4)
SaveToDB_DescA value of the TRANSLATED_DESC field of object translation view for the active object and current data language (Table 4)
SaveToDB_CommentA value of the TRANSLATED_COMMENT field of object translation view for the active object and current data language (Table 4)
SaveToDB_CommandTextCommandText of the active query
SaveToDB_ConnectionStringConnectionString of the active query
SaveToDB_ElapsedMillisecondsQuery execution time in milliseconds.
<Parameter>Query parameter value. Changing cell values changes parameter values also.
<Parameter>__nameQuery parameter value name.

Table 10 - Techniques of Saving Changes

Data SourceSave TypeEventComment
Database tableDefault saving for tablesSave buttonSaving table data changes works by default
View, procedure, or SQL codeSaving to a base tableSave buttonSpecify a table in INSERT_PROCEDURE, UPDATE_PROCEDURE, and DELETE_PROCEDURE
View, procedure, or SQL codeSaving using SQL codeSave buttonSpecify SQL codes in INSERT_PROCEDURE, UPDATE_PROCEDURE, and DELETE_PROCEDURE
View, procedure, or SQL codeSaving using stored proceduresSave buttonSpecify procedures in INSERT_PROCEDURE, UPDATE_PROCEDURE, and DELETE_PROCEDURE
OData EntitySetDefault saving for ODataSave buttonSaving OData EntitySet changes works by default (SaveToDB Enterprise)
Any data sourceMerging into a base tableSave buttonSpecify a table in INSERT_PROCEDURE
Any data sourceMerging using SQL codeSave buttonSpecify an SQL code in INSERT_PROCEDURE. The code is executed for every row.
Any data sourceMerging using a stored procedureSave buttonSpecify a stored procedure in INSERT_PROCEDURE. The procedure is executed for every row.
Any data sourceSaving using an event handlerChangeSpecify a stored procedure or an SQL code in the event handler configuration view (See Table 2)
Any data sourcePublish WizardWizard runPublish wizard creates a new database table and inserts Excel table data
Any data sourceMerge WizardWizard runRequired database table columns can be constructed using Excel formula columns

Remark: see descriptions of INSERT_PROCEDURE, UPDATE_PROCEDURE, and DELETE_PROCEDURE in Table 1.

Table 11 - Features by Query Types

Query TypeSaving
Changes
Query ListsEvent HandlersParameter ValuesName
Translation
Column
(Parameter)
Translation
Table
Formats
DescriptionType KeywordCustomized
Object
Using as Save
Procedures
Customized
Object
Using as Event
Handler
Customized
Object
Using as Select
Value Object
Database tableTABLEDefault
By SQL codes
By procedures
YesYesYesYesYesYesYesYesYes
Database viewVIEWTo a base table
By SQL codes
By procedures
Yes YesYesYesYesYesYesYes
Database procedurePROCEDURETo a base table
By SQL codes
By procedures
YesYesYesYesYesYesYesYesYes
Database functionFUNCTIONNo   Yes Yes   
Database SQL code
configured through Query List
CODETo a base table
By SQL codes
By procedures
YesYesYesYesYesYesYesYesYes
Database SQL codeCODENo Yes Yes Yes   
OData EntitySetEntitySetDefault         
OData FunctionImportFunctionImportNo         
HTTP query
configured through Query List
HTTPTo a base table
By an SQL code
By a procedure
Yes YesYesYes YesYesYes
HTTP queryHTTPNo   Yes     
text query
configured through Query List
TEXTTo a base table
By an SQL code
By a procedure
Yes YesYesYes YesYesYes
text queryTEXTNo   Yes     
Excel macroMACRONo   Yes  YesYes 
Windows shell or CMD commandCMDNo   Yes  YesYes 
Refresh queries commandREFRESHNo   Yes  Yes  
Other Excel tables No         

Remarks: a merge mode is used for saving changes of HTTP and text queries; a portable SQL Server Compact database in the workbook folder can be used to configure Query List for HTTP and text queries and to customize application behavior.

Table 12 - Object Definitions in Configuration Views

ConfigurationCustomized Object DefinitionCustomizationCustomization Objects
Query ListsTABLE_SCHEMATABLE_NAMETABLE_TYPETABLE_CODESave ProceduresINSERT_PROCEDUREUPDATE_PROCEDUREDELETE_PROCEDUREPROCEDURE_TYPE
Event HandlersTABLE_SCHEMATABLE_NAME  Event HandlerHANDLER_SCHEMAHANDLER_NAMEHANDLER_TYPEHANDLER_CODE
Parameter ValuesTABLE_SCHEMATABLE_NAME  Select Value ObjectSELECT_SCHEMASELECT_NAMESELECT_TYPESELECT_CODE
Name TranslationTABLE_SCHEMATABLE_NAMELANGUAGE_NAME TranslationTRANSLATED_NAMETRANSLATED_DESCTRANSLATED_COMMENT 
Column TranslationTABLE_SCHEMATABLE_NAMECOLUMN_NAMELANGUAGE_NAMETranslationTRANSLATED_NAMETRANSLATED_DESC  
Table FormatsTABLE_SCHEMATABLE_NAME  Table Format    

Table 13 - SaveToDB Framework Objects

ConfigurationObjectMicrosoft SQL ServerSQL Server Compact and SQLiteOracle Database, IBM DB2, and NuoDBMySQL, MariaDB, and PostgreSQL
Query ListsConfiguration view for developersdbo01.viewQueryList SAVETODB_DEV.VIEW_QUERY_LISTsavetodb_dev.view_query_list
Configuration view for usersxls01.viewQueryListviewQueryList (SQLite)SAVETODB_XLS.VIEW_QUERY_LISTsavetodb_xls.view_query_list
Underlying tabledbo01.QueryListQueryListSAVETODB_DEV.QUERY_LISTsavetodb_dev.query_list
Event HandlersConfiguration viewxls01.viewEventHandlers SAVETODB_XLS.VIEW_EVENT_HANDLERSsavetodb_xls.view_event_handlers
Underlying tabledbo01.EventHandlersEventHandlersSAVETODB_DEV.EVENT_HANDLERSsavetodb_dev.event_handlers
Parameter ValuesConfiguration viewxls01.viewParameterValues SAVETODB_XLS.VIEW_PARAMETER_VALUESsavetodb_xls.view_parameter_values
Underlying tabledbo01.ParameterValuesParameterValuesSAVETODB_DEV.PARAMETER_VALUESsavetodb_dev.parameter_values
Name TranslationConfiguration viewxls01.viewObjectTranslation SAVETODB_XLS.VIEW_OBJECT_TRANSLATIONsavetodb_xls.view_object_translation
Underlying tabledbo01.ObjectTranslationObjectTranslationSAVETODB_DEV.OBJECT_TRANSLATIONsavetodb_dev.object_translation
Column TranslationConfiguration viewxls01.viewColumnTranslation SAVETODB_XLS.VIEW_COLUMN_TRANSLATIONsavetodb_xls.view_column_translation
Underlying tabledbo01.ColumnTranslationColumnTranslationSAVETODB_DEV.COLUMN_TRANSLATIONsavetodb_dev.column_translation
Table FormatsConfiguration viewxls01.viewTableFormats SAVETODB_XLS.VIEW_TABLE_FORMATSsavetodb_xls.view_table_formats
Underlying tabledbo01.TableFormatsTableFormatsSAVETODB_DEV.TABLE_FORMATSsavetodb_dev.table_formats
Procedure to save formatsdbo01.uspUpdateTableFormatinternalSAVETODB_DEV.TABLE_FORMAT_UPDATEsavetodb_dev.table_format_save
RolesApplication developersSaveToDB_developersnot supportedSAVETODB_DEV_ROLEsavetodb_dev_role (PostgreSQL)
Application usersSaveToDB_usersnot supportedSAVETODB_XLS_ROLEsavetodb_xls_role (PostgreSQL)

Remarks: use the SaveToDB Framework Installer wizard to install and uninstall SaveToDB Framework; use the Configuration Workbook Generator wizard to make a workbook to edit configuration tables.

Configuring Query List

Preface

SaveToDB allows changing database objects of Excel data tables using the ribbon Query List.

It is a useful feature as you do not need to create multiple sheets for different queries. You may use fewer worksheets in a workbook.

Also, when new views or procedures are added to a database, you may refresh the Query List and connect to new objects with no effort.

A query list is defined for each query object using the Data Connection Wizard and can be changed later.

SaveToDB uses an internal query to get all database objects available for the current user.

You can implement custom query list views. So you can make multiple logical lists of the database objects for different business areas.

For example, your application can provide various lists such as lists for finance managers, accountants, or line managers.
In this case, even a user has rights to all objects, he see the logical list of objects for each application area.

Query lists can contain tables, views, stored procedures, SQL code, HTTP queries, and queries to text files.

Query List Configuration View

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

  1. TABLE_SCHEMA
  2. TABLE_NAME
  3. TABLE_TYPE
  4. TABLE_CODE
  5. INSERT_PROCEDURE
  6. UPDATE_PROCEDURE
  7. DELETE_PROCEDURE
  8. PROCEDURE_TYPE

* Tables are used starting SaveToDB 7 and in previous versions for Microsoft SQL Server Compact.

The tables and views can contain an additional primary key column.

Configuration data formats:

TABLE_SCHEMATABLE_NAMETABLE_TYPETABLE_CODEINSERT_PROCEDUREUPDATE_PROCEDUREDELETE_PROCEDUREPROCEDURE_TYPE
<Table schema><Table name>TABLE [<Procedure>
/<SQL code>]
[<Procedure>
/<SQL code>]
[<Procedure>
/<SQL code>]
PROCEDURE
/CODE
<View schema><View name>VIEW [<Table>
/<Procedure>
/<SQL code>]
[<Table>
/<Procedure>
/<SQL code>]
[<Table>
/<Procedure>
/<SQL code>]
TABLE
/PROCEDURE
/CODE
<Procedure schema><Procedure name>PROCEDURE [<Table>
/<Procedure>
/<SQL code>]
[<Table>
/<Procedure>
/<SQL code>]
[<Table>
/<Procedure>
/<SQL code>]
TABLE
/PROCEDURE
/CODE
<Object schema><SQL code name>CODE<SQL code>[<Table>
/<Procedure>
/<SQL code>]
[<Table>
/<Procedure>
/<SQL code>]
[<Table>
/<Procedure>
/<SQL code>]
TABLE
/PROCEDURE
/CODE
<Object schema><HTTP query name>HTTP<HTTP query>[<Table>
/<Procedure>
/<SQL code>]
[<Table>][<Table>]TABLE
/PROCEDURE
/CODE
<Object schema><text query name>TEXT<File name>
[;CodePage=
<Code page>]
[<Table>
/<Procedure>
/<SQL code>]
[<Table>][<Table>]TABLE
/PROCEDURE
/CODE

TABLE_SCHEMA, TABLE_NAME, and TABLE_TYPE are used to specify the query list objects. The following objects can be specified:

  • Database table (TABLE).
  • Database view (VIEW).
  • Database procedure (PROCEDURE).
  • SQL code (CODE).
  • HTTP query (HTTP).
  • Text file query (TEXT).

TABLE_CODE is used to specify an SQL code, HTTP query, or a text file query for the last three types, and TABLE_NAME must have an object name.

TABLE_SCHEMA for SQL codes, HTTP queries, and text queries can contain any logic name.
This schema name is used in other configuration tables (translations, handlers, parameters, and table formats).
Due to this new SaveToDB 5.0 feature, you may have different schemas of configured objects for various business areas.

The INSERT_PROCEDURE, UPDATE_PROCEDURE, and DELETE_PROCEDURE fields are used for Configuring saving changes and can have the NULL value.
A base table, if specified, must be the same in the all three fields.

PROCEDURE_TYPE contains types of edit procedures.

Query List Configuration View 2.x - 4.x

SaveToDB also reads the query list configuration from the views* in the previous format that contain the following fields:

  1. TABLE_SCHEMA
  2. TABLE_NAME
  3. TABLE_TYPE
  4. INSERT_PROCEDURE
  5. UPDATE_PROCEDURE
  6. DELETE_PROCEDURE

* Tables are used instead of views for Microsoft SQL Server Compact.

The views can contain an additional primary key column.

Configuration data formats:

TABLE_SCHEMATABLE_NAMETABLE_TYPEINSERT_PROCEDUREUPDATE_PROCEDUREDELETE_PROCEDURE
<Table schema><Table name>TABLE[<Procedure>
/<SQL code>]
[<Procedure>
/<SQL code>]
[<Procedure>
/<SQL code>]
<View schema><View name>VIEW[<Table>
/<Procedure>
/<SQL code>]
[<Table>
/<Procedure>
/<SQL code>]
[<Table>
/<Procedure>
/<SQL code>]
<Procedure schema><Procedure name>PROCEDURE[<Table>
/<Procedure>
/<SQL code>]
[<Table>
/<Procedure>
/<SQL code>]
[<Table>
/<Procedure>
/<SQL code>]
<SQL code><SQL code name>CODE[<Table>
/<Procedure>
/<SQL code>]
[<Table>
/<Procedure>
/<SQL code>]
[<Table>
/<Procedure>
/<SQL code>]
<HTTP query><HTTP query name>HTTP[<Table>
/<Procedure>
/<SQL code>]
[<Table>][<Table>]
<File name>
[;CodePage
=<Code page>]
<text query name>TEXT[<Table>
/<Procedure>
/<SQL code>]
[<Table>][<Table>]

TABLE_SCHEMA, TABLE_NAME, and TABLE_TYPE are used to specify the query list objects. The following objects can be specified:

  • Database table (TABLE).
  • Database view (VIEW).
  • Database procedure (PROCEDURE).
  • SQL code (CODE).
  • HTTP query (HTTP).
  • Text file query (TEXT).

TABLE_SCHEMA is used to specify an SQL code, HTTP query, or a text file query for the last three types, and TABLE_NAME must have an object name.
You should transfer contents of this field to the TABLE_CODE field when migrating to the SaveToDB 5.0 format.

The INSERT_PROCEDURE, UPDATE_PROCEDURE, and DELETE_PROCEDURE fields are used for Configuring saving changes and can have the NULL value.
A base table, if specified, must be the same in the all three fields.

Using SQL Code

The SQL code can be specified in the TABLE_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.

The parameters are shown on the ribbon, and users can change the values.

For example:

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

Using HTTP Queries

The HTTP query can be specified in the TABLE_CODE field.

HTTP query parameters are shown on the ribbon.

SaveToDB supports the standard scheme of URL parameters: ?ParameterName1=Value1&ParameterName2=Value2...

In facts, a URL from a web browser can be inserted into the TABLE_CODE field without any change.

Alternatively, the HTTP query parameters can be redefined in the following format: {ParameterName=DefaultValue}

It is useful if a URL contains technical parameters that should not be shown.

For example:

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 example URL contains the Symbol parameter with the default value equal YHOO.

SaveToDB allows connecting to web pages and web services including OData web services.

SaveToDB supports the following data formats: XML, JSON, HTML, and CSV.

The table data are parsed by smart algorithms that do not require customizing.

If a web page is not parsed correctly, you may send us a request. We will try to change the algorithms.

The SaveToDB add-in allows connecting to web data sources that require authorization.

SaveToDB supports the following authorization methods: Windows, Forms, Basic, OAuth 1.0, and OAuth 2.0.

SaveToDB supports the following OAuth providers: Google, Facebook, LinkedIn, Twitter, Microsoft Azure Marketplace, Windows Live, Yahoo, and Yahoo API Key.

Using Text File Queries

The file name and code page can be specified in the TABLE_CODE field in the following format:

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

For example:

Contacts.csv;CodePage=65001

The file name can contain a relative path to the workbook directory.
In this case, the file can be moved with the workbook to another place.

Queries can contain parameters in the following format: {ParameterName=DefaultValue}

Such parameters are shown on the ribbon.

For example:

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

In this example, the FileName and CodePage parameters are shown on the ribbon and can be changed by a user.

Configuration View Example

This is an example of the query list configuration view:

Configuration view of Query List

Creating Database Configuration Objects

SaveToDB Framework contains query list views that can be used as a data source for custom views on all supported platforms:
Microsoft Azure SQL Database, Microsoft SQL Server, Microsoft SQL Server Compact, Oracle Database, IBM DB2, MySQL, MariaDB, NuoDB, PostgreSQL, and SQLite.

Configuring Saving Changes

Preface

SaveToDB allows saving data changes made in Microsoft Excel back to a database or OData web service.

SaveToDB supports the following scenarios:

Data SourceSave TypeStart Event
Database tableDefault saving for tablesSave button
View, procedure, or SQL codeSaving or merging to a base tableSave button
View, procedure, or SQL codeSaving or merging using SQL codeSave button
View, procedure, or SQL codeSaving or merging using stored proceduresSave button
OData EntitySetDefault saving for ODataSave button
HTTP query or text fileMerging into a base tableSave button
HTTP query or text fileMerging using SQL codeSave button
HTTP query or text fileMerging using a stored procedureSave button
Any data sourceSaving using an event handlerChange event
Any data sourcePublish WizardWizard run
Any data sourceMerge WizardWizard run

There are three groups of the scenarios:

  1. Saving changes using the Save button.
  2. Saving changes using change event handlers.
  3. Publishing or merging data using SaveToDB wizards.

The second and third scenarios are described in the appropriate topics.

This topic is focused on the first group, saving changes using the Save button.

There are two different methods to save data depend on data sources:

  1. Saving data changes including inserting new rows, updating existing rows and deleting deleted rows.
  2. Merging data including inserting new rows and updating existing rows.

Saving data changes is used for data from databases or OData services. Merging data is used for other sources.

There are four implementation methods for saving or merging data:

  1. Saving data changes for tables and OData EntitySets from-the-box, without any coding.
  2. Saving or merging data into a base table using SaveToDB internal procedures.
  3. Saving or merging data using custom stored procedures.
  4. Saving or merging data using custom SQL codes.

Saving OData data is available in the SaveToDB Enterprise edition and the trial version.

SaveToDB saves the data required to save changes on hidden workbook sheets.
The workbook can be closed and reopened multiple times before saving changes to a database.
The hidden sheets are being inserted during the connection using the Data Connection Wizard or from the Options dialog box.

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

The Query List configuration views are used to configure saving changes.
The Data Connection Wizard is used to select and modify the query list view for connected objects.

Configuration View

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

  1. TABLE_SCHEMA
  2. TABLE_NAME
  3. TABLE_TYPE
  4. TABLE_CODE
  5. INSERT_PROCEDURE
  6. UPDATE_PROCEDURE
  7. DELETE_PROCEDURE
  8. PROCEDURE_TYPE

* Tables are used starting SaveToDB 7 and in previous versions for Microsoft SQL Server Compact.

The tables and views can contain an additional primary key column.

Configuration data formats:

TABLE_SCHEMATABLE_NAMETABLE_TYPETABLE_CODEINSERT_PROCEDUREUPDATE_PROCEDUREDELETE_PROCEDUREPROCEDURE_TYPE
<Table schema><Table name>TABLE [<Procedure>
/<SQL code>]
[<Procedure>
/<SQL code>]
[<Procedure>
/<SQL code>]
PROCEDURE
/CODE
<View schema><View name>VIEW [<Table>
/<Procedure>
/<SQL code>]
[<Table>
/<Procedure>
/<SQL code>]
[<Table>
/<Procedure>
/<SQL code>]
TABLE
/PROCEDURE
/CODE
<Procedure schema><Procedure name>PROCEDURE [<Table>
/<Procedure>
/<SQL code>]
[<Table>
/<Procedure>
/<SQL code>]
[<Table>
/<Procedure>
/<SQL code>]
TABLE
/PROCEDURE
/CODE
<Object schema><SQL code name>CODE<SQL code>[<Table>
/<Procedure>
/<SQL code>]
[<Table>
/<Procedure>
/<SQL code>]
[<Table>
/<Procedure>
/<SQL code>]
TABLE
/PROCEDURE
/CODE
<Object schema><HTTP query name>HTTP<HTTP query>[<Table>
/<Procedure>
/<SQL code>]
[<Table>][<Table>]TABLE
/PROCEDURE
/CODE
<Object schema><text query name>TEXT<File name>
[;CodePage=
<Code page>]
[<Table>
/<Procedure>
/<SQL code>]
[<Table>][<Table>]TABLE
/PROCEDURE
/CODE

TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, and TABLE_CODE are used to specify the query list objects and are described in the Configuring Query List topic.

INSERT_PROCEDURE, UPDATE_PROCEDURE, and DELETE_PROCEDURE are used to specify:

  • A target database table ("base table").
  • Custom stored procedures used to save or merge data.
  • Custom SQL codes used to save or merge data.

A base table, if specified, must be the same in the all three fields. See details about the mode below.

To merge data instead of saving, specify a base table, stored procedure, or an SQL code in the INSERT_PROCEDURE field only.
In this case, the action of the Save button is the same as Save by Merge.

The PROCEDURE_TYPE field is reserved for the future extensions and may specify the type of the edit procedure objects.
However, SaveToDB add-in detects types of database objects itself and allows mixing stored procedures and SQL codes.

Configuration View 2.x-4.x

SaveToDB also reads the query list configuration from the views* in the previous format that contain the following fields:

  1. TABLE_SCHEMA
  2. TABLE_NAME
  3. TABLE_TYPE
  4. INSERT_PROCEDURE
  5. UPDATE_PROCEDURE
  6. DELETE_PROCEDURE

* Tables are used instead of views for Microsoft SQL Server Compact.

The views can contain an additional primary key column.

Configuration data formats:

TABLE_SCHEMATABLE_NAMETABLE_TYPEINSERT_PROCEDUREUPDATE_PROCEDUREDELETE_PROCEDURE
<Table schema><Table name>TABLE[<Procedure>
/<SQL code>]
[<Procedure>
/<SQL code>]
[<Procedure>
/<SQL code>]
<View schema><View name>VIEW[<Table>
/<Procedure>
/<SQL code>]
[<Table>
/<Procedure>
/<SQL code>]
[<Table>
/<Procedure>
/<SQL code>]
<Procedure schema><Procedure name>PROCEDURE[<Table>
/<Procedure>
/<SQL code>]
[<Table>
/<Procedure>
/<SQL code>]
[<Table>
/<Procedure>
/<SQL code>]
<SQL code><SQL code name>CODE[<Table>
/<Procedure>
/<SQL code>]
[<Table>
/<Procedure>
/<SQL code>]
[<Table>
/<Procedure>
/<SQL code>]
<HTTP query><HTTP query name>HTTP[<Table>
/<Procedure>
/<SQL code>]
[<Table>][<Table>]
<File name>[;CodePage=<Code page>]<text query name>TEXT[<Table>
/<Procedure>
/<SQL code>]
[<Table>][<Table>]

TABLE_SCHEMA, TABLE_NAME, and TABLE_TYPE are used to specify the query list objects and are described in the Configuring Query List topic.

INSERT_PROCEDURE, UPDATE_PROCEDURE, and DELETE_PROCEDURE are used to specify:

  • A target database table ("base table").
  • Custom stored procedures used to save or merge data.
  • Custom SQL codes used to save or merge data.

A base table, if specified, must be the same in the all three fields. See details about the mode below.

To merge data instead of saving, specify a base table, stored procedure, or an SQL code in the INSERT_PROCEDURE field only.
In this case, the action of the Save button is the same as Save by Merge.

Saving Changes to Base Table

Saving changes to a base table allows implementing saving data of simple SQL queries, views, and stored procedures back to a database quickly and without coding.

For example, the following stored procedure selects the data from a single table:

CREATE PROCEDURE [xls].[uspStockHistory]
    @Symbol varchar(5) = 'YHOO'
AS
BEGIN
    SELECT
        *
    FROM
        dbo.StockTradeHistory th
    WHERE
        th.Symbol = @Symbol
    ORDER BY
        th.[Date] DESC
END

The data changes can be saved to a database if the dbo.StockTradeHistory table is specified as a base table for the xls.uspStockHistory procedure. It is simple.

The merge mode can be used for any object but is the most useful for HTTP queries and text file queries.

In the merge mode, the SaveToDB add-in generates and executes INSERT and UPDATE statements for all rows in the Excel table.

Excel formulas can prepare base table data. For example, required FirstName and LastName columns can be calculated from an existing Name column.

Saving Changes using Custom SQL Codes and Stored Procedures

You may implement any logic of saving data changes using custom SQL codes or stored procedures, including saving data to multiple tables.

In the save mode, SaveToDB executes the INSERT_PROCEDURE SQL-code or stored procedure for new data rows, the UPDATE_PROCEDURE ones for updated data rows, and the DELETE_PROCEDURE ones for deleted rows.

In the merge mode, SaveToDB executes the INSERT_PROCEDURE SQL-code or stored procedure for each data row. So, the merge logic must be implemented by a developer.

The SQL codes and stored procedures must have parameters to get the data from Excel.

See Using Parameters about all possible parameter value sources.

The most used source is Excel table data. It is important that the parameter names should be identical to the column names.
This rule is a constraint for the database query column names.
However, there are no constraints on translated column names.

Configuration View Example

There is an example of the configuration view (in the SaveToDB 4.x format):

Configuration view of Query List

Default Query List Configuration View Considerations

The SaveToDB default query list query uses the "_insert", "_update", and "_delete" suffixes of stored procedure names to detect procedures to save changes of a base object, as shown above.

The "_select" suffix of a base object is omitted before linking. So the same procedures can be used for two objects: a base view and a base procedure with the "_select" suffix.

Of course, database developers can define other rules of linking in custom views.

Checking and Debugging Configuration

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

Use the View Save Change SQL item of the Save menu to check the generated SQL code to save data changes.

Creating Database Configuration Objects

SaveToDB Framework contains query list views that can be used as a data source for custom views on all supported platforms:
Microsoft Azure SQL Database, Microsoft SQL Server, Microsoft SQL Server Compact, Oracle Database, IBM DB2, MySQL, MariaDB, NuoDB, PostgreSQL, and SQLite.

SaveToDB Framework for Microsoft SQL Server also contains a stored procedure to generate custom INSERT, UPDATE and DELETE procedures.

Configuring Event Handlers

Preface

SaveToDB allows configuring Microsoft Excel applications using database objects.

You may configure:

  • Processing Excel cell Change events using SQL codes or stored procedures.
  • Opening drill-down queries in a popup window, web browser or on Excel sheets on DoubleClick events.
  • Refreshing related queries on SelectionChange events.
  • Executing various actions from the Excel context menu or the SaveToDB Actions menu.

Using database handlers for Excel event handling you may:

  • Change database data when Excel cells have been changed.
  • Protect selected columns, rows, or cells from changes.
  • Implement drill-down queries.
  • Implement master-detail interfaces.

Using the Excel context menu and SaveToDB Actions menu you may:

  • Execute database queries including stored procedures and SQL codes.
  • Open HTTP queries in a web-browser, popup window, or on Excel sheets.
  • Open text file data in a popup window or on Excel sheets.
  • Execute Excel macros.
  • Execute Windows Shell and CMD commands.
  • Refresh data validation lists on specified sheets.
  • Change parameters of Excel data table queries (reset parameters, generate new document numbers, etc.).

Menu items can be translated into a business language using the object name translation feature.

Event handler parameters can be translated using the parameter name translation feature.

Event handler parameter values can be populated using the configuring parameter values feature.

Event Handler Configuration View

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

  1. TABLE_SCHEMA
  2. TABLE_NAME
  3. COLUMN_NAME
  4. EVENT_NAME
  5. HANDLER_SCHEMA
  6. HANDLER_NAME
  7. HANDLER_TYPE
  8. HANDLER_CODE
  9. TARGET_WORKSHEET
  10. MENU_ORDER
  11. EDIT_PARAMETERS

* Tables are used starting SaveToDB 7 and in previous versions for Microsoft SQL Server Compact.

The tables and views can contain an additional primary key column.

Configuration data formats:

TABLE_SCHEMATABLE_NAMECOLUMN_NAMEEVENT_NAMEHANDLER_SCHEMAHANDLER_NAMEHANDLER_TYPEHANDLER_CODETARGET_WORKSHEETMENU_ORDEREDIT_PARAMETERS
<DB object schema>
or <Object schema>
See table 2.1
<Object name>
See table 2.1
[<Column name>]Actions
Change
ContextMenu
DoubleClick
SelectionChange
ConvertFormulas (v7.2)
DoNotConvertFormulas (v7.2)
DoNotSelect (v6.2)
DoNotSave (v6.2)
DoNotChange (v6.2)
ProtectRows (v6.9)
Formula (v6.2)
FormulaValue (v6.2)
ValidationList (v6.2)
SelectionList (v6.2)
<Handler schema>
See table 2.2
<Handler name>
See table 2.2
<Handler type>
See table 2.2
<Handler code>
See table 2.2
[<Output target>]
See table 2.3
[<Item order in
ContextMenu or
Actions menu>]
[1 or 0]
Table 2.1
TABLE_SCHEMATABLE_NAME
<Table schema><Table name>
<View schema><View name>
<Procedure schema><Procedure name>
<Object schema><SQL code name>
<Object schema><HTTP query name>
<Object schema><text query name>
Table 2.2
HANDLER_SCHEMAHANDLER_NAMEHANDLER_TYPEHANDLER_CODE
<Procedure schema><Procedure name>[PROCEDURE] 
<Function schema><Function name>FUNCTION 
<Table schema><Table name>TABLE 
<View schema><View name>VIEW 
<Object schema><SQL code name>CODE<SQL code>
<Object schema><HTTP query name>HTTP<HTTP query>
<Object schema><text query name>TEXT<File name>[;CodePage=<Code page>]
 MenuSeparator[MENUSEPARATOR] 
<Object schema><Menu item name>MACRO<Excel macro> [<Parameters>]
<Object schema><Menu item name>CMD<Windows Shell or CMD command>
 <Handler name>RANGE (v7.1)<Range name or address>
 <Handler name>VALUES (v7.1)<Values>
<Object schema><Menu item name>REFRESH 
Table 2.3
TARGET_WORKSHEET
<Sheet name> [<Window position>]
_new [<Window position>]
_self
_none
_popup
_browser
_taskpane
_reload
_transpose
_transposeauto
_saveas [<File name>[;CodePage=<Code page>]]
<Sheet list for REFRESH>
Table 2.4
Window Position
_TopWindow
_LeftWindow
_RightWindow
_BottomWindow

TABLE_SCHEMA is ignored on Microsoft SQL Server Compact and SQLite.

Event Handler Configuration View 3.x - 4.x

SaveToDB also reads the event handler configuration from the views* in the previous format that contain the following fields (without HANDLER_CODE):

  1. TABLE_SCHEMA
  2. TABLE_NAME
  3. COLUMN_NAME
  4. EVENT_NAME
  5. HANDLER_SCHEMA
  6. HANDLER_NAME
  7. HANDLER_TYPE
  8. TARGET_WORKSHEET
  9. MENU_ORDER
  10. EDIT_PARAMETERS

* Tables are used instead of views for Microsoft SQL Server Compact.

The views can contain an additional primary key column.

Configuration data formats:

TABLE_SCHEMATABLE_NAMECOLUMN_NAMEEVENT_NAMEHANDLER_SCHEMAHANDLER_NAMEHANDLER_TYPETARGET_WORKSHEETMENU_ORDEREDIT_PARAMETERS
<DB object schema>
or <Object type>
See table 3.1
<Object name>
See table 3.1
[<Column name>]Actions
Change
ContextMenu
DoubleClick
SelectionChange
<Handler schema>
See table 3.2
<Handler name>
See table 3.2
<Handler type>
See table 3.2
[<Output target>]
See table 3.3
[<Item order in
ContextMenu or
Actions menu>]
[1 or 0]
Table 3.1
TABLE_SCHEMATABLE_NAME
<Table schema><Table name>
<View schema><View name>
<Procedure schema><Procedure name>
CODE<SQL code name>
HTTP<HTTP query name>
TEXT<text query name>
Table 3.2
HANDLER_SCHEMAHANDLER_NAMEHANDLER_TYPE
<Procedure schema><Procedure name>[PROCEDURE]
<Function schema><Function name>FUNCTION
<Table schema><Table name>TABLE
<View schema><View name>VIEW
<SQL code><SQL code name>CODE
<HTTP query><HTTP query name>HTTP
<File name>[;CodePage=<Code page>]<text query name>TEXT
MenuSeparator[MENUSEPARATOR]
<Excel macro> [<Parameters>]<Menu item name>MACRO
<Windows Shell or CMD command><Menu item name>CMD
<Menu item name>REFRESH
Table 3.3
TARGET_WORKSHEET
<Sheet name> [<Window position>]
_new [<Window position>]
_self
_popup
_browser
_reload
_transpose
_transposeauto
_saveas [<File name>[;CodePage=<Code page>]]
<Sheet list for REFRESH>
Table 3.4
Window Position
_TopWindow
_LeftWindow
_RightWindow
_BottomWindow

TABLE_SCHEMA is ignored on Microsoft SQL Server Compact and SQLite.

There are two differences with the SaveToDB 5.0 configuration format:

  • Custom codes from HANDLER_SCHEMA are moved to HANDLER_CODE.
    As a result, custom handlers may have different object schemas in HANDLER_SCHEMA. These schemas are used in other configuration tables (translations, parameters).
  • TABLE_SCHEMA can contain different object schemas specified in the query list configuration table but not only predefined schemas (CODE, HTTP, TEXT).

Event Handler Configuration View 1.x - 2.x

SaveToDB also reads the event handler configuration from the views in the SaveToDB 1.x-2.x format with the following fields:

  1. TABLE_SCHEMA
  2. TABLE_NAME
  3. COLUMN_NAME
  4. EVENT_NAME
  5. HANDLER_NAME
  6. TARGET_WORKSHEET

However, this format does not allow configuring SaveToDB 3.x features.

Event Handler Configuration View Fields

Mandatory TABLE_SCHEMA and TABLE_NAME specify an Excel data table query object that is being handled. See Table 2.1 or Query List configuration.

Optional COLUMN_NAME specifies a name of a column that is being handled. If the value is NULL, the handler is used for the entire table.

Possible values of the EVENT_NAME field:

EVENT_NAMEDescription
ActionsAn item of the SaveToDB Actions menu
ContextMenuAn item of the Microsoft Excel context menu
ChangeA cell change event handler
DoubleClickA DoubleClick event handler
SelectionChangeA SelectionChange event handler
DoNotSelect (v6.2)A handler prevents selecting a column in the Data Connection Wizard.
DoNotSave (v6.2)A handler prevents saving column values to a database.
DoNotChange (v6.2)A handler prevents changing column values.
ProtectRows (v6.9)A handler prevents adding and deleting rows.
Formula (v6.2)A handler replaces column values with an Excel formula specified in the HANDLER_CODE field.
Formula Value (v6.2)A handler replaces column values with a value calculated by a formula specified in the HANDLER_CODE field.
You may use Excel formulas and special formulas =DomainUserName() and =UserName().
Validation List (v6.2)A handler loads values from a specified handler object to the hidden SaveToDB_Lists worksheet and creates a validation list for the specified column.
Use Reload, Reload Data and Configuration to reload validation lists.

* Event types are available in SaveToDB 6.2 or higher.

Possible values of the HANDLER_SCHEMA, HANDLER_NAME, HANDLER_TYPE, and HANDLER_CODE fields:

HANDLER_SCHEMAHANDLER_NAMEHANDLER_TYPEHANDLER_CODEDescription
<Procedure schema><Procedure name>[PROCEDURE] Executing a database stored procedure
<Function schema><Function name>FUNCTION Executing a database function
<Table schema><Table name>TABLE Selecting all data from a database table
<View schema><View name>VIEW Selecting all data from a database view
<Object schema><SQL code name>CODE<SQL code>Executing an SQL code
<Object schema><HTTP query name>HTTP<HTTP query>Opening an HTTP query
<Object schema><text query name>TEXT<File name>[;CodePage=<Code page>]Opening data from a text file
 MenuSeparator[MENUSEPARATOR] Inserting a menu separator
<Object schema><Menu item name>MACRO<Excel macro> [<Parameters>]Executing an Excel macro
<Object schema><Menu item name>CMD<Windows Shell or CMD command>Executing a Windows Shell or CMD command
<Object schema><Menu item name>REFRESH Refreshing Excel queries on specified sheets

The SaveToDB 3.x-4.x format has no HANDLER_CODE field. The object codes can be stored in the HANDLER_SCHEMA field.

The SaveToDB 1.x-2.x format with the single HANDLER_NAME field is also supported:

HANDLER_NAMEDescription
<Schema>.<Name>Executing a database stored procedure
<HTTP query>Opening an HTTP query. The HTTP query format: [<Handler Name>=]<URL>

Possible values of the TARGET_WORKSHEET field:

TARGER_WORKSHEETDescription
<Sheet name> [<Window position>]The result is inserted on the specified sheet. The sheet is created if not exists.
_new [<Window position>]The result is inserted into a new table on a new sheet. The new table is used for further requests.
_selfThe result replaces the active table query.
_noneThe result is ignored and any window is not created.
_msgboxThe result is outputted into a standard MsgBox window (SaveToDB 6.9 or higher).
_popupThe result is outputted into a popup data window.
_browserThe result is outputted into the default web browser (for HTTP) or the internal browser (for others).
_taskpaneThe result is outputted on task panes. This is a new SaveToDB 5.0 feature.
_reloadThe result is used to set active query parameters. The query is reloaded with the new values.
Return new parameter values in the first row in the columns with parameter names.
_transposeThe first result row is transposed to a name and value table in _popup or _taskpane modes.
_transposeautoThe result row is transposed to a name and value table if the result contains one row only.
_saveas [<File name>[;CodePage=<Code page>]
[;Format=CSV][;Separator=;/,/Tab]]
The result is exported to a CSV file.
Only the CSV format is supported. Available separators: comma, semicolon, and tab.
<Sheet list>Excel queries on the specified sheets are refreshed in the REFRESH handler mode.
NULLHTTP queries are opened in the default web browser.
For database output:
if the result is a data table, then the data popup window is used;
if the result is a single value, then the internal SaveToDB web browser is used.

The TARGET_WORKSHEET field can contain an optional window position (with a leading space) in addition to the <Sheet name> or _new values:

Window PositionDescription
_RightWindowThe result is opened in the right Excel window.
_TopWindowThe result is opened in the top Excel window.
_LeftWindowThe result is opened in the left Excel window.
_BottomWindowThe result is opened in the bottom Excel window.

Using related windows for SelectionChange handlers allows implementing master-detail interfaces.

The window is activated with the following rules:

  1. The target sheet does not exist and is created for the first time.
  2. The target sheet exists and the active window is normal, not maximized.

You may maximize the active window to prevent refreshing detail data in related windows.
Then you may restore the window to return to the master-detail mode.

The MENU_ORDER field allows sorting menu items. Use integer values.

The EDIT_PARAMETERS field allows changing the default behavior for launching menu items:

  • Context menu items are executed without parameter value confirmation.
  • Actions menu items are executed using the Edit Parameter Values dialog box.

The behavior is different as the context menu handlers have table row values in any case and the Actions menu handlers may have not.

Configuration View Example

There is an example of the configuration view (in SaveToDB 4.x format):

Configuration view of event handlers

Example comments:

  • All the rules are applied to the xls10.uspCalendar stored procedure.
  • The event handlers are defined for all columns of the table as the COLUMN_NAME field is empty (not shown).
  • The context menu handlers are ordered (1, 2, and 3) and are divided by MenuSeparator (2).
  • The same uspExcelEvent_DoubleClick_xls11_uspCalendar stored procedure is used to handle multiple events (Actions, DoubleClick, and SelectionChange).
  • The results of the SelectionChange event handler are inserted on a sheet in the right window.
  • The results of the Google Search handler are opened in a web browser as an HTTP query.
    The HTTP query is specified in the HANDLER_CODE field, and the HTTP type is specified in the HANDLER_TYPE field.

Event Handler Parameters

Event handler parameters are populated from the following sources:

Value SourceComment
A table column of the same nameSource object column names without translation are used.
A ribbon parameter of the same nameRibbon parameters are shown for queries inserted into Excel tables.
Ribbon parameters are being synchronized with Excel named cells. Parameters with predefined names (see below) are not shown on the ribbon.
An Excel named cell of the same nameExcel named cell value
An active query parameter of the same nameFor example, event handlers of EXEC dbo.uspCustomers @ManagerID=101 can use a value of the ManagerID parameter.
Predefined parameters:
WorkbookName, workbook_nameActive workbook name without directory
WorkbookPath, workbook_pathActive workbook directory. The value is empty for new workbooks.
SheetName, sheet_nameActive sheet name
DataLanguage, data_languageData language code selected using SaveToDB Options
TableName, table_nameActive query object
EventName, event_nameEvent name as defined in the EVENT_NAME field
ColumnName, column_nameActive column name
CellValue, cell_valueActive cell value. Use text type for this parameter and convert the value to a required data type in SQL code.
CellAddress, cell_addressActive cell address
CellFormula, cell_formulaActive cell formula in the Range.Formula format
ChangedCellCount, changed_cell_countNumber of changed cells
ChangedCellIndex, changed_cell_indexNumber of command in the group of change cell handler commands
TransactionID, transaction_idUnique GUID of a group of executed commands.
TransactionStartTime, transaction_start_timeUTC start time of executing a group of commands.
WindowsUserName, windows_user_nameUser's Windows login
SaveToDbVersion, savetodb_versionSaveToDB version

Parameter value sources are shown in the priority order from highest to lowest.

You may implement universal handlers using the TableName, ColumnName, and CellValue parameters. The CellValue parameter must be the varchar type and should be converted to a destination type in the SQL code.

Item Names of Context Menu and Actions Menu

Handlers are specified in the HANLDER_SCHEMA and HANDLER_NAME fields.

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

Also, handler names can include parameter values specified as {ParameterName}.

For example, a handler configuration view contains the following handlers to open websites and profile URLs using the values of the Website and ProfileURL columns:

EVENT_NAMEHANDLER_SCHEMAHANDLER_NAMEHANDLER_TYPEHANDLER_CODE
ContextMenuhttp32Open website - {Website}HTTP{Website}
ContextMenuhttp32Open profile - {ProfileURL}HTTP{ProfileURL}

You may translate handler names to multiple languages using the following object name translation configuration:

TABLE_SCHEMATABLE_NAMELANGUAGE_NAMETRANSLATED_NAMETRANSLATED_DESCTRANSLATED_COMMENT
http32Open website - {Website}enOpen website - {Website}  
http32Open profile - {ProfileURL}enOpen profile - {ProfileURL}  
http32Open website - {Website}esAbrir el sitio - {Website}  
http32Open profile - {ProfileURL}esAbrir el perfil - {ProfileURL}  
http32Open website - {Website}frOuvrir le site - {Website}  
http32Open profile - {ProfileURL}frOuvrir le profil - {ProfileURL}  
http32Open website - {Website}itApre il site - {Website}  
http32Open profile - {ProfileURL}itApre il profilo - {ProfileURL}  

Of course, base handler names themselves can be in any language.

However, be aware that TABLE_SCHEMA and TABLE_NAME values are used in multiple configuration views, and it is desired to have them stable.
Translated names can be changed as you need in one translation table only.

The example specifies the http32 schema name. SaveToDB 4.x and below can use only the predefined schemas for non-database objects named as HTTP, CODE, or TEXT.

Using SQL Code as Event Handlers

The SQL code can be specified in the HANDLER_CODE field.

The SQL code can contain parameters in the following formats:

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

For example:

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

HTTP Queries as Event Handlers

The HTTP query can be specified in the HANDLER_CODE field.

SaveToDB supports the standard scheme of URL parameters: ?ParameterName1=Value1&ParameterName2=Value2...

However, in the most cases, it is not suitable as the event handlers get parameter values from the table columns with the same name.

So, the HTTP query parameters can be redefined in the following formats: {ParameterName=DefaultValue} or simply {ParameterName}

For example:

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 example URL contains the Symbol parameter with the default value equal YHOO.

SaveToDB allows connecting to web pages and web services including OData web services.

SaveToDB supports the following data formats: XML, JSON, HTML, and CSV.

To insert table data on a sheet, the web data are parsed by smart algorithms that do not require customizing.

If a web page is not parsed correctly, you may send us a request. We will try to change the algorithms.

The SaveToDB add-in allows connecting to web data sources that require authorization.

SaveToDB supports the following authorization methods: Windows, Forms, Basic, OAuth 1.0, and OAuth 2.0.

SaveToDB supports the following OAuth providers: Google, Facebook, LinkedIn, Twitter, Microsoft Azure Marketplace, Windows Live, Yahoo, and Yahoo API Key.

You may use HTTP queries in the Actions menu to show complete documentation about the active Excel table query.

You may use HTTP queries to open websites or web pages defined in Excel table cells.

For example, if a table contains the Website and ProfileURL fields then you may create the following handlers to open website and profile URLs:

EVENT_NAMEHANDLER_SCHEMAHANDLER_NAMEHANDLER_TYPEHANDLER_CODE
ContextMenuhttp32Open website - {Website}HTTP{Website}
ContextMenuhttp32Open profile URL - {ProfileURL}HTTP{ProfileURL}

The menu items are added only for non-empty fields.

If the Website field does not contain http:// then specify the handler as http://{Website}.

Text File Queries

The file name and the code page can be specified in the HANDER_CODE field in the following format:

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

The file name can contain a relative path to the workbook directory.
In this case, the file can be moved with the workbook to another place with the same connection string.

Queries can contain parameters in the following format: {ParameterName=DefaultValue} or simply {ParameterName}

For example:

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

In this case, the values of the FileName and CodePage parameters are populated from the context and can be changed by a user.

Running Macros

You may configure SaveToDB 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>[, ...]]

Parameters can 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>[ ...]]

Parameters 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 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 desired sheets with the 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.

Creating Database Configuration Objects

SaveToDB Framework allows developers to add ready configuration objects quickly to any database on all supported platforms:
Microsoft Azure SQL Database, Microsoft SQL Server, Microsoft SQL Server Compact, Oracle Database, IBM DB2, MySQL, MariaDB, NuoDB, PostgreSQL, and SQLite.

Configuring Data Translation

Preface

SaveToDB allows translating database objects to a business language within Microsoft Excel, without changing the underlying database objects.

For example, users can see a user-friendly name "Budget Form" instead of a database name like xls41.uspBudgetForm, "Manager Name" instead of "MGR_NAME", etc.

SaveToDB supports translation and annotation for the following database objects and queries defined using the Query List configuration and the event handler configuration:

  • Database tables, views, stored procedures, and functions.
  • SQL codes, HTTP queries, and text file queries.
  • Macros, Windows Shell and CMD commands.
  • Fields of tables and views.
  • Parameters of stored procedures and other objects.

The translation is performed within Microsoft Excel after loading data.

Users can change the actual data translation language using the Options dialog box.

Object Translation and Annotation

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

  1. TABLE_SCHEMA
  2. TABLE_NAME
  3. LANGUAGE_NAME
  4. TRANSLATED_NAME
  5. TRANSLATED_DESC
  6. TRANSLATED_COMMENT

* Tables are used starting SaveToDB 7 and in previous versions for Microsoft SQL Server Compact.

The tables and views can contain an additional primary key column.

Configuration data formats:

TABLE_SCHEMATABLE_NAMELANGUAGE_NAMETRANSLATED_NAMETRANSLATED_DESCTRANSLATED_COMMENT
<Object schema>
or <Object type>
<Object name><Language code>[<Translated name>]
[<Description>][<Comment>]

TABLE_SCHEMA and TABLE_NAME define database objects or queries defined using the Query List configuration or the event handler configuration:

TABLE_SCHEMATABLE_NAME
<Table schema><Table name>
<View schema><View name>
<Procedure schema><Procedure name>
<Function schema><Function name>
CODE<SQL code name>
HTTP<HTTP query name>
TEXT<text query name>
MACRO<Menu item name>
CMD<Menu item name>
REFRESH<Menu item name>

TABLE_SCHEMA is ignored on Microsoft SQL Server Compact and SQLite. Only TABLE_NAME is used.

The LANGUAGE_NAME field must contain the first two characters of a language code.

The TRANSLATED_NAME field is used as a name of Query List, context menu and Actions menu items.

You may also use translated values on Excel sheets using the following named cells:

FieldNamed Cell
TRANSLATED_NAMESaveToDB_Name
TRANSLATED_DESCSaveToDB_Desc
TRANSLATED_COMMENTSaveToDB_Comment

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

See Configuring names cells about using annotation fields on sheets.

If the translation data have been changed in a database, or a user has changed the actual data translation language, then the translation data should be reloaded.

SaveToDB reloads configuration data in the following actions:

There is an example of the configuration view:

Configuration view of object translations

Translation and Annotation of Fields and Parameters

SaveToDB reads the translation configuration from tables* and views that contains the following fields:

  1. TABLE_SCHEMA
  2. TABLE_NAME
  3. COLUMN_NAME
  4. LANGUAGE_NAME
  5. TRANSLATED_NAME
  6. TRANSLATED_DESC

* Tables are used starting SaveToDB 7 and in previous versions for Microsoft SQL Server Compact.

The tables and views can contain an additional primary key column.

Configuration data formats:

TABLE_SCHEMATABLE_NAMECOLUMN_NAMELANGUAGE_NAMETRANSLATED_NAMETRANSLATED_DESC
[<Object schema>
or <Object type>]
[<Object name>]<Column name> or
<Parameter name>
<Language code>[<Translated name>]
[<Description>]

TABLE_SCHEMA and TABLE_NAME define database objects or queries as described above.
However, columns can have NULL values to define translation for groups of columns or parameters:

TABLE_SCHEMATABLE_NAMEDescription
[<Object schema>
or <Object type>]
<Object name>The translation is used for the specified object.
[<Object schema>
or <Object type>]
NULLThe translation is used for all schema objects by default.
NULLNULLThe translation is used for all objects by default.

TABLE_SCHEMA is ignored on Microsoft SQL Server Compact and SQLite. Only TABLE_NAME is used.

The mandatory COLUMN_NAME field defines a column or a parameter that is being translated.

The LANGUAGE_NAME field must contain the first two characters of a language code.

TRANSLATED_NAME field values are used to translate Excel table column headers and interface elements like ribbon parameters.

TRANSLATED_DESC field values are used as column header comments and interface element ScreenTips.

If the translation data have been changed in a database, or a user has changed the actual data translation language, then the translation data should be reloaded.

SaveToDB reloads configuration data in the following actions:

There is an example of the configuration view:

Configuration view of column translations

In the example, the translation data are used for all the fields and parameters of objects of the xls40 schema as the TABLE_NAME values are not specified.

Creating Database Configuration Objects

SaveToDB Framework allows developers to add this feature quickly to any database on all supported platforms:
Microsoft Azure SQL Database, Microsoft SQL Server, Microsoft SQL Server Compact, Oracle Database, IBM DB2, MySQL, MariaDB, NuoDB, PostgreSQL, and SQLite.

Configuring Parameter Values

Preface

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 SaveToDB 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, stores procedures, functions, or SQL codes to populate parameter values.

If such queries are defined for tables and views, then SaveToDB places on the ribbon customized fields only, and users cannot change the field parameter sets.

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

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

It is used for working with hierarchy data, for example.

Configuration View

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

  1. TABLE_SCHEMA
  2. TABLE_NAME
  3. PARAMETER_NAME
  4. SELECT_SCHEMA
  5. SELECT_NAME
  6. SELECT_TYPE
  7. SELECT_CODE

* Tables are used starting SaveToDB 7 and in previous versions for Microsoft SQL Server Compact.

The tables and views can contain an additional primary key column.

Configuration view format:

TABLE_SCHEMATABLE_NAMEPARAMETER_NAMESELECT_SCHEMASELECT_NAMESELECT_TYPESELECT_CODE
<Object schema>
See table 1.1
<Object name>
See table 1.1
[<Parameter name>
or <Column name>]
<Value query schema>
See table 1.2
<Value query name>
See table 1.2
<Value query type>
See table 1.2
<SQL code>
See table 1.2
Table 1.1
TABLE_SCHEMATABLE_NAME
<Table schema><Table name>
<View schema><View name>
<Procedure schema><Procedure name>
<Object schema><SQL code name>
<Object schema><HTTP query name>
<Object schema><text query name>
Table 1.2
SELECT_SCHEMASELECT_NAMESELECT_TYPESELECT_CODE
<Procedure schema><Procedure name>[PROCEDURE] 
<Function schema><Function name>FUNCTION 
<Table schema><Table name>TABLE 
<View schema><View name>VIEW 
<SQL code><SQL code name>CODE<SQL code>
<Schema><Name>RANGE *<Range name or address>
<Schema><Name>VALUES *<Values>

* SaveToDB 7.1 or higher.

TABLE_SCHEMA and TABLE_NAME define a customizable object. TABLE_SCHEMA is ignored on Microsoft SQL Server Compact and SQLite.

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.

You can use tables, views, stored procedures, functions, or SQL codes to select values.

The stored procedures, functions, or SQL codes can have parameters too that makes some of the parameters dependent on other parameters.

Configuration View 3.x-4.x

SaveToDB reads the configuration from the views* that contain the following fields:

  1. SPECIFIC_SCHEMA
  2. SPECIFIC_NAME
  3. PARAMETER_NAME
  4. SELECT_SCHEMA
  5. SELECT_NAME
  6. SELECT_TYPE

* Tables are used instead of views for Microsoft SQL Server Compact.

The views can contain an additional primary key column.

Configuration view format:

SPECIFIC_SCHEMASPECIFIC_NAMEPARAMETER_NAMESELECT_SCHEMASELECT_NAMESELECT_TYPE
<Object schema>
or <Object type>
See table 1.1
<Object name>
See table 1.1
[<Parameter name>
or <Column name>]
<Value query schema>
See table 1.2
<Value query name>
See table 1.2
<Value query type>
See table 1.2
Table 1.1
SPECIFIC_SCHEMASPECIFIC_NAME
<Table schema><Table name>
<View schema><View name>
<Procedure schema><Procedure name>
CODE<SQL code name>
HTTP<HTTP query name>
TEXT<text query name>
Table 1.2
SELECT_SCHEMASELECT_NAMESELECT_TYPE
<Procedure schema><Procedure name>[PROCEDURE]
<Function schema><Function name>FUNCTION
<Table schema><Table name>TABLE
<View schema><View name>VIEW
<SQL code><SQL code name>CODE

SPECIFIC_SCHEMA and SPECIFIC_NAME define a customizable object. SPECIFIC_SCHEMA is ignored on Microsoft SQL Server Compact.

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

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

You can use tables, views, stored procedures, functions, or SQL codes to select values.

The stored procedures, functions, or SQL codes can have parameters too that makes some of the parameters dependent on other parameters.

Using SQL Code

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

Configuration View Example

There is an example of the configuration view:

Configuration view of parameter values

The three different procedures are used to populate the BrandID, CategoryID and SubcategoryID parameters of the uspItem_usp_id procedure.
These parameters are shown on the ribbon.

Select procedure declaration headers:

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 the example, the uspParameterValues_SubcategoryID and uspParameterValues_BrandID stored procedures have the CategoryID parameter.
So, if a user changes the CategoryID parameter, then the values of the SubcategoryID and BrandID parameters are updated too accordingly to the CategoryID value.

Reloading Parameter Values in Microsoft Excel

SaveToDB reloads values of all parameters in the following actions:

Dependent parameter values are also reloaded when the base parameter has been changed.

In the example above, SubcategoryID and BrandID values are reloaded when the CategoryID parameter has been changed.

SaveToDB does not reload values to reduce the reload time in the following actions:

  • Refreshing data using Microsoft Excel.
  • Reloading data using the Reload menu item of the Reload menu.

Using Empty Value in Parameter Values

Users can set any parameter value if the parameter has no query to select its values.

If the query to select values is specified, users can select values only.

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

It is necessary as the dependent parameters must have the NULL value to select all data rows for the parent parameter.

SaveToDB does not add the NULL value to independent parameter value lists. Developers have full control over parameter values.

In some cases NULL should be in the list, in other cases should not.

This behavior has been changed in SaveToDB 3.0.

As a result, the SaveToDB 2.x queries for independent parameters should be modified to add the empty value if required.

For example, the following code in the query beginning can be used:

SELECT NULL AS ID, NULL AS Name UNION

Output Fields of Stored Procedures and Functions

If a select value procedure outputs one field, then the values are shown on the ribbon and are used as parameter values.

If the procedure outputs two or more fields, then the first field values are used as parameter values and the second field values are shown on the ribbon.

This feature is used to show value names on the ribbon while the identifier values are used in parameters and WHERE clauses.

Output Fields of Tables and Views

If the values are populated by tables or views, then SaveToDB builds the SELECT query itself.

A certain problem exists as the tables and views can have several fields, and some of them can be used in the WHERE clause.

For example, if the base object has the Category and Subcategory parameters, the view, that selects Subcategory values, should have the Category field in the WHERE clause.

If a table or a view has only one field, then the field values are used as parameter values and as ribbon parameter values.

If a table or a view has two or more fields, then the following rules are used.

The priority of finding a column with parameter values:

  1. The field with the parameter name.
  2. The field named as "ID".
  3. The first field with a parameter data type.

The priority of finding a column with ribbon parameter values:

  1. The field named as "Name" if the field is not used in the WHERE clause.
  2. The first field with a *CHAR data type (char, nchar, varchar, nvarchar) if the field is not used in the WHERE clause.

In most cases, SaveToDB successfully builds the queries for multi-column tables and views that allow using existing database views without changes.

However, in some cases, the views should be modified to change the field order.

For example:

A view has the CategoryID, SubcategoryID, CategoryName, and SubcategoryName fields and is used to select values for the SubcategoryID parameter.

If the base query has no the CategoryID parameter then the following select query is used:

SELECT DISTINCT SubcategoryID, CategoryName FROM ...

If the base query has the CategoryID parameter then the following select query is used:

SELECT DISTINCT SubcategoryID, CategoryName FROM ... WHERE CategoryID = @CategoryID

In the both cases, the wrong CategoryName field is selected as the first *CHAR field.

Accordingly, to select SubcategoryID values, the CategoryName should be moved to the position after the SubcategoryName field or removed at all.

Creating Database Configuration Objects

SaveToDB Framework allows developers to add ready configuration objects quickly to any database on all supported platforms:
Microsoft Azure SQL Database, Microsoft SQL Server, Microsoft SQL Server Compact, Oracle Database, IBM DB2, MySQL, MariaDB, NuoDB, PostgreSQL, and SQLite.

Configuring Table Formats

Preface

Users can change Excel data table queries using the Query List or the Data Connection Wizard.

When the query is being closed, SaveToDB saves the query table format in the active workbook and then restore it when the query is activated again.

The table format includes the following properties:

  • Cell formats.
  • Conditional formatting.
  • Applied auto-filters.
  • Table totals.
  • Data validation.
  • Active window properties.
  • Page setup properties.
  • User formula columns.

So the user sees the reopened query table format as it was when the user closed it.

Database developers can configure a database to store the table formats.
Moreover, application developers or advanced users can save the table formats in the database, and when a regular user opens a new query, he gets the completely formatted data.

It is a good idea to distribute applications as empty workbooks with the Readme worksheet only. When a new user connects to a database, he gets the superior quality tables.

The Table Format Wizard is used to manage the table formats.

View to Select Table Formats

SaveToDB reads the table formats from database tables* and views with the following fields:

  1. TABLE_SCHEMA
  2. TABLE_NAME
  3. TABLE_EXCEL_FORMAT_XML

* Tables are used starting SaveToDB 7 and in previous versions for Microsoft SQL Server Compact.

The tables and views can contain an additional primary key column.

Configuration view format:

TABLE_SCHEMATABLE_NAMETABLE_EXCEL_FORMAT_XML
<Table schema><Table name><Excel table format>
<View schema><View name><Excel table format>
<Procedure schema><Procedure name><Excel table format>
CODE<SQL code name><Excel table format>
HTTP<HTTP query name><Excel table format>
TEXT<text query name><Excel table format>

TABLE_SCHEMA is ignored on Microsoft SQL Server Compact and SQLite.

The table formats are stored in a database as XML. The maximum data size should be greater than 32K.

Recommended data types for TABLE_EXCEL_FORMAT_XML field:

  • XML for Microsoft SQL Server
  • NTEXT for Microsoft SQL Server Compact
  • NCLOB for Oracle Database
  • CLOB(200000) for IBM DB2
  • MEDIUMTEXT for MySQL and MariaDB
  • STRING for NuoDB

There is an example of the configuration view:

Configuration view of table formats

Procedure to Save Table Formats

To save the table formats to a database, SaveToDB uses the first available stored procedure* with the following parameters:

  1. Schema
  2. Name
  3. ExcelFormatXML

* SaveToDB uses internal procedures to save table formats for Microsoft SQL Server Compact.

Also, SaveToDB 7 can use INSERT and DELETE commands to save table formats loaded from the tables.

A user must have permission to execute the procedure to manage formats; otherwise, Save and Clear operations in the Table Format Wizard are unavailable.

Creating Database Configuration Objects

SaveToDB Framework allows developers to add this feature quickly to any database on all supported platforms:
Microsoft Azure SQL Database, Microsoft SQL Server, Microsoft SQL Server Compact, Oracle Database, IBM DB2, MySQL, MariaDB, NuoDB, PostgreSQL, and SQLite.

Working with Foreign Keys

Starting SaveToDB 6.5, you may implement complex Excel forms based on tables, views, and stored procedures with fewer efforts as the add-in solves all tasks of converting between foreign key values and their names.

For example, a database contains the following tables and relations:

Example database schema: tables and relations

The dbo38.Payment table contains foreign keys from the dbo38.Account, dbo38.Company, and dbo38.Item tables.

To create a form for the dbo38.Payment table, we just add the ValidationList handlers to the dbo01.EventHandlers table as shown below:

Validation list configuration

As a result, we have the following table in Excel:

Excel table with validation lists

The add-in has replaced the integer key values to names in Excel and has added validation lists that allow choosing values from the tables of foreign keys.

You may apply this technique also to views and stored procedures. See examples in the Developer Guide 6.5 for SQL Server in the SaveToDB SDK.

When a user selects a cell with a validation list, the add-in shows the List Editor like this:

List editor

The List Editor allows selecting values in a more convenient way. Just double-click on a value or press Enter on a selected value.

You may also filter values. Just type several characters from the desired name.

If you have closed the editor, you may reopen it using Options, Show List Editor Task Pane.

Using Excel Formulas

Customizing Excel formulas in Databases

Users can add formula columns to any Excel data table. Such columns exist in the user workbooks only.
Users can save table formats including formula columns in databases and restore them in other workbooks.

Developers can define Excel formulas (calculated within Excel) directly in views, stored procedures, or in SQL codes.

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

Such formula columns are shown to all users and do not require additional actions to distribute calculated columns.

Formulas must be in the international Microsoft Excel formula notation to use in any localized version of Microsoft Excel.
The main rules are using English names of formulas and commas as a parameter separator.

Formulas are loaded from a database as text values, and SaveToDB changes the text to formulas.
The columns can show formula texts, and the screen can blink in this phase.
To fix this issue, define an empty value for showing string values in the cell format (fourths place in the custom cell format).
For example, the custom cell format "0;-0;;" turns off showing zero and string values.

Customizing DDE Formulas

Database queries can return different formulas for different rows that can be used to configure DDE and RTD (Real-Time Data) formulas.

Customizing DDE formulas using SQL is much easy then customizing using macros.

You may use even Microsoft SQL Server Compact database located in the workbook directory.

For example.

A workbook has a table with the Symbol and Comment fields. We have to add DDE formulas for the Thinkorswim trading platform (TOS).

Simple steps to create an application:

  1. Create a database in the workbook directory and create the WatchList table using the Publish Wizard.
  2. Install SaveToDB Framework into the database using the SaveToDB Framework Installer.
  3. Generate a configuration workbook using the Configuration Workbook Generator.
  4. Add the source WatchList table and a new SQL code (WatchList TOS) with DDE formulas into the QueryList configuration table (see below). Save the table.
  5. Connect to the WatchList TOS query on a new sheet using the Data Connection Wizard.

As a result, we have an Excel table with configured DDE formulas.

You may add, delete, and update data (Symbol and Comment). Then click the Save button.

When the table has been saved, the query is refreshed, and the formulas are updated for new symbols.

QueryList table configuration:

IDTABLE_SCHEMATABLE_NAMETABLE_TYPEINSERT_PROCEDUREUPDATE_PROCEDUREDELETE_PROCEDURE
1 WatchListTABLE   
2SELECT
Symbol
, '=TOS|LAST!' + Symbol AS Last
, '=TOS|NET_CHANGE!' + Symbol As NetChange
, '=TOS|PERCENT_CHANGE!' + Symbol As Change
, '=TOS|HIGH!' + Symbol As High
, '=TOS|LOW!' + Symbol As Low
, '=SUBSTITUTE(TOS|VOLUME!' + Symbol+'," ","")+0' As Volume
, Comment
FROM
WatchList
WatchList TOSCODEWatchListWatchListWatchList

The SQL code is specified in the TABLE_SCHEMA column; TABLE_TYPE must have the CODE type for such queries.

The WatchList table is specified in the INSERT_PROCEDURE, UPDATE_PROCEDURE and DELETE_PROCEDURE fields. So, data changes of the query can be saved to the base WatchList table.

See Configuring Query List and Configuring Saving Changes.

Possible further steps to modify the application:

  • You may change WatchList columns using the Publish Wizard. Just republish the table.
  • You may change existing queries in the QueryList table in the configuration workbook. Then reload data and configuration in the working workbook.
  • You may add new queries into the QueryList table in the configuration workbook and open them in the working workbook using the Data Connection Wizard or the Query List.

Using Named Cells

Updatable Named Cells

SaveToDB updates specially named cells during data refresh:

Named CellDescription
SaveToDB_ConnectionStringThe query connection string.
SaveToDB_CommandTextThe query command text.
SaveToDB_ObjectThe database object name as <schema>.<name> or a TABLE_NAME value for SQL-codes, HTTP queries, and text file queries from the Query List configuration view.
SaveToDB_NameThe value of the TRANSLATED_NAME field from the object translation configuration view if exists; otherwise, the same as SaveToDB_Object.
SaveToDB_DescThe value of the TRANSLATED_DESC field from the object translation configuration view.
SaveToDB_CommentThe value of the TRANSLATED_COMMENT field from the object translation configuration view.
SaveToDB_ElapsedMillisecondsQuery execution time in milliseconds.

You may use these named cells to make report headers and descriptions or to debug current query properties.

To show extended descriptions of Excel forms, you may use HTTP links to online documentation using HTTP handlers in the Actions menu.

SaveToDB_Name, SaveToDB_Desc, and SaveToDB_Comment depend on a data language selected using the Options menu.

SaveToDB also updates the cells named as active query parameters. Such cells also can be used to update the query parameters. This is a dual-direction link.

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

It is the best practice to use named cells with the sheet scope as a workbook can contain multiple data sheets.

Using Parameters

Customizing Query Parameters

The SaveToDB add-in automatically detects parameters of stored procedures, OData FunctionImports, and HTTP queries.

SaveToDB allows users to customize WHERE fields of tables and views in a visual mode.

Developers can define parameters for other types of queries using special insertions in query texts.

These query texts are used in:

Parameter insertion formats:

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

Customizing Parameter Value Lists

Developers can customize parameter value lists. See Configuring parameter values.

Parameter Value Sources

Parameters can get values from different sources:

Value SourceScopeComment
A table column of the same nameAll typesSource object column names without translation are used.
A ribbon parameter of the same nameAll typesRibbon parameters are shown for queries inserted into Excel tables.
Ribbon parameters are being synchronized with Excel named cells. Parameters with predefined names (see below) are not shown on the ribbon.
An Excel named cell of the same nameAll typesExcel named cell value
An active query parameter of the same nameAll typesFor example, event handlers of EXEC dbo.uspCustomers @ManagerID=101 can use a value of the ManagerID parameter.
Predefined parameters:
WorkbookNameAll typesActive workbook name without directory
WorkbookPathAll typesActive workbook directory. The value is empty for new workbooks.
SheetNameAll typesActive sheet name
DataLanguageAll typesData language code selected using SaveToDB Options
TableNameEvent handlersActive query object
EventNameEvent handlersEvent name as defined in the EVENT_NAME field
ColumnNameEvent handlersActive column name
CellValueEvent handlersActive cell value. Use text type for this parameter and convert the value to a required data type in SQL code.
MergeDate or merge_dateData mergeData merge SQL generation date and time. The value allows detecting data presence in the latest data set.

Parameter value sources are shown in the priority order from highest to lowest.