Cory Maass

Subscribe to Cory Maass: eMailAlertsEmail Alerts
Get Cory Maass: homepageHomepage mobileMobile rssRSS facebookFacebook twitterTwitter linkedinLinkedIn


PowerBuilder: Article

PowerBuilder and Alternate Databases

PowerBuilder and Alternate Databases

It's no secret IT budgets aren't what they used to be. Across the industry projects are on hold because there isn't enough money. But what if there were a way your project could actually move forward rather than slip further down "The List?" What if developers could focus on writing new systems, advancing their skill sets, and adding value to the company instead of plodding along in maintenance mode on legacy systems? Would that merit consideration? It should!

As PowerBuilder developers we know we can use almost any DBMS; all we need is an ODBC driver. Why, then, do we almost always use Oracle, Sybase, or SQL Server? For established projects an infrastructure is probably already in place and must be used, but what about new projects in new environments? The "big three" are familiar but in today's market it pays to be flexible and step out of the comfort zone. We should drop the one-size-fits-all approach and use a tool appropriate for the task at hand. For instance, does the local chapter of the Red Cross with 10 employees really need SQL Server? It may not make sense for Dell to drop its Oracle data warehouse and run the entire company on MySQL, but some companies are doing just that and saving money in the process.

This article is an introduction to three nontraditional database choices for PowerBuilder projects: MySQL, Firebird, and SQL Anywhere 8. I'll start by comparing features, cost, and performance, then I'll break out and examine each separately. For the two open-source databases, MySQL and Firebird, I'll highlight potential trouble spots in server installation, client configuration, and PowerBuilder interaction. Included is information that would have saved a lot of time had I known it beforehand.

Features
Knowing the capabilities of candidate solutions is the first step in any product selection. Table 1 compares some of the more important features of our three DBMSs to four popular competitors.

As you can see, our three databases stack up favorably with their competitors. In addition, all three will run on multiple operating systems, including Linux.

Cost
Whether it's because we're tired of paying outrageous licensing fees or we can no longer afford to, cost is probably the single biggest reason people want options. Fortunately, now we have some. MySQL, Firebird, and SQL Anywhere are viable alternatives. All are perfectly capable of handling large amounts of data and a high volume of transactions. And the best part? On a per-user basis, they're cheaper than Microsoft Access. Really! Table 2 compares the street price of various database options.

With 10 users your cost ranges from nothing using MySQL under the GNU Public License (GPL) to $95 per user for SQL Anywhere. Of course, there may be a trade-off in terms of functionality, but if you're not planning on using clustering, it won't hurt you to choose a DBMS that doesn't support it.

In addition to the DBMS purchase price, the server hardware and OS costs should not be overlooked. In general, the smaller footprint of these databases allows them to perform well on modest hardware. Unless you're planning on supporting hundreds of concurrent users, you can probably hold off purchasing a Quad-Xeon box. Because MySQL, Firebird, and SQL Anywhere run under Linux, you could potentially save thousands of dollars in licensing costs over Windows.

Performance
Features and cost are important considerations for developers and management, but performance is the users' main concern. They don't care how much money is saved; if they can't do their jobs, the project is a failure.

To see how our alternatives compare to each other, I put them through some basic trials. My test environment consisted of databases with identical data sets under each DBMS. For MySQL I tested the performance of both the MyISAM and InnoDB table types. For all, the server software was installed with default options since that's where most users will start. The main table used for comparisons had seven columns with a moderate 200,000 rows of data - nothing taxing but large enough to get a feel for performance variations. The averaged results of several test series are displayed in Table 3. Listed times are in seconds.

When experimenting with functionality rather than performance, I frequently had Firebird, SQL Anywhere, and MySQL databases running at the same time on the server. I'd have an instance of my test application working away on each with no noticeable performance degradation.

