Top Ten Reasons to use VDB

This document describes the top ten reasons to use VDB, including a full discussion of each point, with several examples along the way.

Contents

1. VDB allows you to switch among Delphi's built-in DB libraries, and third-party libraries
2. VDB dialects support vendor-neutral SQL
3. VDB supports database connection pooling
4. VDB offers automatic adaptive query preparing and caching
5. VDB objects are interfaces, simplifying memory management
6. VDB offers many handy short-cuts, such as RunQuery and QueryValue
7. VDB has a built-in performance logging feature
8. VDB supports nesting of routines that manipulate transactions
9. VDB profile strings make it easy to reconfigure your applications
10. VDB has routines to simplify the task of formatting SQL statements

#1: VDB allows you to switch among Delphi's four built-in DB libraries, and third-party libraries

Delphi comes with four database access libraries, plus there are third-party options:

• DataSnap Direct (formerly dbExpress)
• dbGo (Formerly ADO Express)
• BDE (Borland Database Engine)
• IBX (InterBase Express)
• Advantage Database Server (from Extended Systems)
• DBISAM (from Elevate Software)
• Flash Filer 2 (from TurboPower Software)

Which one should you use? If you're developing an application, this can be a difficult choice. What if you get most of the program written, and then realize you'd like to switch to one of the other libraries (perhaps because you run into a bug or performance problem with the library you are currently using)?

On the other hand, if you're developing a library, or some business objects that you would like to reuse, then the choice is very difficult -- you are not picking a database library for just one application, but for every application that needs to use this (so-called) reusable code.

So, we see that choosing one of these database access libraries is a difficult choice, and a barrier to code reuse. VDB solves this problem, by allowing you to develop applications and reusable modules without deciding on one of these database libraries.

This was the original motivation for VDB -- to be able to write reusable code that works with any of the database libraries that come with Delphi. (In fact, if you care to take the time, you could even implement support for other database access libraries, such as any of the other numerous off-the-shelf libraries available from third parties). VDB quickly grew to offer other advantages as well, but this was where it all started. We needed to write some routines to perform some non-trivial database processing, and we needed to use them in two different applications: one that used BDE and the other that used ADO. We contemplated having two separate code bases (one for BDE, the other for ADO), but this was clearly going to lead to extra coding and maintenance problems. So, we developed VDB, and then we used it to solve our original problem in a reusable fashion. Since then, we've add many more features, but the original motivation was and still is the ability to write reusable database code.

If reusable code is important to you, VDB belongs in your programming tool chest.

#2: VDB dialects support vendor-neutral SQL

Every database developer has to deal with this issue at some time: will my queries run on any database besides the one I'm currently using? It is surprising how many aspects of SQL are vendor-specific. For example, getting the current time. This is something we take for granted in Delphi (just use Now), but in SQL it depends on which RDBMS you're using. In Informix, it is current, in Microsoft SQL/Server, it is getdate(), and in InterBase, it is current_timestamp.

If your application might need to run on another database, or if you're developing reusable code that needs to run on several databases, you've got trouble. VDB solves this problem with Function Escapes, which were inspired by a similar feature in JDBC. In a nutshell, you can use special escape sequences inside your SQL statements. VDB automatically detects them, and converts them in to the appropriate syntax for the database that you're using. Here's an example:

  // Set the chg_time field of customer #123 to the
  // current time (using the clock on the database machine)
  Database.ExecSQL(
    'update customer set change_time = [now()] where cust_id = 123');

Notice that this example uses the function [now()], which VDB automatically converts to the appropriate syntax depending on which database we're using. There are a number of function escapes for working with strings, date and time values, identity (a.k.a. serial or auto-inc) columns and for mathematical computation. We support all the major database products. See the VDB documentation for details.

#3: VDB supports database connection pooling

