9.4 Table Data Editor

Aqua Data Studio provides a Table Data Editor which allows a user to graphically edit the resultset of an executed query on a table, including its data and rows. The editable result set is displayed in an Excel like grid.

To activate a table editor you must either right click on a table in the Schema Browser and select "Edit Table Data (Top 1000)" or write a table SELECT statement and use the "Execute Edit" button. This will execute the query and return the resultset in a new Table Editor window. From this window a user may edit and save the contents of the resultset.

The editor uses the primary key or any unique constraint to identify the row in the result which it will generate UPDATE statements for. If your resultset doesn’t have a primary key or unique constraint, you will be prompted to define a primary key in the primary key tab. A check mark indicates which column(s) participate in the primary key.

There is an option within File->Options->Permissions to allow Primary Key Definition Changes.

Table Data Editor Workspace

Like the other tools of Aqua Data Studio, the Table Data Editor is part of the Application Docking Framework. The window for the Table Data Editor can remain open as other tools in Aqua Data Studio are used. The Table Data Editor's menus and icons are taken directly from the Main Application Toolbar. Their functionality for refreshing and reconnecting to database servers has been included to make it easier to reconnect and refresh table data without having to access the Main Application Menus and Toolbar.

At the top are the File, Edit and Window menus. Below are Toolbar icons for Refresh, Reconnect, Save and Refresh, Save and Close, Save Script ("Save Script" stores the chosen directory path and uses it for subsequent save operations as a default directory for easier navigation), Stop, Add Row Above, Add Row Below, Clone Selected Row(s), Clear All Changes, Clear Selected Deleted Rows, Clear Changes in Selected Cells, Set Cells to Null, INSERT Current Date and Time, INSERT Current Date Only, Edit in Window, Close Window, Max Results, Aggregate Functions (when enabled) and Quick Filter.

Below the Toolbar Icons are the tabs for Table Data, Primary Key, Preview SQL and Messages. The Window menu can be used to cycle through tabs when so many exist it is difficult know which one was focus via Window->Next Tab; Window->Previous Tab; Window->Focus Max Results.

The layout of the data within the Table Data tab functions similar to a spreadsheet appliction, with the ability to insert rows and cells where desired via Find and Replace when needed. Most of the work done in the Table Data Editor occurs in the Table Data tab, which displays the data for the table(s) from the select statement used in the Execute Edit mentioned earlier. Row color and bolding assist in displaying recent changes and nulls in the data, and appear in the Table Data Editor modification logs (when enabled in File->Options->Table Editor->Modification Logs). A box appears around the content inside of a selected cell to identify content length where blank spaces may appear before text, but not be visible. Table Columns, must be added or removed from tables via context menus with right clicks in the Schema Browser or through the Query Analyzer by way of database commands. Using Edit->Format: AutoFit Column Width, cells can be highlighted and resized automatically using the menu, a key shortcut, or a double-click on the right border of the column header. This option can be configured from the dropdown menu.

The Primary Key tab displays a list of all of the columns relating to the tables in the query used to launch the Table Data Editor via the Execute Edit command from the Query Analyzer. If more than one table was used in the query, a dialog appears to choose the primary keys and an additional Primary Key tab will display for each table with schema information in the tab title.

The Preview SQL tab displays the DDL used to perform the edits made within the Table Data Editor with appropriate Syntax highlighting.

The Messages tab displays any warnings or messages during table data modification.

At the bottom of the Table Data Editor window are the Preview Panel (when enabled) and Status Bar. The Preview Panel creates a cell's preview in a split panel to allow the user to preview the currently selected value in the grid. This helps the display the full value of a narrow cell, including long string values or CLOB values.   The Status Bar contains Pattern information to show the data type and range of selected cells at the bottom left, and Total Rows of the table at bottom right.

After the changes are saved and the table is refreshed (Save and Refresh), the cursor selects the first cell of the selected row before save. When closing a modified window the application will prompt for "Save," "Discard" and "Cancel."

Visual Editing