MySQL 4

  • Web site: www.mysql.com
  • Default user name: root
  • Default password: <none>

    MySQL is sometimes called the Linux of open source databases. It's certainly getting the most press with more articles and books on the shelves than any of the other open source databases. Does that mean it's the best? It depends on what's required. You must carefully evaluate what it can and cannot do to determine if it's right for you.

    MySQL's main focus is on providing core functionality in an efficient manner. As a result it currently lacks some features such as stored procedures, views, subqueries, and triggers. If you need these features, consider one of the other choices. That being said, MySQL is maturing quickly. When I started my research MySQL didn't support an INSERT with SELECT when the select table was the same as the insert table. A couple of weeks later the next version (4.0.14) was released and I discovered this feature is now supported! The moral: if MySQL doesn't meet your needs today, keep checking, it just might tomorrow. The MySQL Web site has a product road map detailing what's scheduled in future releases.

    An interesting aspect of MySQL is its support for five unique table types. Table 4 summarizes the characteristics and suitable applications of each.

    Which table type to employ depends largely on whether or not you need transactional support. If so, InnoDB is best. Otherwise, go with MyISAM. If you choose one and find it was the wrong decision, MySQL has a utility that allows you to change the table type. You won't have to drop, re-create, and reload it.

    If you implement MySQL but think you might move to another database in the future, run MySQL with certain ANSI options set to increase compatibility. If you start MySQL from the command line, use the --ansi option. If MySQL is a service, add the entry ansi under the [mysqld] section in your my.ini file. Either choice will set several default options that cause MySQL to act in a more ANSI-compliant mode.

    If you plan to migrate from another DBMS, be aware that MySQL does not have an overloaded "+" operator for string concatenation. Any existing SQL will need to be rewritten using the concat( ) function.

    ODBC Setup
    MyODBC is available as a separate download from www.mysql.com. Installation is standard and should present no problems. As shown in Figures 1 and 2, you have quite a few options when configuring an ODBC connection.

    One wrinkle in setting up the connection profile for PowerBuilder is the "option" parameter in the connection string. It allows you to set various client-side options, and each option is given a numeric ID value. To pass multiple options, simply add the option IDs. With some experimentation I found that a value of 67 worked well. The individual component values are listed in Table 5. For a complete list consult the MySQL documentation. Sample transaction object settings:

    SQLCA.DBMS = "ODBC"
    SQLCA.DBParm =
    "ConnectString='DSN=mysql_testdatabase;UID=root;PWD=password;
    option=67',DelimitIdentifier='No',OJSyntax='ANSI'"

    Recommended Add-On
    The MySQL server installation has a command-line only interface. As a casual user I was struggling to remember the syntax for various commands. I frequently found myself sitting at the computer with a book awkwardly propped open next to me. To my relief I discovered a GUI admin application called the MySQL Control Center, also from MySQL. It's currently a beta release, but I was able to use it for every admin task I needed to perform.

    Firebird 1.0.3

  • Web site: www.IBPhoenix.com
  • Default user name: sysdba
  • Default password: masterkey

    Several years ago Inprise/Borland briefly released their commercial database, Interbase, into the open source community. Although it eventually returned to the proprietary world, in the short time it was open an improved version called Firebird was created. Practically overnight the open source community gained a full-featured, mature database with everything you would expect: relational integrity, stored procedures, views, external functions, generators (similar to Oracle sequences), and roles. A bonus side effect of its ancestry is any product designed to work with Interbase 6.x will probably work with Firebird.

    Unfortunately, Firebird doesn't seem to have a huge fan base in the United States. I could find only a single English-language book on Firebird/Interbase. There were several in German, Japanese, and even Czechoslovakian. Overseas support is very strong and seems to be growing.

    Server Installation
    During the server installation you have the option to install the Guardian manager. This acts as a service manager for the database server on Windows 9x and ME. There's no need for it on Windows NT, 2000, or XP. If you install Firebird on any version of Windows, do not disable forced writes. Although this causes a performance hit, it has been noted that Windows does not flush the write cache until Firebird shuts down. In the event of a server crash you could lose all of your changes since startup. Linux installations of Firebird do flush the cache periodically, so forced writes should be safe to use.

    Client Installation
    The client software is installed with the same executable as the server. When prompted to select the components, choose "Installation of client tools for developers and database administrators" or "Minimum client install - no server, no tools."

    ODBC Setup
    Firebird stands out from the other databases evaluated here because there are several ODBC drivers available. One is open source and comes with Firebird from IBPhoenix. It was formerly known as the XTG driver. In addition, there are commercial drivers available from EasySoft and Gemini. The EasySoft driver is shipped with Interbase 7.0 by Borland. While the thought of paying for a commercial driver seems to defeat part of the reason for choosing open source, I found the extra cost was well worth it in this case. I wasted a lot of time trying to coax two of these drivers into working with PowerBuilder without complete success.

    For any of the drivers you need to know both the name of the database server and the physical location of the data file (.gdb) on it. Future versions will reportedly have the ability to alias a logical name to a physical location. You'll have several dialect options; specify dialect 3.

    IBPhoenix Driver
    This is the driver most people start with because it's free and comes with Firebird. I could connect to the server but the driver didn't seem to return the correct metadata to PowerBuilder. Only the first table was listed in the Database Painter and none of its columns could be viewed. When a select was run, only the first row in the table was returned. This driver is still under development.

    EasySoft Driver
    This driver is available for download from www.easysoft.com. You can choose either a seven-day trial or a regular commercial license. The commercial license allows for unlimited connections and costs $350 per server.

    This driver was much better than the IBPhoenix driver, but there were still problems. PowerBuilder and EasySoft couldn't seem to agree on the underlying length of an integer data type. There were also some problems with the size of varchar data types. Considering the cost of the driver and that Borland ships it with Interbase, I expected better interoperability. While the driver seemed to work well enough with Microsoft Access, I wouldn't recommend it for PowerBuilder.

    Gemini Driver
    The last driver option comes from Gemini at www.ibdatabase.com. Like EasySoft, it's available in either trial or commercial versions. Commercial licensing is either per user or per server. The per user cost starts at $50 and goes down in tiers for additional purchases. Unlimited users per server costs $475 with a Web/application server license option available for $200.

    Of all the drivers, this was by far the most compatible with PowerBuilder. It handled the metadata and data types correctly. In addition, it allows you to select the network protocol and separates the server entry from the physical location of the database file. Figure 3 is the Gemini ODBC setup dialog.

    Sample transaction object settings:

    SQLCA.DBMS = "ODBC"
    SQLCA.DBParm =
    "ConnectString='DSN=firebird_employee_gem;UID=sysdba;PWD=masterkey'"

    SQL Anywhere 8

  • Web site: www.sybase.com
  • Default administrator user name: dba
  • Default administrator password: sql

    SQL Anywhere suffers from a common trait among Sybase products: it exists, but no one seems to know about it. Even many of those who are conscious of SQL Anywhere aren't aware of its capabilities. SQL Anywhere comes in two versions. Personal server is designed for single-user, same machine use. It limits concurrent use- connections to 10, and doesn't support network communications or use of more than two CPUs. The server version supports multi-user network client/server communication. Applications developed against one version work unchanged against the other.

    Several years ago I had a project with a deadline two weeks after TechWave. A lot of work remained, but I didn't want to miss the conference. We pumped the production data (including tables with millions of rows) from Oracle to SQL Anywhere on my laptop. SQL Anywhere performed flawlessly. I was able to attend sessions during the day and work in the evening. I've been a big fan ever since.

    Windows CE developers need to consider SQL Anywhere. With high reliability and great synchronization capabilities, it dominates the mobile database market. If Pocket PowerBuilder is in your future, the decision is even easier. There simply isn't another product in the niche that can compete.

    Conclusion
    What's the right choice? Like most things the answer is: "It depends." If you have a project on a shoestring, you need to reduce costs where you can. The back end is a great place to start. The database market has traditionally been limited to a handful of expensive high-end products. The past couple of years have seen a dramatic change in which certain market segments are approaching a commodity-like status. This has opened the door to other products capable of doing the job at a fraction of the cost. Check them out; they may be just what your PowerBuilder project needs.

  • 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.