Database Interaction

Efficient interaction, efficient storage, and efficient processing are the three key properties of a successful database platform. In this article, we explore the first: efficient interaction.

Interaction Category 1: Command Line Clients

Many database platforms are shipped with a simple command line utility that allows the user to interact with the database. PostgreSQL ships with psql, which gives the user extensive control over the operation of the database and over the tables and schema in the database. Oracle’s SQLPlus and MySQL’s MySQL are similar utilities. Collectively, these are also called SQL shells.

Interaction Category 2: GUI Clients

Another popular way to interact directly with a database is by using a graphical user interface (GUI) that connects to the database server. Oracle’s proprietary SQL Developer software is one of these, although for every database on the market, there are probably at least two or three good, free GUI packages available. Figure 2 shows the “object browser” in pgAdmin III, a free administration tool for PostgreSQL databases.

Interaction Category 3: Application Development

The final method for interacting with a database is through an application. This indirect interaction might occur, for example, when a bank customer is withdrawing money from an ATM. The customer only presses a few buttons and walks away with cash, but the software running on the ATM is communicating with the bank’s database to execute the customer’s transaction. Applications that need to interact with databases can be written in nearly all programming languages, and almost all database platforms support this form of interaction.

Command Line Clients

A command line client usually provides the most robust functionality for interacting with a database. And, because they are usually developed by the same people who developed the database platform, command line clients are typically also the most reliable. On the other hand, effectively using a command line client to its full extent requires expert database skill. The “help” features of command line clients are often not comprehensive, so figuring out how to perform a complex operation may require extensive study and reference on the part of the user. Some basic usage of the PostgreSQL command line client is shown in Figure 1.

All command line clients operate in a similar manner to that shown in Figure 1. For users with extensive knowledge of SQL, these clients are used frequently.

One typically accesses an SQL command line client by logging into the database server and running them from the shell prompt of a UNIX-like operating system. Logging into the server may be achieved via telnet or, preferably, SSH. In a large company, the Information Technology department may have a preferred application for these purposes.

{qbapagebreak title=GUI Clients}

GUI Clients and Application Development

GUI Clients

The simplest way to think about a GUI client is to consider it to be a sophisticated, flashy wrapper around a command line client. Really, it falls into the third category of interaction, application development, but since the only purpose of this application is to interface with the database, we can refer to it separately as a GUI client.

The GUI client gives the user an easy-to-use, point-and-click interface to the internals of the database. The user may browse databases, schemas, tables, keys, sequences, and, essentially, everything else the user could possibly want to know about a database. In most cases, the GUI client also has a direct interface to a simulated command line, so the user can enter raw SQL code, in addition to browsing through the database. Figure 2 shows the object browser in pgAdmin III, a free, cross-platform GUI client for PostgreSQL .

Figure 2. The object browser in pgAdmin III

With an easy tree format to identify every element of the database and access to even more information with a few simple clicks, the GUI client is an excellent choice for database interaction for many users.

Figure 3 shows the Server Information page of MySQL Administrator, the standard GUI tool for MySQL databases.

Figure 3. The MySQL Administrator Server Information page

{qbapagebreak title=Application Development}

Application Development

Application development is the most difficult and time-consuming of the three methods of interacting with a database. This approach is only considered when a computer program needs to access a database in order to query or update data that is relevant to the program.

For example, the software running on an ATM at a bank needs to access the bank’s central database to retrieve information about a customer’s account and then update that information while the transaction is being performed.

Applications that require databases can be written in virtually any programming language. For stand-alone applications, the most popular language for database programming is C++, with a growing following in the C# and Java communities. For web applications, Perl and PHP are the most popular languages, followed by ASP (and ASP.NET) and Python. Interest in using Ruby with the web and databases is growing, as well.

Many database access extensions for modern programming languages exist, and they all have their advantages and caveats. The expert database programmer will learn these caveats, however, and eventually become comfortable and quite skilled at manipulating database objects within application code.

Figure 4 and Figure 5 show the code for a simple database application written in Perl and its output, respectively.

With all the features of modern programming languages, extremely complex database applications can be written. This example merely glosses over the connection, query, and disconnection parts of a database application.