What's New: Advantage Database Server 8.1

The Advantage Database Server (ADS) is a high-performance, low-maintenance, relational database management system (RDBMS) marketed by iAnywhere, a subsidiary of Sybase. I recently wrote an extensive First Looks article in the SDN Magazine on the new features introduced in Advantage 8.0 (SDN Magazine 89). It has now been a year since the release of ADS 8.0, and as is tradition with the Advantage team, they have just released an incremental update, ADS 8.1. This is a free update for all licensed Advantage 8.0 installations.

If you are not already familiar with Advantage, it makes sense to take a moment and consider why Advantage is such a popular database for small to medium sized companies. In short, Advantage is fast, easy to use, provides a wide variety of data connectivity options, and supports both the navigational and set-based models of data access.

All developers want their database servers to be fast, and Advantage does not disappoint. Not only does Advantage support very large databases (up to 16 exabytes per table, that's 16 billion gigabytes), it performs as well as most databases on the market. I recently spoke to an Advantage developer whose application processes nearly 6000 transactions per second at peak on a single server. That's performance!

While high-performance is often accompanied by complex administration needs, Advantage is a refreshing exception

While high-performance is often accompanied by complex administration needs, Advantage is a refreshing exception. Most Advantage installations require little or no regular maintenance, and no database administrator in most cases. Indeed, the training to become a certified Advantage developer is only a few of days in length, as opposed to many weeks (or more) of training for most RDBMSs.

And when it comes to data connectivity options, few RDBMSs provide the breadth of connectivity options offered by Advantage. In addition to industry standards, such as an ODBC driver (Windows and Linux), an ADO (ActiveX Data Objects) Ole DB Provider, a class 4 JDBC (Java database connectivity) driver, and .NET Data Providers (both ADO.NET 1.1 and 2.0), Advantage also provides an impressive collection of additional data access mechanisms. These include TDataSet descendants for Delphi (version 3-7, 2005, and 2006), Borland Data Providers (BDP) for .NET, a Clipper RDD (replaceable database drive), CA Visual Objects RDD, a Crystal Reports driver, a Delphi VCL for .NET TDataSet, a PERL DBI driver (Windows and Linux), a PHP extension (Windows and Linux), and this is not even a complete list.

Advantage is one of the few RDBMSs that supports both a navigational model of data access as well as the SQL set-based model of data access

Finally, Advantage is one of the few RDBMSs that supports both a navigational model of data access as well as the SQL (structured query language) set-based model of data access. The underlying architecture of Advantage is ISAM (indexed sequential access method), the time-tested technology beneath the venerable dBase and Clipper file structures. At the same time, Advantage provides extensive support for SQL through its optimized Advantage SQL engine.

In short, Advantage developers have it both ways. They can employ direct, index-based searches, filters, and ranges on their data, or they can use portable SQL to query their databases. Even the Advantage .NET Data Provider permits server-side, index-based data access. Using the Advantage AdsExtendedReader class, .NET developers have an IDataReader implementation that supports bi-directional navigation; optimized filters, ranges (scopes), and seeks; as well as full read and write capabilities. Developers using other databases are justified for being jealous.

Updates Found in Advantage 8.1

Advantage 8.1 provides you with a number of valuable updates. These include the introduction of user defined functions, the addition of vertical filtering in replication, a true numeric data type, a unique ID function, support for TOP x in subqueries, and TCP/IP-based communications for all connectivity options. Additional enhancements include improved trigger naming options, expanded server-side alias support, SQL performance improvements, Advantage Data Architect updates, an enhanced Crystal Reports driver, and enhanced integrations with the more popular IDEs (integrated development environments). Many of these updates are discussed in the following sections.

User Defined Functions

Advantage 8.0 introduced support for SQL persistent stored modules (PSMs), a SQL standard for adding procedural capabilities to the SQL language. Advantage 8.1 takes this support to the next level with support for user defined functions (UDFs). User defined functions are reusable, SQL-based subroutines that you can call from your SQL queries. UDFs improve your SQL functionality, readability, and maintenance.

Vertical Filtering in Replication

Database replication was introduced in Advantage 8.0. In that release, Advantage supported conditional and unconditional record replication, but all fields of the replicated records participated in the replication. With vertical replication, you have the option of replicating both selected records and selected fields. Figure 1 shows the Publication dialog box, which you use to select which tables, records, and fields to replicate.

Fig. 1: The Publication dialog box with column (vertical) filtering

New NUMERIC Field Type

Advantage 8.1 introduces an ASCII numeric field to the ADT table format. This new field, named NUMERIC, supports variable field lengths up to 32 bytes with a maximum of 29 bytes of precision. NUMERIC fields are similar to Advantage's MONEY field type, except that the data is not assumed to be currency.

Unique ID Function

Advantage 8.1 introduces a new function both as a SQL scalar function and an Advantage Expression Engine function. This function, NEWIDSTRING, generates a GUID-based (globally unique identifier) string. NEWIDSTRING supports a variety for GUID formats, including MIME (22 byte) and FILE (24 byte) encoded GUIDS, as well as standard 128-bit GUIDs.

The values generated by NEWIDSTRING can be used in a number of useful ways. For example, you can use the value generated by NEWIDSTRING as an arbitrary unique key field for table records. Similarly, you can use this ID to create unique file names for data that needs to be persisted to disk in a multi-user environment.

Support for Top X in Subqueries

Advantage has supported TOP X queries since Advantage 7. TOP X queries return a subset of records that meet the query criteria, either the top n records (where n is an integer) or the top n percent (where n is the percent of records that meet the criteria).

With Advantage 8.1, you can use TOP X queries in subqueries. Subqueries are SELECT statements that appear in the field list of an outer SELECT clause, the WHERE or HAVING clause of a SELECT query, or the WHERE clause of UPDATE and DELETE queries.

Universal TCP/IP Support

Previously, with the exception of the Advantage class 4 JDBC driver, Advantage clients used UDP (user datagram protocol) over IP (Internet protocol) or IPX (for Netware) to communicate with the Advantage Server. The use of UDP, with custom packet sequencing and delivery confirmation, allowed Advantage to optimize its over-the-wire communication.

For those applications that need to communicate between networks, UDP was sometimes an issue. Specifically, some developers found Network Administrators unwilling to open a UDP port in their firewalls, where opening the same port for TCP traffic was less of an issue.

In order to accommodate those users who cannot support UDP, Advantage 8.1 now supports TCP communications on all client connections. Nonetheless, UDP remains the protocol of choice, as it consistently provides better performance than TCP.

Server-side Aliases for Linux and Netware

Server-side aliases are labels defined on the server that a client can use to refer to a data location (for example, the location of a data dictionary). In Advantage 8.0, server-side aliases were available for Windows servers. In Advantage 8.1, server-side alias support has been added to both the Linux and Netware server editions.

Server-side aliases provide two features. First, it provides a means for a client to request data without the client needing to know anything about the directory structures on the server. Second, server-side aliases allow Advantage to access database files residing on network attached storage (NAS) devices.

The client connection requiring a report can be passed to the Advantage Crystal Reports Driver, eliminating the need for creating an additional connection.

Connection Passing to Crystal Reports Drivers

The client connection requiring a report can now be passed to the Advantage Crystal Reports Driver, eliminating the need for creating an additional connection. An added benefit of this improvement is that Crystal Reports can now report on data stored in temporary tables available only on the client connection.

Advantage Data Architect Enhancements

The Advantage Data Architect (ARC), which was re-engineered with the release of Advantage 8.0, has received additional improvements. The most significant of these is the addition of table schemas. Table schemas permit you to save table and index "templates," information about table structures that you can use to easily create new tables based on existing table structures.

Another ARC enhancement gives you flexibility when printing a table's structure. Now, you can either print a table's structure from a compact, pre-defined report template, or you can use your own report template.

Improved IDE Integrations

The final major update found in Advantage 8.1 is associated with how Advantage interacts with several of the major development IDEs (integrated development environments). Over the years, Advantage has provided Delphi developers with exceptional support, and since the release of Visual Studio for .NET, Advantage has been one of the more attractive databases for .NET development. However, with Advantage 8.1, this support has been taken to a higher level.

For Delphi developers, you can now access the Advantage Table Designer directly within the Delphi IDE. This support is provided for Delphi versions 5-7, and Delphi 2005 and 2006. In addition, Borland C++ Builder developers receive this same level of support through Borland Developer Studio 2006. Figure 2 shows a data module where a TAdsTable's context menu displays the Advantage integration.

Fig. 2: Advantage tables can be restructured from within Delphi's IDE

For Visual Studio 2005 developers, you can now add data connections to the Server Explorer using the Advantage .NET Data Provider. Adding an Advantage data connection to the Server Explorer permits you to easily view your database's metadata from within Visual Studio, include the names of tables, views, and stored procedures, as well as field and parameters names and data types. An Advantage data connection is shown in the Server Explorer in Figure 3.

Fig. 3: An Advantage Data Connection open in the Server Explorer of Visual Studio 2005

Summary

This article has listed a number of the more obvious updates found in Advantage 8.1. But to be honest, I have been selective. The Advantage team managed to add many additional improvements that I don’t have room to mention.

But there is a more important message here. With this update, iAnywhere, and its parent company Sybase, are demonstrating their continuing commitment and support for the Advantage Database Server. As a result, we can comfortably look forward to future performance and feature enhancements coming from the Advantage team for years to come.