First Look at Advantage Database Server 9
With the release of Advantage Database Server 9, Sybase has confirmed its commitment to this powerful yet easy-to-use database server. This article is designed to provide you with a first look at the new and improved features in this latest release of this remarkable database product.
This article begins with a quick overview of the Advantage Database Server (ADS), including the features that make it stand out in the world of database servers. It continues with a look at many of the enhancements introduced in this latest release. This discussion is intended to highlight the bigger improvements, as well as those that are of particular importance to large groups of ADS users. In other words, there are more updates in Advantage Database Server 9 than can be covered in this article.
What Is the Advantage Database Server?
The Advantage Database Server is a high-performance, relational database server that simultaneously supports optimized set-based SQL, as well as a blinding-fast, index-based navigational model. Its low per-seat cost, ease of deployment, and very low maintenance requirements makes it particularly well suited for vertical market applications, especially those where the deployed sites lack the IT infrastructure required to maintain normal database servers.
At its core, ADS is an ISAM (indexed sequential access method) database. However, unlike classic ISAM databases, such as Clipper, dBase, and FoxPro, ADS is a true, transaction-supporting, remote database server. And with its extensive optimized support for SQL, it provides a best-of-all-worlds solution to data access.
Originally designed to provide Clipper developers with a remote server for Clipper files, it has grown into a first-class server on its own right, providing its own, high-performance file system, in addition to supporting traditional Clipper and FoxPro tables. (Version 9 now supports Visual FoxPro tables as well. This is discussed in more detail later.)
We simply cannot stress the value of the simultaneous support for both the set-based SQL and navigational models with Advantage. On the largest project I am currently working on (an ASP.NET project), our team regularly creates SQL statements that join 30 or more tables, including a handful of tables that contain tens of millions of records. These queries execute in a fraction of a second.
The Advantage Database Server is a high-performance, relational database server that simultaneously supports optimized set-based SQL, as well as a blinding-fast, index-based navigational model
At the same time, we also get to use the Advantage Data Provider for .NET, which is the only .NET data provider we are aware of that provides server side cursors. Specifically, the Advantage Data Provider for .NET provides a DataReader descendant, called the AdsExtendedReader that supports bi-directional navigation, index-based seeks, pessimistic locking, and read/write capability. And its performance is nothing short of spectacular.
Advantage is also available in a local file server version, similar to the traditional dBase tables of MS Access databases and the Borland Database Engine (BDE). This version, the Advantage Local Server, is free, providing developers with a no-cost solution for delivering database that do not need the reliability of a remote database server, all the while providing their clients with a low-cost option to scale to a reliable, high-performance transaction-based server. The Advantage Local Server is 100% API (application programming interface) compliant with the server version, though it lacks some of the features that only a database server can provide, such as support for true transactions.
As is obvious at this point, we are big fans of Advantage. We use it, many of our clients use it, and we are nothing short of grateful for its combination of power and usability. And with the features added in ADS 9, the Advantage team has added real value while preserving the features that have made it the developer friendly server that has earned loyalty since the early 1990s.
But if you are an Advantage user, you already know all this. So let’s get to the essence of this article, the new features added to Advantage Database Server 9.
Windows and Linux Servers Available in 64-bit Versions
Advantage Database Server is now available in 64-bit versions for both its Windows and its Linux versions. Not only does the 64-bit version execute faster, but it also leverages the ability to address much more memory than 32-bit versions.
It is well known that 32-bit operating systems can address a maximum of only 4 gigabytes (GB) of memory. In fact, the 32-bit version of Advantage can use a maximum of 3 GB of memory on 32-bit processors, though it can use 4 GB on 64-bit processors (with the right configuration).
The addressable memory on a 64-bit operating system has a theoretical limit of 16 exabytes (that’s 16 billion gigabytes, or 16 million terabytes, a very large amount of memory no matter how you measure it). On a practical scale, current 64-bit operating systems support much less RAM. For example Vista 64-bit Home Premium supports 10 GB of RAM, while XP Server 2003 R2 Enterprise Edition can address up to one terabyte (1024 gigabytes).
Not only does the 64-bit version execute faster, but it also leverages the ability to address much more memory than 32-bit versions
The operating system is not the only factor. CPU and motherboard design also affect the upper limits of RAM available to Advantage. Nonetheless, the 64-bit version of Advantage (which is designed for the x86 processor) can make use of much more RAM than the 32-bit version. And the more RAM a database server can access, the more it can cache tables and indexes, permitting it to provide faster operations to more users.
In addition to the 64-bit option, the Advantage team has streamlined its server offerings. Previously, there were separate North American and international versions the Advantage server. These have now been combined. Collation sequences previously only available in the international version are now available to North American developers as well.
ARC Enhancements: A SQL Debugger and More
A number of valuable updates can be found in the Advantage Data Architect (ARC), the interactive Windows application that developers can use to inspect, design, configure, and edit table and database definitions. Of all the updates to ARC, the single most exciting is the inclusion of a SQL debugger within the SQL Utility.
The SQL Debugger
The SQL Debugger is a full-featured debugger, providing you with breakpoints, variable inspection, and call stack tracing. Using the SQL Debugger, you can quickly debug SQL scripts, SQL stored procedures, triggers, views, and user defined functions.
The SQL Debugger supports running a script directly in debug mode by pressing Ctrl-F5 or clicking the Debug button in the SQL Utility toolbar. Alternatively, you can set a breakpoint and run the script normally by pressing F5 or clicking the Run button in the SQL Utility toolbar. When run normally, the debugger will break and suspend execution upon reaching the breakpoint.
Once in the debug mode, you have a number of options. You can step through individual statements, step into or over stored procedure and user defined function calls, step out of function calls, or continue running to the next breakpoint. Figure 1 shows a user defined function (UDF) being debugged in the SQL Utility’s SQL Debugger.

