Any Platform, Any Time

Any Platform, Any Time

SQL is different from standard programming languages such as C, Visual Basic, Java because it cannot be used to create stand-alone applications. It does not exist outside some database engine that is capable of translating its statements into machine language and execute. SQL does not have all the programming constructs that are the staples of other languages: for example, conditional logic, loops, and use of variables.

While these deficiencies are being alleviated, first with the introduction of procedural extensions, and then adding object-oriented features to SQL, it was never meant to be just another multipurpose programming language. This "weakness" is the key to SQL ubiquity — since it is dependent on RDBMS to execute, it is the first truly platform-independent language.

Every program is created by typing in some commands in plain ASCII. These commands are then compiled into binary machine code executable files for most of the programming languages. The problem with this approach is that for each and every platform the program has to be recompiled using the platform-specific compiler, e.g., a program compiled for Microsoft Windows would not work on UNIX, and vice versa. The solution proposed by Java still requires its platform-agnostic byte-code to be executed on platform-specific Java Virtual Machine (JVM).

An SQL query is created in very much the same way as the rest of the programs — by typing in SQL keywords; but here the similarity ends. The SQL program could be stored as a simple ASCII file that could be copied on UNIX, Windows, Mac OS, Linux, and so on — without any changes. Moreover, it could be opened, modified, and saved in any of these operating systems with some standard editing tools; there is no need to take into consideration any platform-specific features. As long as SQL remains a script, it is easily transferable between different platforms (but not between different RDBMS!); in a sense, the RDBMS works like a JVM. Even when SQL becomes part of a host language (e.g., embedded in a C program), it is still only text. It behaves in exactly the same way as HTML, which is just a collection of ASCII characters until it is fed into some Web browser to be executed. In case of SQL, its statements need to be sent to an RDBMS to be translated into executable machine codes; all it needs is a platform-specific database engine implementation.

One of the truly platform-independent features of SQL are basic data types: no matter whether you execute your SQL on UNIX or Windows (32-bit or 64-bit OS), the size and the structure of the reserved storage blocks will be exactly the same. INTEGER data type will always be 4 bytes and DOUBLE data type will occupy 8 bytes no matter what (proprietary data types might behave differently, depending on implementation). It may sound like an obvious thing, but this is not the case with all other programming languages. For C programming language INTEGER is 4 bytes on a 32-bit OS and 8 bytes on a 64-bit OS. (Of course, this also might depend on particular C compiler.) More about data types in Chapter 3.

In a sense, SQL shares the idea of platform independence with the Java programming language: just as Java Virtual Machine translates Java byte-code into platform-specific machine code, RDBMS executes platform-independent SQL code, translating it into OS machine-specific code. Because of this, an SQL program written using Notepad on Windows could be run by an Oracle RDBMS installed on UNIX or Linux.

It was noted before, that there are numerous differences between dialects of SQL: a query written for SQL Server 2000, for example, might not execute on IBM DB2 UDB, and vise versa. But a query that runs on Oracle installed on Windows would require no changes to be executed on Oracle installed on Linux or UNIX. That, in turn, means that as long as one uses SQL features and keywords that are common across all three databases, exactly the same query could run — unchanged! — by any of these RDBMS.

There are virtually dozens of RDBMS products out there running on every imaginable platform (OS): Sybase, Ingres, Informix, Empress, MySQL, mSQL, PostgreSQL, LEAP RDBMS, FirstBase, Ocelot, Progress, Typhoon, SQL/DS, Daffodil DB, Compaq Non-Stop SQL/MX & SQL/MP, Linter RDBMS SQL, Interbase, UniVerse, GNU SQL Server — to name just a few, and new developments continue to sprout.

Of course, each of these RDBMS sports its own SQL dialect, but the good news is that majority of these are SQL standard-compliant. Basic SQL statements in every implementation require either no changes or very few changes to be executed on any of them; you could easily adapt your knowledge of SQL to any of these systems.