This (along with #4) is an important feature for scalable multi-tier database applications. This includes 3-tier application servers as well as web applications.

Database connections are resource intensive. The more connections to a database, the more resources used on the database server. This can be a barrier to scalability. Connection pooling allows your application to create fewer database connections, which can dramatically improve scalability. This affects not only the application using connection pooling but any other applications connecting to the same database server.

Connection pooling allows your application to service hundreds of simultaneous users, while using only a handful of database connections. With connection pooling your application will only need to create enough connections to service the number of simultaneous requests not the number simultaneous connected users. For example, your 3-tier application may have 100 simultaneously connected users, but processes at most 10 simultaneous requests. In this case, connection pooling allows your application to share 10 connections among the 100 users, requiring only one tenth of the connection-related resources on the database server. As the number of concurrent requests increases, the number or connections increases, as the number of concurrent requests decreases, the number of connections also decreases.

VDB's support for connection pooling is seamless and flexible: you can control the pool timeout per connection in each application. By contrast, ODBC has connection pooling, but the settings for it are per ODBC driver. This means that, on a given machine, all applications must use the same pooling properties for all databases using the same driver.

VDB supports connection pooling for all database libraries (BDE, IBX, dbGo, and dbExpress), so you can develop applications that rely on connection pooling, secure in the knowledge that this feature will be available if you switch database libraries and/or drivers.

VDB connection pooling not only works well with automatic adaptive query preparing and caching, but also enhances it. Preparing queries on fewer connections means that the same prepared queries can be shared among users, further improving performance, while reducing the load on the database server.

#4: VDB offers automatic adaptive query preparing and caching

This (along with #3) is an important feature for scalable multi-tier database applications. This includes 3-tier application servers as well as web applications.

Most Delphi developers are familiar with the theory of prepared queries: if a query is executed several times, preparing it first will result in an increase in performance because the RDBMS only parses and builds an execution plan once. This is similar to a program: you compile it once and run it any number of times -- more efficient than compiling each time you run it! Putting this theory into practice is difficult, because a prepared query is tied to a specific database connection. This is easy to work around in a two-tier application, because you typically have a single database connection (created at design-time), and a collection of query objects (also created at design-time).

However, in a multi-tier application, it is not so easy to use prepared queries. This is unfortunate; it is here that they can make the biggest performance difference, because you can share prepared queries between requests for different users. The problem is that a multi-tier application typically needs to employ connection pooling (to scale to a large number of users), but because prepared queries are tied to a specific connection, it is awkward to manage them in code.

There is another issue that complicates using prepared queries: code reuse. Suppose you are developing a reusable routine that does something with a database connection. Perhaps you need to run three SQL statements. Should you prepare them? If you don't, performance will suffer in an application that calls your routine many times (in a loop, for example). On the other hand, if you do prepare the queries, you are wasting resources in an application that only calls your routine once. How do you write a reusable component that works well in both kinds of applications?

VDB solves both of these problems elegantly by letting each database connection maintain a cache of recently executed queries. When you create a new query (using methods of the database object, such as NewQuery, RunQuery, ExecSQL, or QueryValue), this cache is consulted. If a query (with the same SQL) already exists, then it will be prepared. Queries are automatically purged from this cache after a configurable timeout period. Furthermore, the cache has a configurable size limit.

These features combine to make an automatic and adaptive mechanism -- queries are automatically prepared the second time they're run. This means that you don't be explicitly prepare queries in your code (a good thing, because this way you don't have to decide which ones to prepare -- this decision is based on the dynamic behavior of the application).

Because each database connection maintains its own query cache, this feature works great when the connections are pooled. So, for scalable multi-threaded application servers, VDB makes prepared queries a breeze. Even if you're building a single-threaded application, the automatic and adaptive aspects of this feature are essential for reusable code.

#5: VDB objects are interfaces, simplifying memory management

Database connections and query objects are interfaces, eliminating the need to free them with try / finally blocks. This gives three advantages:

• Less code to type
• Less error-prone
• Fewer levels of nesting (code is easier to read)

For example, consider this routine from the VDB demo application...

// BDE version...
procedure BDE_LongestNameBtnClick(Sender: TObject);
var
  Query: TQuery;
  FishName: string;
begin
  Query := TQuery.Create(nil);
  try
    Query.SessionName := BdeDB.SessionName;
    Query.DatabaseName := BdeDB.DatabaseName;
    Query.SQL.Add('select biolife."Species Name" from biolife');
    Query.Open;

    while not Query.Eof do begin
      if Length(Query['Species Name']) > Length(FishName) then
        FishName := Query['Species Name'];
      Query.Next;
    end;
  finally
    FreeAndNil(Query);
  end;

  ShowMessage('The longest species name is: ' + FishName);
end;

The above code is fairly typical of many BDE applications. Every so often, you need to create a new query to do something. Sounds easy, but notice how the try / finally block is needed to property dispose of the query when we're done with it. Also notice how this causes the rest of the routine to "creep" towards the right-hand edge of the screen.

With VDB, things are more compact because the lifetime of the query object is managed by the compiler...

// VDB version...
procedure VDB_LongestNameBtnClick(Sender: TObject);
var
  Query: IQuery;
  FishName: string;
begin
  Query := Database.RunQuery('select biolife."Species Name" from biolife');

  while not Query.Eof do begin
    if Length(Query['Species Name']) > Length(FishName) then
      FishName := Query['Species Name'];
    Query.Next;
  end;

  ShowMessage('The longest species name is: ' + FishName);
end;

Besides being 8 lines shorter, the VDB routine also avoids an extra level of indenting. Just imagine a routine that used half a dozen query objects...

Also, notice how easy it is to create a query object associated with a database connection. With the BDE components, you must first create the query, then set the SessionName and DatabaseName properties of the query to match the corresponding properties of the database connection. With VDB, you simply tell the database to create a query for you -- it will automatically be associated with the database. After all, what's the point of creating a "zombie" query (one that is not associated with any database connection)?

#6: VDB offers many handy short-cuts, such as RunQuery and QueryValue

One of the design goals of VDB was to make it easy to perform common tasks. Besides making database programming a little easier and more enjoyable, this also makes it less error-prone (because whenever you have a tedious task, your attention tends to lapse and mistakes can creep in). For example, it is quite common to fetch a single value from a "singleton" query. You might want to find out how many orders a specific customer has...

function GetNumberOfOrdersForCustomer(CustID: Integer);
begin
  Result := Database.QueryValue(
    'select count(*) from cust_order where cust_id = :cust_id',
    [ParamValue('cust_id', CustID)]);
end;

The above routine does several things:

1. Creates a new (temporary) query object
2. Sets the SQL property of the query object
3. Configures a parameter in the query
4. Sets the value of this parameter
5. Opens the query
6. Makes sure the query returns exactly one row
7. Returns the value of the (one and only) column in this row
8. Frees the query object

• What SQL statement do you need to execute?
• What are the names and values of any parameters?

function GetNumberOfOrdersForCustomer(CustID: Integer);
var
  Query: TQuery;
begin
  Query := TQuery.Create(nil);
  try
    Query.SessionName := Database.SessionName;
    Query.DatabaseName := Database.DatabaseName;
    Query.SQL.Add('select count(*) from cust_order where cust_id = :cust_id');
    Query.ParamByName('cust_id').ParamType := ptInput;
    Query.ParamByName('cust_id').DataType := ftInteger;
    Query.ParamByName('cust_id').Value := CustID;
    Query.Open;
    if Query.IsEmpty then
      raise Exception.Create('Query did not return a singleton');
    Result := Query.Fields[0].AsInteger;
    Query.Next;
    if not Query.Eof then
      raise Exception.Create('Query did not return a singleton');
  finally
    FreeAndNil(Query);
  end;
end;

Notice how awkward it is to set up a single parameter (you have to set three separate properties: ParamType, DataType, and Value). Also, we have to explicitly verify that the query returned a single row. We avoid using RecordCount because it is notoriously unreliable. Instead, we use a combination of IsEmpty, Next, and Eof. Because fetching a value from a "singleton" query is a common pattern, it is frustrating and error-prone to have to write so much code every time. VDB solves this problem by directly supporting this pattern.

VDB offers a related method, QueryValueDef, which allows you to specify a default value to use in case the result set is empty. This is also a common pattern.

#7: VDB has a built-in performance logging feature

If you set the StatisticsEnabled property of a database object to True, then statistics will be kept on every query executed in that connection.

The WriteStatistics method of the database object will write the accumulated log information to a stream. You can specify whether you want that information sorted by maximum execution time, total execution time, average execution time, the number of times the query was executed or the order in which the query was first encountered. For example, the following log is sorted (descending) by total time:

  Trade Date: 02/11/2002
  ===============================================================================
  2002-02-11 09:58:44.618  (Monday, February 11)
  Sort Mode: Total Time
  VDB Database Log for connection:
    vdb:vdbEngine=BDE;DRIVER=Whatever...
  -------------------------------------------------------------------------------
  Query #     : 001 of 105
  Run Order   : 10
  Query SQL   : select * from order where cust_id = :cust_id
  Total Count : 32
  Total Time  : 28.340
  Average Time: 0.886
  Maximum Time: 3.763
  Minimum Time: 0.500

  Query #     : 002 of 105
  Run Order   : 15
  Query SQL   : select * from customer where cust_id = :cust_id
  Total Count : 40
  Total Time  : 26.253
  Average Time: 0.656
  Maximum Time: 2.630
  Minimum Time: 0.601

  ...

By changing the sort order, you can focus on specific areas of inquiry. For example, sorting by the number of times run will show you which SQL statements were run the most often. This can point out bugs. On the other hand, sorting by the total time can point out the SQL statement that accounts for the most execution time (helpful for performance tuning). Sorting by the execution order displays SQL statements in the order that they were executed, which can also potentially point out bugs in your code.

These statistics include the following information for each query executed:

In addition to the statistics you can log on a single connection, you can enable logging of query execution and performance information to a log file. While useful for debugging, VDB comes with a handy utility for analyzing the VDB log file to provide the same kind of information mentioned. This is especially useful for profiling your application's database usage for longer periods of time, even when using connection pooling or if you run your application more than once.

#8: VDB supports nesting of routines that manipulate transactions

As any database programmer knows, transactions are an essential part of any robust database application. Unfortunately, it can be difficult to organize your code to support transactions, especially if you focus on reusable code. The problem is simple: how do you code a routine that needs to run in a transaction, but might be called when a transaction is active or when one is not active? For example:

procedure DeleteCustomerAndOrders(CustID: Integer);
begin
  Database.ExecSQL(
    'delete * from customer where cust_id = :cust_id',
    [ParamValue('cust_id', CustID)]);

  Database.ExecSQL(
    'delete * from cust_order where cust_id = :cust_id',
    [ParamValue('cust_id', CustID)]);
end;

The above routine deletes a customer and all of their orders. Let's assume we need these two statements to run in a transaction:

procedure DeleteCustomerAndOrders(CustID: Integer);
begin
  Database.StartTransaction;
  try
    Database.ExecSQL(
      'delete * from customer where cust_id = :cust_id',
      [ParamValue('cust_id', CustID)]);

    Database.ExecSQL(
      'delete * from cust_order where cust_id = :cust_id',
      [ParamValue('cust_id', CustID)]);

    Database.Commit;
  except
    Database.Rollback;
    raise;
  end;
end;

Everything looks fine, but we've introduced a subtle barrier to reuse: this routine can no longer be called when a transaction is already active. (StartTransaction raises an exception if a transaction is already active). After running into this situation a few times, the clever programmer might adopt the following solution: start the transaction (and subsequently commit or rollback) only if a transaction was not active when the routine was called. For example:

procedure DeleteCustomerAndOrders(CustID: Integer);
var
  NeedTrans: Boolean;
begin
  NeedTrans := not Database.InTransaction;

  if NeedTrans then
    Database.StartTransaction;
   
  try
    Database.ExecSQL(
      'delete * from customer where cust_id = :cust_id',
      [ParamValue('cust_id', CustID)]);

    Database.ExecSQL(
      'delete * from cust_order where cust_id = :cust_id',
      [ParamValue('cust_id', CustID)]);

    if NeedTrans then
      Database.Commit;
  except
    if NeedTrans then
      Database.Rollback;
    raise;
  end;
end;

This works pretty well, but it is tedious (and therefore, error-prone) to implement this every time you want to start a transaction. Therefore, VDB introduces the concept of "stack-based" transactions. Rather than explicitly starting a transaction, you "push" a transaction (which starts a transaction, or if one is already active, it merely pushes an indicator that a nested routine has been entered). Subsequently, you would "pop" a commit or "pop" a rollback. These operations balance, and essentially implement the trick involving the NeedTrans flag automatically. For example:

procedure DeleteCustomerAndOrders(CustID: Integer);
begin
  Database.PushTrans;
  try
    Database.ExecSQL(
      'delete * from customer where cust_id = :cust_id',
      [ParamValue('cust_id', CustID)]);

    Database.ExecSQL(
      'delete * from cust_order where cust_id = :cust_id',
      [ParamValue('cust_id', CustID)]);

    Database.PopCommit;
  except
    Database.PopRollback;
    raise;
  end;
end;

By simply using PushTrans instead of StartTransaction; PopCommit instead of Commit; and PopRollback instead of Rollback, you can ensure that any routine can call any other routine. This makes it much easier to develop reusable database routines.

#9: VDB profile strings make it easy to reconfigure your applications

Many database applications hard-code their connection parameters, making it impossible to reconfigure them without recompiling. Those that do keep their connection parameters in some external storage (the system registry or an INI file) don't have a standard way to encode the configuration settings. VDB uses a single string to encode all configuration settings for a database connection, regardless of which database library you use. In fact, VDB even standardizes the names of various parameters. For example, who can remember that BDE calls it "User Name", while dbGo calls it "User ID", and IBX calls it "User_Name"? With VDB, it is always "User", no matter which database library you're using under the hood. If you keep the profile strings in an INI file, you can reconfigure the application without recompiling it. Best of all, every application that uses VDB will use the same encoding for connection settings.

Some example VDB profile strings:

vdb:vdbEngine=BDE;driver=IntrBase;database=LocalHost:C:\Data\MyDB.gdb;user=SYSDBA;password=masterkey

vdb:vdbEngine=IBX;database=LocalHost:C:\Data\MyDB.gdb;user=SYSDBA;password=masterkey

vdb:vdbEngine=BDE;alias=DBDemos

#10: VDB has routines to simplify the task of formatting SQL statements

When an application needs to build a (non-parameterized) SQL statement dynamically, several complications arise:

• It is awkward to quote string literals, and to escape quotes inside them
• The format for encoding date/time literals depends on the RDBMS vendor
• Delphi's built-in Format routine does not handle Variant values

  s := Database.FormatSQL(
    'select * from cust_order where due_date < %t', [Now]);

  Query := Database.RunQuery(s);

The FormatSQL method handles date/time literals properly (depending on the RDBMS in question), automatically quotes string literals (and even doubles-up any embedded quote characters), and also handles variants (including null values). To streamline things even further, VDB offers variations on NewQuery, RunQuery, QueryValue, and QueryValueDef that use FormatSQL behind the scenes. For example, RunQueryFmt runs a query with a SQL statement that is built via FormatSQL...

  Query := Database.RunQueryFmt(
    'select * from cust_order where due_date < %t', [Now]);