Chapter 2
Interfacing SQL to a Procedural Language
IN THIS CHAPTER
Interfacing procedural language programs with Microsoft Access
Giving Microsoft SQL Server a shot
Doing the same with MySQL
Oracle 18c’s turn
Ending with IBM Db2
You can’t build a user-friendly database application with SQL alone. SQL is a data sublanguage and as such lacks many of the facilities required to build a user interface or even execute a sequence of steps. Building a moderately sophisticated application that uses the data in a database requires a procedural language in addition to SQL. Most database management systems (DBMSs) offer compatibility with several procedural languages. Which ones are offered depends on the source of the DBMS and its history, as well as considerations of what capabilities users are most likely to need. In this chapter, I discuss the most common ways of connecting and interfacing to the most popular DBMS products: Access, SQL Server, MySQL, Oracle 18c, and IBM Db2.
Building an Application with SQL and a Procedural Language
Although languages such as C, Java, and Visual Basic don’t intrinsically support database operations, you can use those languages to write procedures that perform such operations. To make their products more usable, DBMS vendors offer libraries of such procedures. Some of these procedures perform operations that SQL can’t perform; others work with SQL to perform a needed function. As a result, in some environments, you can create quite complex database operations without ever having to resort to SQL. Read on to find out how this issue is addressed by the popular database platforms.
Access and VBA
Visual Basic for Applications (VBA) is a subset of Microsoft’s Visual Basic language, specifically designed to be the procedural language to go along with Microsoft Access. Hundreds of libraries are available to the VBA database programmer. Figure 2-1 shows a small subset of these libraries. The figure depicts the References dialog box, which is accessible from the Visual Basic for Applications Tools menu, which in turn is accessible from the Access Database Tools ⇒ Macro menu.
FIGURE 2-1: Visual Basic for Applications References dialog box enables you to select libraries to include with your program.
Two of the most important libraries for most applications are ADOdb and ADOX. The next sections tell you why they’re so important.
The ADOdb library
The ADOdb library is a data abstraction library for PHP and Python modeled after the ADO object model. It has a small memory footprint and contains only basic procedures that just about every application needs. Programs that use this library aren’t burdened with having to carry along a bunch of procedures that they never use.
In keeping with the object-oriented nature of the ActiveX Data Objects (ADO) object model, the library contains objects that perform basic functions, including the following:
- Making connections
- Issuing commands
- Retrieving recordsets
- Navigating a recordset
- Performing basic maintenance tasks
Clearly, any application that deals with a database has to connect to it before it can do anything else. The ADOdb library gives you that capability with the connection object. In addition, the library contains procedures for retrieving data from a data source, specifying a location in the database, and setting the type of locking that will be in force. The command object works with the SQL Data Manipulation Language (DML) commands to perform SELECTUPDATE, INSERT, and DELETE operations. (For more information on DML commands, see Book 1, Chapter 5.)
The ADOX library
ADOX is short for ADO Extensions for DDL and Security. DDL, of course, is SQL’s Data Definition Language, which is that part of SQL used to create and destroy database objects. With the ADOX library, you can create tables, indexes, keys, groups, user identities, and views. You can also delete any of those things.
Other libraries
In addition to the general-purpose libraries with broad applicability, many specialty libraries may be of value to your application. Be sure to check on what’s available from your DBMS vendor or independent third parties before you go about reinventing the wheel.
SQL Server and the .NET languages
Microsoft’s .NET initiative introduced the idea of managed code as a way of eliminating several of the most common sources of programming bugs, as well as eliminating the chaos and lack of portability that results when each language has its own application programming interface (API) that isn’t compatible with any other. All the .NET languages, such as Visual Basic.NET and C#, create code that runs under the control of the Common Language Runtime (CLR). The CLR provides just-in-time compilation, memory management, type safety enforcement, exception handling, thread management, and security.
Regardless of what .NET language you write in, your code is compiled down to Common Intermediate Language (CIL). As a result, all the .NET languages are essentially equivalent, so anything that you can do with any of them, you can do with all of them. If you feel more comfortable writing in Visual Basic.NET, go for it. You can do everything that the people writing in C# or C++.NET can do. When you’re programming in the .NET world, you can use the thousands of classes, structs, interfaces, enumerations, and delegates in the .NET Framework class library. Because every language uses the same API, after you learn the .NET Framework as it applies to one language, you’ve learned it as it applies to any other language.
How does this relate to SQL? Microsoft’s implementation of SQL, Transact-SQL, runs on the database server to operate on data stored there. The managed code you write in VB.NET, C#, or any of the other .NET languages can run on either the server or the client. This flexibility is welcome when you want to minimize the computational load on the server. Many of the functions that have traditionally been performed by Transact-SQL can be performed by managed code — in many cases, more efficiently. The net result (no pun intended) is a reduction in the overall use of SQL in applications written in a .NET language.
MySQL and C++.NET or C#
Although .NET technology was developed by Microsoft, it works with non-Microsoft products, such as the open source database MySQL. You can access MySQL from C++.NET or C# via an Open Database Connectivity (ODBC) data provider or the MySQL.Data.dll connector. In either case, you have the advantages of managed code but don’t need to use a proprietary DBMS such as SQL Server. All the resources of the .NET Framework are available for you to use on a MySQL database. This situation may well enable you to do some data manipulations with MySQL that you couldn’t do by using MySQL’s implementation of SQL in conjunction with other languages.
MySQL and C
MySQL provides a client library written in C. The library enables you to access a MySQL database from within an application program written in C. The library provides an API that defines how clients establish contact with the database server and how communication is handled. Other languages — such as Perl, PHP, Java, Python, C++, and Tcl — all have client APIs that are built on top of the C library.
MySQL and Perl
Perl scripts connect to MySQL databases through the Perl interpreter. The Perl interpreter comprises two levels: the database interface (DBI) level and the database driver (DBD) level. The DBI is generic and can direct commands to a MySQL driver, but also to a PostgreSQL driver or drivers that connect to other kinds of databases. Perl, an interpreted scripting language, is probably the most commonly used language for developing MySQL applications.
MySQL and PHP
PHP, like Perl, is an interpreted scripting language, but unlike Perl, it’s especially designed for the development of web applications. It provides a means of embedding executable scripts in web pages. The web page is processed by PHP before being sent to the client for display, which enables the script to generate dynamic content.
MySQL and Java
MySQL connects to programs written in the Java language through the Java DataBase Connectivity (JDBC) protocol. MySQL/J is a native Java driver that converts JDBC calls into the network protocol used by the MySQL database.
Oracle SQL and Java
You can connect to an Oracle database from a Java program by using either of two technologies:
- Java-based embedded SQL (SQLJ): SQLJ statements may appear anywhere in a Java program where a Java statement may appear. All SQLJ statements begin with
#sqlto distinguish them from other Java statements. Two kinds of SQLJ statements exist:- Declarations: With a declaration, you can establish a connection to a database. You can also use a declaration to store result sets that come back from the database.
- Executable statements: Executable statements execute embedded SQL statements and PL/SQL blocks. PL/SQL includes extensions to SQL for performing procedural operations. Executable expressions may also be used to exchange information between the Java program and the database, using variables.
- Java-based DataBase Connectivity (JDBC): JDBC is an API for connecting Java programs to a wide variety of database back ends. In this respect, it’s similar to ODBC.
Db2 and Java
IBM’s Db2 database is accessible to Java application programs via SQLJ and JDBC. JDBC drivers of various types are available on platforms that include Linux, Unix, and Windows, as well as IBM proprietary operating systems such as OS/390, z/OS, and iSeries. SQLJ applets and applications contain embedded SQL statements that are precompiled and bound to a Db2 database. The SQLJ driver translates the embedded SQL code into Java code.
SQL user-defined functions (UDFs) can be in the form of Java modules. Stored procedures can also be created from Java classes. With UDFs, you can extend the functionality of the “plain vanilla” SQL provided by DB2. Putting program logic in stored procedures that reside on the server improves performance by reducing traffic between the client and the server. Instead of the client’s issuing a command and receiving a response for each operation, the client merely calls the stored procedure, which performs all the operations and returns the result to the client.