Mark Pruett is a programmer/analyst for Virginia Power. He received a degree in mathematics/computer science from Virginia Commonwealth University in 1985. His interests include language design, interactive graphics and user-interface design. He can be reached at 5000 Dominion Blvd., Glen Allen, VA 23060; or at 804-273-2498.
Recently, I was involved in developing a networked application at my company. The application had to be accessible from both the technical center and our two nuclear power stations, both of which were dozens of miles away. The company has several installed Novell local-area networks. A new one was to be installed at the technical center for this application.
Ethernet lines were already in place to connect us to one of the power stations. Similar connections were planned for the other station. The problem was finding the tools that would allow me to develop a database application with sufficient response time to function across the wide-area network.
The database application design required more than two dozen relational data tables containing information on nuclear fuel, its current and previous locations, and rules and restrictions on its movement. The application would allow the planning of nuclear-fuel move sequences and would allow the simulated performance of these move sequences to check for possible problems. It was a relatively nasty problem. I welcomed any tool that would assist me in programming the system.
We discussed several approaches to the problem, including database languages such as Clipper. Although this language is well-suited for a specific class of problems, I was concerned that it would be too confining for this application.
I decided to write the application in C. Because I was working in a network environment, I was starting with some memory penalties on workstations. Novell requires two memory-resident applications, IPX.COM and NETx.COM for the workstation to communicate with a network file server. Wide-area network performance was another concern. I was afraid that a conventional database would be too slow when communicating with the power stations.
We decided to use a Novell product, Netware SQL, and its application program interface, Netware XQL, to develop the application. Netware SQL is a client-server SQL database engine. The client-server aspect might improve our wide area network performance, and SQL might help handle the complexity of the data.
But I was still concerned. Netware SQL ran on top of Novell's Btrieve low-level database management software. I worried that with so many layers, performance would suffer. What if it was a performance dog?
Throughout the project I learned a bit about wide-area networks, client-server applications, high-level database libraries, and Novell networks.
Client-Server Architecture
In a conventional LAN-based application, the LAN file server is merely a disk drive. The workstation application must retrieve any data it needs directly from the file server. An application with multiple active agents differs from a stand-alone PC application in an important way. It needs file or record locking to assure concurrency.In a client-server LAN application, some portion of the program logic is shifted from the workstation to the file server. In the case of a client-server database application, the workstation, or client, requests information from the file server. This request is received by a database server program running on the file server. The server application interprets the client's request and gathers just the information that the client needs from the server's data files.
This client-server model can greatly reduce network traffic because the only data traveling through the network lines are the client's request and the server's reply. In a conventional application, the workstation must sift through a lot of data to decide what to accept and what to reject. All that data is traveling across the network lines. On a wide-area network running at relatively low line speeds, this can make a conventional application unworkably slow.
Structured Query Language
Structured Query Language, or SQL, has fast become arguably the most popular database query language. It is available in some form on every major platform. SQL is almost always implemented through some host language that serves as vehicle for embedded SQL calls. One of the major differences between various SQL servers is the method used to interface between SQL and the host language. Later, I'll show some examples using Novell's Netware SQL, but first I'll cover some of the basics of SQL syntax.An SQL database consists of a group of data tables. Each of these tables contains records, or rows, and fields, or columns. Figure 1 shows two tables, Employees and Departments. These two tables are related by a common field, Dept. Code. Neither of these tables alone gives you all the information you may need, but SQL allows you to join tables to form views. A view is a logical representation of the data stored in one or more tables. Similar to a table, it consists of rows and columns. If you needed to know just the department and floor number of each employee, the following SQL query is all you need.
SELECT a.Last^Name, a.First^Name, b.Dept^Name, b.Floor FROM Employees a, Departments b WHERE a.Dept^Code = b.Dept^Code (+)In SQL, a SELECT statement retrieves data. After the SELECT keyword is a list of field names selected from those available in the tables listed after the FROM clause. The WHERE clause specifies the restrictions we require for the view. In this case we want a view containing only those records in which the two tables' department codes are equal. The plus sign within parentheses indicates that this is an outer, or null, join. In an outer join, one record is returned for every record in the primary file Employees, even if there is no corresponding record in the secondary file.Figure 2 shows the view that would result from this query. In our example, a record is created for Martha Harris, even though there is no matching record in the Departments table. In her record, the fields Dept^Name and Floor would contain null values.
Using SQL, a programmer can maintain a more abstract view of the database, without worrying about its physical representation. The more complex the database the more useful this abstraction becomes. The physical data may reside on a local server, a server 60 miles away, the workstation hard disk, or some combination of all three. The client application (and thus the programmer) does not need to know where the data is, because the database server handles it.
Data Dictionaries
The database is managed by the database-server application through a data dictionary. This dictionary contains information about the database: table names, the fields and data types contained therein, indexes, the physical location of data files, and other information needed to maintain the database.The data dictionary allows the database server to "know" the structure of the database. The application can request a field from the database but does not need to tell the database server what data type or length the data field is.
Netware Products
Netware SQL is Novell's database-server application. It is available as both a Netware Loadable Module (NLM) for the Netware 386 operating system and a Value Added Process (VAP) for Netware 286. Developers use the Netware XQL Application Program Interface to communicate with the database server.Netware XQL provides two methods for accessing these services. The first, XQL Primitives, is a set of more than 40 low-level non-SQL function calls. The other method, XQL Manager (XQLM), is a set of about 16 function calls that support SQL access. The manager functions establish an SQL session, request SQL cursors, send SQL queries to the server and retrieve the resulting data back to the workstation, and perform other auxiliary functions. An SQL cursor is a pointer into a particular SQL table. By establishing multiple cursors, an application can use data from multiple views simultaneously.
A typical Netware SQL application must perform the following steps:
Step 1. Establishing the Session
Using the XQLLogin function, the application makes itself known to the database server. At this time it can also specify the physical location of the data dictionary and database files used by the application. If the data dictionary has security enabled, then XQLLogin also establishes the user's access rights.
Step 2. Requesting a Cursor
Before any data can be retrieved, at least one cursor must be allocated for later use. This is done with XQLCursor, which assigns a value to an integer CursorID variable that is passed to it.
Step 3. Creating and Sending an SQL Query
The function XQLCompile passes a text string containing an SQL Query to the database server for processing. In an earlier example with the Employees and Departments tables, the code to retrieve John Smith's phone number is shown in Listing 1.This step only requests that the server find this information in the database, it does not actually retrieve it.
Step 4. Retrieving Data
Once an SQL SELECT query has been successfully executed, one or more records will be awaiting retrieval. XQLFetch provides several formats for retrieving data. The programmer provides a pointer to a data area. XQLFetch places the requested data in the provided buffer. Two of the available data formats return the data in displayable form, with fields separated by spaces or binary zeroes. In these formats, numeric values, times, and dates are all converted to displayable ASCII characters. The remaining format is especially useful to C programmers. This option returns data with the fields packed one after another, with no data conversion performed. Since the formats for integer, long integer, float, and double are the same in the Netware SQL database as they are for the supported C compilers, the buffer area can be a C struct. No data conversion need to be performed. In the previous example, the following data structure will suffice:
struct { int length; char phone[9]; int dept_code; } employee;(XQLFetch requires an integer length field to precede each record buffer. The function returns the actual length of the retrieved record in this field.) This format is useful for applications that require numerics in their proper form.XQLFetch also provides options for moving through the multiple rows of data that may result from a query. Once a view has been established, XQLFetch allows you to move backward, forward, to the top, or to the bottom of the view.
An SQL Utility Program
Listing 2 is a general-purpose SQL interpreter. It requires the Netware XQL API to run. Listing 3 contains the function prototypes required by the SQL API. The program accepts an SQL query from standard input. If the program is able to establish an SQL session and obtain a cursor, it uses XQLCompile to pass the query to the SQL server.A query might be a request for data to be retrieved, but it might also be a request to perform some other SQL command, such as inserting, updating, and deleting records. If this is the case, and informatory message such as "1 record(s) UPDATED" is sent to standard output.
If a SELECT statement is processed, the utility uses the XQLM function XQLDescribe to retrieve information about the structure of the data being requested: field names, data types, storage sizes, and decimal precisions. This information is sent to standard output. After this, the program uses XQLFetch to actually display the requested data to standard output.
This program illustrates one of the interesting qualities of a program that uses Netware SQL — it can be completely oblivious to the actual structure of the data. INTRPRET.C has no prior specific information about any data it processes. This can be useful for general-purpose report generators.
This utility also illustrates Netware SQL's difficulty with error handling. Each XQLM call returns an integer status code. The XQL manuals contain an appendix explaining each error message, but the section of the manual explaining the XQLM function calls does not indicate what the likely error messages might be for a particular function. Because there are more than 300 separate status values that can be returned, error checking and error recovery are somewhat inexact.
Problems
Novell SQL is not without some problems. The product has some annoying bugs that can cause not just your workstation but your network file server to crash. For example, Netware SQL supports data masks. These masks specify how a data field will be formatted. A mask can be specified in an SQL SELECT statement or through the XQLFormat API call. In either case, a mask string more than 30 characters long will cause the file server to lock up. Problems such as these (and there are others) can generally be worked around, but they are nonetheless annoying, especially if you don't have a server isolated for software development.The bug mentioned above points out another weakness of the Novell product — poor documentation. Nowhere in the description of the XQLFormat does the documentation mention an upper limit on mask strings. This information is also missing from the description of masks in the appendix. The description of a related function, XQLMask, mentions the limit, but it doesn't mention the rather grim repercussions of exceeding the limit.
Considerations
Any workstation memory savings that might be attained by offloading some of your program logic to a database server probably will be offset by the memory requirements of your network shell and SQL requestor programs. In addition to IPX.COM and NETx.COM, there is NSREQ.COM, the program that allows your application to communicate with the database server. You can minimize the impact by using XMSNETx.COM instead of NETx.COM, thus using less base memory by using high memory. NSREQS.COM saves some memory by forcing the database server to perform services that are more efficiently performed locally. Even so, you can expect a minimum overhead of 54K.We improved the WANs by creating a client-server application. Nonetheless, the WAN was still not always as fast as I'd have liked. Even a client-server application will be slow if the amount of data being requested is large. Client-server applications excel at handling complex queries that retrieve modest amounts of data.
The idea of grafting SQL onto a C program might seem a bit counter-intuitive at first. It did to me. After completing the project, I realize that it makes perfect sense. C excels as a general-purpose application-development language but has almost no intrinsic facility for the type of data abstraction that SQL allows. On the other hand, SQL is of little use without some host language. In practice, their strengths complement each other. I cannot imagine writing code in C to allow the types of complex data table joining that SQL does so simply.