5.3.3 Global Options

Each operation, such as Import MySQL Data, has its own set of options. This section describes the options that apply to all connections, SQL queries, spatial data, imported tables, and edit sessions.

To configure global options, open an existing connection and then click Options in the MySQL for Excel task pane.

Option buttons include:

• Accept: Saves option changes to your host and preserves these changes across all sessions and future Excel instances.

• Reset to Defaults: Resets all option values in the current options window to their default settings. Click Accept to save the changes.

A set of global options affect the entire add-in, as described in the sections that follow.

• Connections Tab

• SQL Queries Tab

• Spatial Data Tab

• Imported Tables Tab

• Edit Sessions Tab

• Manage Import/Edit Connections Information

Connections Tab

The following figure shows the options that apply to all MySQL connections.

Figure 5.5 Global Options: Connections

Connection Options:

• Wait [ ] seconds for a connection to the server before timing out. Defaults to 15.

• Wait [ ] seconds for a database query to execute before timing out. Defaults to 60.

• Automatic connections migration delayed until: Migration status

  Migrate stored connections to MySQL Workbench now

  You can create new MySQL connections in MySQL for Excel when MySQL Workbench is not installed. As soon as you install MySQL Workbench, MySQL for Excel attempts to migrate the saved connections to merge them with those of MySQL Workbench to enable connection reused in both products. MySQL connections cannot be migrated if MySQL Workbench is open.

  MySQL for Excel prompts you to either migrate the connections automatically, keep the connections stored with MySQL for Excel only, or to delay the migration by:

  • one hour

  • one day

  • one week

  • one month

  • indefinitely

  Delaying by an hour, day, week, or month displays the prompt to migrate again after that time elapses. If you choose to delay the migration indefinitely, you can click Migrate stored connections to MySQL Workbench now to migrate all the new connections from MySQL for Excel to MySQL Workbench manually, as long as MySQL Workbench is installed.

SQL Queries Tab

The following figure shows the options that apply to all SQL queries.

Figure 5.6 Global Options: SQL Queries

SQL Queries Options:

• Do not show SQL statements sent to the server: When the option is selected, SQL statements are not displayed and only their results are displayed in the information dialog. Selected by default.

• Preview SQL statements before they are sent to the server: When selected, the option adds an extra step to the Create New Schema, Export Data, Append Data, and Edit Data operations before a statement is committed to the server. This option opens the Review SQL Script dialog, as shown in the figure that follows for an export-data operation.

  Figure 5.7 The MySQL for Excel configuration: Preview Option

  
  
  

  From here you can modify the SQL statements before they are executed, which also enables the Original Query button. If clicked, it will revert all modifications to the script to restore the SQL to its original form (when the dialog first opened).

  This option is deselected by default.

• Show executed SQL statements along with their results: When enabled, SQL statements are first executed and then the information dialog includes both the results and the executed statements. This is helpful when reviewing the recently executed queries when comparing the results.

  This option is deselected by default.

Spatial Data Tab

The spatial data option (see the figure that follows) enables you to choose a format for handling the data as text. Use the drop-down list to select a format to apply:

• Well-Known Text (default)

• Keyhole Markup Language

• Geography Markup Language

• GeoJSON

Figure 5.8 Global Options: Spatial Data

Imported Tables Tab

The following figure shows the options apply to all imported tables.

Figure 5.9 Global Options: Imported Tables

Imported Tables Options:

• Restore imported MySQL data in Excel Tables when:

  • Opening an Excel workbook: Selected by default.

  • Showing the Add-In's sidebar: Deselected by default.

• Manage connection information stored in the user settings file

  Provides a list of saved Excel files with information about MySQL connections. Clicking this button opens the Manage Import/Export Connection Information dialog.

Edit Sessions Tab

The following figure shows the options that apply to all edit sessions.

Figure 5.10 Global Options: Edit Sessions

Edit Session Options:

• Preview MySQL table data before an Edit Data session is opened: Selected by default.

• Use optimistic updates on all Edit Data sessions. This option helps prevent unintentional data overwrite, in that it checks for external edits before committing your changes. For example, between the time you loaded the data into Excel, made changes in Excel, and committed, a different user could have edited the same cells elsewhere in MySQL using MySQL Workbench or some other means. The optimistic updates feature checks for these changes, and notifies you accordingly.

  Optimistic updates can also be configured at runtime for all edit sessions, or for a specific edit session by right-clicking the Edit Session floating dialog and choosing the desired Use Optimistic Update option, as shown in the figure that follows.

  Figure 5.11 Optimistic Updates: Configuring at Runtime

  
  
  

  The optimistic updates option is selected by default.

  Tolerance for FLOAT and DOUBLE comparisons in WHERE clause: This related option provides a way to edit data of type FLOAT or DOUBLE that enables proper row-matching in the database when it is used together with optimistic updates. For example, the WHERE clause for optimistic updates uses a construct similar to the following to match rows in the database:

  ??float_or_double_column?? BETWEEN 
  [some_float_or_double_value]-[epsilon_tolerance] AND
  [some_float_or_double_value]+[epsilon_tolerance]

• Restore saved Edit sessions when opening an Excel workbook: Selected by default.

  • Reuse Excel worksheets matching their names with the session table names: Selected by default.

  • Create new Excel worksheets for the restored Edit sessions: Deselected by default.

• Manage connection information stored in the user settings file

  Provides a list of saved Excel files with information about MySQL connections. Clicking this button opens the Manage Import/Export Connection Information dialog.

Manage Import/Edit Connections Information

This dialog lists the connection information for Excel worksheets that are known to MySQL for Excel. From here you can view all saved workbooks that contain worksheets with imported or edited data from MySQL tables and optionally delete the connection information associated with each workbook.

Note

The list of missing information will be empty in most cases because MySQL for Excel manages orphaned connection information for you. If you see items in the Manage Import/Edit Connections Information dialog, it means that you should remove connection information as described in this section.

By default, clicking Accept deletes connection information for missing worksheets, but this behavior is configurable with the Delete automatically connection information where Workbook is no longer found option. In addition, Select connection information entries selects (for deletion) the check box of each book you have not accessed for n days, where n defaults to 30. The following figure shows this dialog populated with connections.

Figure 5.12 MySQL for Excel: Manage Connection Information

Note

This option was added in MySQL for Excel 1.3.0

Note

The options to automatically delete missing connections, or delete connections not accessed for n days, were added in MySQL for Excel 1.3.4.