Welcome!

Related Topics: PowerBuilder

PowerBuilder: Article

The Little PowerBuilder App that Grew

PowerBuilder provides a powerful environment for creating end-user applications whose primary purpose is data entry & reporting

This article will describe the development of a fairly large (over 4,000 users) one-man software business, selling an application developed with PowerBuilder. I'll touch on a number of technical issues I encountered in the development of the program and the business.

How the Business Got Started
The DONATION program is used by small to mid-sized charities and churches, mostly in North America, to track their donors and donations, and to issue charitable receipts. The first version was actually written in C, in 1994, to fill a gap at my own Quaker Meeting, after a commercial program we had bought started falling apart (no referential integrity). I decided I could write something better. By 1999, it was time to create a Windows version, and the PowerBuilder version was born.

Having put so much work into it by this time, I felt there was no point having only one organization use it, so I put it up for free download and use on a web site. At that point I had a regular programming job and had no desire to make a business out of it. A few years later, I started inviting voluntary contributions for it, but that never did any more than pay my expenses for hosting, software, etc. After 8 years of giving it away for free, there were over 4,000 users.

In 2007, I thought I would like to open source the application, partly to provide a way for it to continue to exist if anything should happen to me, like being run over by a truck! PowerBuilder did not seem ideal for an open source app - too few programmers, plus it's a bit expensive. So, I took a three-month leave of absence from my job, and started a .NET port.

Two months in, I was less than halfway done, and could see that although .NET's database access capabilities had improved significantly, they were nowhere near matching PowerBuilder's. Visual Studio didn't even include a report builder whose runtime could be distributed as part of an open source app. So, after serious reflection, I decided to abandon the port (and the open source idea), go back and continue development with PowerBuilder, quit my job, and start a business with DONATION. I was so relieved to have a powerful, comprehensive and fast development environment back, not to mention one where I didn't have to wait for compiles to test small changes.

The business was launched by announcing to all users that while they were welcome to keep using their current versions for free, future upgrades and support would require a small payment, with a discount being given to existing users. Fortunately, many users paid right away, and the business continued to grow from there.

Issues with SQL Anywhere / Adaptive Server Anywhere
For quite a few years, the program used Sybase SQL Anywhere / ASA as its database. It was in most respects perfect for this use - a great embedded DB, requiring no administration, and rock solid. However, there were two problems.

The first was that the runtime version that could be freely distributed with PowerBuilder applications did not allow any Data Definition Language to be used. As a result, any time I had to make changes to the database structure, like adding new fields to tables, I had to go through a rather convoluted and scary process. First, create a new empty database with the new fields. Then, add code to the program, using Pipelines, to convert from the previous database structure to the new one. Finally, make a new version of my installer that installed the new empty database, and have the first run of the application do the conversion.

Although I could have sold copies of the full ASA to users to eliminate this limitation, this was not an option when the program was free. Even when the program became commercial, the price was so low that adding in the price of an ASA license did not seem to be a reasonable option. (Plus, it would have eliminated the option of giving a free 60-day evaluation period, which I do.)

The other problem was an odd one. Periodically, users' ODBC data sources that had been installed by the program's installer for the ASA DB just stopped working, for no apparent reason. Deleting the data sources and rerunning the installer sometimes fixed it, and sometimes didn't. Sometimes, the information about the driver also had to be removed directly using the Registry Editor before a reinstall would fix it. And very occasionally, nothing I could do would fix it, and the user would have to give up and use the app on a different computer.

Taking these two points together, I finally decided as part of a significant relaunch after abandoning the .NET port to switch to an open source DB with an embedded option. I chose Firebird SQL. It has proven to be a very capable and solid database, though its SQL dialect does have a few weaknesses in comparison to that of SQL Anywhere, such as far fewer built-in functions. Also, it does not support UPDATE statements with FROM clauses, so UPDATEs involving data from multiple tables have to use subqueries as columns' values. The server version of Firebird was also eventually used to provide a multi-user version of DONATION.

Working Around Some PowerBuilder Limitations
DONATION uses PowerBuilder's built-in DataWindow (DW) Sort and Filter dialogs, and until recently also used its DW SaveAs dialog, to give users these capabilities. You can specify a help file and help topic to be used in a Help button on those dialogs, which is extremely helpful since they are a bit techie for end users. Unfortunately, only old-style WinHelp files (*.hlp) can be used, not the more modern HTML Help (*.chm) files. Starting in Windows Vista, Windows no longer comes by default with the capability to display WinHelp files. I am a bit amazed that this still has not been fixed in PowerBuilder, despite being identified as a problem quite a few versions ago. As a result, I have to distribute both a WinHelp help file (for use in those three dialogs) and an HTML Help file (for use everywhere else in the program) with my application.

