VDB - Frequently Asked Questions

What is VDB?
How is it "virtual"?
What is wrong with depending on a specific DB library?
What database libraries are supported?
What is the benefit of using interfaces instead of regular objects?
How do I use database and query components at design-time?
What is a profile string?
What is a dialect?
How can I enable connection pooling?
How can I enable automatic adaptive query preparing and caching?
How can I develop reusable database routines?
How can I develop scalable application servers with VDB?
How does VDB support vendor-neutral SQL syntax?
How do I get VDB to work with my favorite 3rd-party database library?
How do I use VDB in a MIDAS application server?

#1: What is VDB?

VDB is a database access library for Delphi applications. VDB is a layer that sits between your application and a library such as BDE or DBX. VDB simply delegates all operations to the underlying database engine, while at the same time adding consistency and new features.

VDB supports versions 5 and 6 of Delphi.

To find out more about the many advantages of VDB, see the Top Ten Reasons to use VDB.

#2: How is it "virtual"?

VDB stands for Virtual Database library, to distinguish it from other, "real" database libraries. You can develop applications (and reusable modules) in terms of VDB, but at run-time, the database access is carried out by another library (such as BDE or dbExpress).

#3: What is wrong with depending on a specific DB library?

There are many differences among the various database libraries that Delphi 6 supports. Some of the ways in which they differ include: performance, functionality, robustness, availability of drivers, deployment, and programming interface. These and other differences make it difficult to select the "ideal" library for a given application. Even worse, it is impossible to make this choice for reusable code modules, because they may be needed in several different applications.

For more information on this issue, see #1 in the Top Ten Reasons to use VDB.

#4: What database libraries are supported?

VDB provides out-of-the-box support for all four database libraries that ship with Delphi, plus two popular third-party libraries:

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

VDB has a plug-in architecture that allows adding support for other libraries, such as any of the other numerous third-party database libraries available for Delphi.

The Advantage Database Server is a popular third-party database library and server that does not ship with Delphi. For more information, go to http://www.AdvantageDatabase.com

DBISAM is a popular third-party database library and server that does not ship with Delphi. For more information, go to http://www.ElevateSoft.com

Flash Filer is a popular third-party database library and server that does not ship with Delphi. For more information, go to http://www.TurboPower.com

For more information on this issue, see #1 in the Top Ten Reasons to use VDB.

#5: What is the benefit of using interfaces instead of regular objects?

There are several advantages to using interfaces over regular objects:

Less error-prone
More concise code (code is easier to write)
Fewer levels of nesting (code is easier to read)

For more information on this issue, see #5 in the Top Ten Reasons to use VDB.

#6: How do I use database and query components at design-time?

Although you could use design-time database components, this practice will lock you into a specific database library. For example, if you use a TQuery at design time, you're stuck with BDE. On the other hand, TIBQuery will lock you into IBX. Furthermore, design-time database components are counter- productive in multi-tier application servers, especially because they conflict with connection pooling.

VDB encourages you to keep all database objects in code. Initially, this may seem to be a limitation, but after you've tried it for a little while, you will soon see how this approach is actually quicker and easier. VDB is ideal for "console mode" applications that connect directly to a database, but have no data-aware controls. VDB is also great for N-tier server applications, especially with connection pooling. VDB is not a good fit for "fat client" applications that connect directly to a database and employ data-aware controls.

One notable exception to this rule is distributed applications. If you are using MIDAS, you can take advantage of data-aware controls linked to client dataset components. On the server side, you can use VDB to perform all database access, and use connection pooling to conserve resources.

If you really need data-aware controls in a "fat client" application, you may still use VDB, but you will still have to create datasets at runtime. This implies linking data sources to datasets at runtime (i.e., no design-time fields editor, etc.) The VDBDemo application that comes with VDB illustrates these techniques. These restrictions will not be acceptable for most "fat client" projects, in which case you should consider MIDAS.

See #15 for details on using VDB in MIDAS applications.

#7: What is a profile string?

A profile string resembles a URL. Profile strings contain all the information VDB needs to create and configure an IDatabase connection object (which wraps a connection component, such as a TDatabase or TSQLConnection). All you need to create an IDatabase object is a profile string.

Below are two example profile strings for connecting to an InterBase database (the first via InterBase Express, the second via BDE):

vdb:vdbEngine=ibx;server=LocalHost:C:\Data\Demo.gdb;user=SYSDBA;password=masterkey

vdb:vdbEngine=bde;driver=IntrBase;server=LocalHost:C:\Data\Demo.gdb;user=SYSDBA;password=masterkey

