SQL Editor

Graphical Versus Character-Based Utilities

The SQL Editor application is a graphical program equivalent to the ISQL and dbaccess programs in the character-based world. For a while it seemed that Informix was intending to completely replace the character-based utilities with these new graphical utilities, but an outcry from the users convinced Informix to retain the character-based utilities. There are still many things that can be done from the character-based utilities that cannot (yet)-be done with the graphical utilities, and the character-based utilities still are the main method of database access and manipulation on UNIX systems, since the Microsoft Windows-based graphical utilities will not run on UNIX. To date, one of the main limitations of all of the GUI utilities is the lack of a scripting language. Until there is a workable scripting language, even Windows systems will need to retain the character-based utilities.

Advantages of SQL Editor

SQL Editor provides a much more user friendly environment on Windows 95 and Windows NT systems than dbaccess. One of the big improvements is a much more sophisticated editor that supports cut-and-paste, undo, toolbars, find and replace, and a useful help system. It supports multiple SQL statements that send results to separate tabbed output pages. It also allows the user to highlight and run individual SQL statements from the editor.

SQL Editor Operations

When entering SQL Editor, the user is presented with the now-familiar drop-down list box that allows the user to navigate through multiple servers and drill down to individual databases on each server.

SQL Editor allows the user to work with the sysmaster database, but each time it generates a warning that can be somewhat annoying. A way to do this is to choose any database but sysmaster and to access the sysmaster database with a statement like:

SELECT * from sys master:sysprofile

This syntax has the advantage of allowing the user to work with the sysmaster (or any other database, for that matter) from any database on the system.

After choosing a database, the user can use either the toolbar or the menu to either open an existing SQL script or create a new one. Hitting the open-folder icon or using the menu opens a familiar (to Windows users) window that allows the user to choose a script.

Clicking on the diskette icon or using the File→Save menu option pops up a similar screen to allow the user to save a new or modified script. After choosing an existing script or creating a new script, the script is brought up in the top window of SQL Editor.

The View menu item allows the user to choose whether or not to see the toolbar, the status bar (at the very bottom of the screen), and the SQL result history (the bottom box), which gives feedback about the execution status of the SQL statements. Error messages appear in both the result history box and in a separate pop-up window, so not making the result history box viewable does not interfere with receiving error messages from the Informix database engine.

Several new toolbar icons become available when there is an SQL script resident in the editor window (they are grayed-out if not available). These icons are the find button, the check button, and the execute button (the triangle, VCR-like button). The checkmark button checks the syntax of the SQL statement(s), and the execute button opens an output tab and displays the result of the SQL statement. If there are multiple SQL statements separated by semicolons, multiple tabbed output pages are generated, one for each SQL statement. If any of the SQL statements is highlighted with the mouse, two additional toolbar buttons are activated that allow the user to either check the syntax of the highlighted statement or to execute the statement.

When there is a script in the window, the SQL→Set Explain option becomes usable. This has the same effect as inserting a SET EXPLAIN ON statement at the beginning of the script. When a script is actually running, the little square box icon will allow the user to interrupt the script in the same manner as hitting a control-c in dbaccess.

When the user clicks on one of the output tabs, a grid is displayed giving the data retrieved by the query. This also makes a few more options active from the menu. The Save menu now has an option to Save As Data. This allows you to highlight some or all of the data on the screen and save it into a text file with the extension .txt. This has the data delimited by tab characters. A very useful upgrade to the program would allow the data to be saved in multiple formats, such as pipe-delimited for imports and exports or spreadsheet format for importing directly into a spreadsheet.

The grid output has a few other shortcomings that could benefit from further development. First, the titles for the tabbed pages are simply Output1, Output2, etc. It would be much more user friendly if these tabs could be user-defined.

There's one little quirk in the SQL Editor that is not immediately obvious and can cause a lot of confusion if the user is not aware of it. If there is an active query in the editor that has been run and if it has generated one or more output pages, minimizing the SQL Editor and then maximizing it causes the SQL tab to disappear. When this happens, a little arrow appears to the right of the tabs. Clicking on the left-hand arrow causes the editor window to reappear.