Sequel Overview

 

Note

This Sequel overview is not intended to serve as reference guide. It has to provide a view on Sequel's role and to outline when and why use Sequel. Therefore this document also describes other database interfaces.

 

Preface

Sequel is an object-oriented wrapper for ODBC implementation for Win32 platforms. It means that the ODBC API is wrapped into classes, which represent all data structures and routines needed to work with databases with taken of SQL. Realized as COM automation server it covers the area of programming with e.g. Delphi, C++ Builder, Visual Studio, Visual Basic Scripting and creating of active server pages. As ODBC itself is a low-level programming interface, Sequel is the high-level one. It provides object model for database programming via its automation COM interfaces.

 

Database Client Technologies

As programmers we can generally treat database client technology as a way to work with database from a program. Looking at the figure 1 shows that this is the part identified as database interface.

Accessing database data
Figure 1: Accessing database data

Nevertheless we shouldn't forget abstraction from database, because not all interfaces are simple and easy to understand. The low-level ODBC is good example of this. It gives you speed, universality and efficiency, but obviously pure ODBC use makes source code hard to port it to another interface. To make ODBC use simple there are two ways how to do it:

  1. ODBC wrapper
    Wrapper can be seen as something what turns all those API calls into a simple to use form; e.g. via encapsulating into fashionable objects. MFC ODBC Classes and Sequel are using this way, for example. Such solution always grants no or insignificant performance degradation, because it's just only pre-programmed piece of code in either compiled or source code form. But you should know that anyhow these two wrappers look to be the same, they are not. MFC ODBC Classes are only for Visual C++ compatible, the same way Sequel was for Delphi till version 2.0, while Sequel 2.0 is in-process COM automation server. Therefore Sequel is no longer only development tool dependent and you can use it even from scripts.

  2. Other simple-enough interface using ODBC
    This way always causes a slow-down, because it at-least duplicates the role of your application when manipulating with database. DAO accessing another than MS Access database is exactly this case. Let's imagine following situation: You (standing as your application) need some data, but you are unable to get them alone. Therefore you have to ask someone (simple-enough interface) to do it for you. The problem is that this guy is not able to do it too and he has to ask someone else. This can finally be someone who can, or someone who can't once again. Next problem is that these guys don't necessarily handle data in same way and therefore conversions can occur. In contrast, wrapper is that part of you (application), which doesn't need anyone to get data. It is apparent that using this way is acceptable only in cases like these:

    1. When badly running out of time
    2. It is the only possibility you can/must use

 

Anyway this evokes an impression that ODBC is the only one way to directly access a database; it is not true. ODBC, unlike e.g. DAO, supports multiple databases via its drivers - each database has its own driver => when OBDC is not able to do something, it's only a matter of driver (and of course database's capabilities).

Because this is real world the comfort costs speed and efficiency. Some interfaces like BDE gives you data aware components, but don't forget the tax you have to pay for that. Such visual components must be updated when you make a change in database and because you don't control that (components' updating), this can result into a significant slow-down. When establishing a database connection, there are some modes how to read data. Except forward-only cursors (non-technically: reading data right away as they come and only once), it can result into a very expensive data processing if database doesn't have such capabilities. Moreover independently on capabilities it is always another load causing slow-down. Forward-only cursors are the only way, which doesn't force databases to create locks even when it is not necessary and this is always when you want only to read or write data. Visual data-aware components in 99% can not operate with forward-only cursors. Actually it looks like situation, which can be solved by investing into more advanced hardware to buy enough of power. But remember that user wants quick response, not to spend money.

Some interfaces gives you API that allows you to specify how to perform actions (mainly embedded SQL interfaces). Herein referenced ODBC provides a call-level interface (CLI). CLI is a special kind of API enabling to send the SQL code at runtime and that's when it is interpreted. You should keep on your mind that with modern database system it is better to express what (SQL statements does that) to do and not how (direct API calls).

To better understand what's exactly going on, few database interfaces are introduced below.

 

ODBC

This is quite popular and generally accepted interface for relational databases. There are ODBC drivers almost for all existing databases today. This makes it ideal for using with various data sources, because ODBC itself also brings some abstraction making uniform database handling possible. Nevertheless you can still use all specific features of database in SQL way.

From the programmer point of view, this is nontrivial topic but using ODBC can give you fast and efficient access on low-level. It requires pointers and introduces asynchronous events. It's preferable to use some wrapper like MFC ODBC Classes or Sequel, than to directly use ODBC.

ODBC Program Architecture
Figure 2: ODBC Program Architecture

As it is apparent from figure 2, there's nothing between your code and database except ODBC. It is only a set of dll libraries, so that everything is one process - i.e. these libraries reside in the same address space as your code.

 

MFC ODBC Classes

This has been created for Visual C++ to simplify ODBC for programmers. Unlike ODBC itself, this is high-level interface. When utilizing you still use only one database interface.

 

Sequel

Sequel is an object-oriented wrapper for ODBC implementation for Win32 platforms. Realized as COM automation server it covers the area of programming with e.g. Delphi, C++ Builder, Visual Studio, scripting and creating of active server pages. As ODBC itself is a low-level programming interface, Sequel is the high-level one.

Sequel Program Architecture
Figure 3: Sequel Program Architecture

Figure 3 shows Sequel as something what stands between your code and driver manager. This is the place where all those ODBC handles, events and calls turn into fashionable objects and vice-versa. Therefore Sequel is only a wrapper, not database interface.

As well as MFC ODBC Classes, also Sequel is closely tied to ODBC. Because both calls the ODBC API directly (rather than Jet, like DAO), they can provide excellent   performance if used wisely.

 

DAO

DAO stands for Data Access Objects. It is a set of automation interfaces for the Access Jet database engine. As shown on figure 4 DAO directly use Access databases via Jet engine, but any other database can be accessed only via Jet using ODBC. Therefore DAO is not a wrapper, but as Sequel does, it also provides object model for database programming.

DAO Program Architecture
Figure 4: DAO Program Architecture

 

 

Performance

It is always a playing with magic when doing benchmarks, because there's always some influence from the system side. It can be system cache, network load or current state of RDBMS server. Therefore analyzing the way a database interface works is more accurate. Generally the shortest way for data from your code to database (server or ISAM files) is the fastest one. Comparing all discussed interfaces on access to ODBC-compliant, not Access, database, we can see that the first one is ODBC followed by Sequel and MFC ODBC Classes. But in fact, there's no significant difference among them. Only the DAO is noticeable slower. To improve its placing, Access database is to be accessed directly via Jet. Nevertheless this still doesn't guarantee the first place. Of course you'll get direct access, but what about transactions? It is up to you to serialize API calls, while ODBC driver does itself. Simply said it is your knowledge of Jet vs. MS one. (I guess that you will not be the winner.)

Created as a complementary technical paper for Sequel 2.0 by Ing. Tomáš Koutný.