Explore a Database
This tutorial part shows you how to explore the contents of an existing database, and modify some data. To this end, let's open the demo DatabaseSpy project which already includes a ready SQLite database populated with some data, see Opening the Tutorial Project .
Since the demo database is a SQLite database, the Online Browser displays a fixed database root object called "main". Other database kinds may display multiple root objects (databases or schemas, as applicable).
You can explore the database by expanding folders of interest in the Online Browser. The actual folder structure depends on the database kind. For the scope of this tutorial, you can expand each table in order to view its columns, as well as other objects, such as primary keys, constraints, indices, and so on. Keep in mind that the Properties window displays at all times detailed information about the currently selected object.
To view the structure of one or more tables as a diagram, open a new Design Editor (Ctrl+D) and drag the tables from the Online Browser into the editor area.
To view the actual data inside a table (remember this will display data as read-only):
1.Right-click the table in the Online Browser.
2.Select Retrieve Data | All rows or Retrieve Data | First 200 rows from the context menu. As a side note, the number of rows for the option Retrieve Data | First N rows can be configured from Tools | Options | SQL Editor.
If you want to open data for editing as opposed to just viewing it, right-click the table in Online Browser and select Edit Data. Alternatively, select Retrieve Data | N rows, and then run the command Execute for data editing . Selecting or updating data stored in the database is also possible if you run SQL queries in the SQL Editor window, see Run SQL Scripts.
Regardless of the approach you choose to retrieve data, the content retrieved from the database is displayed in a Result view, as a grid.
To be able edit data, make sure that you retrieved it using the Edit Data command (or the Execute for data editing command ). In this case, you can use the commands available at the top of the Results grid to add new rows or delete existing ones. To modify a cell value, double-click it and enter the new value.
|To protect the integrity of the database, various data editing restrictions may apply, depending on the constraints defined for the table. For example, it is not possible to edit a column defined as a primary key, or enter text inside a numeric column. DatabaseSpy performs some basic data integrity validation checks as you modify data. If the integrity check fails, the cell background color changes to brick red. A second level of data integrity checks is provided by the database, when you actually commit the changes. If the changes are rejected by the database for any reason, an error is displayed in the Messages window, and data is not committed to the database.
Any changes made to the grid are not immediately reflected in the database. To update the database, click the Commit button, which deploys all changes to the database. In case you change your mind and would like to revert all the changes to the grid before committing them to the database, click the Undo all button.
Various options applicable to the Result view, including display and data editing options, can be configured from Tools | Options | SQL Editor | Result View.
You have now learned the basics of retrieving and editing database data. For more information about working in the Result grid, see Retrieving and Editing Data.