SaveToDB Framework 7 for SQLite

SaveToDB Framework 7 for SQLite

SaveToDB Framework for SQLite

SaveToDB Framework for SQLite contains ready-to-use solutions for application development using Microsoft Excel and SQLite.

The framework can be easily installed and uninstalled.

The framework can be installed from the SaveToDB add-in in a couple of minutes. Also, you can generate an Excel workbook to customize SaveToDB features using Microsoft Excel.

See SaveToDB Framework Installer and Configuration Workbook Generator.

 

After installation you can customize required features:

 

SQLite supports fewer features than other database servers.
Stored procedures are not supported. A standalone database file is used instead of a server database.

Microsoft Excel allows connecting to SQLite using ODBC drivers.
In addition, the SaveToDB add-in includes .NET SQLite Data Provider that allows for working with SQLite database files without installed ODBC drivers.

 

Microsoft Excel applications with underlying SQLite databases have great features:

  • The same data can be used on different sheets in different workbooks as the data stored in database tables.
  • The data views can be customized using views and SQL codes.
  • SQL codes or HTTP queries can be easily executed using the ribbon Query List, Microsoft Excel context menu, or the SaveToDB Actions menu.
  • SQL codes can be used for handling Microsoft Excel events.
  • Table fields can be translated into multiple languages within Microsoft Excel.

SQLite perfectly complements Microsoft Excel for implementing applications for personal use.

For example, views and SQL codes can be used to build DDE formulas instead of Excel macros.

 

Additional tools for working with SQLite are not necessary as SaveToDB allows end users to:

Install and Uninstall

Installation Methods

SaveToDB Framework supports four methods to install or remove the framework:

  1. Installation using the SaveToDB Framework Installer integrated into the SaveToDB add-in.
  2. Installation using the standalone SaveToDB Framework Installer.
  3. Installation using source codes.
  4. Installation using batch files.

The first two methods allow installing or removing the framework in databases on all supported platforms:
Microsoft SQL Server, Microsoft SQL Server Compact, Oracle Database, IBM DB2, MySQL, MariaDB, NuoDB, PostgreSQL, and SQLite.

See SaveToDB Framework Installer.

The source code is located in the "SaveToDB Framework for SQL Server Compact" folder:

  • framework-install-en.sql
  • framework-remove-en.sql

The installation using batch files is described below.

Install and Uninstall Using Batch Files

The source code and batch files are located in the "SaveToDB Framework for SQL Server Compact" folder.

Installing SaveToDB Framework

  1. Run 1-edit-config.cmd and edit the "setup" connection.
  2. Run 2-install-savetodb-framework.cmd.
  3. Run 3-clear-credentials.cmd and clear the "setup" connection password.

Instead of clearing the setup password on step 3, you may protect gsqlcmd.exe.config:

select the "User protected" option in gConnectionManager.

Note that the free SQLite version does not support encrypting.

Uninstalling SaveToDB Framework

  1. Run 1-edit-config.cmd and edit the "setup" connection.
  2. Run framework-remove.cmd.
  3. Run 3-clear-credentials.cmd and clear the "setup" connection password.

Verifying Framework Objects

  1. Run 1-edit-config.cmd and edit the "setup" connection.
  2. Run framework-list.cmd.
  3. Run 3-clear-credentials.cmd and clear the "setup" connection password.

Query Lists

Preface

SaveToDB allows changing queries using the ribbon Query List.

It is a useful feature as application workbooks can contain fewer worksheets. Also, users can easily connect to new database objects after query list refresh.

The query lists can contain database tables, custom SQL codes, HTTP and text file queries.

The query list configuration views are also used to configure base tables or custom SQL codes to save changes of data loaded from SQL codes and from HTTP and text file queries.

SaveToDB Framework for SQLite creates database tables to configure these advanced features.

See Configuring Query List, Configuring Saving Changes.

Configuration Data Table

The QueryList table contains query list configuration data and is used by the SaveToDB add-in.

CREATE TABLE [QueryList] (
    [ID] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    [TABLE_SCHEMA] [nvarchar](128),
    [TABLE_NAME] [nvarchar](128) NOT NULL,
    [TABLE_TYPE] [nvarchar](128) NOT NULL,
    [TABLE_CODE] [ntext],
    [INSERT_PROCEDURE] [ntext],
    [UPDATE_PROCEDURE] [ntext],
    [DELETE_PROCEDURE] [ntext],
    [PROCEDURE_TYPE] [nvarchar](128)
);

CREATE UNIQUE INDEX [QueryList_TableName]
    ON [QueryList] (
    [TABLE_NAME]
);

