| DCOM Explained by Rosemary Rock-Evans Digital Press ISBN: 1555582168 Pub Date: 09/01/98

Previous | Table of Contents | Next

The dialects of each DML (Data Manipulation Language)-Not only have DBMS developers produced many types of DBMS, they have also produced many “flavors” of the same original standard DML. Codasyl databases and Relational databases both suffer from this problem. As DBMS developers developed their own implementations, they “adapted” the standard. Many of the additions were added to provide missing functionality and some were added to provide some form of obviously discriminating feature.

What has resulted, however, is a large collection of both Codasyl and Relational DBMSs that do not adhere to the original single standard. The problem is probably more severe in the Relational DBMS area, simply because this class of DBMS has had longer to evolve and there are more products on the market to exacerbate the problem. In the following paragraphs, some of the differences that can occur between the various versions of SQL are explored in more detail.

  • Variations in syntax and semantics of commands-Although the SQL standards have provided some degree of commonality in statements such as SELECT, INSERT, UPDATE, DELETE, and so on, there are still some significant differences in the vendors’ implementations of these commands. SQL Server, for example, used to assume that certain keywords were optional—such as the INTO keyword in the INSERT statement. DBM, on the other hand, used to assume such keywords were required.
  • Data types-Although every relational DBMS supports the simple data types for integer and character, there are big differences in the way and the extent to which other data types are supported. Varying character strings, date, time, decimal, binary data, floating point, and so on may or may not be supported. Even if the data type is supported, the format used may be different; for example, DBM used to support DATE and TIME with two constructs, whereas SQL Server had one DATETIME construct
  • Status codes and messages-Every database system must return a status code upon the completion of each SQL operation. Even successful operations generate status codes. Clearly, the operation of most programs is likely to depend on these status codes, whether they indicate a successful conclusion to a request or an unsuccessful one. In practically all the DBMSs, not only are the status codes themselves different for the same errors and conditions but the method in which the errors are handled is also different. For example, DBM used to return status codes as binary values in a field called SQLCODE in the SQL Communication Area. SQL Server, on the other hand, handled error conditions through two call-back procedures that received an integer identifying the error condition. Furthermore, some error conditions in DBM did not exist in SQL server and vice versa.

### So What Does Database Connectivity Middleware Really Do?

It can handle dialects of the DML-In general, the majority of database connectivity middleware smoothes out the differences in the dialects between products of the same type. So, for example, a developer can use one standard version of SQL and the middleware will translate this to the dialect used by Oracle, the dialect used by Sybase, the dialect used by Informix, and so on.

These products can handle the differences in semantics and syntax, can potentially handle the difference in status codes and error messages (though not all translation products do), and can occasionally handle name translation. They have problems with data types. Where a developer wants to access a table or record containing data types that the standard API doesn’t recognize as a data type, he or she has to use “pass thru,” meaning an instruction in the native dialect that is simply passed through to the DBMS.

A small number can handle different DMLs-Only a few specialized products can translate the DML of different DBMS types. So, for example, only a few products can translate from one standard version of SQL to the DML used in IDMS. The translation performed by these products is almost entirely limited to translation of query functions, not update functions. So, products such as EDA/SQL, for example, are able to translate from an SQL dialect to the IDMS dialect, but only if the DML is for a query.

Some of these specialized translation products can also help a little with security privilege translation. As you are probably aware, the database administrator can set privileges and passwords for access to data, and for each DBMS this information has to be repeated. This information is often set up differently with different names and passwords. The translation software keeps its own “translation files” to show the mapping between all the ids. Some products also automatically synchronize their central security database with those of the DBMSs, meaning that an administrator has even less work to do.

So what doesn’t it do?-The one thing database connectivity middleware cannot do is protect the developer from having to know he or she is accessing a certain DBMS or from having to know how that DBMS works. He or she may be able to use a single API, but the developer still has to understand the effect of that API within that DBMS—how it affects performance, how it affects the integrity of the data, and so on. Runtime services such as locking, deadlock detection, optimization, and so on all work differently in each DBMS, and all affect the workings of the DBMS differently. You could write a single line of common SQL code for Oracle and the performance could be fine, but in Sybase it could be awful, simply because the locking levels are different.

The developer also has to understand the database design, understand the meaning of the fields, and handle all the problems of data duplication, incompatible formats, incompatible coding systems, and so on. This is really the crux of the problem with database connectivity products—they don’t tackle the main problem. What makes access of all these DBMSs so difficult and time consuming is not the differences in the DML, but the awful designs, the lack of documentation, the duplication, and the misuse of data fields by endusers and programmers alike. The real problem isn’t actually solved by database connectivity middleware at all.

So, database connectivity middleware looks like promising stuff on the face of it, but in practice it doesn’t bring the total transparency it promises and doesn’t deliver the levels of productivity improvement often claimed. Nevertheless, one shouldn’t knock it. It simplifies things considerably for low- to medium-volume queries as long as the data is reasonably well documented and does not contain too much duplication and too many incompatibilities. I’m not sure I would recommend it for high-volume queries or any form of update.

Why? Well, we have seen that you really need to know what you are doing for an update (locking, commit actions, deadlock detection, etc.), and in this case you might just as well use native DML to ensure you get the right result. Similarly, you also need to know what you are doing with high-volume queries to get the best performance, so again it might be better to use native DML where you understand the effect of the command.

Another reason is simply that translation is performed at runtime, which of itself is bound to slow processing down. In fact, most developers know that updates and high-volume queries are often better compiled (as static SQL, for example) as the compiled code provides better performance.

Previous | Table of Contents | Next