% '------------------------------------------------------------------------------- 'File : dBase_Knowledge.asp 'Author : Surya Kumar 'Task : Knowledge Page for dBASE 'Version : 1.0.0.pk 10.08.2004 Base version 'Copyright : Ebase Interactive Inc., NY, USA '------------------------------------------------------------------------------- %>
Working with BDE Aliases in dBASE Plus
Last Modified: December, 2002
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 MSSQL (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.
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.
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 Database 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()
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()
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).
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:
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.
(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):
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.
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>
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
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
What if ...?
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
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
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
And I'm sure lots of other combinations could be come up with.
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 re-opened when you start up, and this can be confusing or frustrating if you did not expect that behavior.
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.
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 Newsgroups. Details on the Newsgroups can be obtained at the dBASE Inc. website.
Enjoy -- Ken Mayer
Thanks to Geoff Wass (dBVIPS) for editing to make this more clear to the reader.
EoHT: BDEAliases.htm -- December, 2002 -- KJM