Visual Basic Expert SolutionsChapter 7The Jet Engine and ODBCBy James A. Dooley |
The Microsoft Jet Database Engine (or Jet for short) provides Visual Basic with all its database access capabilities. Jet is responsible for all aspects of database management. It also provides the DAO (Data Access Objects), an object-based model used to operate upon the database and its contents.
A good analogy is that you don't have to be a motor mechanic to drive a car. Once you learn to drive, you can tour the countryside to your heart's content. But if your car breaks down in some out-of-the-way place, your knowledge of motor mechanics can help you get back on the road. Like a car, Visual Basic implements many powerful features that allow you to develop professional applications quickly. And like motor mechanics, your knowledge of the Jet Database Engine can help you optimize the performance of your application and troubleshoot problems when they arise.
Jet is an advanced database engine, offering many features that are found only on mid-range and mainframe database systems. This chapter concentrates on those aspects of Jet that enable you to write professional applications using Visual Basic. This chapter will provide you with the following:
Jet Database Engine is the database management system (DBMS) included with the Visual Basic programming environment. Jet has no user interface of its own. Instead, users and developers interface with Jet via an application. Figure 7.1 shows how a Visual Basic application interacts with Jet.
Fig. 7.1 The Jet engine applications interface.
One of the difficulties in understanding how Jet works is that there is currently little documentation available on the versions of Jet that are delivered with Visual Basic 4. However, we can learn a lot from white papers released by Micorsoft on earlier versions of the engine and by drawing conclusions from our observations of tests we carry out in this chapter. To help us better understand Jet, we're going to break it down into a number of components, naming them as indicated in figure 7.2.
Fig. 7.2 The Jet Database Engine components.
Data Access Objects (DAOs) provide the interface to Jet functionality from Visual Basic and MS Access. With the release of Visual Basic 4, Microsoft has made considerable changes to the DAO object model. The new DAO available to developers are shown in light gray in figure 7.3. With the introduction of the new model, Microsoft dropped some components of the model presented in Visual Basic 3 (although these components are still supported for backwards compatibility). In figure 7.3, the dropped components are shown in dark gray.
Fig. 7.3 The Data Access Object model.
The Query Engine is responsible for processing SQL queries submitted to it via the DAO. While Microsoft has not documented the internal workings of the Query Engine, we can make the following educated assumptions about how it handles queries.
The Query Engine examines our query to ensure that it complies with the version of SQL supported by Jet.
When you store a query in the database, the Query Engine determines the execution plan for the query and stores it together with the query. The Engine uses the database statistics available to it at the time—such as number of records and indexes on the table—to determine how to execute the query.
When you execute the query subsequently it uses this plan to process the query. This has both a positive and negative side. On the positive side, Jet can execute your query faster because it does not have to go through the procedure outlined above to decide on an execution plan. On the negative side, any changes to the database structure—such as adding an index—will not be taken into account. For this reason, have the Query Engine recompile your query after you make significant changes to the database or when the size of the table has grown significantly. To have the Jet Engine recompile your query, you need to adopt a two-phase approach: first delete the query, then re-create it. This causes Jet to generate a new execution plan for the query.
The Jet Database Control System is the subsystem that manages databases stored in the Jet format. In particular it is responsible for data integrity, security, and basic file I/O.
Record locking for Jet format databases is implemented via a page locking concept, in this case pages of 2K. A page is the technical term for a block of memory or disk space. When Jet needs to lock a record, it locks the entire page containing the record. This can have a major side effect on tables with a short record length, because all records in the locked page are unavailable to other users.
The External ISAM Interface is the subsystem that interfaces with the External ISAM Drivers (DLLs) that Jet uses to access data stored in formats such as Xbase, Paradox and Btrieve.
One primary use of the External ISAM Interface is to enable its applications using Jet to co-exist with other database applications. The interface allows Jet to access data stored in the following formats:
Transparent access to data is a key concept in the design of Jet. While Jet makes great efforts to ensure that external tables appear as native Jet tables, it falls short on a number of occasions.
Because of these restrictions, most users convert existing databases to the Jet format wherever possible.
Regardless of the external database type, the steps involved in attaching the table are the same:
As previously indicated, we can attach a table from may external sources, the following example shows how to attach an Excel 5.0 worksheet. For the purpose of this example we're going to attach the Excel worksheet shown in figure 7.4. This worksheet contains the product table for the Northwind traders database.
Fig. 7.4 The Northwind product table.
Before we can attach a worksheet, we need to name the range containing the data we want to make available to Jet. If the range has not already been named, then follow these steps in MS Excel:
1. 1. Select the data range.
2. 2. From the Insert menu, choose Name, Define.
3. 3. After choosing Define, the Define Name dialog box appears
(as shown in fig. 7.5).
Fig. 7.5 The Define Name dialog box.
1. 4. Type ProdList in the text box and click OK to define the data range name.
Once you have named the data range in the Excel Worksheet, you're ready to attach it to a Jet database. The following code in Listing 7.1 is used to attach the product table:
Listing 7.1 The Attach Table Source Code
The ODBC Interface is the subsystem that interfaces with the ODBC Manager to establish connections with database servers, such as SQL Server and Oracle. When Jet communicates with a database server via ODBC, it needs to make several ODBC API calls. This subsystem manages these calls.
(c)ODBC Database Connections
Jet communicates with client/server database via database connections. These are established on behalf of Jet by the ODBC Driver Manager. The engine not only shields the Visual Basic developer from the complexities of the ODBC Application Programming Interface (API), but also provides sophisticated features for managing these connections.
During the development cycle, it is unlikely that more than a few users will be connected to the database at any one time. As a result, problems associated with database connections may not arise until intensive user testing begins. At this stage the developer may be faced with the prospect of having to redesign any code changes necessary to overcome these problems. Having to make such changes so late in a project can be a costly experience. Much better then, to factor in the management of connections early in the design and avoid the problems. This section deals with three aspects of connection management featured in the development process: connection management on the server and client and optimizing connection usage.
When first connected to an ODBC data source, Jet looks for a table called MSysConf. Apart from this special significance to ODBC, this table is like any other in the database. It is normally created by the Database Administrator to assist in managing the connections to the database. If you have Table Create and Update privileges on the database you are accessing, you can create and maintain this table. The structure of MSysConf is set out in Table 7.1.
Table 7.1
Table 7.1 Structure of the MSysConf Table
Column Name | Datatype | Description |
Config | SNALLINT | The number of the configuration option. |
chValue | VARCHAR(255) | The text value of the configuration option. |
nValue | INTEGER | The integer value of the configuration option. |
Comment | VARCHAR(255) | A description of the configuration option. |
If Jet finds this table, it examines the entries to determine how to manage the connection. The current version of Jet examines the table with a View to determine the following:
• Whether it should allow the user to store his/her user id and password when attaching tables to a Jet database
• The strategy it should adopt to optimize the usage of database connections when retrieving data from the database server
While Jet's ability to store the user's id and password with an attachment enables the development of transparent data access applications, it also introduces a security risk. If you attach a table from a server to a Jet database that is not itself password protected, for example, then anyone who opens the Jet database gets access to your server table without being authenticated. For this reason it is recommended that you turn off this feature in databases containing sensitive data by making the appropriate entry in the MSysConf table. Table 7.2 lists all the possible entries for MSysConf.
Table 7.2
Table 7.2 Valid Entries for the MSysConf Table
Config | nValue | Explanation |
101 | 0 | Do not let the user save his id and password in attachments. |
101 | 1 | Allow the user to have his id and password in attachments (default). |
102 | D | Jet delays D seconds between each background chunk fetch (default = 10). |
103 | N | Jet fetches N rows on each background chunk fetch (default = 100). |
When Jet is asked to return a result set as a Dynaset, it does not retrieve all records in the set before returning control to the requesting program. Instead it retrieves a chunk of these records, returns control to the calling program, and retrieves the remaining records as a background task. Entries 102 and 103 control how Jet carries out this task. There are no recommended settings for these entries. Test your system with various entries to determine the best settings for your network configuration.
Jet issues the following query to access the MSysConf table:
Select Config, nValue from MSysConf
Caution: When setting up security on your database, ensure that the above query can be executed from the user's account. If it can't be executed as typed above, then Jet will be unable to find the table and will ignore your table entries.
Including the MSysConf table in your database design and documenting its entries enables your Database Administrators to actively manage the database connections.
Jet provides an extensive array of options for managing the database connection on the client side. Most significant for our purposes are as follows:
Depending on the version of Jet you are using, when your application first starts, Jet will try to locate an [ODBC] section in your application's INI file or alternatively try to find similar entries in the Windows Registration database. If it finds this section, it uses the entries for the section to determine how it should manage all database connections for your application. Table 7.3 has been adopted from an earlier white paper by Microsoft on Jet and lists all entries for the [ODBC] section.
Table 7.3 Table 7.3 [ODBC]Section Entries
Entry | Value | Effect |
TraceSQLMode | 1 | Trace SQL Jet send to ODBC into file sqlout.txt |
TraceODBCAPI | 1 | Trace ODBC API calls into file odbcapi.txt |
DisableAsync | 0 | Use asynchronous query execution if
possible (default) |
1 | Force synchronous query execution | |
LoginTimeout | S | Cancel login attempts that don't finish in S seconds (default = 20) |
QueryTimeout | S | Cancel queries that don't finish in S seconds (default = 60) |
ConnectionTimeout | S | Close cached connections after S seconds idle time (default 600) |
AsyncRetryInterval | M | Ask server "Is query done?" every M milliseconds (default = 500) |
AttachCaseSensitive | 0 | Attach to first table matching specified names, regardless of case (default) |
SnapshotsOnly | 0 | Allow the creation of dynasets
(default) |
1 | Return all result sets as snapshots | |
AttachableObjects | string | List of server object types to allow attaching to (default = 'TABLE','VIEW','SYSTEM TABLE','ALIAS','SYNONYM') |
TryJetAuth | 1 | Try Jet Userid and password before |
prompting | ||
0 | Don't try Jet Userid and password
before prompting |
|
PreparedInsert | 0 | Use custom INSERT that only inserts
non-NULL values (default) |
1 | Use prepared INSERT that inserts all columns | |
PreparedUpdate | 0 | Use custom UPDATE that only SETs
columns that changed (default) |
1 | Use prepared UPDATE that updates all columns |
In most cases, where you are accessing a database server on a local area network (LAN), the default values are satisfactory. However, if you have to access a database over a wide area network (WAN), via modem, or over a busy LAN, you can use the time-out related settings to overcome poor network performance. Regardless of whether you use the default settings or customized settings, document the settings in your user documentation so that people using your application on a slow network can take advantage of them.
During the development phase, the TraceSQLMode can be very useful in assisting you to optimize the SQL queries being used to access the remote data. For example, if you have two attached tables in your database, both located on an Oracle server, use the following query to access the tables:
You can find out about how Jet process the query by setting TraceSQLMode=1 and issuing the query. After processing we find that the log file contains the following entry:
In this situation we can see that Jet submits the entire query to the Oracle server for processing. If we now move the PUBS Table to SQL Server and execute the query again, we see that the query execution plan has changed:
In this case Jet sends one query to SQL Server to get the required data from the Pubs table and another query to Oracle to get the required data from the Titles table. It then carries out the Join on the workstation. Thus you can use the TraceSQLMode option to help you understand how Jet processes your query.
All database connections are equal, right? Wrong! Some database servers support powerful connections that can handle multiple partially complete queries at the same time, while other servers can only process a single query at a time. Regardless of whether the connection supports multiple or single queries, Jet allows multiple queries and manages the connections automatically. Let's look at an example:
Assume we're building a data browser to allow a user to view tables on an SQL Server database, which is configured to allow for 20 concurrent connections. We decide to build the application using a MDI interface so that the user can view more than one table at a time. We code and test our application and when we're satisfied, we give it to a group of 10 users for testing. Shortly after testing begins, our users complain that they are not able to connect to the server because the number of concurrent connections have been exhausted.
The cause of the problem is that SQL Server supports only a single query per connection. As a result, Jet was opening additional database connections in order to support the multiple queries. The number of connections that Jet opened depended on the status of the current connection. If it contained a partially completed query, then Jet had to open a new connection to run the new query. As a result, the 10 users were exceeding the maximum number of concurrent connections being supported by SQL Server.
If your server does not support multiple partially completed queries on a single database connection, use the following tips to reduce the number of connections you application requires:
When you begin working with remote database servers, you will notice that the first time you access a remote table takes much longer than subsequent accesses. This is because Jet caches and shares the database connections as much as possible. Jet will share two connections where the data source name (DSN) and the DATABASE name are the same. Jet also shares the database connection, in the absence of a DATABASE name, where the DSN is the same. Furthermore, Jet maintains the connections even when they are not explicitly in use.
When you use a database's Close method, Jet does not in fact close the database connection. Instead, Jet maintains a persistent connection in case the user reopens the database later in the application. If the user does not reopen the database, a connection time-out will occur and Jet will close the connection. To force Jet to close a connection try this:
Listing 7.2 Closing a Connection
While you cannot reduce connection time without first improving network performance, you can improve the perceived performance of the application by taking advantage of the fact that Jet caches and shares database connections. The trick is to establish a dummy database connection during application start-up. As part of your startup routine, open the database and close it. Jet will cache the connection and use it later when the user attempts to access the database.
Traditionally, database servers hold corporate-wide data, which supports many applications. Accessing data on such servers is very different from accessing a database located on your PC. In particular, you will find that the databases are under the supervision of a Database Administrator (DBA).
Database Administrators are responsible for granting access to the data stored on the server and creating new databases, tables, and indexes. They also optimize the performance of the server and back up the databases. In addition to this added level of administration, the methods used to provide access to the data are different.
Jet provides the following two methods of accessing data stored on a database server:
***
Note: Microsoft recommends that you use attached tables rather than connecting directly to the ODBC data source because it appears to offer better performance in most cases.
***
Attaching an ODBC data source table to a local Jet database requires the following steps:
The code in Listing 7.3 illustrates these steps:
Listing 7.3 Example of Attaching an ODBC Table
When you attempt to update or add new records to some attached tables, you may receive an error message stating that the Dynaset is read-only. The following are two common reasons for this:
In most case there is not a one-to-one relationship between the data types supported by the ODBC data source and Jet. When attaching a table, Jet calls several ODBC APIs to determine the data type, precision, and scale of each column in the table. It then uses this information to map the ODBC data sources to Jet data types. This mapping, together with the connection information, is stored in the attached table definition. Thereafter, Jet uses this information to communicate with the server to obtain data from the table.
Note: In situations where Jet does not support a particular ODBC data type, it must select an appropriate Jet data type. When this conversion fails, the value is treated as NULL. Zero-length text retrieved from a server is also treated as NULL. As a result, the user cannot always distinguish between a NULL value and a data-conversion failure.
In some environments, access to data is provided via what is known as a View. From a user perspective, a View is very much like a table, with one exception: you cannot normally update a View. In the database, however, a View is actually stored as a predefined SELECT query that is executed every time the user queries the View. Because a View is in fact a query, it can span more than one table, restrict the records returned via a WHERE clause, and so on.
For example, suppose you wanted to allow the sales force to view the sales data for the state in which they operate, but not for any other state. One way to do this would be to create a table containing only the sales data for each state and grant the salesperson access to the table for their state. While this solution would work for the sales force, it will cause problem later when others want to view the data. Suppose you now want to let the Marketing Manager view the sales data for the Eastern Region. To do this, you'd have to grant him access to the table for each state in the Eastern region and then he would have to create queries to join the tables before he could see the data at region level. The best way to handle such situations is to store all the data in one table and then create different views over it, to meet each groups needs. For example, to let the sales force for CA see their data, you create a CA view using the following SQL:
The code required to attach a View is exactly the same as that used to attach a table. Jet treats a View as if it were a table without indexes. In other words, when you open it, it will be opened as read-only.
While Views normally are treated as read-only, you can have Jet open them as updatable if your server allows you to update Views. Because Jet requires a unique index to update records, you need to tell Jet which field or fields can be used to uniquely identify the records returned by the View. To do this, issue a data definition query (not a pass-through query) to define a unique index on the View. This will not create an index on the server, nor will it create an index in the local Jet database. It simply tells Jet which fields it can use to uniquely identify the records when communicating with the server.
Understanding how Jet processes your ODBC related queries is a prerequisite for optimization. Users who are familiar with Oracle or SQL Server know that these systems provide facilities to enable them to ascertain how the system intends to process the query submitted. Unfortunately, Jet does not provide such facilities, at least not directly. To understand more about how Jet processes an ODBC based query, you can use the TraceSQLMode=1 option to cause Jet to log the SQL commands it sends to the server. This output is logged to a file called SQLOUT.TXT. Using this log, we can draw some conclusions about how Jet is handling our query.
The key to understanding this output is to understand the commands on the left-hand side. An explanation of these commands is provided in Microsoft's white paper and has been reproduced in Table 7.4.
Table 7.4
Table 7.4 Explanation of the SQL Trace Commands
Command | Explanation |
SQLExecDirect : <SQL-String> | Execute non-parameterized user query |
SQLPrepare : <SQL-String> | Prepare parameterized query |
SQLExecute : (PARAMETERIZED QUERY) | Execute prepared, parameterized user query |
SQLExecute : (GOTO BOOKMARK) | Fetch single row based on bookmark |
SQLExecute : (MULTI-ROW FETCH) | Fetch 10 rows based on 10 bookmarks |
SQLExecute : (MEMO FETCH) | Fetch Memos for single row based on bookmark |
SQLExecute : (GRAPHIC FETCH) | Fetch OLE Objects for a single row based on bookmark |
SQLExecute : (ROW-FIXUP SEEK) | Fetch single row based on some index key (not necessarily bookmark index) |
SQLExecute : (UPDATE) | Update single row based on bookmark |
SQLExecute : (DELETE) | Delete single row based on bookmark |
SQLExecute : (INSERT) | Insert single row (dynaset mode) |
This section examines how Jet processes a number of common types of query. To carry out this investigation, assume you have set up two databases on an SQL server and a local database on the workstation. The structure of these databases are the same as the Northwind database shipped with Visual Basic. Figure 7.6 illustrates the test environment.
Fig. 7.6 The Query Processing environment.
Although Jet will parse all queries, we'd expect that if we issue simple select queries on a remote table, Jet is smart enough to let the server do the work. Let's see what happens when we issue the following query:
As you can see, our single query has produced a lot of activity. Because the result set is a Dynaset, the first thing seen is that Jet has issued a single query to retrieve the key information about the customer records. It then prepares a parameterized query, which it uses to return the records in sets of 10.
As with our ORDER BY query, we'd expect to find that Jet also passes our GROUP BY query to the server.
Examining the trace file, we find that there was not as much activity as before because this result will be returned as a Snapshot:
How does Jet handle Access SQL supported features when dealing with a remote database server? Let's begin by issuing the TOP N query in Listing 7.5.
Listing 7.5 Top 10 Percent Query
From the trace file we see that because the server does not implement a TOP N capability, Jet must handle it, itself. To do this, Jet has sent a standard GROUP BY/ORDER BY query to the server (see Listing 7.6).
Listing 7.6 SQL Trace Output
So although Jet returned only 10% of the records to the application, we discover that it in fact retrieved all records from the server. In our case this was not really a problem, because the table was small. If the table had contained several million records, however, Jet would have had major problems.
One interesting thing about Jet queries is that they can be based on another query in the database. To see how Jet handles such queries, execute the query in Listing 7.7, which is based on the previous GROUP BY and ORDER BY queries.
Listing 7.7 SQL Query
The conclusion to be drawn from these last queries is that in cases where a particular feature of Access SQL is not supported on the server, Jet takes over and processes it. This will have a major impact on performance if the tables being processed are large.
Having examined these simple queries, we're now going to move up a gear and examine how Jet deals with joining various tables in a result set.
In a LEFT JOIN, all records from the table in the left are included, while records from the right table are included only if there is a match. Let's see how Jet handles the following query:
After executing the query, we examine the SQLOUT.TXT log through Listing 7.8 to see how the query was processed:
Listing 7.8 SQL Trace Output
In this case, Jet has taken the following steps:
1. Submitted a query to the server to join the orders and customer table, returning only the key information—in other words, the ids of the orders and customers that meet the join criteria.
2. Retrieved the customer information for the customer ids returned by step 1.
3. Retrieved the order information for the order ids returned by step 1.
4. Joined the two result sets locally and returned the final result to the calling application.
By the way, {oj is the instruction to carry out an outer join.
A heterogeneous join is the name give to the process where two tables located in different databases are joined to form a result set. Let's try to join the Orders table in one server database to the Order Details table in the other server database (see Listing 7.9).
Listing 7.9 An SQL Heterogeneous Join Query
On examining the trace file, we find the activity shown in Listing 7.10.
Listing 7.10 An SQL Trace Output for Heterogeneous Join
This output appears more complex than it actually is because the two databases use the same underlying table names and column names. Filtering out this, we find that Jet has done the following:
This concludes our review of Jet's query processor. To summarize, we have learned to do the following:
Our investigation of Jet's query processing capability is not comprehensive. Jet's execution plan is based on several factors: the complexity of the query, the content and structure of the tables, and the indexes available. For this reason it is strongly recommended that you undertake an exercise similar to what we have done here, as you strive to gain optimal performance. Bear in mind, however, that the execution plan Jet uses in evaluating your queries may change over time, as the structure and content of your server databases change.
So far, all our queries have been processed by Jet. It has been responsible for ensuring that our query has been formulated correctly and decided on how to execute it. But there is another type of query—known as a Pass-Through query—in which Jet plays only a minor role. As its name suggests, this type of query passes right through the Jet Database Engine to the database server for processing. Jet is responsible only for providing the database connection and for returning any result sets or errors to the Visual Basic application. This is depicted in figure 7.7.
Fig. 7.7 The processing of Pass-Through queries.
In addition to being able to select and change data, such queries can carry out database administrative tasks, such as changing roles on an Oracle database, adding users or groups on an SQL Server database, and so on. The following example in Listing 7.11 shows how to use a Pass-Through query to add a user to the current database on an SQL Server:
Listing 7.11 An Example Pass-Through Query
Pass-Through queries offer a number of advantages over standard Jet queries:
However there are also some disadvantages.
Clearly the biggest disadvantage of using Pass-Through queries is that they may be server-specific and as a result cause difficulty in porting the application later. If you do decide to use Pass-Through queries, then you need to adopt a strategy that will allow you to port your application easily should you need to at a later stage. The following are a few ideas to consider:
Developing professional database applications with Visual Basic requires an understanding of the Jet Engine. In this chapter you have gained an understanding of the various activities performed by Jet. By examining the Query-processing capabilites of Jet, you have learned how to optimize your queries and, by using the trace option, you have learned how to debug a query.
| Previous Chapter | Next Chapter | Search | Table of Contents | Book Home Page |
| Buy This Book | Que Home Page | Digital Bookshelf | Disclaimer |
To order books from QUE, call us at 800-716-0044 or 317-361-5400.
For comments or technical support for our books and software, select Talk to Us.
© 1996, QUE Corporation, an imprint of Macmillan Publishing USA, a Simon and Schuster Company.