Volume Number: 13 (1997)
Issue Number: 10
Column Tag: Tools Of The Trade

PowerBuilder Version 5

by John Schettino

Cross-Platform Database 4GL

The PowerBuilder product line from Powersoft (a division of Sybase, Inc.) is a cross-platform database application builder. I've always called these things 4GLs because they are database-centered environments that include a custom programming language, application framework, and database back end.

PowerBuilder is the mother of all 4GLs, since it is available on every major hardware and operating system platform and it can access data stored in a comprehensive set of databases. If you're creating enterprise or vertical applications that have heavy database requirements, or if you need to create font-ends to access data stored in non-Macintosh database servers, PowerBuilder is the product for you. It's also the product for you if you need to support non-Macintosh systems including Unix and Windows with a single product.

At just under three thousand dollars for the Enterprise version, this is not an impulse purchase. This product is aimed squarely at in-house IS departments, database consultants, and vertical market developers.

The product is delivered in a typical large software box. What is surprising to me is that this box was actually filled with manuals and a CD wallet containing four CDs. Fortunately only one CD contains the product. The other three cross-platform Mac/Windows CDs are filled with on-line documentation and reference material.

You can run PowerBuilder on 680x0 processor machines, but you'll get better performance on a PowerPC processor computer. Memory requirements are also fairly stiff. A minimum of 16MB on a PPC is suggested. I evaluated the product on a 7100/80 with a 256kb cache and 40MB of RAM and it ran comfortably fast. You'll also want to have a fair amount of free disk space. I installed the "Easy Install" package onto a volume other than the System volume and it ended up installing 13MB of extensions into the System folder and 50MB of files onto the selected volume.

PowerBuilder is complex. That's not a criticism, it's a warning. You will not sit down with this product and start building applications. Instead, you'll have to start climbing a long learning curve to become proficient. To that end, the documentation is split into two pieces.

Printed Documentation

The printed documentation includes a 15 page installation guide that walks you through the installation and tells you what gets installed where. Following that is a 235 page getting started tutorial. You'll want to spend time here actually working through the tutorial example. If you do, you will visit most of the commonly used tools in the environment. You won't learn anything about the PowerScript language used to program in PowerBuilder, but you will get to type some of it into the example at the appropriate places. The tutorial is very nice: not only do you get a tour of the environment, but the application is built in the steps that you would use when creating your own. Each section begins with a three part overview: What you will do, How long it will take, and What will you learn. This helps keep you focused to the task at hand.

The next document is called the Project Primer. This is a 214 page "how to" guide that explains one method for tackling a problem. It provides a life-cycle approach to application development. This kind of road map is vital when you're working on a large problem, and this document came as a pleasant surprise to me. Even if you use some other development method, you should scan this document to see where and when the PowerBuilder tools are applied.

The largest document is the 580 page tome "Connecting to Your Database." It is a reference guide explaining how to connect your application to each of the supported database back-ends for PowerBuilder. This document can be completely skipped while you learn the environment and tools. Only when it comes time to access one of those databases would it become necessary.

Additional documents include the usual license agreements, customer service references, and third-party directories. You can optionally order printed versions of the electronic documentation as well.

Electronic Documentation

As you probably realized, there is really no user's guide or comprehensive reference in the printed documentation; that is left to the CDs. Three are supplied; the Reference CD is specific to the Macintosh product. Here you will find all the expected documents, including the Guide to Documentation. The electronic documents viewer application, as well as one or more of the books, can be installed onto you system for access without the CD. You can also print your own hard copy version of selected chapters or entire books. Searching is also supported by the viewer.

Two "InfoBase" CDs are provided. This is a subscription-based information delivery system. You have instant access to every tip, problem and work around, and general information in the PowerBuilder system. There seemed to be a lot of valuable information there, and you can print or search as with the other electronic documentation.