The table is used to:

  • Define database objects to show in the ribbon Query List.
    Specify table names in the TABLE_NAME field.
  • Define SQL codes to select data using the ribbon Query List.
    Specify an SQL code in the TABLE_CODE field and the CODE type in the TABLE_TYPE field.
  • Define HTTP queries to load web data using the ribbon Query List.
    Specify an HTTP query in the TABLE_CODE field and the HTTP type in the TABLE_TYPE field.
  • Define queries to load data from text files using the ribbon Query List.
    Specify a text file query in the TABLE_CODE field and the TEXT type in the TABLE_TYPE field.
  • Define base tables to save changes of data selected using SQL codes or to merge data loaded from any data source including HTTP and text file queries.
    To have the possibility to save changes, specify the table in all INSERT_PROCEDURE, UPDATE_PROCEDURE, and DELETE_PROCEDURE fields.
    To have the possibility to merge data, specify the table in the INSERT_PROCEDURE field only.
  • Define SQL codes to save changes of data selected using SQL codes.
    Specify the code in the INSERT_PROCEDURE, UPDATE_PROCEDURE, and DELETE_PROCEDURE fields.
  • Define SQL codes to merge data loaded from any data source including HTTP and text file queries.
    Specify the code in the INSERT_PROCEDURE field.

See Configuring Query List, Configuring Saving Changes.

TABLE_NAME field values must be non-empty and unique.

The TABLE_SCHEMA field is ignored by the SaveToDB add-in. However, the field may be used by developers.

When you use the table as a query list in the Data Connection Wizard, the add-in shows only objects configured in the table.

To show the configured objects and existing database objects, use the following view as a query list.

Query List View

The viewQueryList view selects database objects and the objects configured in the QueryList table.

CREATE VIEW viewQueryList
AS
SELECT
    NULL AS TABLE_SCHEMA
    , t.name AS TABLE_NAME
    , t.type AS TABLE_TYPE
    , NULL AS TABLE_CODE
    , NULL AS INSERT_PROCEDURE
    , NULL AS UPDATE_PROCEDURE
    , NULL AS DELETE_PROCEDURE
    , NULL AS PROCEDURE_TYPE
FROM
    sqlite_master t
WHERE
    t.type IN ('table', 'view')
    AND t.name NOT IN ('sqlite_master', 'sqlite_sequence',
        'ColumnTranslation', 'ObjectTranslation', 'EventHandlers', 'ParameterValues', 'TableFormats', 'QueryList',
        'viewColumnTranslation', 'viewObjectTranslation', 'viewEventHandlers', 'viewParameterValues', 'viewTableFormats', 'viewQueryList')
UNION
SELECT
    t.TABLE_SCHEMA
    , t.TABLE_NAME
    , t.TABLE_TYPE
    , t.TABLE_CODE
    , t.INSERT_PROCEDURE
    , t.UPDATE_PROCEDURE
    , t.DELETE_PROCEDURE
    , t.PROCEDURE_TYPE
FROM
    QueryList t
;

You may use this view as a data source for other views to show subsets of objects to users.

Event Handlers

Preface

SaveToDB allows using SQL codes and HTTP queries for handling the following Microsoft Excel events:

  • Change
  • DoubleClick
  • SelectionChange

Using event handling developers can implement tasks like database data changes, drill-down queries, or master-detail interfaces.

Also, developers can customize the Microsoft Excel context menu and SaveToDB Actions menu to run:

  • SQL codes
  • HTTP queries
  • Text file queries
  • Macros
  • Windows Shell and CMD commands

See Configuring Event Handlers.

Configuration Data Table

The EventHandlers table contains configuration data used by the SaveToDB add-in to get the event handler configuration data.

CREATE TABLE [EventHandlers] (
    [ID] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    [TABLE_SCHEMA] [nvarchar](128),
    [TABLE_NAME] [nvarchar](128) NOT NULL,
    [COLUMN_NAME] [nvarchar](128),
    [EVENT_NAME] [nvarchar](50) NOT NULL,
    [HANDLER_SCHEMA] [nvarchar](128),
    [HANDLER_NAME] [nvarchar](128) NOT NULL,
    [HANDLER_TYPE] [nvarchar](60),
    [HANDLER_CODE] [ntext],
    [TARGET_WORKSHEET] [nvarchar](255),
    [MENU_ORDER] integer,
    [EDIT_PARAMETERS] [bit]
);

CREATE UNIQUE INDEX [EventHandlers_TableName_ColumnName_EventName_HandlerName]
    ON [EventHandlers] (
    [TABLE_NAME],
    [COLUMN_NAME],
    [EVENT_NAME],
    [HANDLER_NAME]
);

