Working with BDE Aliases
in dBASE Plus

Last Modified: December, 2002
Ken Mayer, Senior SQA Engineer
dBASE, Inc.


What Is a BDE Alias and Why Should I Care?
A BDE (Borland Database Engine) Alias is a method of referencing either a database (if using a SQL database Server such as Microsoft SQL Server, Oracle, Interbase, etc.), or a folder of tables (which is treated by the BDE as a database).

Why should you care if all you use are local (native dBASE .DBF, Paradox .DB, etc.) tables?

Largely because it is useful for organizing your data and helping you find the tables after an installation. Example: your users decide it is better to move the tables to a network drive, but you hard-coded your application to look for the tables on a local computer's hard drive. What now?

If you had used a BDE Alias, all you would have had to do is change the path in the BDE Administrator. Everything else would have been done for you.

Some Definitions
Before going much further it may help to understand what a database is, and what the BDE (Borland Database Engine) does with them.

By definition a database is a collection of tables. In the case of some software packages (such as Access) and most SQL Server software (such as Oracle, Interbase, etc.), a database may be a single file (Interbase uses a file extension of .GDB), but it may contain a large number of tables. When you work with tables local to dBASE (.DBF or .DB) a database is a folder that contains the tables. In the case of local tables the folder may be the folder that your application is in, or the tables may be stored in a separate folder.

The Borland Database Engine (BDE) by definition works with Aliases, which are names that are used in software to reference the databases. When you use dBASE there is a 'default' database alias - which has no specific name (this is the directory you are currently working in). By using BDE Aliases we can refer to the databases no matter where they are. If the database gets moved you can update the location of the database in the BDE Administrator (see below) and not have to modify your source code at all.


Okay, BDE Aliases Are a Good Idea. How Do I Use Them?
The first thing you need to do is to create the BDE Alias. The simplest method of doing this is to use the BDE Administrator. Use the START menu for Windows and go to the dBASE Plus folder. In that folder you should find an icon for the BDE Administrator.

When you start this up, you will see a lot of stuff going on. Don't panic.

To create a new BDE Alias, all you have to do is follow the steps given below (this assumes you are using local tables, but most of the following applies equally for SQL Servers). Note that in the images below that there are probably more BDE Aliases listed than you will see in your BDE Administrator screen. Don't panic -- the author has been using dBASE for many years and has a lot of aliases created.

  1. Click on the 'Databases' tab
  2. Type Ctrl+N (for 'New' Alias, or use the 'Object' menu, and select 'New' from there)
  3. When the dialog appears, select 'Standard' (unless you are using a specific SQL Server, such as Oracle, in which case you would select the name of that Server)
  4. Click 'OK'
  5. You will see 'STANDARD1' in the list of BDE Aliases that already exist.
  6. Click on that and change the name to what you want to call your alias (you might want to use the name of your application, for example).
  7. On the right side of the screen you will see a tab marked 'Definition'
  8. If you are using dBASE tables, it's a good idea to change the 'Default Driver' to dBASE.
  9. Set the path to the folder your tables are in. (The simple way is to use the '...' button and in the dialog that appears select the folder your tables are stored in.)
  10. Press Ctrl+A (for 'Apply', or select the 'Object' menu and then 'Apply')

That's it. You've created the BDE Alias. You can close the BDE Administrator if you wish.


Using A BDE Alias With 'OODML'
OODML is the Object-Oriented Data Manipulation Language in dBASE (dBL).

If you are already used to working with OODML, you have worked with Query objects, either on your forms, reports and/or in your source code.

Adding a BDE Alias means some minor changes. You need to create an instance of a Database object for each alias you want to use. Then you need to set a property for each query object that tells dBASE to use the database object.

The syntax in standard dBL to create a database object is:

   dMyData = new Database()
   dMyData.databaseName := "MyAlias"
   dMyData.active       := true

To add a database alias reference to an existing query, just add:

   qMyQuery.database := dMyData

before the query object's SQL and ACTIVE properties are set.

Note: the names given above of 'dMyData', 'MyAlias' and 'qMyQuery' are completely made up and should be changed as needed to match your own code.