By now you're probably as eager as I am to actually build something with PowerBuilder. One of PowerBuilder's main strengths is in cross-platform development. When you're working on an application you can use a wide range of database back-ends, or you can use the supplied SQL Anywhere Database. I used that database for the review. To put PowerBuilder through its paces I'll create a simple database, a record entry and search screen, and a report. This will give you a feel for most of the tools.

Where Do You Want To Go Today?

You begin by launching the PowerBuilder environment. It is immediately apparent that this is clearly a Windows95 application running on a Mac. Once you get over the initial shock, the environment works well. If you happen to be familiar with Windows95, you'll find all your favorites here: endless rows of icons, status bars, tool tips that pop up when you move the mouse over an icon, tabbed dialog boxes, and Windows95-style collapsible hierarchies abound. On the plus side, using PowerBuilder on a Mac or a PC should be pretty much the same.

Database Definition

Each major task has a corresponding tool (called a painter) in PowerBuilder. To work with databases you use the Database Painter. Once you open a painter, you get another row of icons for operations specific to that painter. You can create a new database or work with existing databases from any of the back ends supporting by using the painter. I was able to quickly define a pair of related tables using SQL Anywhere (a self-contained relational database back end that ships with PowerBuilder) in the painter. Figure 1 shows the tables and their relations.

Figure 1. Database Painter.

You begin by connecting to an existing database, or creating a new one. Then you create or edit each table. The table editor allows you to add or modify fields. You create key fields, indexes, and other attributes of a table by accessing it's Properties. This is another one of those Windows95 conventions that appear throughout the product. Once you're done, you save the table. This generates the correct SQL to create a table, and sends it off to the back-end. Editing a table with data in it does cause you to lose that data, so you'd have to extract the data before making the change, and then re-load the data afterwards. This is very normal for Unix relational databases, but it may come as a surprise to someone used to Mac databases.

Applications the Template Way

PowerBuilder applications are a collection of resources that are contained within an application library. They may include menus, Multiple Document Interface (MDI) frames that include icon bars, and windows. To make a new application you begin by creating a new application library. Since most PowerBuilder applications contain similar components, you are given the option of filling your new library with a commonly used set of objects. This greatly speeds initial development. Figure 2 shows my application's initial objects, as well as the Application Painter icons. Although you can name your application and its components however you'd like, you'll need to restrict yourself to the Windows 3.1 "8 dot 3" style names if you intend to run the application on that platform.

Figure 2. Application Painter.

The application library (like almost everything else) has a property window where you control settings such as the default fonts and finder icon it uses. You can also add library search paths to the application here. PowerBuilder supports user-generated and third-party libraries. This is great for reuse, as well as for creating a consistent look throughout a set of related applications.

The environment supports rapid development very well. You can always run the application in its current state by clicking the Run button. Even the basic template application can be run. As you would expect, you can edit the windows supplied in the template, add new windows, and delete windows in an application. Adding a new window opens the Window Painter. This is where you draw the interface for a particular window. The window itself may be modal (called a Response Window) or not. It can be set to be resizable, to include a title bar (with out without the zoom and minimize controls.) You set these using the window's Properties menu.

All the usual window elements (called controls, in the Windows95 nomenclature) are available including various line and box tools, checkboxes, radio buttons, static and editable text fields, buttons, scroll bars, and others. Preview mode allows you to try out the window exactly as it will be rendered, to see if the controls are working as you expected. You have total control over the tab order of the different controls, and you can set the initial state of each control's visible and enabled attribute from within the Painter.

Coding in PowerScript

The next step in application development is to add PowerScript code to actually open a window and process the user's input. Using PowerScript is a bit like using AppleScript within FaceSpan: You open up the Script Painter for a particular object in the library, and then you add script code to process specific events that are sent to that object when the application is running. This event-based style of programming should be familiar to most Macintosh programmers. The hardest part of this style of programming is learning exactly which events are generated for a particular action, and where they will be sent. In this case, the documentation contains plenty of information. In addition, you gain some exposure to the most commonly used events by working the tutorial and looking at the samples.