The Table Data Editor allows you to add, edit and delete rows and changes in the editor are color coded so that you may see your changes before commiting them. Inserted/Modified/Deleted rows have light blue cell background. Modified cells have a slightly darker color for the text so it allows the user to see spaces. Cells in Inserted Rows also have the background color of the text shaded to identify invisible characters.

• Entering a string value with length longer than the datatype can handle will notify the user of a possibility for truncation.
• Editing a cell with a key stroke will clear the cell and start the cell value from the key typed. Editing the cell with a mouse double click will edit the cell but will leave the old value.
• Edit->Clone Selected Rows - Clones the selected rows. This option can also be accessed from the dropdown menu.
• Edit->Insert Current Date Only: Inserts the current date, and a time of 12:00 p.m. if the date type is a timestamp. This option can also be accessed from the dropdown menu.
• DateTime columns: If the user enters or pastes a date or datetime in a different format than the default locale, ADS will make a conversion. Example: 8/9/06 will be converted to 08/09/2006 12:00:00 AM
• Fill Functionality as Edit->Fill Down and Edit->Fill Right: Fills currently selected cells in the chosen direction with the first selected cell content. This option can also be accessed from the dropdown menu.
• "Paste" pastes the value from the clipboard into all the selected cells.
• A user can copy a row and then paste it into an empty row. Select row, press CTRL-C, then select another row and press CTRL-V.
• A user can copy x number of rows and paste into the last empty row and the necessary number of rows will be created.
• Pasting into a cell from a spreadsheet:
  • Pasting into a cell in edit mode will trim carriage return ("/r")
  • If a user copies one cell from a spreadsheet and pastes it into a single cell of the Table Data Editor it will paste the value without trailing carriage returns and line feeds.
  • If the user copies one cell from a spreadsheet and paste it into a number of selected cells, the Table Data Editor will paste the single cell into each individual cell.
  • If the user copies a block of cells from a spreadsheet then the Table Data Editor will paste the block into the corresponding cells starting from the leading selected cell; whether a single cell or multiple cells are selected does not change the functionality.
• "Edit in window" option has an extra tab that provides an editor which wraps the text so the user may edit in a regular or a wrapped text mode. Wrapped mode is preferred when editing large amounts of text that would not comfortably fit within a cell in the Table Data Tab. Edit in Window only functions when cell contents are text data types and not numeric. This allows the user to easily edit the full value, including long string values or CLOB values

Saving changes

Before saving you may clear any part of your changes. You may also preview the changes that will be made in the ’Preview SQL’ tab window, or save the SQL statements for the changes to a file.

• If no results are returned, an error message is displayed.
• Warnings are displayed when warnings are returned.
• If a statement is executed that doesn’t make any modifications, the transaction is still commited. Example: Deleting a row which has already been deleted by a different user.
• The table data editor is now threaded, and there is a status bar at the bottom that displays the status of execution. Including in the status bar is the number of statements to be executed and on which execution it is on. The toolbar has a cancel button so that the user may cancel the execution at any given time.
• If Save is cancelled while being executed, the status bar displays the total number of statements executed before the cancel.
• Transaction Handling:
  File->Options:Table Data Editor: Added section for Table Data Editor Options.
  • Transactions: Transaction Type: {Full, Batch, Threshold} :
    • Full: A "Full" transaction type causes all changes to be made in one transaction. This is the default.
    • Batch: A "Batch" transaction type will batch all the statements to be executed into batches with an X number of statements per batch. Each batch will be commited independently. If a commit fails, the execution will stop and rollback the current transaction batch, but it will not be able to rollback the previosly executed transaction batches.
    • Threshold: A "Threshold" transaction batch executes the statements in order and checks the amount of time ellapsed after each executed statement since the beginning of the batch. If the time ellapsed has reached the threshold time, the current transaction will be committed, and a new transaction will be started. This will allow the statements to be batched in separate transactions based on a time Threshold.
    • Batch Size: Number of statements per transaction batch [For Transaction Type: Batch].
    • Threshold: Number of milliseconds ellapsed to trigger a transaction batch commit [For Transaction Type: Threshold].
    • Wait Time: Amount of time to wait between transactions [For all Transactions].
  • Transaction Log Monitor [Sybase ASE]: This will allow ADS to queue editor modifications for Sybase ASE if the Transaction Log percentage hits a certain Threshold which allows the server to process the current transactions before receiving requests from ADS.
    • Log Used Threshold: Percentage of transaction log used that would trigger a transaction to wait.
    • Log Used Wait Time: Time to wait if a transaction log used threshold is triggered.