If you are mostly used to working with the designers (form designer, dQuery, etc.) the syntax will look a little different:

   this.MYALIAS1 = new DATABASE()
   this.MYALIAS1.parent = this
   with (this.MYALIAS1)
      left = 4.1429
      top = 14.4091
      databaseName = "MYALIAS"
      active = true
   endwith

Note that if you are using the designers you normally do not have to create the code shown above -- it will be created for you.

Adding a Database Object to An Existing Form or Report
If you have a form or report you want to modify to use a BDE Alias, there are a couple of ways to do this. One is to add the object and then make the changes in the designer to the query object(s).

Version 1:
In the form or report designer, on the Component Palette, is a tab 'Data Access'. If you run your mouse over the different objects shown, one of them is a Database. Drag a database object to the design surface.

  1. In the Inspector change the 'databaseName' property to your alias by selecting it in the drop-down list.
  2. Set the 'active' property to true.

The designer will assign a name to the database object, such as 'database1'. The database objects are named in different ways depending on how they are created - in this case the designer has no idea what the BDE Alias is going to be so it uses the more generic name of 'database1', if you place a second database object on the design surface it will be named 'database2', etc.

The designer does not automatically assign the query object's reference (the database property) to this database. To do that, you need to follow these steps:

  1. Click on the query object
  2. In the inspector, change the 'active' property to false.
  3. Select the 'database' property in the Inspector.
  4. Click on the down arrow, and select 'database1' from the list.
  5. Set the 'active' property to true in the Inspector.

Problem: datalinks for your controls have gone away and you have to reset them.

If you are comfortable with modifying source code, it may be easier to do this another way.

Version 2:
In the form or report designer, on the Component Palette, is a tab 'Data Access'. If you run your mouse over the different objects shown, one of them is a Database. Drag a database object to the design surface.

  1. In the Inspector, change the 'databaseName' property to your alias, by selecting it in the drop-down list.
  2. Set the 'active' property to true.

(To this point is exactly the same as before, but the steps now change.)

Save the form or report and exit the designer.

Open the form or report in the Source Editor (right click on it in the Navigator and select 'Open in Source Editor' or click on it and press the F12 key).

You will see that your database object exists (with the name 'database1') and is active. Go to your query and simply add the following line (before the SQL and active statements):

      database = form.database1

Why do it this way? Because doing this in the Source Editor means that your datalinks won't be broken and have to be reset. If you save the form or report and run it, or bring it into the designer, you will see that it is now using the table(s) from the alias.

Adding a Database Object to A New Form or Report
As it turns out, this is even easier to do than it might seem.

In the form or report designer, when you are ready to add a table to the design surface, click on the Navigator.

Next, click on the 'Tables' tab. Then select the 'Look in:' combobox, and select your BDE Alias from the list (you may need to scroll down a bit).

Once you have selected your alias, it will appear in the 'Look in:' list, and any tables in that alias will appear in the Navigator. Drag the table(s) you wish to use for your form or report to the design surface. The first table you drag to the surface will create a database object, and the database property of the query object will be set automatically for you.

Before exiting your session of dBASE, you should probably close the database you opened, unless you know that you will be going back there for more design work. This is done with the XDML command (in the Command Window):

   CLOSE DATABASE

If you do not close the open database(s), then when you restart dBASE the next time it will automatically reopen the previously opened database, which may not be what you want to have happen.

Using BDE Aliases and OODML With SQL Servers
You have to use BDE Aliases to work with SQL Servers. This means you will need to deal with userids and login strings. SQL Servers include Oracle, Interbase, dB2, Microsoft's SQL Server (MSSQL), etc.

For your application if you want to just log your users in with the same login string and password, the database object has a 'loginString' property which uses a string in the format of 'userid/password'. For example, the default login for an Interbase database is 'SYSDBA/masterkey'. If you set the 'loginString' of the database object to that string, then your user would not need to login.

Of course, you may wish your users to login, in which case you don't have to do anything -- when your form (or report) opens, the database object will automatically request the login information in a dialog.

Database Objects
There is more detail in other parts of the Knowledgebase on these very powerful and versatile objects. There are many methods that can be used with a database object to manipulate data and you should take a look at these.