See field description in the Configuring Event Handlers topic.

The TABLE_SCHEMA field is ignored in SQLite configurations. Database and non-database objects must be identified by unique names.

The HANDLER_CODE field can contain SQL codes, HTTP and text queries, macro codes, or Windows Shell and CMD commands.

Data Translation

Preface

SaveToDB allows translating names of database objects, object fields, and code parameters within Microsoft Excel.

It is a useful feature as the names can be changed in configuration tables without changes of underlying database objects.

In addition, the translation data are used in ScreenTips and table header comments.

See Configuring Data Translation.

Object Translation Table

The ObjectTranslation table contains database object translation data and is used by the SaveToDB add-in to get the object translation data.

CREATE TABLE [ObjectTranslation] (
    [ID] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    [TABLE_SCHEMA] [nvarchar](128),
    [TABLE_NAME] [nvarchar](128) NOT NULL,
    [LANGUAGE_NAME] [nchar](2) NOT NULL,
    [TRANSLATED_NAME] [nvarchar](128),
    [TRANSLATED_DESC] [nvarchar](255),
    [TRANSLATED_COMMENT] [nvarchar](4000)
);

CREATE UNIQUE INDEX [IX_ObjectTranslation_TableName_LanguageName]
    ON [ObjectTranslation] (
    [TABLE_NAME],
    [LANGUAGE_NAME]
);

See field description in the Configuring Data Translation topic.

The TABLE_SCHEMA field is ignored in SQLite configurations. Database tables and non-database objects must be identified by unique names.

The size of the TRANSLATED_* fields can be changed.

Column Translation Table

The ColumnTranslation table contains database object field and parameter translation data and is used by the SaveToDB add-in.

CREATE TABLE [ColumnTranslation] (
    [ID] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    [TABLE_SCHEMA] [nvarchar](128),
    [TABLE_NAME] [nvarchar](128),
    [COLUMN_NAME] [nvarchar](128) NOT NULL,
    [LANGUAGE_NAME] [nchar](2) NOT NULL,
    [TRANSLATED_NAME] [nvarchar](128),
    [TRANSLATED_DESC] [nvarchar](1024)
);

CREATE UNIQUE INDEX [ColumnTranslation_TableName_ColumnName_Language]
    ON [ColumnTranslation] (
    [TABLE_NAME],
    [COLUMN_NAME],
    [LANGUAGE_NAME]
);

See field description in the Configuring Data Translation topic.

The TABLE_SCHEMA field is ignored in SQLite configurations. Database tables and non-database objects must be identified by unique names.

Parameter Values

Preface

SaveToDB allows using views and SQL codes to populate ribbon parameter values.

The ribbon parameters are used to change parameters of SQL codes, HTTP and text file queries, or WHERE fields for tables and views.

See Configuring Parameter Values.

Configuration Data Table

The ParameterValues table contains configuration data used by the SaveToDB add-in.

CREATE TABLE [ParameterValues] (
    [ID] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    [TABLE_SCHEMA] [nvarchar](128),
    [TABLE_NAME] [nvarchar](128) NOT NULL,
    [PARAMETER_NAME] [nvarchar](128) NOT NULL,
    [SELECT_SCHEMA] [nvarchar](128),
    [SELECT_NAME] [nvarchar](128) NOT NULL,
    [SELECT_TYPE] [nvarchar](128),
    [SELECT_CODE] [ntext]
);

CREATE UNIQUE INDEX [ParameterValues_SpecificName_ParameterName]
    ON [ParameterValues] (
    [TABLE_NAME],
    [PARAMETER_NAME]
);

See field description in the Configuring Parameter Values topic.

The TABLE_SCHEMA field is ignored in SQLite configurations. Database and non-database objects must be identified by unique names.

The SELECT_CODE field can contain SQL codes to select parameter values. In this case, the SELECT_TYPE field must contain the CODE type.

Table Formats

Preface

SaveToDB allows storing and distributing Microsoft Excel table formats of database objects using a database.

Database objects are formatted in Microsoft Excel when the objects are connected for the first time, or when required using the Table Format Wizard.

Table formats include the following elements:

  • Cell format.
  • Conditional formatting.
  • Formula columns.
  • Table totals.
  • Applied auto-filters.
  • Print page setup.
  • Window properties.
  • Column visibility.
  • Custom table views.

Use the Table Format Wizard to save, clear, or apply table formats.

See also Configuring Table Formats.

Configuration Data Table

The TableFormats table contains Microsoft Excel table formats of database objects.

CREATE TABLE [TableFormats] (
    [ID] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    [TABLE_SCHEMA] [nvarchar](128),
    [TABLE_NAME] [nvarchar](128) NOT NULL,
    [TABLE_EXCEL_FORMAT_XML] [ntext]
);

