DBMS, May 1996
Recently Borland International executives briefed analysts and the press on progress the company has made in shifting its direction and building a profitable business as a supplier of application development tools. As evidence of the shift in Borland's business, Borland President and CEO Gary Wetsel presented charts that showed that revenues from desktop database products (Paradox and dBASE) declined from 70 percent to 35 percent of overall revenues while revenues from Delphi, C++, and Interbase increased enough to result in a modest overall increase from FY '95 to FY '96. As the desktop DBMS guy in attendance, that really raised my antenna.
Paul Gross, Borland's senior VP of R&D, elaborated on future plans for the company's desktop products. Enhancements to Paradox and dBASE will focus on enabling Paradox and dBASE developers to create Internet applications. dBASE will be positioned as the application development tool of choice for sophisticated Xbase developers who want to continue to leverage their skills. Paradox will be enhanced and repositioned as a product that complements applications created with Delphi. In the short run, Borland will emphasize how well Paradox works as a query and reporting tool in conjunction with Delphi applications that use the Borland Database Engine. At the same time, Paradox developers will be encouraged to create smaller scale applications that interact with the data managed by Delphi applications.
Going out further, Borland will de-emphasize Paradox's ObjectPAL as a language for developing database applications. Paradox has always been the second place database; as a result, its development language was never widely adopted or supported by an extensive third-party tools market. While continuing to support existing Paradox developers and applications, Borland will encourage developers to adopt Delphi as their departmental application development tool of choice. Gross indicated that lines between the two products will blur, which I interpret to mean that Borland is considering building a more accessible version of Delphi's Pascal into Paradox, so that Paradox can function as a power user's Delphi. Borland will also recommend using Paradox as an assembly platform for components written as DLLs and OCXs in Delphi.
I appreciate that Borland has thought through the relative positioning of its desktop database and development products. Not only is it clear, it also makes sense. When I attended the Borland Developers Conference in 1994, I brought an Xbase partisan's bias to the introduction of Delphi. At the time, Borland was representing itself as "the upsizing company." I was hoping to see Borland focus on "upsizing" dBASE and Paradox instead of developing an entirely new development tool for departmental client/server applications. Such a strategy would have made it easier for me and my colleagues to upsize our own skills into the client/server arena. In retrospect, Borland was able to bring its language technologies to bear on Delphi in way that it couldn't have done with its desktop database products. The result is today's hot development tool.
Borland has continued improving Paradox, and released Paradox 7 at the end of 1995. Although there were no specific Internet features added in this release, developers are already taking advantage of its new functionality as an OLE automation server and as an OCX consumer to develop Internet applications. I know one Paradox developer who has already used a Winsock OCX and a Web browser OCX together in a Paradox application that searches Web sites for specified keywords and retrieves pages that contains them back into a Paradox database. Borland set performance and functionality in client/server environments as objectives for version 7. The Paradox 7 client/server version also includes new 32-bit SQL Links, a single-user Local Interbase Server, and Borland's Data Pump Expert, which provides point-and-click copying of local tables into server databases, bulk copies into existing server databases, and data movement from one server to another.
At the heart of Paradox and of all Borland products' database capabilities lies the Borland Database Engine (BDE). (See Figure 1, page 93.) In addition to supporting ISAM data access to Paradox and dBASE format files, BDE provides an API for installing drivers for SQL databases. Borland packages native drivers for Oracle, SQL Server, and Informix, in addition to its own Interbase server in the Paradox client/server edition. BDE also supports an ODBC socket, which enables it to access data via any installed ODBC drivers.
BDE makes database engine functionality available in a very natural and flexible way, minimizing the amount of work that is incumbent on the developer. It is a particularly attractive approach for developers upsizing their skills, because it works effectively with both ISAM and SQL databases, and emphasizes row-by-row navigational access to data. BDE accomplishes this trick by unifying both set-oriented and ISAM data access into a single cursor model. When accessing client/server databases, BDE maps navigational requests to the appropriate SQL queries.
Whether an application is opening an indexed Paradox table or executing a query against either a Paradox table or a server database, BDE creates a cursor as the result. BDE populates cursors on an as-needed basis, automatically managing a local cache of records retrieved from the data source. These cursors represent live connections to data sources that the engine can refresh as needed to ensure their currency and consistency. BDE endows its cursors with a consistent set of navigational capabilities; I discuss later those that make Paradox an effective tool in client/server environments.
Clearly, the ability to move row by row through a result set in both forward and reverse directions is an important capability for interactive users and PC application developers familiar with this type of data behavior. You can order the cursor in accordance with indexes available on the data source, locate records in the cursor using a key value, and switch to another index or to "natural" order without changing the record that currently has focus. You can apply secondary selection criteria to a cursor; these are evaluated without having to recreate the cursor, and result in a cursor that maintains its updatability.
The BDE bookmark feature provides a uniform way to mark the current position in a database and return to it in the same session or in a later session. To facilitate implementing one-to-many relationships, BDE provides linked cursors in which the many cursor's record set can be constrained based on the value of the one cursor's key; as this value changes, the engine dynamically updates the contents of the many cursor. Linked cursors can be created from joins between heterogeneous data sources. The new 32-bit BDE, together with the 32-bit SQL links, have significantly improved the update performance of such linked cursors.
The common query engine supports both ANSI-92 SQL and Paradox-style QBE as query languages. A SQL Generator enables the engine to respond to QBE queries against a SQL data source, using the DBMS capabilities enumerated by the driver to determine a set of valid queries. The engine manages allocating query processing work between the client and the server; for instance, the client would take responsibility for cross-database joins. Result sets can be committed to Paradox or dBASE tables and managed by the engine as if they were local tables. Data translation services support cross-database operations as well, enabling such functions as data type mapping and exporting from one format to another.
The engine provides several features to support applications in which data is being updated back to the server, and applications in which data on the server is being updated during the life of a cursor. For instance, you can update rows that don't have keys and you can maintain focus on the current row after an update even if you change the value of the key. BDE supports the notion of record locking that is familiar to desktop developers, simulating record locks if they are not supported by the underlying data source. Record locks are respected across multiple active sessions initiated by the same client; for instance, a user might initiate sessions while performing a query interactively within Paradox while running an application developed with Delphi.
The first thing I noticed when I started working with Paradox client/server version was the fact that the freestanding SQL Tools were gone. In Paradox 5.0, a SQL Tools menu option brought forth a toolbox that included an alias manager, table definition worksheets, an ad hoc query editor, and a structure viewer. These tools have been integrated into the standard Paradox menus. For instance, when I choose New from the File menu, a dialog prompts me for a file type and includes the servers for which I installed SQL Links on the list of available types. After creating a table, I added tables to my project from Oracle and SQL Server databases. Paradox listed all of the tables I selected in the tables panel of its project viewer. When I right-clicked on a table name, a menu popped up from which I could view or change the table's structure, view and edit the contents of the table, add records in bulk, copy the contents of the table to another table, or even delete the table entirely. Of course, these capabilities respected the permissions established on my server.
Instead of going through the SQL Tools to get to a SQL editor, Paradox 7 treats SQL scripts identically whether they refer to local or remote tables. In addition to improved accessibility, the SQL editor gains the benefit of application-wide program editor enhancements, which include color syntax highlighting, indenting and outdenting, recording common keystroke sequences, incremental search, bookmarks, block marking, multiple undo and redo, and a choice of Brief, Epsilon, or CUA keyboard maps. Paradox 7 scales much more effectively between local and client/server databases because the new version of BDE is SQL-92 compliant. SQL scripts written in the SQL editor using SQL-92 syntax will work against either local databases or server databases, provided that tables and their structures use consistent names.
Another indication of the first-class citizenship of SQL scripts is the ability to use them as the data source for forms and reports. As with previous versions of the product, the query-by-example (QBE) designer generates SQL code optimized for the specified query conditions. BDE will use this SQL code to present queries to the server automatically; alternatively, you can choose the Show SQL command from the Query menu, which will open the SQL editor and display the SQL code for the query on which you are working. You can then save the SQL query separately from QBE, use it as the basis for additional SQL code that you want to write, and use the SQL query as the data source for forms and reports.
In addition to allowing you to select the data format for the result set, Paradox QBE's page settings allow you to control whether queries should be executed remotely or locally, and whether to generate auxiliary tables that record the rows impacted by insert, delete, and changeto (update) queries. Suppressing Paradox's generation of auxiliary tables when running queries that change data will improve query performance, at the cost of a local audit trail of changes that were made. If you execute a query locally, you are in effect requesting that the server return all rows in the indicated tables to Paradox for processing by the BDE. Although this option should be used carefully, it is particularly applicable to instances when the query joins heterogeneous tables or the query performs aggregation or other functions that are beyond the capabilities of the target server.
A substantial number of developers continue to create database applications with Paradox. While most are PC database applications with limited numbers of users, some are very large client/server applications. For instance, the PanAgora trading application that I profiled in my April 1995 column (page 94) is a large mission-critical application that its owner continues to use and enhance. In simple terms, developing applications with Paradox requires creating a data model, designing queries that serve as the basis for forms and reports, laying out those forms and reports, and using Paradox's ObjectPAL language to provide event management and data-processing services. You write ObjectPAL procedures in event handlers and libraries; it's a high-level language with strong support for event management and data-processing tasks.
PC application developers starting to develop client/server applications face major architectural decisions regarding how to partition their single-tier solutions into a two- or three-tier framework. Of course, that's the same issue that mainframe application developers are struggling with as they enter the client/server arena from the opposite direction. Many of the developers I correspond and work with are migrating toward a strategy of moving fairly sizable chunks of application logic from the client to the server. Of course, this strategy fails to deliver the current fashion of a three-tier architecture.
There are several reasons for migrating to a fat server, two-tier solution. Foremost, it minimizes network load. It provides the highest degree of integrity and reduces the complexity of client applications that only need to call stored procedures rather than execute embedded SQL. This facilitates sharing of application logic among applications written in different languages. It also moves that logic out of what may be a proprietary or legacy language (such as PAL or ObjectPAL, Cobol, and even Xbase) and into SQL, which has become a fairly standard platform for business applications. (The SQL standards for stored procedure languages [SQL/PSM] are not yet standardized, although that effort is proceeding. Every DBMS vendor has its own proprietary stored procedure language, even if the SQL statements used in procedures follow the ANSI/ISO standard.) However, for PC developers the added complexity of a three-tier solution is typically a mismatch for the requirements of workgroup, departmental, and midsize business applications. For these kinds of clients, the cost of implementing such solutions with reasonable performance appears to be prohibitive in today's hardware and software environments.
ObjectPAL's support for server processing effectively serves the needs of both fat client and fat server applications. It heavily leverages the BDE's ability to make server access transparent and manage cursors automatically. For small applications, it's feasible to continue using existing Paradox logic, but you must wrap it in transactions. Paradox provides functions to begin, commit, and rollback server-based transactions. When starting a transaction, the optional argument isoLevel specifies what isolation level to use when transactions are supported: DirtyRead allows the transaction to read uncommitted changes made by other transactions; ReadCommitted allows changes made by other transactions to affect data read by this transaction; RepeatableRead ensures that data previously read in this transaction is not affected by changes made by other transactions. Only one transaction is allowed for each database. While the transaction is active, statements (except SQL passthrough statements) that operate on any table associated with the specified database are included as part of the transaction.
Using SQL passthrough is an alternative technique, and it is better for applications in which more logic will be based on the server. ObjectPAL's executesql function provides a way to send SQL statements directly to the server. An executesql parameter lets developers control whether query results are returned to a string, table, or cursor that is managed by the engine. By using the Paradox notion of tilde (~) variables, developers can use variables when setting up where, order by, and group by clauses, and assign values to those variables based on user entries at runtime. With local cursors managed by the engine, you can develop an application in which data is retrieved on demand and changes are sent to the server via insert or update statements. Any dependent updates can be conducted via triggers on the tables being changed or through direct execution of stored procedures through executesql.
Considering where Paradox fits into a database user's desktop led me to rethink the ongoing role of PC database products. As information systems professionals we are changing the landscape by increasingly deploying personal computers as intelligent network terminals rather than as personal productivity tools. These intelligent terminals are capable of acquiring information in a variety of forms from a myriad of sources. The Internet phenomenon represents one axis of this shift, and the resounding growth in client/server database connections represents the other. At the desktop, this is leading to a schism between the promotion of Web browsers to full-scale operating environments for Internet and client/server applications and the extension of existing desktop operating systems and products to serve these applications.
It will be a while before this dynamic is resolved, but it's clearly going to impact the role of desktop productivity tools, including database products. While it plays itself out, both approaches will gain sizable numbers of adherents, and developers will try to support both paradigms. Borland is certainly trying to cover both bets by ensuring that Delphi and Paradox work well within Microsoft's OLE-centric operating environment, and by focusing significant resources on developing Latte tools for building browser-hosted Java applications. At the same time, Borland is acknowledging that Microsoft will be the leading supplier of desktop productivity tools, including database products.
Desktop database products like Paradox were conceived before the client/server and Internet shift occurred. In their historic form, I question their relevance in distributed environments characterized by many different types of data. That doesn't mean that they will simply disappear, but in the long run I foresee the adoption of smaller special-purpose components rather than large application products that cram query, report, and form design tools into one package with a programming language and a database engine. With its easy-to-use tools for retrieving data from a variety of sources, Paradox is well-equipped to assume the new role that Borland officials articulated.
Borland International Inc., 100 Borland Way, Scotts Valley, CA 95066; 800-245-7367 or fax 408-431-4122; http://www.borland.com.