Fig. 1: Using the SQL Debugger in the SQL Utility
For SQL stored procedures and user defined functions, you can also enter the debugger by right-clicking the stored procedure or function in the Connection Repository and selecting Debug/Test, as shown in the following illustration. In this mode, changes that you make to the stored procedure or function are saved directly to the data dictionary, saving you time.

While the debugger is active, variables, the call stack, and breakpoints are displayed in individual panes, as you can see in figure 1. You can drag-dock these panes within the SQL Utility, or if you prefer, you can drag them outside of the SQL Utility as floating panes. Either way, these customizations permit you to configure the debugger to best suit your needs.
SQL Utility Usability Enhancements
While the SQL Debugger is undeniably the most significant addition to the Advantage Data Architect, there are many additional convenient enhancements. In addition to the debugger, the SQL Utility now sports a tabbed interface, permitting you to open multiple scripts in a single window. Another small addition, but one that is very welcome, is the implementation of text search along with find and replace features within the SQL Utility.
Non-Modal Dialogs in ARC
The Advantage Data Architect now also makes more extensive use of non-modal dialogs for many of its configuration features. Gone are the many modal dialogs that required you to finish a task before beginning another. For example, while changing a table structure, you can view the structures of other tables, create a stored procedure, and configure a view, all at the same time.
Windows Themes and Automatic Update Detection
Additional enhancements include support for Windows themes and the automatic detection and installation of updates. In short, the Advantage Data Architect, which received a complete rewrite with the release of ADS 8.0, is maturing nicely, providing you with the power and ease of use you’ve come to expect from the Advantage server itself.
Additional enhancements include support for Windows themes and the automatic detection and installation of updates
Improved Client Behavior with Notifications
With each major released of Advantage, ADS gains one or more impressive new features associated with enterprise-level database servers. Advantage 6 introduced stored procedures, users and groups, and data dictionaries. Advantage 7 added triggers to the mix, while Advantage 8 provided replication and online backup services.
And now, ADS 9 adds support for notifications. Notifications provide a mechanism for communicating to a client that something has occurred in the database. For example, notifications can be used to inform a client application that data in a critical table has changed. The client application can then use this information to refresh its view of that table, providing the end-user with the most current data. Or a notification might be used to signal a client that a record has been added to a special table created for the purpose of communicating messages from the system administrator to the end users. The client can then read the latest message and display it within its interface.
Client applications subscribe to an event by calling the sp_CreateEvent system stored procedure, to which it passes a string identifying the event of interest. The events themselves are created with calls to the system stored procedure sp_SignalEvent, and can only be executed from within a stored procedure or trigger.
Like sp_CreateEvent, sp_SignalEvent is passed a string that corresponds to events that one or more clients are expected to subscribe to. The call to sp_SignalEvent is passed an additional parameter that determines whether subscribing clients should be signalled immediately, or only after the current transaction is committed (assuming that the call to sp_SignalEvent was initiated within a transaction), permitting the database to signal events that might be rolled back, in which case, the client will not receive the notification.
Clients receive an event by calling either sp_WaitForEvent or sp_WaitForAnyEvent. Both of these procedure calls are synchronous, or blocking. Specifically, the call to either of these methods will not return until an event is signalled by the database, or the call times out. The timeout, which is specified by a parameter passed to the wait procedures, can either be infinite, or limited to a specified number of milliseconds. (Note that the wait call times out immediately if the database has already signalled the event since the last wait call.)
Because sp_WaitForEvent and sp_WaitForAnyEvent are blocking calls (the call does not complete until a signal is received or the timeout expires, whichever comes first), most developers who need real-time notifications from the database will employ multithreaded techniques.
Here is an example of a typical notification scenario. Before a client application begins displaying a table that must always display the most current data to the user, the client will subscribe to an event associated with updates to that table. The client does this by calling sp_CreateEvent, passing the string you have created to uniquely identify the event.
On the server, you create AFTER INSERT, AFTER UPDATE, and AFTER DELETE triggers on the underlying table. From these triggers, you call sp_SignalEvent, again using the same string that the client applications use to subscribe to the event.
As soon as one of the client applications begins displaying the critical data, the client creates a new thread that then calls either sp_WaitForEvent or sp_WaitForAnyEvent. Code that immediately follows the call to the wait procedure typically tests whether the call has timed out or has been signalled, which can be determined by the values returned by this stored procedure call. If the call was signalled, the thread will attempt to lock a synchronization object and update the display of the data. This synchronization object must also be shared with the thread responsible for the user interface, in order to prevent the multiple threads from interfering with each other.
Once the display has been updated, the notification thread releases the synchronization object and loops back to another call to the wait procedure.
While this type of programming requires careful consideration of shared resources, such as the user interface elements that display the data, when done properly, any signals received by the clients can be reacted to immediately as opposed to enduring the delays inherent with polling (the period check for database updates).
Like ADS, ALS also supports notifications. However, due to the local server nature of ALS, and the lack of a centralized service, ALS notifications are much less responsive than those supported by ADS.
Dynamic Database Configuration
The Advantage Database Server has always been a low-maintenance solution. In most cases, you rarely need to adjust your database settings following your initial installation. For example, as a developer you may have determined that your typical installation requires 250 connections, 750 work areas, 100,000 data locks, and 150 tables.
In the past, if you did not configure enough resources, for example, and you did not allocate enough connections, some connections would have been rejected. The remedy in those cases was to increase the number of connections. While easy enough to do, this required manual intervention and a restart of the server.
With ADS 9, the configurable parameters on the Advantage Configuration Utility, shown in figure 2, are merely default maximums. (Linux and Novell NLM servers use a configuration file to configure default database settings.) If more resources are required than configured, ADS 9 automatically allocates the additional resources. For example, imagine that you’ve configured your server to support 100 tables, but in reality, your application needs 200.
When ADS 9 first encounters the need for more than 100 table handles, it automatically increases the resources available for tables. If the number of required resource later decreases, ADS 9 can release these additional resources, making additional memory available for other needs.