An annoying weakness of the DW SaveAs dialog is that it doesn't return the filename or file type that the user saved. As a result, it's impossible for an app to do things like offer to open a file saved in Excel format in Excel for the user. Also, if you aren't using one of PowerBuilder's two ways of saving PDF files, the PDF SaveAs option just silently fails. Providing my own SaveAs window allowed me to limit the types to those that I thought would most likely be used by the program's users, and also do things like use a third-party tool DW2XLS (see sidebar) to export almost perfectly formatted versions of reports to Excel.

Speaking of PDF, neither PDF option provided by PB seemed sufficient for DONATION's needs - one required end users to separately download GhostScript, something unrealistic for my users (many of whom are not computer experts), and the other just doesn't work for some report types. As a result, I ended up including a commercial PDF printer driver with API capabilities into the application (novaPDF SDK - see sidebar), which mostly works very well.

Mail Merge
The application required mail merge capabilities for users to send correspondence, including thank you letters to donors about their donations, and to allow users to format their own charitable receipts if they didn't like the built-in receipts in the program (which were normal PB reports).

For years this was done in DONATION by controlling Microsoft Word or WordPerfect via OLE. This was awkward and didn't provide a good user experience because it involved going in and out of the program, and using another UI that they might not be familiar with. (They would probably be familiar with their word processor, but not necessarily with its mail merge features.)

In addition, because I was using the mail merge features in those word processors, there was no way to do anything like sub-reports, which were optimally required for the charitable receipts. (Many users, especially in churches, want a main section describing the total donation amount, for the government, and a second section, for them to keep, listing the details - date, amount and donation category - of each donation.)

To work around these issues required taking another approach, with some form of embedded word processor that could be controlled with an API, to do the merging programmatically. I looked at some commercial options, such as TX Text Control, but ended up with a much simpler solution: have the merge files be HTML files, edited using Internet Explorer's Design Mode.

Design Mode is a little-known feature of IE (at least, it was unknown to me). Activating it turns IE into a relatively simple but still relatively capable HTML editor. You embed an IE control into a PB window, add some menus and toolbars that activate functions in IE's OLE API, and you have something that works really well. Fortunately, there is a freely usable PowerBuilder example of doing this, at http://www.topwizprogramming.com/freecode_webbrowser.html.

