François Ghoche, ATON Consulting (France) & The European Xbase Institute
With Visual dBASE, you can develop applications that are scalable from standalone PCs to network file servers, and to client/server SQL databases as well. This session will provide information on how to build your applications so that they become as independant as possible from the physical source of data, and be scaled to different platforms. The client/server version of Visual dBASE, featuring the Local Interbase Server, will also be briefly covered.
Understanding the structure of Visual dBASE is important for implementing a strategy of portable development.
From the diagram, you can see that all Borland database aware products share a common component : the Borland Database Engine (BDE). All access to data files is done through the BDE.
Although not shown in the diagram, the BDE handles direct access to native physical data files : namely dBASE (DBF), Paradox (DB) and ASCII, whether on local disk drives or distant file server drives.
SQL back end engines are handled via the SQL Links (IDAPI specification conforming) drivers. Current available drivers from Borland include Interbase, Oracle, Informix, SQL Server, Sybase.
Other data files formats can be connected via the appropriate ODBC driver. A large collection of drivers are available from third parties. The best known source is Intersolv, Inc. Some ODBC drivers may already be installed in your computer by other applications. To know which ones, go to the Windows Control Panel and open the ODBC icon.
The BDE is loaded only once in memory for all running instances of Borland applications. For instance, if you run Visual dBASE twice, both instances share the same copy of the BDE.
Please note that in this talk we shall call native data engine file formats the dBASE (DBF) and Paradox (DB) tables. These can point either to local or distant data, the former being stored in tables on the local hard drive, while the latter may be stored on a network file server.
External data engine file formats are handled through appropriate drivers, either SQL Links or ODBC. These can also point either to local or distant data. The latter is the usual in this case, however, you can also have local databases if you are using the Local Interbase Server.
We can now briefly describe some of the BDE features.
One important feature brought to Visual dBASE by the BDE is that the logical data model is to a large extent independant from the physical data format. The query engine is part of what brings this independance.
The obvious illustration of this independance is that while in a Visual dBASE program you can use dBASE data manipulation language (DML) commands on a dBASE or Paradox table, and you can also use the same commands on a SQL table.
Also, you can use SQL DML statements on both SQL tables and on native engine format DBF and DB tables.
If necessary, the BDE will take care of translating the incoming DML commands into the appropriate data engine language and logics.
When you USE a table in Visual dBASE, it can mean for the BDE to open a physical DBF or DB file, an SQL engine server table, or even a mini or mainframe specific data file. This is the task of the core of the BDE. Nevertheless, connecting to the appropriate data sources is also required.
The driver manager will enable installing the SQL Link driver appropriate for connecting to a specific SQL server back end. The ODBC driver manager will take care of interfacing to appropriate ODBC drivers.
There is also an API (Application Programming Interface) for direct programmer access to the functionalites of the BDE. This is intended for system level developement, and is usually not necessary for Visual dBASE developpers. The API function documentation is provided with the BDE package, sold separately by Borland for C/C++ programmers. However, if you are sure you know what you are doing, and you need to access some BDE functions, you can use the EXTERN prototyping command in Visual dBASE to declare the appropriate functions, and then use them accordingly.
Using Visual dBASE to access different data sources can be pretty straightforward. However, each data engine and each physical data format may carry its specificities, which have to be taken into account. As a result, while switching from one data source to the other may be simple, it may be necessary to include some customized programming code to handle the specifics.
The basic principle is simple : the physical location and interfacing with the target database is determined by the settings in the Borland Database Engine (BDE) configuration file, usually called IDAPI.CFG.
When you run the BDE configuration utility (icon in the Visual dBASE program group), the first page of the displayed dialog window handles the drivers installation and configuration.
As you know by now, standard drivers (native data engine) handle the DBF (dBASE) and DB (Paradox) physical table file formats. SQL Links drivers handle the various SQL server engines (Interbase, Oracle, SQL Server, Sybase, Informix). They are installed by the Setup/install program which comes on the drivers diskettes.
ODBC version 2 compliant drivers (DB2, OS-400, Btrieve, MDB, FoxPro with CDXs, etc.) are installed by clicking the ODBC button on the left pane, and then providing the driver's DLL information.
Note that the driver must have been installed first, and must appear in the Windows Control Panel ODBC drivers list.
The settings on the right pane will depend on the driver, and on the server/database location (its network address).
Of course, installing the SQL Links or ODBC drivers is not the only opration to be performed. The appropriate client driver for the back end database engine, as well as the drivers for the appropriate communications protocols must also be installed on the client computers and properly configured.
We shall now give some more details on some of the paramaters to be set on the right pane for some drivers. Note that some of these parameters may only be availlable for specific driver types :
DLL
Name of the DLL program file driver.
LANGDRIVER
The language driver determines the character set and
locale cutltural conventions used for the data sent to the BDE by the physical
data source. A default driver can be set on the Drivers pane, but a different
one can be set for each database alias as well. Proper setting is critical
not only so that the data read from the source be displayed and processed
correctly, but also to insure that the data recording in the table files
be done in the correct format. The parameter SQLQRYMODE
can help if you don't find an appropriate BDE driver for a distant data
source.
OPEN MODE
Defines whether you want tables opened via user interface commands
to be READ ONLY or READ/WRITE.
SCHEMA CACHE SIZE
Number of SQL tables which schema data will be kept in cache memory.
Default is 8, and range is between 0 and 32.
SCHEMA CACHE TIME
In relation with the previous parameter, this one lets you specify
a possible time limit to keep the schema data in memory. "-1"
means no limit, while "0" cancels the cache. Other numbers give
the maximum duration in seconds.
SERVER NAME / PATH
Give the complete name and path to the database or directory.
SQLPASSTHRU MODE
Pass through SQL commands are sent by the Visual dBASE application
directly to the server engine via SQLEXEC() functions. The BDE then doesn't
attempt to interpret them, and just transmits them to the the distant data
engine. Possible parameters are as follows:
For these two last cases, if a connection is shared between pass-through and embedded SQL, it is safer to handle transaction processing through dBASE language in your program, and not via SQLEXEC() SQL statements on the server.
In any case, it is obviously more difficult to build a scalable application locally if you intend to use server specific SQL language sent in pass-though mode. However, you can still do it by having two different sets of instructions, which may be controlled via compiler directives.
SQLQRYMODE
By default (no value), a query is directed towards the distant engine
(the server). If the server cannot process the query, then the BDE takes
over. Why wouldn't the server handle the query ? Well, this will be the
case if your query includes tables from different databases / servers /
formats, or SQL syntax unsupported by the server, or SQL queries needing
more than one statement.
In these cases, processing is done locally, at the BDE level. You can prevent this default behavior by setting this entry to SERVER, if you want to force processing on the distant server, or LOCAL, if you want it done on the local BDE.
Again, be careful when handling data with specific language drivers. Some operations can have different results if done on the server or locally : sorting and comparing mixed case strings are some of them.
NET DIR
This parameter can be found on the Drivers page tab for the Paradox
standard native engine driver. If you intend to share DB tables, you will
have to mention the directory where the Paradox network control file will
be stored.
If you switch to the "Aliases" page of the BDE configuration dialog form on screen, you will get to a very important part of our talk : that's where you define one (or more) database alias for your data.
The database alias is new to DOS dBASE users. You already knew and used the table alias in DOS versions of dBASE, which is a different animal. The table alias is defined within your program code by the USE command. If you don't include an ALIAS clause in the USE command, the table alias is the same as the table filename (without the extension). The database alias, however, can only be defined in the BDE configuration utility.
If your data are in native data engine format (DBF & DB), the database alias will point to a specific directory. All the tables stored in this directory will be considered part of the pseudo "database" when the appropriate alias will be opened in Visual dBASE.
If your database alias points to an external data engine (through an SQL Links or ODBC driver), the full database name including the server and path will have to be mentioned in the right pane.
One last hint : you can have several different BDE configuration files carrying different file names than IDAPI.CFG. The extension just have to be "cfg". The CONFIGFILE01= entry in the [IDAPI] section of the WIN.ini file determines the one which will be used when the BDE is first loaded. Then, if a different file name is found in the entry with the same name in the DBASEWIN.ini file when Visual dBASE is loaded, the latter is loaded instead as the global driver for the current Visual dBASE session.
Finally, for any changes made to the BDE configuration to takes effect :
The methodology to use to develop small applications intended to access an external data engine database is as follows :
Alright, this is the theory. The real life thing however, will force you to face some changes in your application, such as possible different syntax for table names, index handling variations and limitations, different data types and behavior, etc.
Of course, after you become more experienced, you can get prepared to face these challenges, by avoiding using native data engine specific features, for instance, and by using compiler preprocessor directives to handle automaticlaly the different syntax for table/field names.
If you try using database aliases from the Navigator, you will have the correct syntax generated in the Command window.
On the upper part of the Navigator Window, when the Tables view is selected, you can find two radio buttons which enable selecting tables from the local directory, or tables from a database. Select the latter, and then, select the appropriate database alias from the drop-down list.
Suppose you have created a database alias named AppData in the BDE configuration. When you select it in the list, the following commands will be generated in the Command window :
OPEN DATABASE APPDATA SET DATABASE TO APPDATA
The first command establishes a connection to the database. In this case it is a pseudo database, pointing to a local directory. However, both commands will be the same when you change the alias in the BDE configuration, and direct it towards the external data engine destination database.
The second command establishes this database as the default source for tables when requested (by the USE command or the SQL SELECT statement). the effect is somehow similar to SET DIRECTORY for native engine files formats.
If you double click on the table name MyTable in the Navigator now, the following will be generated in the Command Window :
USE :APPDATA:MYTABLE.DBF
The table name has been prefixed with the datatabase alias. This is necessary only if the table you are opening doesn't belong to the default database alias. However, the Navigators generates it anyway.
Also, you will notice that the database alias is delimited between ':' characters. This will avoid the characters of the database alias name string to be controlled or transliterated by Visual dBASE, which could be a problem if the language driver of the database and the global one in Visual dBASE dont use the same locale conventions.
As a quick exemple at this stage, just remember that some accented characters are considered alphabetic in some languages, while they are not in other languages.
The ':' delimiter can be used for table and field names anytime they may contain characters which may be unauthorized on the host platform (or within the active language driver). This is the case also if you find spaces or other delimiters and special characters ( '"', '[', ']', ')', '(', '#' ). For instance :
SET FIELDS TO Name, :Original Date:, :Cust#:
Back to our Command window example, you may want not to include the table file extension - DBF in this case - in your program code. When switching to the external data engine database, the extension will not be appropriate anymore.
If you are using DBF tables, you can just forget the extension altogether, as 'DBF' is the default. If you are using DB tables, use the command SET DBTYPE TO PARADOX, and you won't have to mention the 'DB' extension either.
Remember that SET DBTYPE is scoped to the current session. you will have to make sure is is set accordingly in the DBASEWIN.INI configuration file, or even better, in an environment settings routine you will call at the beginning of each new session.
We shall use SET DBTYPE later in this paper as an example for how to handle the specifics when switching from the local engine to the distant engine data.
As we have already explained, the switching is done through the BDE configuration utility. Let's review the necessary steps :
An even better way to do this is to have two BDE configuration files ready, each one with the settings for the appropriate environment, so that you can switch back and forth just by changing the CFG file and running Visual dBASE again.
Compiler preprocessor directives can help you handle the engine or platform specific "features" you will inevitably encounter. We shall use the table type as an example of such.
#DEFINE EXTERNAL && We are compiling for the external
&& data source engine as a target.
#IFNDEF EXTERNAL && If we are not compiling for the external.
SET DBTYPE TO PARADOX && to avoid using 'DB' extension
&& in table names.
*-- other possible platform specific commands.
#ENDIF
When working on the native data source environement, you just have to comment out the #DEFINE command line, and then re-compile the application.
Beware of the fact that the scope of preprocessor directives is limited to the program file in which they are used.
Two ways to work around this limitation :
Using this last method, you will have to handle the #DEFINE lines in one file only; the Include file. For instance, if you put your directives in a file called MyApp.h, to include its contents in your code use the following statement :
#INCLUDE MYAPP.H
Of course, a combination of the two techniques is an even better solution.
Those of us who have been using dBASE in the DOS environment will usually have a tendancy to chose to work with DBF tables. However, if you are building an application to be ported to a client/server data environment, there are some advantages in using the Paradox DB native engine file format instead. Here are the main differences :
From these points you can see that DB tables are much more similar to SQL tables than DBFs. Using this format for development on your PC may help scaling the application to the client/server data environment more easily.
Primary and secondary indexes
Visual dBASE will open automatically the indexes associated with the
DB table you open. The index will be handled by the BDE, same as for DBF
tables. As far as SQL engines tables are concerned, the indexes are managed
by the back end engine. The BDE just sends the appropriate instructions.
The first index key created in a Paradox table is called the Primary index. Once it is created, you can then define one or several other Secondary indexes. Index keys are composed of one or more fields. You cannot use expressions in index keys (no functions, UDFs or concatenation). Further, the fields for the primary key have to be in the beginning of the table structure.
The primary key is a unique identifier, meaning that no two records can exist with the same value for the primary key.
Note that this is different from the UNIQUE clause in DBF indexes, which excludes duplicate keys from the index, but not the related records from the table.
The PRIMARY clause is used in the INDEX command to specify such tag
Appending records
As a consequence of primary keys, you have to beware of the traditional
use of APPEND BLANK / REPLACE as done in most DOS dBASE code. If you are
in a multiuser or multitasking environment and two users / applications
add a record to the same table, the second APPEND will be rejected before
the REPLACE can be performed. This is because you will be trying to add
a record with a duplicate Blank key.
Instead, you can use the APPEND AUTOMEM new feature, which adds the record and fills its fields simultaneously with the contents of memory variables. the variables have the same name as the fields. They have been previously created automatically by opening the table with the command :
USE <table> .... AUTOMEM
An even better way is to use the table record buffer management functions instead of memory variables. BeginAppend(), SaveRecord(), AbandonRecord() and IsRecordChanged() are methods of the Form class, and are available since Visual dBASE version 5.5. You can refer to my paper Encapsulating datasets and transactions in Visual dBASE (on the Conference CD-ROM) for more information on this subject.
Multiple fields keys
As far as multiple field keys are concerned, they are defined by specifying
the key using list syntax instead of expression syntax. In other words,
this command for a DBF table :
INDEX ON Name + First TAG Names
would be :
INDEX ON Name, First TAG Names
for DB and SQL tables. One advantage is that you can put fields of different types in an index key without having to convert them all to Characters by building an expression.
Seeking and finding keys
You can only use SEEK and SET KEY TO to search such an index key with
more than one argument. FIND, SEEK(), LOOKUP() and KEYMATCH() will only
take one argument. For instance, these constructs are correct :
SEEK "Ghoche", "François" SEEK( "Ghoche" )
Of course, the second form searches on the first part of the index only.
Note that the behavior of FOUND() will be different from that on the DBF tables. In this case the result will be True whether SET EXACT is ON or OFF, while this wouldn't be the case on a DBF index.
Unsupported indexes
Conditional and descending indexing are not supported in DB tables.
As far as SQL tables are concerned, conditional index keys are not supported,
but descending order may be supported on some engines.
Unique tag names
Tag names for SQL tables indexes must be unique to the whole database,
not only to the table.
Read only views
If you order an SQL table in the QBE Designer on a field for which
no index is available, the resulting view will be read-only.
The following table shows the corresponding data types between SQL, DBF and DB data tables, as automatically performed by Visual dBASE and the BDE.
SQL Syntax dBASE Paradox
SMALLINT Numeric (6,10) Short Integer INTEGER Numeric (20,4) Long Integer DECIMAL(x,y) NA BCD NUMERIC(x,y) Numeric (x,y) Numeric FLOAT(x,y) Floating (x,y) Numeric CHARACTER(n) Character Alphabetic VARCHAR(n) Character Alphabetic DATE Date Date BOOLEAN Logical Logical BLOB(n,1) Memo Memo BLOB(n,2) Binary Binary BLOB(n,3) NA Formatted memo BLOB(n,4) OLE OLE BLOB(n,5) NA Graphic TIME NA Time TIMESTAMP NA DateTime MONEY Float (20,4) Monetary AUTOINC NA Autoincrement BYTES(n) NA Octets
x = length (default : 20 for dBASE)
y = decimals (default : 4 for dBASE)
n = length in octets (default : 1)
1-5 = sub-type blob (default : 1)
In addition to what has been already described, you will not be able to use explicit locking on SQL tables and rows the way you may be used to do it on DBF tables. On an SQL table, the locking will be done by the user who first validates a modification. You can work around this by checking SQLERROR() (or ERROR() for Visual dBASE level errors and DBERROR() for BDE level errors), but the more appropriate method is to use transaction processing ( BEGINTRANS(), COMMIT() and ROLLBACK() ), instead of record locking.
Don't forget that one essential difference between the Visual dBASE language and the SQL language is that in the former you write down the complete procedure to handle the data, while in the latter you send the engine the description of the result you want to obtain. The database engine takes care of the procedure to give the result back as requested.
As a result, in addtion to the different mindset, you also may have to expect to optimize queries differently depending on the external data source.
If you are using ODBC drivers instead, then the situation may become even more strange. Not all Visual dBASE DML commands will give the result you are used to expect on such file formats as Btrieve, OS/400, Excel, or even FoxPro tables... I don't know any other way than test by yourself, build your own experience, join discussions in electronic fora (the Compuserve VBISUALDB forum has an outstanding reputation), newsgroups, etc.
Oh yes, and sometimes it may be a good idea to look for a better ODBC driver...
Without going through an extensive explanation of the subejct, a general understanding of how character sets and cultural conventions are managed in Visual dBASE is essential to cross-platform portable development. The handling of these features depend on the following :
The language driver controls the following :
As an attempt to simplify while explaining the importance of this issue, let's summarize the following steps for language driver handling and behavior:
Language driver and DOS
If the underlying DOS code page is not a subset of the current language
driver, file names may not be properly registered in the operating system
file directory. You will discover about that next time you try to open
the file, as the name will not be as expected...
Preventing translation
When you open tables from a database on a distant platform, you are
not in control of the equivalent features on the distant host. However,
the tables will be openend according to the language driver specified in
the BDE configuration. Visual dBASE (essentially the BDE) will then try
to transliterate the character flow from the distant source. But this must
not be done for table names and field names, for instance. You can use
delimiters to prevent this translation to occur.
Comparing strings
As soon as you are using tables containing data with characters other
than simple English 7 bit ASCII, you have to beware of primary and secondary
weights when comparing character strings. Consider the following :
SET EXACT ON && Use primary weight only. USE MaTable ORDER TAG :Prénoms: SEEK "Francois" && "François" will not be found
SET EXACT OFF && Use secondary weight. USE MaTable ORDER TAG :Prénoms: SEEK "Francois" && "François" will be found
Checking for the active driver
To know what is the current language driver and character set, use
the LDRIVER() and CHARSET() functions. The response will depend on the
current work area (and session if appropriate). If you want to know the
global driver, SELECT an empty work area first.
Conditions and filters
When you create an index tag, or set a relation, sort, and generally
speaking process according to table field data, the table language driver
is in control. However, if you SET FILTER TO, DO WHILE, use a FOR condition,
or an expression in SET RELATION, then the global language driver rules.
You can understand why all this is important, and has to be taken into account when switching from one environment to a different one.
While the general strategy we have covered may be an acceptable solution in some and many cases, there is also a different and more straightforward way to develop large client/server applications in Visual dBASE when SQL databases are the target : use the Interbase local engine which comes with the Client/server edition of Visual dBASE.
If the final data source for your application will reside on an Interbase server, switching from the development to the production environment will require much less efforts. Also, switching to a different SQL external data engine is also more straightforward than doing so from a native engine table format.
The Visual dBASE client/server edition comes with the following :
The local Interbase server is a one-user version of the Borland Interbase Workgroup Server. While the latter is scalable and available on several platforms, the local engine which comes with the Visual dBASE client/server edition is Microsoft Windows based.
Some of the features of the local Interbase server are as follows :
The details of Interbase server and database design and management are not covered in this talk.
Client/server development is a more complicated world than the dBASE world by its own nature. However, Visual dBASE can help you upgrade your applications to the advantages of this different world, still without loosing the advantages of the PC development environment.
If you get deeper into comparing with competing products, you will quickly discover that the BDE live cursors approach in handling views on SQL tables is more efficient than its competitors' use of snapshots or keysets. It also requires less coding, as live cursors are handled in a similar way as a local table. They also don't require additional coding to simulate live access on the distant data, while intensive pass-through SQL is often required for similar results on competing products.
Visual dBASE will also leverage your knowledge about object orientation, an even more important trend of the coming times. And don't be shy in front of the guys from big systems. C/S and OOP are no piece of cake for them either. Also, the coming migration to intranet and Web tools is going to keep everybody in the computer world busy for the next couple years.
So, relax, you are in a good position to face the challenge, as Visual dBASE gives you access to all of these worlds. And remember, we hear that some people get bored doing the same job again and again everyday of their life. We, developers, are the lucky ones indeed !...
Some of the examples provided have been obtained from the VISUALDB forum on CompuServe (where TeamB and other volunteers provide useful help to Visual dBASE users). Many useful information on the client/server tools and environment have been provided to me by Jeff Winchell. Thanks to all.
François Ghoche
can be reached at : Internet fgweb
fghoche.com
;
CompuServe 100023,24 ; mail : A.T.O.N. - 33 allée des Genêts
- 78280 Guyancourt (France) ;
voice : +33 (0) 139 440 301 ; fax : +33 (0) 139 440 331 ;
World Wide Web : http://www.fghoche.com.
Page Top / début de cette page
Page d'accueil de ce site / Homepage
Last update : 14 Oct 96