Configuring Query Lists

Configuring Query Lists

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:

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.