Chapter 1
Database Development Environments
IN THIS CHAPTER
Using Microsoft’s Access and SQL Server
Working with IBM Db2
Employing Oracle 18c
Using SQL Anywhere, PostgreSQL, and MySQL
Aside from organizations that locked themselves into a database environment before about 1985, for most applications, any organization that’s using a database system now is probably using a relational database system. Any relational database system that’s still around today uses a version of SQL for communication between users and data. Although several specialty database products serve specific niche markets, for general use, a relatively small number of database management system (DBMS) products have significant market share. These products are Access and SQL Server from Microsoft, DB2 from IBM, Oracle and MySQL from Oracle Corporation, SQL Anywhere from SAP, and PostgreSQL from PostgreSQL Global Development Group.
In this chapter, I take a brief look at the popular relational DBMS products and how they implement SQL.
Microsoft Access
Microsoft Access, like all relational DBMS products today, uses SQL for communication between the user and the database, but it does a really good job of hiding that fact. Access comes with a procedural language called Visual Basic for Applications (VBA). The normal way of writing a data-driven application is to write it in VBA and use a library of classes for dealing with the data. The tools for doing that have undergone a massive upheaval in recent years. The back-end database engine part of Access, called the Jet engine, has undergone changes and expanded in flexibility. In addition, the recommended method of talking to the Jet engine has gone through one change after another.
Over the years, Access has evolved and expanded in its capabilities. As part of the evolution, the way of connecting to the database itself has changed several times. The following sections give brief descriptions of those different ways.
The Jet engine
The Jet engine originated in 1992 as the back end of Access 1.0. Initially, it didn’t support data access via SQL, but a later version implemented a subset of SQL-92 functionality. In the early days, connecting to native Access .mdb
The Jet engine has evolved over the years and is no longer included with Access; now, in fact, it’s integrated into all Windows operating systems from Windows 2000 onward.
DAO
The DAO interface to the Jet database engine is an object-oriented Dynamic Link Library (DLL) that creates a workspace object, which acts as a container within which all database operations are performed. The DAO DLL, which for years was used with products in addition to Access, has been superseded and deprecated for those other uses. It remains in the playbook for Access, however, including Microsoft Office Access 2016.
ADO
Microsoft introduced ActiveX Data Objects (ADO) in 1996 as a successor to DAO and as yet another alternative to SQL. Developers can create database applications with ADO without any knowledge of SQL. High-level procedural languages such as Visual Basic, VBScript, Embarcadero Delphi, and Embarcadero C++ Builder support the ADO interface.
ODBC
Open Database Connectivity (ODBC) is a procedural application programming interface (API) that connects an SQL query to a database. Developed and released by Microsoft in 1992, ODBC has come to be used in many programming environments to access many databases. Hundreds of ODBC drivers exist. Microsoft’s version of ODBC ships with every supported version of Windows. Open-source implementations are widely used by Unix and Unix-derived operating systems.
OLE DB
Object Linking and Embedding Database (OLE DB) is a Microsoft API — designed as a successor to ODBC — for accessing a wide variety of data stores, including but not limited to SQL-compliant relational databases. OLE DB interfaces also work with such diverse data sources as object databases, text files, and spreadsheets.
Files with the .mdb extension
One of the unusual characteristics of Access databases is that they’re entirely contained in a single file. All versions of Access up to and including Access Office 2010 are capable of storing data, metadata, and everything else in a single file with a .mdb extension.
The Access Database Engine
Back with Access Office 2007, a new database engine (now called the Access Database Engine) was introduced that operates on a new file format, with a .accdb extension, although it continues to support .mdb files. This new format enables new features, chief among which is interoperability with Microsoft Office SharePoint Server. The .accdb format isn’t usable by earlier versions of Access, so if you read in a .mdb file but then write it out in .accdb format, it will no longer run on older versions of Access.
Microsoft SQL Server
SQL Server is Microsoft’s primary entry in the database arena. Ranging from the entry-level SQL Server Express 2017 to the unlimited-class SQL Server 2017 Enterprise Edition, SQL Server is based on Microsoft’s Transact-SQL, which, in contrast to the SQL in Access, is a full-featured robust implementation of the SQL:2016 international standard that also includes numerous proprietary extensions to the standard syntax. SQL Server runs only under Microsoft operating systems. You can connect to an SQL Server 2017 database via the SQL native client. Transact-SQL is also compatible with ODBC, OLE DB, and ADO.NET.
ADO.NET is a set of data access application programming interfaces (APIs) that evolved from ADO, but it has changed so much that it is essentially a different product. It is a part of the base class library included with the Microsoft .NET framework.
IBM Db2
Db2 is IBM’s full-range relational DBMS. Scaling from a single user to an unlimited-class enterprise DBMS, Db2 also features a robust implementation of SQL. Db2 operates in a variety of environments, including Microsoft Windows, IBM mainframe z/OS, Unix, and Linux. Db2 supports a wide variety of interfaces, including the following:
- ODBC
- OLE DB
- ADO
- JDBC (Java-based DataBase Connectivity)
- SQLJ (Java-based embedded SQL)
- SQL
- DRDA (X/Open Distributed Database Standard)
- CLI (X/Open Database Access Standard)
- EDA/SQL (IBI’s EDA SQL Standard)
- DAL (Apple Relational Database Standard APIs)
- Net.Data (Internet Database Access)
Oracle 18c
Oracle 18c is the current version of Oracle Corp.’s full-range DBMS. Oracle Version 2 was the first commercial relational database product when it hit the market in 1979, and Oracle has retained a leading position in the marketplace ever since. Oracle’s implementation of SQL, called PL/SQL, is a very complete implementation with a high degree of conformance to the latest version of the ANSI/ISO standard, SQL:2016, as well as useful proprietary extensions.
Oracle Database 18c supports all standard relational data types and can be connected to in many ways, such as with PL/SQL, JDBC, SQLJ, ODBC.NET, OLE.NET, ODP.NET, XML, XQUERY, and WebDAV. You can write stored procedures in Java or PL/SQL. You can also use .NET CLR support.
SQL Anywhere
SQL Anywhere is a DBMS developed by Sybase, and now marketed by SAP. The product is a powerful, full-featured database product that has been optimized for connecting mobile devices to a central database server. SQL Anywhere is operated via Sybase Transact-SQL.
The sharp-eyed person will notice that Sybase’s implementation of SQL has a very similar name to the version of SQL used by Microsoft’s SQL Server. This similarity is no accident; Microsoft’s SQL Server was based on a Sybase product. After several years of cooperation, the two companies parted ways, and product development diverged.
PostgreSQL
PostgreSQL is a full-featured open source object-relational DBMS. It is used by a diverse array of organizations, ranging from Yahoo! to the International Space Station. The version of SQL used by PostgreSQL, PL/pgSQL, resembles Oracle’s PL/SQL.
MySQL
MySQL is an open source DBMS owned by Oracle. The product has grown in capability over the years to the point where it’s competitive with the other DBMS products mentioned in this chapter in terms of the SQL functionality that it supports.
MySQL 8.0.2 is a full-featured implementation of SQL and the most widely used open source DBMS in the world. It offers ODBC, JDBC, and OLE DB connectivity, as well as APIs for most popular languages, including C, C++, Python, Tcl, Perl, PHP, and Eiffel.