Find/Replace Options

Find and Replace within the Table Data Editor functions similar to the Find and Replace of Aqua Data Studio's Editors. Regular Expressions cannot be used in the Find and Replace within the Table Data Editor.

• Edit->Find: Finds the first occurence of the text in the grid values starting from the current position and using the specified direction for search.
• Edit->Find Next: Find the next occurance of the last find.
• Edit->Find Previous: Find the previous occurance of the last find.
• Edit->Replace: Finds and replaces a string occurence(s) by a specified string; if it replaces all of the occurences in the table, it shows how many have been replaced.
• String matching options for Find and Replace: "match case", "match entire cell" and "match whole words."

• Quick Selection - Ctrl-Shift-R and Ctrl-Shift-K (by default) selects all the rows or columns respectively that have cells that are currently selected.
• Default Key Binding for Delete Row is Ctrl-Delete, for Clear Changes is Ctrl-Alt-Z and for Clear Cell Changes is Ctrl-Alt-Y.
• Key "Edit" will overwrite, F2 will put in edit mode with text highlighted and mouse double click will put in edit without highlight.
• DELETE Key: sets to NULL all the selected cells.
• CTRL-D Key: deletes the row.

Table Data Editor Features

• Sortable Columns: To sort by a certain column a user can click on that particular column. The first click will sort in ascending order, the second in descending order and the third will unsort the column values. Simultaneous multiple column sorting is available if the user keeps the Ctrl key pressed. Columns can also be shifted left to right by dragging their column headers into new positions within the grid.
• Filterable Rows: Quick filter field is available at the top right corner to help the user to filter and view desired column values. If the user clears changes which were in the filter so that the rows are no longer in the filter, the view stays the same for user convenience until a new filter is applied.
• Multi-Table Editing: Table Data Editor allows the user to insert an empty row before and after any row in the table. Cloned rows are inserted directly after the row being cloned.

  Multi-Table Editing Limitations
  1. Columns with the same name belonging to different tables are not editable because the table they belong to cannot be identified.
  2. Insert, clone and delete row operations are not available in the multitable mode as they can’t distinguish between the tables.

• Insert Row Before & After: Table Data Editor allows the user to insert an empty row before and after any row in the table. Cloned rows are inserted directly after the row being cloned. If the corresponding columns are sorted, the view stays the same until new sorting is applied.
• Status Bar: If data loading takes an extended period of time, a progress bar pops up to show that the operation is in progress.
• Option to save queries to SQL History: This option can be enabled/disabled in File->Options[Table Editor]. When the option is enabled, queries generated in Table Data Editor are saved to SQL History after being executed and commited. With the Full (default) transaction type all of the queries are saved as one entry, otherwise queries are saved by batches being commited. If a query or a batch fails, including the case when a user cancels it, further queries are not executed and not saved.
• Modification Logs: A log of all modifications to tables is stored as a .xls document. An option in File->Options->Table Editor->Modification Logs sets where these logs are kept. This option allows the saving of modifications made in the Table Data Editor to Excel spreadsheets containing the old data and new data so user know what was modified. The resulting files follow this naming convention:
  year-M/d-HHmmss-SSS-connection-user.xls
  Example:
  2008-0523-093109-552-MySQL51_local-root.xls
  The resulting document shows Modifications on the first sheet and Original Data on the next. Row coloring and bolding indicate the changes made.
• Assign Table Column: Tables in Assign Table column dialog and in Select Delete Participants dialog are now sortable.