Edit and export results

This topic introduces how to edit and export execution results.

Background information

After editing and executing SQL statements in the SQL window of OceanBase Developer Center (ODC), you can view, edit, and export execution results on the Results tab.

Edit result sets

Assume that you want to insert data into the employee table in the odc_test database on the Results tab.

1. In the SQL window, edit and execute SQL statements to query data in the employee table.

   SELECT `emp_no`, `birthday`, `name` FROM `odc_test`.`employee`;
   

2. Edit the result set on the Results tab.

   The navigation bar on the Results tab also provides the following icons.

   Icon	Description
   Edit	Click the Edit icon to enable editing mode for the current result set. The editing mode supports the following operations: Add, Copy Current Row, Delete, Cancel, Confirm Modification, which submits a transaction when auto commit is enabled, and Modify and Submit, which is displayed when auto commit is disabled. In the editing mode, you can either double-click target data to directly modify it or click the preceding icons for convenient operations. In the editing mode, right-click a cell. The Copy and Set to Null buttons appear. Note Whether the ResultMetaData obtained during the execution of an SQL statement can be edited indicates whether the result set of the SQL statement is editable. You can also determine whether the result set is editable based on the prompts on the Result tab when the result set is generated. If the result set cannot be edited, the Edit icon is grayed out and a prompt is displayed indicating that the result set cannot be edited. If the result set can be edited, the Edit icon is active and you can click it to go to the result set editing page. You can edit result sets in single-table and single-view queries. However, result sets that involve the SET and ENUM fields cannot be edited.
   Plan	Click this icon to view the actual resource consumption and execution plan of an executed SQL statement. This allows you to evaluate the performance of the statement.
   Back to Start	Click this icon to go back to the first page.
   Previous	Click this icon to go to the previous page.
   Next	Click this icon to go to the next page.
   Jump to Bottom	Click this icon to go to the last page.
   Search	Enter a keyword in the search field to search for the desired results.
   Download Data	Click this icon to export query results.
   Columns	Click this icon to select the columns to be displayed on the page.
   Column Mode	Click this icon to display the selected data row in the form of a table. On the Column Mode tab, you can switch to the previous or next row. The column mode makes it easier to view data in a row that has many columns. The procedure is as follows: Select the data that you want to view and click Column Mode . On the Column Mode tab, click the left or right arrow to switch between the column values of adjacent rows. Hover the pointer over a value field and click the zoom-in icon to view the details. In the table on the Column Mode tab, you can view the comments for a column in the Remarks column. Perform the following steps to modify the comments: In the left-side navigation pane, right-click the target table and select View Table Structure to go to the Attribute tab. In the left-side navigation pane of the Attribute tab, click the Column tab. Select a field and click the Edit icon. The field editing dialog box appears. In the Comment field, edit the comments, and click OK. In the SQL Confirmation dialog box, click Execute. View the modified comments on the Column Mode tab.

3. Click + to add a row and edit data.

4. Click Modify and Submit.

   Note

   The current connection uses a shared session and the commit will apply to all windows.

5. In the SQL Confirmation dialog box, confirm the SQL statement and click Execute.

6. The data is added to the Results tab.

   The result set in the Results tab can be used in the following interactive operations to facilitate daily development work.

   Action	Description
   Select required data	You can select the required data of a result set and copy the data to an external file by using hotkeys. You can also click a field name or row ID to select an entire row or column. You can also click Export in the toolbar to export data of a result set to a local file.
   Select rows and columns	You can select consecutive rows or columns while holding the Shift key. You can select inconsecutive rows or columns while holding the Ctrl or Command key.
   Right-click a cell	You can right-click a cell and select Copy or Export to Clipboard to directly export the data into an external file.
   Right-click a row ID	You can select Copy Row, Freeze this row, Unlock all frozen rows, or Export to Clipboard . Copy Row: Click this option to copy the selected row. Freeze this row: Click this option to freeze the selected row and pin it to the top. You can freeze multiple rows. The rows under the table header and frozen rows can be vertically scrolled. Unlock all frozen rows: Click this option to unfreeze all frozen rows. Export to Clipboard: Click this option to copy the entire row of data to the clipboard in SQL or CSV format.
   Zoom in	If the data in a cell is too long and cannot be displayed in full, you can use the zoom-in icon to view the data in full: 1. Place the pointer over the cell. 2. Click the icon on the right of the cell. In the window that appears, view the data in full. Note LOBs are displayed in text by default. You can convert an LOB into the hexadecimal format and download the LOB to a local device.
   View BLOBs	On the Results tab: In Oracle mode, you can directly edit text data of the CLOB, BLOB, or RAW type, edit hexadecimal data, and upload files. The maximum allowed size is 2 MB for text data, 200 KB for hexadecimal data, and 20 MB for image files. In MySQL mode, you can directly edit text data of the LOB types such as BLOB, MEDIUMBLOB, and TINYBLOB, edit hexadecimal data, and upload files.
   Right-click the tab name of the results tab	For example, you can right-click the tab name of the Result 1 tab and click Pin in the context menu to pin the tab, so that it remains displayed. In this way, when you execute a new query, a new result tab appears but does not overwrite the pinned result tab. This allows you to compare the query results. Click Unpin to unpin a result tab.
   Filter, sort, and search for data	Each field name in the result set is provided with a filter icon, a sort icon, and a search icon. You can use them to filter, sort, and search for data in a single column.
   Drag the column name	You can adjust the order of fields by dragging column names in a result set.
   Adjust the column width	You can adjust the column width by dragging the column edge.
   Display the field type and comments in the status bar	You can click a field in the result set to view information such as the type and description of the field in the status bar at the bottom.

Export the result set

Assume that you want to export the employee table to your local disk in the EXCEL format.

1. In the result set, click .

2. On the Download Data page, specify the export information and click Download.

   Parameter	Description
   Query SQL	Edit the query SQL statement and select the data to export.
   Maximum Number of Lines in Result Set	You can specify a custom number of rows to export as needed.
   File Name	The name of the file to export.
   File Format	You can export the query results to a CSV, SQL, or Excel file. To export the query results to an SQL file, edit the SQL statements in the SQL Query field, specify Maximum Number of Lines in Result Set, File Name, File Format (SQL), File Encoding, Data Desensitization, and SQL File Settings (Table Name). To export the query results to a CSV file, edit the SQL statements in the SQL Query field, specify Maximum Number of Lines in Result Set, File Name, File Format, File Encoding, and Data Desensitization. In the CSV File Settings section, specify Include Column Header, Convert Empty Character String into Null Value, Field Separator, Text Identifier, and Line Break Symbol. If you export data in CSV format, the exported CSV file can be opened by Microsoft Excel. To export the query results to an EXCEL file, edit the SQL statements in the SQL Query field, specify Maximum Number of Lines in Result Set, File Name, File Format, File Encoding, and Data Desensitization. In the Excel File Settings section, specify Include Column Header and Export SQL statements to another sheet. If you export data in EXCEL format, you can specify whether to include the column header and whether to export the SQL statements of the query.
   File Encoding	Select the desired file encoding.
   Excel File Settings	Specify Include Column Header and Export SQL statements to another sheet.

3. View the exported employee_excel table in your local disk.

References

• Edit and execute SQL statements

• Compile and debug PL objects

• Command-line window

• Analyze SQL statements

• Generate mock data

• Manage database changes

• Manage partitions

• Synchronize shadow tables

• Change lock-free structures