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 :

1. Launch the ODBC Admin application from the Windows Control Panel or by executing ODBCADMN.EXE.
2. Click the add button of the data sources dialog box to display the Add Data source dialog box.
3. 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.
4. 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.
5. 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.
6. Confirm that the NONE option is selected unless you have MS Access 1.x installed on your PC. (system.MDA).
7. 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

  clear

  close database

• 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:

  clear

  close database

  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.

check (bonus < salary * .10))

create rule state_rule as @state in ("IL", "WI", "IN", "IA")
  exec sp_bindrule "state_rule", "authors.state"

create trigger default_sale_date for myorders
  after insert as
begin
  update myorders
    set SALEDATE = "NOW"
    where ORDER_NO = NEW.ORDER_NO;
end

CREATE TRIGGER SET_CUST_NO FOR CUSTOMER
  ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  new.cust_no = gen_id(cust_no_gen, 1);
END

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

CREATE PROCEDURE price_increase @avg as
while (select avg(price) from part) <= @avg
update part
set price = 1.1 * price

/* 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