Back-end independent applications with SQL Windows. (Client/Server Techniques)

One of the biggest challenges facing application developers working in the client/server environment is how to write a multi-user Windows application that works with any SQL RDBMS and requires little or no conversion code when it's moved from one server to another.

If you use Gupta's SQL Windows, you can use the table window to write applications that are back end-independent. SQL Windows comes with a sophisticated sample application, called Muform.app, that implements a technique that lets you write back end-independent applications. In this article, I explore the approach used in Muform.app.

The technique outlined in this article can be used with other graphical development tools. This article focuses on SQL Windows' table window object, user-defined message passing and processing, modular code design, the construction of SQL statements, and how to design Windows applications for SQLBase, Sybase, Oracle, DB2, and OS/2 Extended Services.

The hurdles

One of the primary obstacles in designing back end-independent applications with SQLWindows is that only one of the database servers mentioned above, SQLBase, was designed with graphical interfaces in mind. You can, however, make the other servers behave more graphically. Equally important, you must write the application for the PC environment, where database applications are used as much for decision-support as they are for transaction-oriented systems.

Graphical decision-support applications are supposed to behave in predictable ways. Users take for granted that they'll be able to move forward or backward through multiple lists of rows simultaneously. If users stop to change one or more values in a row, they assume that once the changes are saved they can continue from where they left off. Users don't expect to rebuild a set of rows each time they make simple changes.

Most SQL databases don't let you move backward through a result set. (Forward is no problem.) Many of them also lack isolation levels that allow browsing while inserts, updates, or deletes are underway. They leave locks on data you may only be reading, perhaps as a result of a question you asked 45 minutes earilier. The data might be found in one of the 10 to 20 information windows open on the desktop. If the browse lasts for an extended period of time or if you go for lunch without closing your windows, you may prevent others from changing data until you return and finish. There are times when users need to be assured that the values they're looking at aren't changing. However, this happens pretty infrequently. More often, users are just browsing around, not overly concerned about the absolute accuracy of the data.

The terrain is equally barren concerning result-set preservation across transactions. In most relational database servers, with the exception of SQLBase, committing a transaction results in the loss of sets of rows used for browsing. If you update one column in one row and commit the transaction, the 500 or so rows you worked so diligently to construct in the first place disappear. To rebuild the set of rows you must re-issue your query clause.

Muform.app, which stands for multi-user form, builds PC-oriented graphical behavior into databases like DB2, Sybase, and OS/2 ES. It tries to make the databases servers behave more like SQLBase. With SQLBase, you construct applications using native features like ROWID, release lock isolation, multiple, simultaneous, scrollable cursors, editable cursors, and cursor context preservation. For the other databases, you need to invent these features. Let's see how Muform.app solves the problem.

How it works

For anyone familiar with Clipper, SQLWindows' table window is like a graphical version of dbedit. It presents data in table format (in rows and column). The table window has its own dynamic cache; rows can be flagged as new, edited, or deleted. There are a number of SQLWindows functions designed specifically for the table window including functions for populating it with SQL data, inserting, deleting, or updating all appropriately marked rows in batch SQL transactions, or enlarging or shrinking the size of the table window cache.

In Muform.app, the table window manages pre-fetched, cached rows from any of the back ends. …