Front-End to Database Connectivity

5. Front-End to Database Connectivity

Why connectivity is needed. The front-end and the back-end are two separate pieces of software — often even on two different computers. For the interface to store and fetch data, there must be a connection between them: a controlled channel along which the front-end can send SQL statements to the database and receive the results. Setting up and using this channel is called database connectivity.

The connectivity driver. A front-end program does not know, by itself, how to speak to a particular database. It uses a driver — a small software component that acts as a translator between the front-end's programming language and the database. The driver knows the exact way the chosen database expects to receive requests and return results, so the application programmer can work through one standard set of commands and let the driver handle the details. (You can think of the driver as an interpreter who speaks both the language of the program and the language of the database.)

The standard steps of connectivity. Whatever tools are used, connecting a front-end to a database follows the same sequence:

1. Load / prepare the driver for the database being used.
2. Open a connection to the database, giving its location and the login details (a user name and password). The details needed to find and log in to the database are together called the connection string / connection details.
3. Prepare a statement — the SQL command to be run (for example a SELECT or an INSERT).
4. Execute the statement — send it to the database. A query that reads data returns a result set (the rows found); a statement that changes data returns how many rows were affected.
5. Process the result — the front-end reads the returned rows one by one and shows them on the screen (or confirms the change).
6. Close the connection when the work is done, to free resources.

In pseudo-code, fetching customers from one city looks like this:

prepare the database driver
connection = open connection to database using (location, user, password)
query      = "SELECT Name, Phone FROM Customer WHERE City = 'Cuttack'"
result     = execute query on connection
FOR each row in result:
    display row.Name and row.Phone on the form
close connection

Reading versus writing. When the front-end reads data, it runs a SELECT and walks through the returned result set, copying values into the screen controls. When it writes data — after the user fills a form and clicks Save — it builds an INSERT, UPDATE or DELETE and executes it; the database reports success (or an error, which the front-end shows to the user). …