GUI access to Informix databases has always been in the background at Informix. It seems that just when Informix as a company is getting settled into one interface, it changes directions and gives us something new. Take, for example, the Informix WingZ product of the early '90s. Backed by a strong promotional campaign that featured flashy giveaway tote bags at shows such as Comdex, the product provided a spreadsheet interface to Informix. Just when it was beginning to show some promise, Informix sold it to a consultancy and the product disappeared into a black hole.

The same thing happened with NewEra (which many of us called "New Error"). It was supposed to provide an object-oriented environment and actually did a fairly good job of that, but it also vanished.

More to the point for this article, Informix released a product initially called "Remote Object Manager," which morphed into "Informix Enterprise Control Panel." This product gave Informix users a GUI that was reminiscent of the simple Microsoft Access interface for administering the database. It had many very useful features, but it too was abandoned in favor of Informix's current administration product, Informix Server Administrator (ISA), a Web-based administration tool that has been the subject of my last three 10-Minute Solutions. ISA is a very slick product that has some great potential—an indication that Informix probably is ready to kill it.

Many users have been looking for simpler, more stable, and more flexible tools. In this 10-Minute Solution I introduce you to WinSQL, a freeware product created by a small one-man company that seems to have listened to users' wants and needs. I demonstrate how to download the product for free, install, and set it up; and cover some of the things you can do with the product.

What Is WinSQL?
You can consider WinSQL as a direct replacement for IECC. It allows full database access and manipulation through a Windows GUI, features that IECC users have wanted since IECC was introduced. For example, WinSQL highlights syntactic elements in different colors so that you can visualize the structure of a SQL statement more easily. It includes wizards for generating SQL by dragging and dropping from a graphic (much like PowerBuilder). It also has wizards for exporting data and for generating test data. Besides that, it is so simple to install and use that trying it carries little or no risk.

WinSQL comes in two versions: Lite and Professional. The Lite version is totally free. Users don't even need to register the product. The Professional version offers a 30-day free trial and requires registration at the end of that period for a paltry sum of $99—cheap at any price. The Lite version is very comprehensive and usable and may well meet all your needs. The Professional version does only a few things that the Lite version doesn't:

• Command-line interface
  
• Data export wizard
  
• Test data generation wizard
  
• Catalog printing
  
• List of available datatypes

Installing WinSQL
Point your browser to Imran Hussain's Web site and click the first link to the WinSQL section. Click on the Download button and fill out Imran's short form to get to the download. Choose the Professional version of the product and download it to a temporary location on your hard disk. It's a 740K zipped archive containing only one 2MB file, winsql.exe. (This in itself is a rarity with current software—no DLLs, no modifying your batch files, no complex Registry entries. It's just one file.)

Unzip it and put it somewhere in your path. Create a shortcut to it if you wish. No actual installation is needed. It doesn't even get placed on your start menu. Uninstalling it is just as easy: delete the file. Imran's Web site claims that more than 50,000 customers are using WinSQL. A major reason for this is its simplicity.

Using WinSQL Lite
WinSQL is different from Informix products. First, it is not Informix-specific. It connects to the database through ODBC and it can work with all the major databases. Because of this openness, it is possible to use WinSQL to move data from one brand of database to another, as long as the database supports ODBC. This is a great boon for people using WinSQL in a multi-database environment.

The first requirement for using WinSQL is that you have the Informix-CLI (ODBC) product installed in your system. If your system does not have it installed, check with my previous 10-Minute Solution that covered installation and setup of Informix-CLI. While you are setting up CLI, be sure to create a DSN definition for one of the databases that you want to use with WinSQL. One of the really useful, although trivial, applications of WinSQL is as a tester for an ODBC/CLI installation. When you create an ODBC connection, there is no built-in way to test the connection in the same way that ilogin tests the native connections. Just crank up WinSQL on a new DSN to see if the connection is set up properly.

Start WinSQL by typing winsql at the command line or by clicking on your newly created link to WinSQL. You'll get the logon screen (see Figure 1).

Figure 1: Logon screen for logging onto a database

Enter your password and you will get the main WinSQL screen (see Figure 2).

Figure 2: The main WinSQL screen

You can see how much more readable the query is after the keywords, variables, and operators are highlighted. Queries can be saved and retrieved. When you create a new query with the new query menu options or icons, WinSQL remembers that query in its "queries" box and you can refer back to it later in your work.

The results of the query are shown in the Results tab (see Figure 3). On the main menu, you can go to the Query menu and decide whether these results are shown in free-form text or in a database grid. In this demonstration, I'm using the grid. You can enter multiple queries in the query box, separated by semicolons. Results from each query are contained in a separate result set tab. Unlike IECC, there is no separate icon to run just the highlighted query, but the functionality is still there.

