Database Reference
In-Depth Information
2.4. Executing Statements and Retrieving Results
Problem
You want a program to send an SQL statement to the MySQL server and retrieve its
result.
Solution
Some statements return only a status code; others return a result set (a set of rows).
Some APIs provide different methods for executing each type of statement. If so, use
the appropriate method for the statement to be executed.
Discussion
You can execute two general categories of SQL statements. Some retrieve information
from the database; others change that information. Statements in the two categories are
handled differently. In addition, some APIs provide multiple routines for executing
statements, complicating matters further. Before we get to examples demonstrating how
to execute statements from within each API, I'll describe the database table the examples
use, and then further discuss the two statement categories and outline a general strategy
for processing statements in each category.
In
Chapter 1
, we created a table named
limbs
to try some sample statements. In this
chapter, we'll use a different table named
profile
. It's based on the idea of a “buddy
list,” that is, the set of people we like to keep in touch with while we're online. The table
definition looks like this:
CREATE
TABLE
profile
(
id
INT
UNSIGNED
NOT
NULL
AUTO_INCREMENT
,
name
VARCHAR
(
20
)
NOT
NULL
,
birth
DATE
,
color
ENUM
(
'blue'
,
'red'
,
'green'
,
'brown'
,
'black'
,
'white'
),
foods
SET
(
'lutefisk'
,
'burrito'
,
'curry'
,
'eggroll'
,
'fadge'
,
'pizza'
),
cats
INT
,
PRIMARY
KEY
(
id
)
);
The
profile
table indicates the things that are important to us about each buddy: name,
age, favorite color, favorite foods, and number of cats—obviously one of those goofy
tables used only for examples in a book! (Actually, it's not that goofy. The table uses
several different data types for its columns, and these come in handy to illustrate how
to solve problems that pertain to specific data types.)
The table also includes an
id
column containing unique values so that we can distinguish
one row from another, even if two buddies have the same name.
id
and
name
are declared
