b. A table of products available to order from your online shop. Duplicates

are not allowed.

c. A table that records the sales tax applied to products sold. It is written to

each time a product is sold, but read from only once per quarter.

Day 12: How to Get the Data—Database Interfaces

Overview

Up to this point, you have communicated with your database solely through the MySQL monitor. While this is
a convenient and easy way to create your database and manipulate data, it may not always suit your needs.
The MySQL monitor may not fill the needs of your users who must use the data day in and day out either.
Don't worry, there is more than one way to skin a cat.

MySQL provides you with a number of programming interfaces. These interfaces or APIs allow you to

build applications that use MySQL. Applications that are built to interface with databases are generally

called "front-end" applications. They supply the user interface, while the database engine is usually

called the "back-end." MySQL makes for a very good "back end" database.

Today, you will learn about interfacing MySQL. You will learn

ƒ How interfacing works

ƒ The basic steps that all interfaces share

Making the Connection

Nowadays, it is hard to create a business application that does not interface with a database at some point.
Sure, there are utility programs, but, for the most part, if you create an application in the business world, it is
going to talk to a database. To understand how applications can interface with databases, you need to know
how database servers work and a little about client/server programming.

As you have learned, database servers are programs that run continuously in the background. They are

known as services or daemons, depending on which operating system you use. To take advantage of

the database, an application must first establish a connection to this server program. This connection is

usually established through an interface of some sort.

An interface is a layer between your program and the database. It acts as a kind of middleman.

Commands issued from your program go through this interpreter and are translated for the database.

The interface is simply a collection of functions that are available to the programmer. The programmer

can use these functions to accomplish certain database tasks, such as connecting to the database or

issuing a query.

There are generally two parts to the interface today. One part is the actual code you use in your

application. This part contains the actual function calls and variables. This part is also universal,

meaning that no matter which database you're using, the functions and variables you use remain the

same. The second piece is the actual interpreter, or as it's usually called, the driver. This piece of the

interface handles the grunt work. It takes the function calls from the first part, translates them, and then

interacts with the database to produce the requested results. These drivers are created by the database

manufacturer and are specific to that database. So, if you were using Sybase and you switched to

MySQL, you would need to get the MySQL driver for your code to work. See Figure 12.1.

Figure 12.1 Process flow for interfaces and drivers.

One of the advantages of interfacing a database in this way is that you only have to learn one set of

function calls. It doesn't matter what your back end is, your code will remain the same. For example, say

that you developed an application that was using Sybase as its back-end database product. Because

you are smart and know how the real world works, you used the Perl DBI/DBD in your application. After

everything was completed, the code was tested, beta versions were sent out, customers seemed pretty