VDB passes along any parameters it does not recoginze to the underlying database component. Most data access libraries allow parameters to be specified via a string list (e.g., the Params property of TDatabase and TIBDatabase) -- VDB uses this property to pass along any parameters it doesn't understand.

#8: What is a dialect?

A dialect is a vendor-specific version of SQL. For example, the SQL dialect that Microsoft's SQL Server understands differs from the SQL dialect that Oracle understands, which differs from DB2, and so on. Each database may use different syntax for the same functionality.

VDB provides a common set of "Function Escapes" that mask many of these differences. For example, VDB provides a "now()" function that you can use in your SQL statements. This function returns the current date and time on the database server. VDB automatically determines what code to substitute for the "now()" function, based on the dialect of SQL understood by the database. For DB2, for example, VDB uses "current_timestamp" for the "now()" function, but for SQL Server, it uses "getdate()".

In many cases, VDB can determine the SQL dialect from the profile string. For example, if you are connecting to SQL Server through the BDE via the MSSQL native driver provided by Borland, VDB will automatically set the dialect to SQL Server. If you want to make this choice explicitly, you can set the dialect with the "vdbDialect" option in the profile string. For example, "vdbDialect=SQLServer".

#9: How can I enable connection pooling?

Enabling connection pooling is as easy as setting a single value in your profile string. You set the "vdbPoolTimeout" variable to a number of seconds. For example, "vdbPoolTimeout=300" signals VDB to keep connections in the connection pool for up to 5 minutes. When a new connection is requested, VDB will first look for a pooled connection with the identical profile string. If one is found, it is returned; if not, a new connection is created.

You can control pooling on a connection-by-connection basis, through the PoolTimeout property of IDatabase.

#10: How can I enable automatic adaptive query preparing and caching?

Automatic, adaptive query preparing and caching is on by default. The default timeout value for cached queries is 60 seconds. If you create and release a query with the same SQL twice or more in 60 seconds, the second and subsequent queries (which will really be the same, cached, query object) will automatically be prepared. Likewise, if you execute the same SQL through the same query object within one minute, the second and subsequent executions will be prepared.

You can change the query preparing and caching timeout in the profile string using the "vdbQueryTimeout" parameter, or by setting the Timeout property on individual query objects. Setting the timeout to zero disables automatic query preparing and caching.

#11: How can I develop reusable database routines?