I added a script to the application frame window that opens the login window when the application is run. The PowerScript code and the Script Painter are shown in Figure 3. The script editor only shows the script for a single event or user-defined function. This is less confusing for the beginning developer but may become tedious when working on complex applications. There are a few helpful tools in the painter for commenting or un-commenting lines of code. C++ style comments are supported, as is syntax coloring. You can also get context-specific help within the painter by selecting the item and pressing shift-F1.

Figure 3. Script Painter.

To use a Database in an application you must establish the connection to the desired database through a script in the application. The script is attached to a transaction object within the library. It contains commands that set up an SQL connection request including the user id, password, and database name. When you're administering the database you need to set up user accounts and grant the necessary privileges to each user.

Menus

PowerBuilder include a Menu Painter for creating the application menu. This painter supports definition of keyboard command-key equivalents (called accelerators in the Windows95 tradition) and separators. There is no access to the standard Apple or Help Menus other than the "About this application" menu item, and hierarchical menus are not supported. Full support is given for a Toolbar that corresponds to each menu. For each menu item you can specify a toolbar button with an icon (several standard icons are supplied) and optionally a pull-down menu. You can also define menus that are specific to a window. In this case an additional tool bar is displayed when that window is frontmost. You must attach a script to each menu item to send user events to your application when the menu item is selected.

Database Access

PowerBuilder includes the patented DataWindows tool that automates much of the process of accessing displaying, and updating databases. Although you can (and probably will) design your own data entry, access, and display windows, using DataWindows can speed application development in the early stages. In my application I used a DataWindow to display a sample table. Figure 4 shows the DataWindow configuration window.

Figure 4. DataWindow Configuration.

Once you select the type of window, you select the table and fields to present. You can specify a sort order for each field. You can even specify complex SQL operations such as JOINs using the Painter. Once that is completed the DataWindow Painter opens, showing the window. You can customize the field titles, detail lines, summary lines, and footer for the window here. The summary and footer are used for printing. When this window is displayed, the user is able to Add, Edit, and Delete entries, as well as filter the information presented, all without a single line of SQL.

One common use of the DataWindows is to create a Master/Detail window. This is a window containing two sub-windows. The first sub-window contains a DataWindow in tabular format for searching and reviewing records. The second window contains a Detail window where all the editable fields of the currently selected record are displayed. The tutorial guides you through the process of creating one such Master/Detail window.

SQL Tools

PowerBuilder is designed to access SQL-based databases. You can write your own SQL statements within scripts, or use the SQL Painter to construct statements and embed them in your scripts. The painter can construct queries using SELECT that extract a single row, or it can construct a CURSOR that you use to visit the matching rows for the query. The painter supports advanced SQL features including multi-table JOINs and functions. I found the painter to be very helpful in creating complex SQL statements.

PowerBuilder also includes a tool called the data pipeline (along with its corresponding painter) that you use to set up fast copy actions between databases. This moves data between tables (or databases) as fast as the databases can do it, rather than shipping each row of data to your application and then back out to the database.

Sybase SQL Anywhere

I've already mentioned SQL Anywhere briefly before. This is the bundled relational database for PowerBuilder 5.0. It provides all the features needed to create Macintosh database applications. In other words, you don't have to use a third-party database with PowerBuilder. SQL Anywhere supports multiple databases of unlimited size - in fact databases may span multiple files that exist on different drives. It includes a server application and a separate administration application that allows for database and table creation, user access control, and other administration tasks. Most of these tasks can be performed using a nicer interface from within the PowerBuilder environment. The server application is started automatically whenever you access an SQL Anywhere database for the first time.

