Upsizing to Client/Server with dBASE for Windows
by Sundar Rajan, Borland Developers Conference 1995.
dBASE for Windows is a new generation application development environment that is unique for combining the popular dBASE language with the familiar Windows user interface and access to the entire Windows programming environment.
Not only does dBASE for Windows protect your investment in the dBASE language but it provides powerful tools that make the transition to the event-driven GUI world of Windows as easy as possible. At the fifth annual conference in June 94 (Orlando), Borland spelled out details on an even grander mission - create a smooth 'upsizing' path for all their desktop products.
Borland rates upsizing to be an even more important trend than downsizing and contends that upsizing will occur when PC databases become business critical at the workgroup level. Borland's upsizing strategy for dBASE is based entirely upon the powerful Borland Database Engine whose goal is to provide full access to all types of data, no matter where it resides. This means that you'll be able to create applications that not only use desktop data, but SQL data from ODBC data sources and SQL servers.
Borland's product developers have used IDAPI and BDE as components in dBASE for Windows. Because all data access in the new Borland desktop applications occur via BDE/IDAPI, switching from desktop databases to SQL servers should be transparent.
BDE has an impressive list of features including bi-directional cursors, linked cursors, bookmarks, navigational access to SQL data sources, and support for either SQL or QBE queries against all data sources. While BDE handles dBASE, Paradox and ODBC data sources, SQL Link provides high performance native access to SQL servers. It also supports database aliases that provide a powerful metaphor for pointing to databases.
Key points in Borland's upsizing strategy for dBASE are :
Leverage existing knowledge. The key to Borland's upsizing strategy is to make use of existing development resources to create client/server solutions - in other words, upsizing with what you already know. Developers can use familiar dBASE commands against SQL data without having to learn the SQL paradigm.
Scalable applications. By letting developers reference tables by aliases setup using the IDAPI utility, dBASE makes it easier to develop an application using test data in local tables and later deploy it using a SQL database server. Applications (theoretically) can be ported by merely changing the aliases. Although this is true for simple decision support systems, most OLTP applications require considerable planning and design upfront to be scalable. Nevertheless aliases go a long way towards easy migration.
Concurrent and transparent Access to ODBC, SQL, dBASE and Paradox. dBASE for Windows is built on the Borland Database Engine, which enables dBASE to concurrently connect to and join dBASE IV, Paradox, ODBC data sources as well as Oracle, Sybase and Interbase SQL servers.
SQL Link 2.0 is required for connecting to Interbase, Informix, Oracle, SYBASE/Microsoft SQL Servers and provides high-performance, native access to data residing on these servers. It provides key advantages to SQL database users such as bi-directional navigation, data ordering by index, "book mark" reusability, and dynamic manipulation of SQL data via "live" data source access.
You can use ODBC to connect everything else (DB2, AS/400, Btrieve etc). Users access ODBC by using the BDE and third-party ODBC drivers such as the INTERSOLV ODBC Pack or the ones that come with MS OFFICE, MS Excel and others.
When dBASE for Windows is connected to SQL tables, the BDE automatically converts dBASE commands into the appropriate SQL "dialect" for each supported database. You don't need to grapple with the peculiarities of Oracle's and Sybase's SQL implementations, for example. Interactive users can combine local and SQL data in queries, forms and reports without having to enter SQL commands.
SQL Passthrough. If however you would like to make your database requests in SQL instead of dBASE, SQLExec can help you out. Using SQLExec, SQL statements (including stored procedures) can be directly executed and results captured in dBASE or Paradox tables. Examples of using simple SQL as well as stored procedures will be shown later. While INTERBASE, SYBASE, ORACLE support stored procedures, note that not all databases support this.
FIGURE SQL1 shows a simple program that counts the number of books published by each publisher in the biblio database using a SQL Select statement. The results of the SQL query are returned to a dBASE table results.dbf which is then opened in browse mode for display.
Let us look at some practical examples of this 'upsizing' architecture. First, we'll create an ODBC data source for an MS Access database and create a dBASE form to maintain that data.
ODBC drivers. ODBC drivers to a variety of databases can be purchased from sources such as INTERSOLV or MICROSOFT. INTERSOLV's DATA DIRECT Pack has more than 35 ODBC drivers. However, if you have Excel, Word, Office or Visual C++ installed on your machine, you already have the drivers for Access, Oracle and SQL Server.
Using an ODBC data source in dBASE for Windows involves a three step process:
The first step is to create a Data Source by using the ODBC Administrator (ODBCADM.EXE) from the Windows Control Panel. (If ODBC installed on your system, then ODBCADM.EXE should be in the Windows/system directory).
If you have the MS Access ODBC driver, RED110.DLL, that is supplied with MS ODBC Desktop Database Drivers kit or that comes with MS OFFICE, MS EXCEL, you can create an ODBC data source for the MS Access sample database BIBLIO.MDB included with the code disk :
To create the Bibliography ODBC data source, follow these steps :
- Launch the ODBC Admin application from the Windows Control Panel or by executing ODBCADMN.EXE.
- Click the add button of the data sources dialog box to display the Add Data source dialog box.
- Double click Access Data (*.MDB) in the installed drivers list box to display the ODBC MS Access Setup dialog box. Click the options button to display the additional choices that are available.
- Enter Biblio in the Data Source Name text box and a description of the Biblio.MDB in the Description text box; then click the Select Database button to open the select Database dialog box.
- Double-click BIBLIO.MDB in your working directory (or navigate to the directory into which it has been copied. BIBLIO.MDB is included with the code disk) to choose biblio.mdb as the data source and close the select database dialog box.
- Confirm that the NONE option is selected unless you have MS Access 1.x installed on your PC. (system.MDA).
- Click the OK button to close the ODBC MS Access setup dialog box, and the click the close button of the Data source dialog box to exit the ODBC Administration application.
Next, create a new IDAPI driver (See above figure IDAPI) To do this - launch the IDAPI Configuration utility, select the new driver option from the drivers tab. Enter the name of the driver, for example BIBLIO (IDAPI prefixes it with ODBC_). From the list boxes, Select Access driver (Access Data (*.MDB) ) to be the Default_ODBC_Driver; Specify Biblio to be the ODBC data source name from the list of data sources for the Access driver.
Before you can use the ODBC data source, you need to define an Alias for it. To do this, select the add alias from the Aliases tab (IDAPI Config utility). Enter an Alias Name (e.g. BIBLIO_DB) Select the Alias Type - specify the driver name for your ODBC data source from step II. Once you have setup an alias, you can perform normal desktop functions such as viewing tables in browse mode by selecting from the databases option in the open table dialog box. To create a query using the ACCESS Biblio database, select New Query, click on the database option - you are then prompted for a userid/password. Press ENTER and select Authors from the list of tables. Select titles and join them by clicking on the query join icon.
· Creating a form for a 'remote' or SQL database involves the same steps as for dBASE tables. Create a query joining the tables you want and then use the QBE file to create a form. You can navigate through the tables using PGDN/PGUP keys as you would do with dBASE tables.
· In addition to QBEs and native dBASE commands, dBASE for Windows lets you interact with a back-end database directly using SQL statements via the SQLExec pass-through function.
A simple SQL Query example
Finds out the number of books published by each publisher
opens the MS Access sample database BIBLIO.MDB (ODBC):
OPEN DATABASE Biblio_Db SET DATABASE TO biblio_db errorCode = SQLEXEC("SELECT Name, count(*) from publishers,titles ; WHERE publishers.pubid = titles.pubid ; GROUP BY Name", "RESULTS.DBF") IF errorcode = 0 SET DATABASE TO USE Results LIST ENDIF Close database set database TO RETURN
Apart from ODBC data sources, dBASE can also access SYBASE and other SQL servers directly using the BORLAND SQL Link. If you have SQL Link 2.00 (or later) and the SYBASE drivers installed, you can get connected by creating an alias for the SYBASE database. (Note : Although SYBASE/MS SQL Server tables can be accessed by ODBC, SQL Link provides native connectivity and is faster).
executes the stored procedure sp_who on a remote SQL Server:
OPEN DATABASE SYBASE_PUBS SET DATABASE TO SYBASE_PUBS errorCode = SQLEXEC("exec sp_who ", "RESULTS.DBF") IF errorCode = 0 SET DATABASE TO USE Results LIST ENDIF Close database set database TO RETURN
Figure DBSPWHO shows an example of executing the SYBASE stored procedure sp_who to display the list of current users and processes.
Overview of Client/Server And Some Practical Tips
"Client/Server (like any technology) is not guaranteed to make life better for anyone, just as owning a $200 pair of hiking shoes doesn't mean that you can ascend Mount Rainier." Casey Kiernan, Program Manager, Microsoft SQL Server
Needless to say, any front-end tool can only go so far in the application building process. Building successful client/server applications requires complex multi-disciplinary skills - knowledge of the client operating system and front-end tools, a firm grounding in client/server architecture, SQL literacy, middle-ware know-how and back-end server skills.
Model of the Client/Server Application Architecture
|Front end||Desktop application, graphical user interface|
|Back end||Database, relational SQL|
Most client/server configurations today use a two-tiered model, consisting of a client that invokes services from a server. While the Client Application interacts with and presents information to end-users using a GUI, the database servers perform high speed data manipulation, protect data integrity and enforce business rules. To go with the new skills, there are a few general tips to keep in mind while creating client/server applications :
Divide and Rule - Use the Server to enforce data integrity and business rules
One of the major benefits of Client/Server Architecture is that it provides a more efficient division of labor between client Applications and database Servers. Try to make use of this architecture - Split your application into presentation (UI) and business logic components and off load business logic to the back-end server where possible.
You can reduce the load on both the client and the network by moving the business logic to the server, leaving just the presentation services and logic on the client to minimize the impact on the client. The mechanism most commonly used for putting business logic on the servers is stored procedures, which are collections of procedural code that typically include SQL for accessing the database.
Since the database could be accessed from outside the application you are building ( a query tool such MS-QUERY may be updating your tables), it makes sense to implement the business rules in the back-end server rather than the front-end application.
SQL servers such as INTERBASE and SYBASE provide a number of tools to implement Data Integrity and Business Rules. For example, if an employee's bonus must always be less than a certain percentage of his or her salary, the following check constraint would be appropriate:
check (bonus < salary * .10))
Rules can be bound to database columns to implement validity checks and business constraints. Here is a SYBASE example:
create rule state_rule as @state in ("IL", "WI", "IN", "IA") exec sp_bindrule "state_rule", "authors.state"
The first statement creates the rule - valid states are IL, WI, IN, IA; the second binds it to a specific column (state) in a specific table (authors). You can bind the same rule to as many different columns in as many different tables as you desire, making this a reusable, database-wide business rule.
Likewise, a combination of user-defined data types, rules, and defaults can be used to enforce validity and 'picture' clauses. Take an example where the product code in your database needs to conform to the following standards :
- product codes are six bytes long;
- a product code must begin with an alphabet;
- the second byte must be numeric, the next three bytes can be anything, and the last byte must be either "@" or "#"; and
- defaults to null
You can implement the above business rule using a user defined datatype, a rule and a default in SYBASE as follows:
Create a user-defined data type, such as prodcode:
sp_addtype prodcode, "char(6)", "null"
Create a rule, such as prodcode_rule:
create rule prodcode_rule as @prodcode like "[A-Z][1-9]___[#,@]"
Create a default, such as prodcode_deflt:
create default prodcode_dflt as NULL
INTERBASE 4.0 Default Example
Triggers in INTERBASE 4.0 also come in handy for setting defaults. In the following example the date field SALEDATE is automatically set to the current date:
create trigger default_sale_date for myorders after insert as begin update myorders set SALEDATE = "NOW" where ORDER_NO = NEW.ORDER_NO; end
Business rules, such as - 'All Customers should have a unique ID' can be implemented using INTERBASE 4.0's Auto-generate feature:
CREATE TRIGGER SET_CUST_NO FOR CUSTOMER ACTIVE BEFORE INSERT POSITION 0 AS BEGIN new.cust_no = gen_id(cust_no_gen, 1); END
SYBASE Referential integrity example
Many other Business Rules translate directly to referential integrity constraints; Consider the following Sybase trigger, which verifies that new orders are assigned to existing customers:
create trigger trig_insert on orders for insert as if (select count(*) from customer, inserted where customer.CustomerNo = inserted.CustomerNo) = 0 begin raiserror 21111 "Customer number not found; insert canceled." rollback transaction end
If you try to post an order record that contains an invalid customer number, this trigger raises an error and rolls the transaction back.
Minimize network traffic between the client and server
Follow the general rule for client/server front-ends of dealing with fewer records at a time. Any time a set of records has to be fetched from the server, you will incur the transmission time for the records from the server to the front-end. Large number of records fetched from the server increase network traffic. Watch out for this especially in QBEs. (If you are using SYBASE/MS SQL Server, you can make effective use of temporary tables (#tables) to hold intermediate results on a pass-through SQL query or write stored procedures. Stored procedures offer significant benefits over regular SQL statements - improved performance and reduced network traffic. In addition, they are both shared and cached.)
If accessing dBASE files on your file server is akin to making local phone calls, doing SQL queries against SQL servers is a bit like making long-distance phone calls. As with any long-distance phone call, you will have to plan your conversations so as to minimize the amount of time (thereby the cost) you spend on the network.
Use stored procedures to fine tune performance
Because they are compiled, the judicial use of stored procedures can improve performance and reduce server load (compared to direct QBE's or uncompiled embedded SQL sent from the client). Another aspect of performance is network load. Instead of sending SQL statements from the client to the database server and returning intermediate results to the client, all the processing and decisions can happen on the server with a simple call to the stored procedure. For example, you could use the following stored procedure to update a Part table so that the average price of all the parts would increase an amount specified in the parameter @avg :
CREATE PROCEDURE price_increase @avg as while (select avg(price) from part) <= @avg update part set price = 1.1 * price
Stored Procedures can be used for implementing simple tasks such as the one above or complex business procedures that require several checks and validations. The INTERBASE 4.0 stored procedure SHIP_ORDER implements a order shipping procedure with the following set of business rules :
- Order should not have been shipped already;
- Customer status cannot be 'hold'; and
- Customer should be credit-worthy (should have paid-off balances that are two-months old)
/* Setup error messages */ CREATE EXCEPTION ORDER_ALREADY_SHIPPED "Order status is 'shipped.'" CREATE EXCEPTION CUSTOMER_ON_HOLD "This customer is on hold." CREATE EXCEPTION CUSTOMER_CHECK "Overdue balance -- can't ship." CREATE PROCEDURE SHIP_ORDER (PO_NUM CHAR(8)) AS DECLARE VARIABLE ord_stat CHAR(7); DECLARE VARIABLE hold_stat CHAR(1); DECLARE VARIABLE cust_no INTEGER; DECLARE VARIABLE any_po CHAR(8); BEGIN SELECT S.ORDER_STATUS, C.ON_HOLD, C.CUST_NO FROM SALES S, CUSTOMER C WHERE PO_NUMBER = :po_num AND S.CUST_NO = C.CUST_NO INTO :ord_stat, :hold_stat, :cust_no; /* This purchase order has been already shipped. */ IF (ord_stat = "shipped") THEN BEGIN EXCEPTION ORDER_ALREADY_SHIPPED; SUSPEND; END /* Customer is on hold. */ ELSE IF (hold_stat = "*") THEN BEGIN EXCEPTION CUSTOMER_ON_HOLD; SUSPEND; END /* * If there is an unpaid balance on orders shipped over 2 months ago, * put the customer on hold. */ FOR SELECT po_number FROM sales WHERE CUST_NO = :cust_no AND ORDER_STATUS = "shipped" AND paid = "n" AND SHIP_DATE < 'NOW' - 60 INTO :any_po DO BEGIN EXCEPTION CUSTOMER_CHECK; UPDATE CUSTOMER SET on_hold = "*" WHERE CUST_NO = :cust_no; SUSPEND; END /* * Ship the order. */ UPDATE SALES SET ORDER_STATUS = "shipped", SHIP_DATE = 'NOW' WHERE PO_NUMBER = :po_num; SUSPEND; END
Stored procedures improve database and application integrity. Because they are shared, they ensure the consistency of operations and calculations.
dBASE for Windows provides an attractive framework for making the transition to client/server. While the opportunities are immense, several new skills have to be acquired especially in the areas of application design, SQL server know-how and SQL. There are exciting times ahead for dBASE for Windows developers willing to take on the client/server challenge.