SaveToDB Configuration Poster

SaveToDB Configuration Poster

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.