SQL Anywhere is a full-featured SQL database. It supports the Open Database Connectivity (ODBC) interface developed by Microsoft and used by PowerBuilder. Its features include transactions, transaction log mirroring, triggers, functions, and other advanced capabilities you'd expect in a relational database. There are two versions: a network server (not part of the PowerBuilder package) for multi-user access, and the database engine for single user standalone use. The standalone version is bundled with all versions of PowerBuilder. The standalone engine does not support network connections, and otherwise processes all requests exactly the same as the server version. That means you can run your application with a network server in the future if your needs change.

Database performance is hard to judge, because it is so dependent on the underlying hardware. Rather than quote you hard numbers, I'd like to give you my impression of the speed of SQL Anywhere as compared to FileMaker Pro 3.0. In general, SQL Anywhere outperformed FMP by a factor of two while performing single table insertions, deletions, and queries. Complex multi-table queries were two to three times faster. In all fairness to FMP, it's a lot easier to set up a simple database there and FMP does not support SQL.

SQL Anywhere allows you to define multiple keys and multi-field indexes to speed queries. It seems to do a decent job of employing all available keys and indices in a table during query execution. As with all databases, the more indices you define, the more insert and update operations prolong. I noticed a slight increase in update times on indexed fields in a database, but nothing out of the ordinary.

Third Party Databases

Performance of third party databases is dependent on three factors: raw performance of the database, raw performance of and congestion on the network used to access the database, and raw performance of the interface. PowerBuilder uses ODBC to access a wide range of databases under Windows. According to the "Connecting to Your Databases" Document the only Powersoft supported ODBC database for Macintosh is SQL Anywhere. You may get an ODBC-compliant driver from your database vendor to access its data via PowerBuilder.

PowerBuilder Enterprise supports native database interfaces for additional third party databases. These include Oracle 7.0 and 7.1, and Sybase SQL Server. I made no attempt to test the access speed of these databases. You can find lots of benchmarks of Oracle and Sybase databases on the web by searching for "rdbms performance comparison". You can assume that a PowerBuilder application using one of these databases will achieve similar performance.

Other Goodies

There is simply no way to cover every feature in PowerBuilder. Suffice it to say that if you're looking for something that I haven't specifically mentioned here, it's probably in PowerBuilder in some form! I've only glossed over the PowerScript Language, but it is as complete and robust as any 4GL I've seen. There is a large and useful set of functions, the ability to create user-defined functions includes a simple yet powerful interface specification tool, and support for local, global, and instance variables is included. There are also a number of other tools for creating quick reports, administering databases, and application debugging.

Debugging Applications

Each painter in PowerBuilder checks the validity of the window/menu/script/etc. when you close the window. That takes care of the compile-time syntax errors, but what about the semantic errors? You know, bugs in the scripts. The PowerBuilder environment halts execution and displays an error dialog when it attempts to execute an erroneous script. If you enable debugging mode, then you can select each script and set one or more breakpoints. Once all the scripts have their desired breakpoints you can run the script. When a breakpoint is reached the debugger returns. At this point you can examine and change variables values. Figure 5 shows the debugger, Variables window, and a modification window.

Figure 5. Debugging Environment.

You can set additional breakpoints, continue execution, or single-step execution. You always "step into" functions - there is no way to step over a function or to continue until a function returns. In other words, this is a fairly complete and powerful debugging environment, lacking only some nice-to-have but non-essential features. Debugging is one situation where having only a single function displayed in the Script Painter windows is a real disadvantage. Forcing you to select each script one at a time to set breakpoints makes the debugging process more tedious that it should be. I'm still quite impressed with the debugger, it just could be easier to use.

Creating a Shipping Cross-Platform Application

Here's the big payoff for using PowerBuilder. Once the application is running the way you want, you just use the Project button to create an executable file. Enter a name and click the Save button and you get one last painter to fill in. The Project Painter allows you to specify the application signature, minimum, and preferred heap sizes for the Macintosh file. You can also set the optimization choice (speed vs. size, or none) and the executable format (Mac machine code using Fat, PPC, or 68k code, or cross-platform pcode.) To move the project to a Unix or Windows platform you need only copy the application library to the platform, open it, and create a new Project.