With traditional database libraries there are many barriers to writing reusable database routines. In Delphi, the first hurdle to overcome is which component library to use. Should you use BDE, dbGo (ADO), dbExpress, a third-party library, or (if you're using InterBase) IBX? For maximum reuse, no single choice is better. VDB, of course, abstracts these libraries, enabling you to write a database-related routine that can be used with all of these libraries. VDB makes the process of switching as simple as using a profile string to select among the various libraries. You don't even have to recompile. In fact, a running program could switch on the fly. It's that flexible. See questions #1, #2 and #3 above for more.

Another barrier to code reuse is coding to a particular dialect of SQL. The dialect of SQL understood by SQL Server differs from that of Oracle. VDB provides "function escapes" that help manage this complexity. See question #8 for more information.

Although not a feature provided by VDB, it is worth noting that most database vendors support the ANSI-92 JOIN syntax, which simplifies your SQL and makes previously non-portable SQL code portable. In particular, ANSI-92 JOIN syntax includes an outer-join syntax that is portable across all of the major database vendor's products. For example, the following query is an outer-join from the customer table to the order table. It selects all customers and their orders, if any. If a customer does not have an order, the customer record is still returned. Using this syntax will make your code more portable, and thus, more reusable.

  select *
  from customer c
  left join cust_order o on o.cust_id = c.cust_id

Another barrier to reuse is transaction management. Suppose you want to write a routine that adds two records to the database, and that it is important that these records be added transactionally. It should not be possible to add one record without adding the other. Also, suppose that this routine may be called in a number of places. When it is called, it is passed a database connection that may or may not be in a transaction. If it isn't in a transaction, you want to start one (and commit or rollback at the end of the routine). If it isn't in a transaction, you can't call StartTransaction because that would be an error. You certainly shouldn't commit or rollback the already-started transaction. What do you do? You can use the VDB IDatabase methods PushTrans, PopCommit and PopRollback. They allow you to nest your calls to start and terminate a transaction. For more information on this issue, see #8 in the Top Ten Reasons to use VDB.

#12: How can I develop scalable application servers with VDB?

VDB provides several features that simplify developing scalable application and web servers. In particular, built-in connection pooling and automatic, adaptive query preparing and caching features can significantly improve both the performance and scalability of your application servers.

Database connections are resource-intensive. The more connections, the greater the load on the database server. When using connection pooling, your application server will only have as many connections to the database as there are concurrent requests, not concurrently logged in users. If your application has 100 concurrent users, but only 10 of them have active requests at any given time, then you will have at most 10 connections to the database, instead of 100. This greatly reduces the load on the database server, which affects not only the application server, but also any other programs connected to the database. See #9 for information on enabling connection pooling.

Note that for connection pooling to work, your application code must be written so that it doesn't hold onto connections. Once a piece of code is finished with a connection, it releases it. Instead of being freed, the IDatabase object goes into a pool maintained by VDB. The next time your application creates a "new" IDatabase connection, VDB will look to see if it has a connection in its internal pool with an identical profile string. If so, then that connection is reused. It is removed from the pool and returned to the caller. You write your code as if you were creating and releasing connections, when in fact, you are re-using pooled connections. This way, the number of connections to the database varies with the load on your application server. When the load increases, the number of connections increases. When the load decreases, so does the number of connections.

Connection pooling, by itself, can significantly improve scalability. However, using connection pooling makes using prepared queries difficult. You normally prepare a query so that the performance of repeated execution is improved. That's great if you have code in a loop that you want to optimize. For example, you are going to insert multiple records into the same table in a loop. That's an obvious time to prepare the query object you are going to use repeatedly.

What about the other times you want to prepare a query. Suppose that at a certain time of day, lots of users are going to be executing the same code in the application server. You'd like for the queries involved to be prepared, because they will be repeatedly executed. Because prepared queries are tied to a specific connection, you would have to put the prepared query in the connection pool, along with the database. Also, what if different queries are "hot" at different times of day. You wouldn't want to always prepare certain queries. That would make your application efficient at one time of day, but would load the database with little-used prepared queries at other times of day. You'd have to do careful profiling to get the right mix.

What if, instead, VDB were to automatically cache and prepare queries that are executed frequently? That's just what it does. If a certain SQL statement is executed repeatedly in quick succession, then VDB will automatically prepare it. Basically, you can forget about all the headaches described in the previous two paragraphs. VDB will prepare your queries when it makes sense to do so, and won't when it doesn't -- and that behavior can vary throughout the day. See #10 for how to configure automatic, adaptive query preparing and caching.

Note that in order to take full advantage of prepared SQL, you should use parameterized SQL over dynamically generating SQL strings. Using parameterized SQL in VDB is vastly simpler than using database components directly. See the VDB documentation for details.

#13: How does VDB support vendor-neutral SQL syntax?

VDB supports "Function Escapes" which are functions embedded in your SQL, delimited by square brackets "[" and "]". These functions are written in vendor-neutral syntax and translated into vendor-specific syntax when executed. This makes your code far more portable from one database vendor's database to another (such as from SQL Server to Oracle). The following example illustrates setting an order's date to the current date. This code will execute on all the major vendor's databases.

update cust_order
  set order_date = [today()]
  where order_id = 1234

#14: How do I get VDB to work with my favorite 3rd-party database library?

As of version 1.4, VDB supports three 3rd-party database access libraries (see #4 for details). Each of these is implemented in its own unit. For example, DBISAM support is implemented in the unit VDB_DBISAM. Simply add this unit to your project, and then you can use VDB profile strings that reference this engine (for example, "vdb:vdbEngine=DBISAM;...").

You may encounter a problem with the DCU files for these units (for example, VDB_DBISAM.dcu) if you are using a different version of the 3rd-party library than we did when we compiled these DCU files. If this is the case, you can easily recompile these units. There are two ways to do this:

Add the .PAS file directly to your project. You can find the .PAS files in the folder $(DELPHI)\Dimeric\VirtualDB.
Copy the .PAS file into the folder $(DELPHI)\Dimeric\Lib. This folder is on your library search path (for all projects).

The trial version, which does not include source code for other units, does include the source for 3rd-party library support, so that you may recompile these units.

#15: How do I use VDB in a MIDAS application server?

VDB allows you to create MIDAS servers that use any supported VDB engine. You can define remote datasets and remote methods that take advantage of VDB features such as connection pooling and vendor-neutral SQL. On the MIDAS client, you can use TClientDataset components and data-aware controls for a powerful user interface.

The VDB documentation discusses MIDAS application servers in detail, under the topic "N-Tier Development". Also, the VDBNTier demo program is an example MIDAS application that uses remote datasets and remote methods.