Very often we face the situation when we need to view some information in a database quickly or change a few values, but the application we use does not allow it or is not available. The word application is used here to denote quite a broad range of tools, from MS Access to complicated client programs of corporate information systems. You have a file of MS Access format, but don't have the Access program itself. Client application does not allow viewing or changing any values - it simply does not have the required user interface.
What are the possible solutions to this problem for an end user? To buy and install an
application created to support the required format. Ask application developers to expand
the functionality of the existing application or to develop a new custom application.
Both ways are sometimes unpractical - if you need to view the database only once,
the solution cost is not acceptable. There is one more way for advanced users and
developers - creating the required statement (or set of statements) manually using
SQL language and perform required operations. However, this way is rather slow if
there are some complicated operations, it's not visual and there is a risk of errors.
The task becomes even more complicated if you need to work with large binary or text database objects (BLOB).
Domain of Use
Facing this problem every day, we have decided to build an easy and quick tool for viewing a database and making modifications. Our main goals are:
- provide independence from specific data format, it means that the program must support Oracle as well as MS SQL Server or Microsoft Access, and any data source for which an ODBC driver is available;
- make the process of working with a data record as visual as possible organized as a screen form;
- provide comfortable work with BLOB - their import, export and previewing;
- support of links between tables and master-detail relationships.
DTM Data Editor is easy to use, it meets all the listed requirements, and has a range of
important additional features and options.
The program analyses BLOB content and can recognize several tens of most popular data formats: pictures, office formats, desktop databases, etc. This allowed the user to add a feature of viewing BLOB by opening an application associated with the format of a certain object.
First, a few words about the user interface. It consists of three windows: a table tree (it also shows view and synonyms if such options are selected in the Settings), a records list of the selected table and a form for editing the current record (its header shows the table name, number of the current record and the total number of records). You can resize windows - the program saves them both during the active session and between sessions. If the table is not selected, the list shows detailed connection information, and the form for editing the record is empty.
Let's now look how the form for editing database records works. For each field, there is a corresponding line in the form. The line can be of two types - for common field and for BLOB. In any case, the field name is present at the beginning of the line; the name can be highlighted bold if the field forms or is a part of primary key. If the NULL value is allowed for the field, there is a corresponding check-box at the end of the line.
For common fields, there is one more element - the edit field that can look like an edit box or a combo box where you can type.
All edit fields have a tool tip that shows field name, its data type and default value and, if necessary, a notice that the field is a primary key or has a link to the master table.
The program uses a combo box when the field is a foreign key, and its possible value is not arbitrary but is determined by a list of values of the primary key in another (master) table. To make viewing and editing more visual, Data Editor allows viewing not only the key value but also its description. Let's explain it by an example: suppose we have a ProductID field that refers to the ID field of the Products table. This table also has a Name field. It's much more comfortable to view the source table not by the product code, but by their names, so when the program sends a query, we'll select Name filed as a description for ID field.
Large Text and Binary Object Operations
If the field corresponds to BLOB, the program shows three buttons for Import, Export and Viewing objects instead of showing the edit field. As mentioned above, the program recognizes object format and calls the most suitable application for viewing or editing that it can find in the client operating system. If it cannot find the program or the object format wasn't identified, Data Editor suggests that the user should choose the program manually.
If it's preferable to use one and the same application for all objects and avoid recognition (it can be convenient if the database contains objects of the same type), you can specify a universal viewer/editor in the product Settings.
After you have made all the necessary changes to the record, you can save it as a new record or refresh the record from which you took data to be placed in the form. There are three ways to do it: using toolbar buttons, menu items or shortcuts. The Clear form function removes values from all form fields - it's convenient for entering a new record.
If changes have been made but the user didn't enter the save command, the program shows a dialog box asking to save the changes and listing old and new field values.
For advanced users, there is a mode where before performing a record insert, update or delete operation the program shows an SQL statement created for the operation. You can edit it and thus perform operations of deleting or updating bulk record (you cannot do it in automatic mode).
Let's look at some additional options that make your work with the program more comfortable. Global search is a search engine that allows finding the necessary record by substring, and unlike the where clause of the SQL statement, you don't have to remember in which field the necessary value is located. A feature of sorting allows you to show the list of records in the necessary order and permits using any number of table (or view) fields.
The filter is a comfortable way to restrict the number of rows used by the program. The filter allows you to specify a set of conditions that the record should satisfy to be shown in the data grid.
Database objects tree has a context menu that allows carrying out a number of useful actions: refresh the list of tables or list of rows, delete all data from the selected table, drop a table, etc.
There are three ways to navigate among records: using keyboard, toolbar buttons and menu items: you can move by one record, by page, and move to the first and last record.