After enhancing that to add mail merge features, including special mail merge fields that handled the required subreports, everything worked extremely smoothly (see Figure 1). For cases when the editing capabilities of Design Mode were insufficient, an option was given to edit the HTML file in MS Word. (The editor window's activate event is used to check for the file being saved externally and prompt to reload it.)

Conclusion
PowerBuilder provides a powerful environment for creating end-user applications whose primary purpose is data entry and reporting, such as DONATION. It allows me to be very fast and responsive about fixing bugs and creating new features needed by the program's many users, and to design an application usable even by users who consider themselves to be fairly computer-illiterate.

More Stories By Dan Cooperstock

Dan Cooperstock is the owner and sole staff person of Cooperstock Software, which creates and sells the DONATION program from www.Software4Nonprofits.com. He has been a professional programmer for nearly 30 years, working for a range of employers, including both small and large software companies, and financial institutions. Although his income as a one-man software business is lower than it was as an employed programmer, and he doesn’t get to do as much programming (you can’t keep forcing features on users!), he finds the job satisfaction greatly outweighs those other factors. He has used PowerBuilder since version 5.

Comments (0)

Share your thoughts on this story.

Add your comment
You must be signed in to add a comment. Sign-in | Register

In accordance with our Comment Policy, we encourage comments that are on topic, relevant and to-the-point. We will remove comments that include profanity, personal attacks, racial slurs, threats of violence, or other inappropriate material that violates our Terms and Conditions, and will block users who make repeated violations. We ask all readers to expect diversity of opinion and to treat one another with dignity and respect.


Latest Stories
Apache Hadoop is emerging as a distributed platform for handling large and fast incoming streams of data. Predictive maintenance, supply chain optimization, and Internet-of-Things analysis are examples where Hadoop provides the scalable storage, processing, and analytics platform to gain meaningful insights from granular data that is typically only valuable from a large-scale, aggregate view. One architecture useful for capturing and analyzing streaming data is the Lambda Architecture, represent...
As organizations realize the scope of the Internet of Things, gaining key insights from Big Data, through the use of advanced analytics, becomes crucial. However, IoT also creates the need for petabyte scale storage of data from millions of devices. A new type of Storage is required which seamlessly integrates robust data analytics with massive scale. These storage systems will act as “smart systems” provide in-place analytics that speed discovery and enable businesses to quickly derive meaningf...
Your homes and cars can be automated and self-serviced. Why can't your storage? From simply asking questions to analyze and troubleshoot your infrastructure, to provisioning storage with snapshots, recovery and replication, your wildest sci-fi dream has come true. In his session at @DevOpsSummit at 20th Cloud Expo, Dan Florea, Director of Product Management at Tintri, will provide a ChatOps demo where you can talk to your storage and manage it from anywhere, through Slack and similar services ...
SYS-CON Events announced today that Ocean9will exhibit at SYS-CON's 20th International Cloud Expo®, which will take place on June 6-8, 2017, at the Javits Center in New York City, NY. Ocean9 provides cloud services for Backup, Disaster Recovery (DRaaS) and instant Innovation, and redefines enterprise infrastructure with its cloud native subscription offerings for mission critical SAP workloads.
The taxi industry never saw Uber coming. Startups are a threat to incumbents like never before, and a major enabler for startups is that they are instantly “cloud ready.” If innovation moves at the pace of IT, then your company is in trouble. Why? Because your data center will not keep up with frenetic pace AWS, Microsoft and Google are rolling out new capabilities In his session at 20th Cloud Expo, Don Browning, VP of Cloud Architecture at Turner, will posit that disruption is inevitable for c...
Building a cross-cloud operational model can be a daunting task. Per-cloud silos are not the answer, but neither is a fully generic abstraction plane that strips out capabilities unique to a particular provider. In his session at 20th Cloud Expo, Chris Wolf, VP & Chief Technology Officer, Global Field & Industry at VMware, will discuss how successful organizations approach cloud operations and management, with insights into where operations should be centralized and when it’s best to decentraliz...
Providing the needed data for application development and testing is a huge headache for most organizations. The problems are often the same across companies - speed, quality, cost, and control. Provisioning data can take days or weeks, every time a refresh is required. Using dummy data leads to quality problems. Creating physical copies of large data sets and sending them to distributed teams of developers eats up expensive storage and bandwidth resources. And, all of these copies proliferating...
DevOps is often described as a combination of technology and culture. Without both, DevOps isn't complete. However, applying the culture to outdated technology is a recipe for disaster; as response times grow and connections between teams are delayed by technology, the culture will die. A Nutanix Enterprise Cloud has many benefits that provide the needed base for a true DevOps paradigm. In his Day 3 Keynote at 20th Cloud Expo, Chris Brown, a Solutions Marketing Manager at Nutanix, will explore t...
DevOps has often been described in terms of CAMS: Culture, Automation, Measuring, Sharing. While we’ve seen a lot of focus on the “A” and even on the “M”, there are very few examples of why the “C" is equally important in the DevOps equation. In her session at @DevOps Summit, Lori MacVittie, of F5 Networks, explored HTTP/1 and HTTP/2 along with Microservices to illustrate why a collaborative culture between Dev, Ops, and the Network is critical to ensuring success.
Interoute has announced the integration of its Global Cloud Infrastructure platform with Rancher Labs’ container management platform, Rancher. This approach enables enterprises to accelerate their digital transformation and infrastructure investments. Matthew Finnie, Interoute CTO commented “Enterprises developing and building apps in the cloud and those on a path to Digital Transformation need Digital ICT Infrastructure that allows them to build, test and deploy faster than ever before. The int...
SYS-CON Events announced today that SoftLayer, an IBM Company, has been named “Gold Sponsor” of SYS-CON's 18th Cloud Expo, which will take place on June 7-9, 2016, at the Javits Center in New York, New York. SoftLayer, an IBM Company, provides cloud infrastructure as a service from a growing number of data centers and network points of presence around the world. SoftLayer’s customers range from Web startups to global enterprises.
SYS-CON Events announced today that Conference Guru has been named “Media Sponsor” of SYS-CON's 20th International Cloud Expo, which will take place on June 6–8, 2017, at the Javits Center in New York City, NY. A valuable conference experience generates new contacts, sales leads, potential strategic partners and potential investors; helps gather competitive intelligence and even provides inspiration for new products and services. Conference Guru works with conference organizers to pass great dea...
SYS-CON Events announced today that Technologic Systems Inc., an embedded systems solutions company, will exhibit at SYS-CON's @ThingsExpo, which will take place on June 6-8, 2017, at the Javits Center in New York City, NY. Technologic Systems is an embedded systems company with headquarters in Fountain Hills, Arizona. They have been in business for 32 years, helping more than 8,000 OEM customers and building over a hundred COTS products that have never been discontinued. Technologic Systems’ pr...
SYS-CON Events announced today that CA Technologies has been named “Platinum Sponsor” of SYS-CON's 20th International Cloud Expo®, which will take place on June 6-8, 2017, at the Javits Center in New York City, NY, and the 21st International Cloud Expo®, which will take place October 31-November 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. CA Technologies helps customers succeed in a future where every business – from apparel to energy – is being rewritten by software. From ...
With major technology companies and startups seriously embracing Cloud strategies, now is the perfect time to attend @CloudExpo | @ThingsExpo, June 6-8, 2017, at the Javits Center in New York City, NY and October 31 - November 2, 2017, Santa Clara Convention Center, CA. Learn what is going on, contribute to the discussions, and ensure that your enterprise is on the right path to Digital Transformation.