Retrieving and Editing Data

www.altova.com Print this Topic Previous Page Up One Level Next page

Home > 

Retrieving and Editing Data

Altova website: _ic_link Database tool

 

In DatabaseSpy, you can retrieve database data directly from the Online Browser or from a table design in the Design Editor using the Retrieve data option in the context menu. The required query is generated and executed with one single mouse click and the data is displayed in a Result tab of the SQL Browser. You can choose to retrieve all rows at once or only a particular number of rows at a time.

app_online-browser-retrieve-data

If you need to edit data after retrieving it, DatabaseSpy provides the Edit Data option in the context menu of the Online Browser for this purpose. The Result window is switched into the Editing mode in this case, indicating this condition in its status bar.

tab_result-statusbar-editable

You can also select individual columns of a table when using the Retrieve data and Edit data commands. Only data from these columns will be retrieved in this case. Note, however, that all columns may be retrieved if you have deactivated the Generate SELECT statements with full column list check box in the SQL Generation options.

 

 

To retrieve data:

In the Online Browser, right-click the object(s) you want to retrieve data from and select Retrieve data | All rows (Ctrl+Alt+R) or Retrieve data | First n rows (Ctrl+Alt+T) from the context menu.
In the Design Editor, right-click either the title bar or one or several individual columns (using Ctrl+click) in a table design and select Retrieve data | All rows (Ctrl+Alt+R) or Retrieve data | First n rows (Ctrl+Alt+T) from the context menu.

 

An SQL Editor window opens which displays the query for the data retrieval. If you have selected objects from different tables, separate SELECT statements for the individual tables are created. The results of the query are displayed in the Result window below.

 

 

To retrieve data for editing:

Provided that direct editing of database data is supported for the respective data source connection, right-click a table or column in the Online Browser and select Edit Data from the context menu.

 

Partial retrieval

In the SQL Editor options, you can define the number n of rows to be retrieved if you select the Retrieve data | First n rows option from the context menu. You can then use the toolbar icons of the Result window to retrieve the data in fractions.

 

Stopping the retrieval

While data is being retrieved, the status bar of the Result tab counts up the rows already retrieved and displays the execution time. In addition, the tab of the respective SQL Editor window shows an icon at the left side, indicating that data is still being retrieved.

sql_tab-retrieving

While data is being retrieved, the Execute ic_execute-sql button is replaced by the Stop Retrieval ic_stop-retrieval button in the SQL Editor toolbar. You can stop the retrieval process at any time by clicking the Stop Retrieval button. The status bar indicates that the retrieval has been stopped.

tab_result-statusbar-stopped

 

To stop the retrieval of database data:

Click the Stop Retrieval ic_stop-retrieval button which is visible in the SQL Editor toolbar while data is being retrieved.

 

 

To resume the interrupted retrieval of database data:

Do one of the following:

 

To retrieve the next n rows of data, click the Retrieve next n rows ic_retrieve-next-n-rows icon or right-click anywhere in the Result tab and select Retrieve next n rows from the context menu.
To retrieve all outstanding data, click the Retrieve outstanding rows ic_retrieve-outstanding-rows icon or right-click anywhere in the Result tab and select Retrieve outstanding rows from the context menu.

 

Editing database data in the Result window

To be able to edit the data contained in your database tables, DatabaseSpy provides two different approaches: You can either generate the corresponding SQL statements and execute them in the SQL Editor or, more conveniently, retrieve data and edit it directly in the Result window.

 

The Result window has to be enabled for editing before you can make any changes to the data displayed in the result grid. For this purpose, the context menu of the Online Browser as well as the context menu that opens when you right-click into a table design in the Design Editor provides the Edit Data command which is available on table and column level. This command generates a SELECT statement which appears in a new SQL Editor window and is executed immediately, thus switching the Result window into the Editing mode.

 

In addition, also the Execute for Data Editing ic_edit-data button is available in the SQL Editor toolbar, allowing you to execute a SELECT statement and, at the same time, switch the Result window into the Editing mode. This is particularly useful if you have already retrieved data and now want to update some of the records. The table below shows the editing options for the individual data source connections.

 

 

To select data for editing in the Result window:

Do one of the following:

 

In the Online Browser or in a Design Editor window, right-click the table or column you want to edit, and choose Edit Data from the context menu. A SELECT statement is created and executed automatically in the SQL Editor.
Generate a SELECT statement in a new SQL Editor window and click the Execute for Data Editing ic_edit-data button, if available for the currently used data source connection.

 

Note:Not all editing options (update, insert, or delete row) may be available, depending on the data source connection you are currently using, and on whether the table you are editing has or has not a primary key defined.

 

The following restrictions apply when editing data in the Result window:

 

Only SELECT statements may be present in the SQL Editor window if the Execute for Data Editing command is used. If you click the Execute for Data Editing button while any other statement is in the SQL Editor, the statement will be executed without switching the Result window into the Editing mode.
Tables that do not have a primary key defined are only partly supported for editing. DatabaseSpy displays a warning dialog box if you select the Edit Data command for such a table.

dbs_dlg_editing_limited_no_pk

The Show Details >> button allows you to display the reason why the editing command has failed. If you do not want to have this dialog box displayed, select the Don't show this dialog again! check box or disable this feature in the SQL Editor options. Upon clicking OK in the dialog box, DatabaseSpy executes the statement and disables the editing functions in the Result window. You can then, however, insert new rows into such tables or save the content of XML fields as XML files.

 

All columns that form part of the table's primary key must be included in the statement that is used for populating the result grid with records. A warning dialog box similar to the one above ("Editing of data is limited for this statement") will be displayed if you omit such a column in your SELECT statement.
Columns that are the result of an equation or have certain properties such as autoincrement, timestamp, etc., are excluded from editing. If such columns are present, a warning dialog box similar to the one above appears before the results are displayed. You can, however, still edit the remaining columns of the table.

© 2019 Altova GmbH