Getting to the Data
Every interface shares the same concept for accessing data. There are a series of steps that are the same
for accessing your data, no matter which interface/driver you are using. They all basically use the same
information. The following is the general cycle:
- Connect to the database.
- Issue a query or a command.
- If a query was issued, then next step is to receive a recordset.
- Manipulate the recordset.
- Close the connection. (See Figure 12.2.)
Figure 12.2 The common interface cycle.
This cycle can be found in every interface, no matter what the programming language is or what
platform on which it is operating. You will hardly ever deviate from this natural, logical cycle. The
following sections take a more in-depth look at what happens at every step.Establishing the Connection
The first step of the process is to establish a connection with the database. This connection is what
establishes a link between your application and the database. This link is used to transfer all your SQL
commands from your application to the database and return all your resultsets from the database to your
application.
The connection requires the parameters that were stated before. The database, the driver to use, the
location of the database (either IP address or server name), and the username and password. A
connection cannot be established if these parameters are not present. During the next three days, you
will see this in action. You will see how each interface uses these same parameters to establish a
connection and create a link between your program and the database.This step only needs to occur once during a session. After you have established a connection, you can
keep using the same connection for all your queries. You don't have to close the session and then
reopen it to requery the database. After your application has created the link, that link remains in
existence either until your application closes the link or the link is closed because it is not being used.Querying the Database
The next step of the cycle is to query the database. This is where you will embed SQL commands into your
application to pass to the database. These can be SQL commands that create data structures, such as
tables, or they can be SELECT statements; It all depends on your program's requirements. Pretty much
anything you can type in the MySQL monitor can be embedded in your program. This includes OPTIMIZE
statements, as well as the FLUSH privileges statement. You can even use the LOAD DATA INFILE
command statement. This allows you to create very flexible and powerful applications that use MySQL to its
fullest extent.
Most interfaces differentiate between creating a recordset and issuing a command that does not return
data. Most interfaces will have a simple execute function that will just execute the SQL that you have
embedded. This is great for INSERT, UPDATE and DELETE queries, but not good for a SELECT query.
How would you get the results back if you just executed a SELECT statement? There wouldn't be
anywhere for the records to go. So the interfaces have supplied an object or an array to which these