Configuring Saving Changes

Configuring Saving Changes

The SaveToDB add-in allows saving data changes back to database tables and updatable views, and OData web services by default.

Just click the Save button to save changes.

This topic shows how to configure saving changes using the Save button for data loaded from views, stored procedures, SQL codes, HTTP queries, and text files.

Note that you may also use other techniques:

Basic Ways to Save Changes

There are two basic ways to save changes of data loaded from any data source:

  • To update a single target ("base") table;
  • To update multiple target tables.

In the first case, it is enough to specify the base table. The add-in generates and uses the required INSERT, UPDATE, and DELETE commands automatically.

In the second case, you have to provide SQL codes or stored procedures for INSERT, UPDATE, and DELETE operations ("edit procedures").

Parameters of Edit Procedures

Edit procedures may contain parameters with the names of selected fields.

For example, the select procedure has the following code:

SELECT ID, [Date], [Sum], CompanyID, ItemID FROM dbo.Payments

We may use the following SQL codes to update the underlying table (INSERT, UPDATE, and DELETE codes):

INSERT INTO dbo.Payments ([Date], [Sum], CompanyID, ItemID) VALUES (@Date, @Sum, @CompanyID, @ItemID)

UPDATE dbo.Payments SET [Date] = @Date, [Sum] = @Sum, CompanyID = @CompanyID, ItemID = @ItemID WHERE ID = @ID

DELETE FROM dbo.Payments WHERE ID = @ID

Use the :ParameterName form like :Date for Oracle, DB2, MySQL, PostrgeSQL, and NuoDB.

This model is simple and effective.

However, it requires that the selected fields must contain no spaces.

You may create columns for parameter values by formulas. This is useful for importing data from the Web and text files.

Using row values of selected fields as parameter values is the main scenario.

However, if the Excel table does not contain the column with the parameter name, the add-in looks for the parameter value in other places in the following order:

  1. A ribbon parameter with a parameter name (the parameter of the select query);
  2. A named cell with a parameter name;
  3. A built-in variable.

Specifying Target Tables and Edit Procedures

The add-in looks for target tables and edit procedures in the following order:

  1. Query list configurations;
  2. Object configurations;
  3. Name considerations;
  4. Automatic detection.

Query List Configurations

When users connect to a database using the Connection Wizard, they may select a configured query list table or view as a list of objects.

Such tables or views must contain the following fields:

  1. An optional primary key column like ID
  2. TABLE_SCHEMA *
  3. TABLE_NAME
  4. TABLE_TYPE
  5. TABLE_CODE
  6. INSERT_PROCEDURE
  7. UPDATE_PROCEDURE
  8. DELETE_PROCEDURE
  9. PROCEDURE_TYPE

* The add-in does not use the TABLE_SCHEMA field values with Microsoft SQL Server Compact and SQLite.

You may use the INSERT_PROCEDURE, UPDATE_PROCEDURE, and DELETE_PROCEDURE fields to specify:

  • A base table (the same table name in all fields);
  • Stored procedure names (including schemas) for INSERT, UPDATE, and DELETE operations accordingly;
  • SQL codes for INSERT, UPDATE, and DELETE operations accordingly.

SaveToDB 8.16 allows using a single procedure in the UPDATE_PROCEDURE field for all operations.

Also, you may specify the value in the INSERT_PROCEDURE field only:

  • A target table to merge data;
  • A stored procedure name (including a schema) for the MERGE operation;
  • An SQL code for the MERGE operation.

In the merge mode, the add-in executes operations for all rows in an Excel table on the Save button click.

The merge mode is useful for importing data from external data sources.

You may use the MergeDate or merge_date built-in variables as parameters to detect deleted rows in external data sources.

Using query list views is a main method to configure saving changes in the previous SaveToDB versions.

It has the highest priority in SaveToDB 8 also.

It is a good method.

However, user must select the query list view explicitly. It does not work by default.

Object Configurations

SaveToDB 8 introduces a new type of configuration objects.

It reads object configrations from tables and views with the following fields:

  1. An optional primary key column like ID
  2. TABLE_SCHEMA *
  3. TABLE_NAME
  4. TABLE_TYPE
  5. TABLE_CODE
  6. INSERT_OBJECT
  7. UPDATE_OBJECT
  8. DELETE_OBJECT

* The add-in does not use the TABLE_SCHEMA field values with Microsoft SQL Server Compact and SQLite.

Contrary to query list views, the add-in applies this configuration to any object.

So, users may select a default query list or a specific schema, and they will have configured objects.

SaveToDB Framework 8 creates the xls.objects table that implements this specification.

The new configuration object has renamed fields (INSERT_OBJECT, UPDATE_OBJECT, and DELETE_OBJECT) to avoid mixing with query list configurations.

The fields have the same meaning and features.

Name Considerations

The SaveToDB add-in links edit procudures with the _INSERT, _UPDATE, and _DELETE suffixes to objects with the name without suffixes or with the _SELECT suffix.

For example, you may have the following objects in a database:

  • dbo.uspPayments_select
  • dbo.uspPayments_insert
  • dbo.uspPayments_update
  • dbo.uspPayments_delete

In this case, the add-in links the edit procedures to the dbo.uspPayments_SELECT object automatically.

Automatic Detection

SaveToDB 8 loads and caches complete database metadata.

It analyzes object definitions (if a user has the VIEW DEFINITION permission) and may configure saving changes automatically.

For example, a stored procedure has the code like this

SELECT * FROM dbo.Payments WHERE COALESCE(CompanyID, 0) = COALESCE(@CompanyID, CompanyID, 0)

The add-in enables the Save button and saves data changes to the dbo.Payments table.

To enable this feature, grant VIEW DEFINITION permissions on underlying tables, views, and procedures to users.

Technical Remarks

Checking Configuration

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

Use the Check SQL item of the Save menu to check the generated SQL code used to save data changes.

Row Numbers

To be saveable, the Excel table must have row numbers turned on.

To turn on row numbers, run the Excel Data, Connections, Properties dialog box and check Include row numbers.

Alternatively, run the Reload, Configure Connection... dialog box, click Next, then Finish.

Hidden Data Sheets

SaveToDB saves the loaded data required to save changes on hidden workbook sheets.

Due this, users may close and reopen workbooks multiple times before saving changes to a database.

The add-in inserts such worksheets during the connection using the Connection Wizard.

You may manage worksheets using the Options dialog box.

Transaction Mode

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

If any row cannot be saved, the database server rollbacks the entire transaction.

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.