What About Windows?

PowerBuilder for Macintosh is clearly a port of the Windows95 product. I have mixed feelings about that. On the one hand, I know this means that there is quite probably a lot of common code between the two products. This means the Mac version will be well supported. It means that Powersoft can save support and documentation costs by using common manuals and support personnel. It also means that you could move to the Windows95 version of the product and retain 99% of what you learned here on the Mac side. Finally, you really do get used to the interface pretty quickly; it's a consistent and well done Windows95 interface.

On the other hand, it would not have been immensely difficult to recast key portions of the user interface into a more Mac-like look. For the average Mac developer who is familiar with the CodeWarrior or Symantec IDEs PowerBuilder will feel pretty alien at first. Using Windows95 terminology for the window elements means that you don't get that instant recognition you'd expect as an experienced Mac developer. In other words, it just throws a small wall between you and the work at hand. It's the price of admission for using PowerBuilder. I think it's worth it, but I could do without it.

I did not run into any operational errors while reviewing PowerBuilder. I had some difficulty using SQL Anywhere, almost all of which I'm certain were of my own doing. The product performed reasonably well on my somewhat old 7100/80. I'm sure it would be quite snappy on any of the modern 603 or 604 PPC based Macs.

PowerBuilder is a full-blown and very capable Unix-class 4GL (in power) with a state of the art Windows95 user interface, running efficiently on PowerPC Macintoshes with 32MB or more memory installed. I think that's a pretty fair assessment. You can create powerful front-ends to a large number of existing third party SQL databases, create new applications using the supplied SQL Anywhere Server, and design your interface to be Mac-like, Windows-like, or however else you'd like. You get a full featured development environment that includes visual layout of windows, incremental development, and decent debugging. You get powerful building blocks such as the DataWindow to speed development. You get cross-platform support for all the major hardware and operating systems platforms.

You also get to learn a new programming language (PowerScript) that is not exactly like any other language I'm familiar with, and you get to deal with the Windows95-style user interface. That interface is carried throughout the product, even to the on-line manual viewers and sample applications. The interface, coupled with the sheer complexity and scope of PowerBuilder, makes the initial learning process pretty daunting. Sticking to the tutorial, browsing (and using parts of) the large suite of sample applications, and reading the on-line documentation eventually imparts a degree of proficiency to you. If you're looking for more help, your local bookstore probably stocks one of the many books and magazines devoted to PowerBuilder.

If you're just making Mac-only database applications with small or simple data access requirements then one of the less expensive and less capable Mac-only or Mac/Windows database programs, such as FileMaker Pro, would probably serve you better than PowerBuilder. If you want to create enterprise, vertical, or cross-platform database applications then the power and feature set of PowerBuilder make it a compelling choice.

If you're interested in PowerBuilder 5.0 for the Macintosh, but you don't have need for some of the advanced features, there is a "Desktop" version that costs $295. This version lacks native database drivers but still includes the SQL Anywhere database. It also is missing some features such as version control, support for distributed applications, and support for external C++ code, but you'll still be able to create stand-alone applications that run on the Mac. For a powerful SQL-based database with a strong interface builder this is really worth consideration. Although I didn't review the PowerBuilder Desktop version, it is essentially identical to the reviewed version except for these advanced features.

John Schettino is an author and Senior Member of the Technical Staff at GTE Laboratories, Inc. He is the co-author of the books BASIC for the Newton: Programming for the Newton with NS BASIC and AppleScript Applications: Building Applications with FaceSpan and AppleScript, both published by AP Professional. He is also a contributing editor for the Handheld Systems Journal and for the web eZine Mobilis, where he writes about Newton programming. You can reach him via http://members.aol.com/pdcjohns/.