Fig. 2: The Advantage Configuration Utility
Group Default Privileges
In order to simplify the process of granting privileges to database objects, ADS 9 introduces four new default groups. The DB:Admin group has the same rights as ADSSYS, with the sole exception of changing the ADSSYS password. DB:Backup provides permissions to execute backup and restore system stored procedures. Similarly, the DB:Debug group has permission to debug SQL scripts, as well as rights to modify SQL stored procedures, user defined functions, and triggers.
The privileges of these three default groups are not customizable. In other words, the privileges conferred by these default groups are specific to the tasks associated with users whose rights match the intent of these groups. By comparison, the fourth group, DB:Public, is completely customizable. Furthermore, all users are by default members of the DB:Public group, although this group membership can be selectively revoked for individual users.
The purpose of the DB:Public group is to define a default set of privileges that most users should inherit. For example, by giving insert, update, and modify privileges to a particular table to the DB:Public group, you are in effect granting these rights to any new users you add to the data dictionary (although these rights can be revoked for individual users, if necessary).
Support for Visual FoxPro Developers
While ADS has provided support for FoxPro tables and CDX/IDX indexes for a long time, ADS 9 has added full support for Visual FoxPro tables, including auto-increment, date/time, and VarChar fields. Advantage can use its proprietary, high-performance locking with Visual FoxPro tables, but also supports a compatibility mode, permitting legacy applications that access the Visual FoxPro tables directly to co-exist with newer, Advantage-based applications. Advantage adds the new ADS_VFP table type for this support.
To further assist Visual FoxPro developers with migrating their applications to ADS 9, Advantage includes a special tool called the Visual FoxPro Conversion Utility. This utility, which is a Visual FoxPro application, can import most of the information from a Visual FoxPro Database Container (.DBC) into an Advantage Data Dictionary, including field and record constraints, referential integrity rules, default values, views, and primary keys.
In addition to the Visual FoxPro table support, ADS 9 includes a number of enhancements to its support for traditional DBF files. Now, when DBF files are used with a data dictionary, they gain support for default field values, as well as minimum and maximum field constraints. And when the new Visual FoxPro file format is used, indexes can be based on binary concatenation, simplifying the creation of multi-field indexes based on different data types, as well as permitting the inclusion of null-values to be part of an index expression, without forcing the whole expression to evaluation to null.
Further Support for Delphi Developers
Delphi developers have always enjoyed a very high level of support for database development using ADS, making it a favorite database server for Delphi applications (and a perfect replacement for the Borland Database Engine). ADS 9 continues this tradition with the introduction of the TDataSet Switching Utility.
Delphi developers who distribute vertical market applications sometimes need to support several versions of the Advantage Database Server, meaning that they need to compile their applications using different versions of the TDataSet Descendant. For these developers, the TDataSet Switching Utility, which is installed with ADS 9 version of the TDataSet Descendant (Delphi 3 – 7) and Advantage Delphi Components (Borland Developer Studio and CodeGear RAD Studio), automates the update of the version of the TDataSet descendant installed in their IDE (integrated development environment), including the path to the Advantage Client Engine DLLs.
In addition to the TDataSet Switching Utility, ADS 9 also provides an Advantage Delphi Components installer for RAD Studio 2007.
General Improvements to SQL Performance, Including Transactions
In general, SQL performance has been improved in almost every area of Advantage SQL. These improvements are representative of Advantage’s commitment to providing Advantage developers with a first class SQL engine that just happens to be based on navigational ISAM. The improvements to overall SQL performance are significant. The Advantage team testing has found that version 9 showed 9-20% SQL performance improvements over the previous version. Improvement is most pronounced with large tables (one million or more records). General SQL improvement is due to the optimization of multi-segment AOFs (Advantage Optimized Filters) that are used extensively by the Advantage SQL engine.
In addition, transactions are also improved, particularly when those transactions involve thousands of updates.
In general, SQL performance has been improved in almost every area of Advantage SQL
VarChar and VarBinary Fields in ADT Tables
The Advantage ADT proprietary table type has also received an upgrade. ADT tables now support two variable length fields, VarChar and VarBinary. These fields, which unlike memo and binary fields, are stored in the ADT table file, rather than the memo file. These field types support variable length data up to 64K in size.
Backup and Restore AEPs and Triggers
While the ability to backup and restore Advantage databases was a significant addition to version 8, you did have to back up non-SQL Advantage Extended Procedures (AEPs) and triggers manually (those based on DLLs, COM objects, or .NET assemblies). Version 9 now backs up and restores these files external to the data dictionary. In addition, a new backup and restore option, TableTypeMap, permits you to indicate the table type of each of your free tables, which is useful if your application uses a mix of free table types, such as a combination of DBF and ADT tables. This feature is not necessary if all of your free tables are of the same table type.
Support for the SQL MERGE Statement
ADS 9 also introduces support for the SQL MERGE statement. MERGE provides you with a flexible statement for inserting or updating records in a table, all in a single statement. For example, MERGE permits you to perform the equivalent of an UPSERT. In an UPSERT, records from a source table are updated in a destination table, if the source records match destination table records based on a WHERE clause. Where no matches are found, source records are inserted into the destination table. Without MERGE, the equivalent of an UPSERT requires two, somewhat more complicated SQL statements.
Miscellaneous Advantage Enhancements
As mentioned at the outset, we are not attempting to cover every updated feature in ADS 9. While many of the features we are overlooking may be particularly important to certain developers, we have tried to focus on those features that will be of interest to the greatest number of developers. Nonetheless, there are additional updates and enhancements that deserve mention, though space considerations do not permit us to feature them in their own section. Additional enhancements to Advantage Database Server 9 include:
- Native drivers for Crystal Reports v11 R2 and Crystal Reports 2008
- Native drivers for Visual Objects AXSQL RDDs
- Full Text Search enhancements (non-English enhanced search functionality, and CONTAINS AND searches across multiple fields)
- Significantly improved performance on re-index operations
- The ability to pause replication subscriptions on a subscription-by-subscription basis
- Merge functionality added to replication
- Support for variable length expressions
- An option for flipping the direction of an index at runtime (from ascending to descending, or visa versa)
- An AdsSkipUnique API added for table navigation, permitting to you seek to the next unique value in an index
Conclusion
Advantage Database Server 9 includes significant performance improvements and important feature enhancements, continuing Advantage's history of invaluable improvements over the years. The most amazing aspect is while the Advantage team has added advanced features and excellent support for a wide variety of development environments, they’ve kept true to Advantage’s promise of simplicity of use and low maintenance.