Figure 3: The Results tab

Just highlight the query you want to run, and only that result set will appear (see Figure 4). Status messages and error messages for the query will appear under the Messages tab. A convenient feature is the status line at the bottom of the screen that tells you which DSN you are connected to and gives you the elapsed time for the query.

Figure 4: Highlighting just one query

Another convenient feature is the ability to see and print the database catalog (see Figure 5), which details the tables, views, and stored procedures in the database (see also Figure 6).

Figure 5: The database catalog

One glaring omission is triggers. There is no provision for seeing, creating, or manipulating database triggers. If triggers are important to you, you'll need to handle them outside of WinSQL (at least until somebody convinces Imran to include the feature). If you use either triggers or stored procedures, WinSQL will leave you asking for more help in these areas (see Figure 6).

Figure 6: Field display in the Catalogs tab

The new table wizard also has an icon that leads you through the process of creating a new table. This wizard allows you to pick and choose datatypes for fields by clicking on the datatype in a drop-down list box.

In addition to the trashcan icon for deleting a query and the green arrow for executing a query, a featured button allows you to publish the table in HTML format. Choosing all of the defaults for a simple query, you'll find that the HTML publish icon generates a decent-looking table from the database (see Figure 7).

Figure 7: HTML output from WinSQL

Exploring WinSQL Professional
Up to now, everything that I have shown you has been from the WinSQL Lite version. As I mentioned earlier, the Professional version has a few more features. One of the Pro features is the ability to print your database catalog (see Figure 8).

Figure 8: Printing your data catalog

Unfortunately, the printing process seems to take an inordinate amount of time. I requested an output of just one table and just one stored procedure, but the printout seemed to take forever. The program appears to have to load all of the stored procedures before it can print just one. It's a new feature and will probably be tuned up in later releases.

One feature I want to cover here is the SQL Wizard. The idea behind this wizard is to make generating complex SQL queries easy by allowing users to drag and drop fields from a graphic depiction of the table (see Figure 9). I have had envied the GUIs for such tools as PowerBuilder and even lowly Microsoft Access when developing my own systems. It seems that if you want these types of interfaces, you have to build them yourself. Imran included the following Wizard in WinSQL.

Figure 9: The SQL Wizard

To use this wizard, just choose the tables you want to use from the List of Tables window. Here I create a simple query with only two tables. To create the join, just click on one of the join fields. Here it is id_code in the code table. Drag this to the join field in the other table (id_code in the code_value table) and you will see a line connecting the two fields, indicating a join. Now simply fill in the checkboxes next to the desired field names.

You can see the SELECT statement being built as you choose each element. Finally, use the three tabs at the bottom (Criteria, Aggregation, and Group By) to refine the query further (see Figure 10). The Criteria tab provides the WHERE clause. The Aggregation tab gives you aggregation functions such as SUM and AVG. The Group By tab allows you to create the GROUP BY clause if you need one.

Figure 10: Refining the query in the SQL Wizard

I believe that Imran Hussain has a product here that he's not yet aware of. I believe that a substantial market exists for an ActiveX control that allows programmers to build queries within their own applications. If he just packages this feature separately and sells it as a programming tool, I believe he would be pleasantly surprised.

I've saved one of the best features for last. WinSQL allows the user to generate test data for a table. This feature is only visible from the Catalog tab (see Figure 11). From here, either click on the Generate Test Data icon or right-click on a table name and choose Generate Test Data from the menu.

Figure 11: The Test Data Wizard

Choose either a new or an existing template to generate the test data. Here, I choose the New button. WinSQL then shows the fields in the table (se Figure 12). For each field, choose it in the left column and indicate how the data is to be generated in the right-hand area.

Figure 12: Creating the test data

Based upon the type of field in the database, you can insert random data, data from another table, data from a text file, serial data generated automatically from the Informix serial datatype, or WinSQL-generated sequential numbers. When you have finished with the data definitions, you will get to the screen shown in Figure 13.

Figure 13: What you see just prior to generating test data

This screen allows you to run the transfer without saving the template, run it and save the template, or save the template without running the export. When you click the Generate button, the random data will be inserted into the table of your choice. Note that when you experiment with this, do not try to insert the data unless you are dealing with disposable tables. The last thing you want in a live database is this kind of fake data.

All in all, WinSQL is a very impressive product that has many uses for an Informix user. The fact that it is universal and can talk to all ODBC databases is an added plus. WinSQL is better than anything I have seen from Informix. It's better thought out, it's better executed, it's cheaper, and it's created by a one-man show.

Good show, Imran!