CREATE UNIQUE INDEX [TableFormats_TableName]
    ON [TableFormats] (
    [TABLE_NAME]
);

The data are updated by the SaveToDB add-in using built-in procedures.

Version History

Version 7.2, March 20, 2017

[+] New types of event handlers: ConvertFormulas and DoNotConvertFormulas.
[*] Added validation lists for the QueryList, EventHandlers, and ParameterValues tables and derived views.
[*] The EventHandlers.HANDLER_NAME field may have NULL values.

Version 7.1, February 3, 2017

[*] New VALUES and RANGE types in the SELECT_TYPE field.
[*] New VALUES and RANGE types in the HANDLER_TYPE field.

Version 7.0, January 17, 2017

[+] The first version.

System Requirements

Supported Versions of SaveToDB:

  • SaveToDB 7.0 and higher

Supported Versions of SQLite:

  • SQLite 3

SaveToDB Examples for SQLite

SaveToDB SDK includes examples that demonstrate creating personal applications using Microsoft Excel and SQLite.

The SDK includes complete source codes and batch files used to install and remove applications.

Monthly Cash Flow for SQLite

The example application allows getting money transactions in one place and building cash flow statements.
The application uses a local SQLite database, so you may use it right after installation.

Northwind

The example shows using the following features:

  • Cursors and forms fields.
  • Implementing master-detail interfaces.
  • Showing images.
  • Pivot table views.

DataTypes

The example helps testing working with various SQLite data types in Excel.

Installing and Uninstalling Examples

Installing Application Examples for SQLite

Use the following steps to install the application examples for SQLite:

  1. Install example database objects.
  2. Change connection data in Microsoft Excel example workbooks.

Installing Examples

The source codes of examples for SQLite are located in the "source code" subfolders.

To install the example, open the subfolder and do the following:

  1. Run 1-edit-config.cmd and edit the "setup" connection.
  2. Run 2-create-database.cmd if the database does not exist.
  3. Run 3-install-savetodb-framework.cmd if SaveToDB Framework for SQLite is not installed yet.
  4. Run 4-install-application.cmd.
  5. Run 5-clear-credentials.cmd and clear the "setup" connection password.

You may create a SQLite database in a visual mode in the first step.

Instead of clearing the setup password on the last step, you may protect gsqlcmd.exe.config by selecting the "User protected" option in gConnectionManager.

Alternatively to batch files, you may use queries from the application-install.sql file to install the examples.

Changing Excel Workbook Connections

Examples contain preconfigured Excel workbooks.

You should change the database files and passwords in the workbooks if you have changed the default settings in the previous step.

By default, all the examples use database files with empty passwords in the example folders.

You may use the Change Connection Wizard to modify all connections at once:

  1. Select the SaveToDB tab and run the Change Connection Wizard,
  2. Connect to the target database.
  3. Select all the connections and click Finish.

Then you should refresh all the data tables. You may use the Reload, Reload All Workbook Tables menu item.

Uninstalling Application Examples for SQLite

To uninstall the application example, open the "source code" folder and do the following:

  1. Run 1-edit-config.cmd and edit the "setup" connection.
  2. Run application-remove.cmd.
  3. Run 5-clear-credentials.cmd and clear the "setup" connection password.

Instead of clearing the setup password on the last step, you may protect gsqlcmd.exe.config by selecting the "User protected" option in gConnectionManager.

Alternatively to batch files, you may use queries from the application-remove.sql file.

Command Line Utility gsqlcmd

The batch files to install and remove the examples use the command line utility gsqlcmd.
gsqlcmd allows executing SQL scripts and exporting data against all SaveToDB supported database platforms including SQLite.

SaveToDB installs gsqlcmd and changes the PATH environment variable that allows using gsqlcmd anywhere.

SaveToDB SDK includes the gsqlcmd folder with the executables, and the utility is available from the SaveToDB SDK subfolders only.

Version History

Version 7.2, March 20, 2017

[*] Updated SaveToDB Framework 7.2.

Version 7.0, January 17, 2017

[+] The first version.
[+] The Monthly Cash Flow for SQLite application.
[+] The Northwind example.
[+] The DataTypes example.

System Requirements

Supported Versions of SaveToDB:

  • SaveToDB 7.0 and higher

Supported Versions of SaveToDB Framework for SQLite:

  • SaveToDB Framework 7.0 for SQLite

Supported Versions of SQLite:

  • SQLite 3
This website is using cookies. By continuing to browse, you give us your consent to our use of cookies as explained in our Cookie Policy.OK