Configuring Parameter Values

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:

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.