How to specify the name of a database that is in a different directory

Problem/Question/Abstract:

In an SQL select statement, how do I specify the name of a database that is in a different directory? Is there a way to parameterize the name so that a BDE alias is used to supply the directory?

Answer:

You cannot parameterize the value in an SQL statement such that a BDE alias could use the value. You could programmatically change the directory specified in the BDE alias itself, though. For that, see the TSession.ModifyAlias method.

But there are other ways to do this, ways that do not involve aliases. The table reference in a local SQL statement can consist of just a table name:

SELECT *
FROM Customer

It can be a table name and filename extension:


SELECT *
FROM "Customer.db"

It can be a table name prefixed with the name of a BDE alias:

SELECT *
FROM ": DBDEMOS: Customer.db"

or it can be a table name prefixed with a specific drive and directory reference:

SELECT *
FROM "C:\Program Files\Common Files\Borland Shared\Data\Customer"

With the different parts of an SQL statement on different lines within the TQuery.SQL property, you can more easily change just one of those parts without affecting the other parts. Do this by referencing one element of the string list object that is the SQL property. For example, to change just the second line (the FROM clause):

with Query1 do
begin
  Close;
  SQL[1] := '"' + DatabaseStrVar + 'Customer.db"';
  Open;
end;

You could then set this memory variable DatabaseStrVar to a number of different values and the same code would still work.

1. An empty string.
2. The name of an alias (including colons in the variable).
3. A valid drive/ directory reference (ending in a back-slash).