records can go. You will learn more about this in the upcoming days. You'll see how to pass variables
from your application to the database to get the results you need.Receiving the Recordset and Manipulating the Data
The next step in the cycle is receiving the recordset. A resultset or recordset is the result of a SELECT query.
A recordset is the same information that is returned to the screen when you type a SELECT statement into
the MySQL monitor program, but instead of these results being displayed onscreen, they are put into a
variable of some sort to be used in your application. These variables are usually an array of some sort. The
recordset is retrieved as a series of rows. Each row contains the columns that were used in the SELECT
statements. This creates a grid-like structure, very much like the display in the MySQL monitor. To retrieve
all records from a recordset, you have to pull the data out of the row, move to the next row and get the
record, move to the next row, and so on. It's like walking—you are taking rsow one step at a time. Hence, the
term "walking a recordset."
The results are stored on a column name/value pair basis. For example, there is a First Name column
in the Customers database. In your recordset, depending on the interface you were using, you could
refer to the column by name. If you were using Perl, referring to the First_Name field in a resultset
would look something like the following:
$ref->{'First_Name'}
This would return the value that was held in the First_Name column for that row. You can also refer to
column names by their indexes. If the First_Name column was the first column named in your SELECT
query, you could refer to it using an ADO Recordset object, as shown in the following:
RstCustomers.Fields(0)
You are not limited to the number of recordsets you have. You can create a new recordset for each
query you issue. The choice is yours, just remember that these types of variables take up some
memory. You may need to take performance into consideration before you create a hundred recordset
objects.Now that you have the recordset, you are free to do whatever you like with the data. You can display it
to the user, use it in graphs and charts, or even create reports. The number of things you can do is
endless.Closing the Connection
Like your mother always said (at least mine did) you have to clean up all your toys when you are done
playing. This same advice holds true in the programming world. When you are done creating objects and
they no longer have a use, get rid of them—throw them away. The same holds true for connections. If you
are no longer using a connection, get rid of it. Free it up for someone else to use. Each connection takes up
resources both on the client and on the server. Cleaning up after yourself saves resources and prevents
bugs.
Summary
This day provided you with a preview of things to come. It introduced you to the interface and driver
concepts. You learned that these two layers work in conjunction with one another to provide access to your
database. You saw that the interface provides you with the same functions and variables no matter which
database you are using. The only thing that needs to be changed is the driver. You also learned the
advantages of doing things this way.
Today, you also learned about the parameters that are required for every database connection. This
information needs to be collected before any connections can take place.
You also learned about the common steps that all interfaces share when working with a database. You
learned that this cycle is logical and provides a great deal of flexibility and power to the programmer.
You saw how you can embed SQL commands in your application to accomplish any task from database
administration to SELECT statements.Interfacing your database is the most important part of any business. If you cannot access or
manipulate your data, a database is useless. Interfaces provide a way for programmers, Web
developers, and database administrators to manipulate their databases programmatically.