Using A BDE Alias With 'XDML'
XDML is the older XBASE Data Manipulation Language in dBASE (dBL). This is syntax based on the dBASE for DOS syntax, which is also used in dBASE 5.0 for Windows and Visual dBASE 5.x. Most of it still works fine in dBASE Plus. If your code works, great.

Using a BDE Alias in XDML is actually pretty simple. In the simplest case, you need two commands:

   OPEN DATABASE <bdealias>
   SET DATABASE TO <bdealias>

If your code simply 'uses' tables with the 'USE' command, you can tell it to open the tables from a different database by opening the database and then setting the database to being the current location to open the tables.

For example, if you have an alias named 'MyAlias', you would issue the commands:

   OPEN DATABASE MyAlias
   SET DATABASE TO MyAlias

From this point on, all tables that are opened with the USE command will automatically be opened in the alias referenced by 'MyAlias'. If 'MyAlias' has two tables, named 'MyTable1' and 'MyTable2', you would simply open them like this:

   USE MyTable1 IN 1
   USE MyTable2 IN 2

What if ...?
What if you want to use some tables in an alias and some in the current directory? Or what if you want to use some tables in one alias and some in another?

Well, only one BDE Alias can be the 'current' alias.

You can open (with the OPEN DATABASE command) multiple aliases and then you can switch the 'current' one with the SET DATABASE command:

   OPEN DATABASE MyAlias1
   OPEN DATABASE MyAlias2
   SET DATABASE TO MyAlias1
   // do something
   SET DATABASE TO MyAlias2
   // do something

That can get a little confusing, but it does work. Another way of dealing with it is to open the databases and then refer to the aliases when opening the tables:

   OPEN DATABASE MyAlias1
   OPEN DATABASE MyAlias2
   use :MyAlias1:MyTable
   use :MyAlias2:MyTable2 in select()   

And so on. This gives you a lot of flexibility. You can combine these so that if you want to open an alias and work with tables in the same folder your application is in, you could do something like:

   OPEN DATABASE MyAlias
   use MyTable
   use :MyAlias:MyTable2 in select()   

And I'm sure lots of other combinations could be come up with.

Login Strings
Note that the OPEN DATABASE command has the ability for you to open a BDE Alias with a login string:

   OPEN DATABASE MyAlias1 LOGIN userid/password
This is noted here, in case you need to login to a SQL Server database (such as Interbase, Oracle, etc.), which generally require userids and passwords.

Close Your Aliases!
You should remember to close your BDE Aliases using the CLOSE DATABASE command. If you do not, the next time you start dBASE the last open database will be reopened when you start up, and this can be confusing or frustrating if you did not expect that behavior.

Just issuing:

   CLOSE DATABASE

will close all open databases and all open tables. This can be useful as a quick way to close everything.

If you want to just close a specific BDE Alias, however, you can do that with:

   CLOSE DATABASE MyAlias


Creating BDE Aliases Programmatically
In the dBASE Users' Function Library Project (dUFLP) is a program called 'BDEAlias.cc'. This program can be used to create, modify and delete BDE Aliases. It has other functionality as well. It is something you may wish to take a look at.


The End
This paper is aimed at getting you started using BDE Aliases in your applications. It cannot possibly cover all the various needs and permutations of what can be done with them. If you need to go beyond what is covered here, use the standard resources: the dBASE, Inc. newsgroups. Details on the newsgroups can be gotten at the dBASE, Inc. website.

Enjoy -- Ken Mayer

Thanks to Geoff Wass (dBVIPS) for editing to make this more clear to the reader.


DISCLAIMER: the author is an employee of dBASE, Inc., but has written this on his own time. If you have questions regarding this HOW TO document or about dBASE, you can communicate directly with the author and dBVIPS in the appropriate newsgroups on the internet.

HOW TO files are created as a free service by members of dBVIPS and dBASE, Inc. employees to help users learn to use dBASE more effectively. They are edited by both dBVIPS members and dBASE, Inc. Technical Support (to ensure quality). This HOW TO file MAY NOT BE POSTED ELSEWHERE without the explicit permission of the author, who retains all rights to the document.

Copyright 2002, Kenneth J. Mayer. All rights reserved.

Information about dBASE, Inc. can be found at:

       http://www.dbase.com
    

EoHT: BDEAliases.htm -- December, 2002 -- KJM