Easily find issues by searching: #<Issue ID>
Easily find members by searching in: <username>, <first name> and <last name>.
Example: Search smith, will return results smith and adamsmith
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."
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.
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.
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.
Table Data Editor Features
Updating BLOB/CLOB Data
Table cells containing BLOB data cannot be directly edited within the Table Data Editor. In order to modify a BLOB value, you must save the BLOB value to a file, edit the file using an external editor, then upload the edited file (or a new file) back into the table cell.
Use the following options in the table cell context menu to update BLOB or CLOB data: