Database Reference
In-Depth Information
a database named
cookbook
. To create the account or the database, see the instruc‐
tions in that recipe.
• The discussion here shows how to use each API language to perform database
operations, but assumes a basic understanding of the language itself. If a recipe uses
programming constructs with which you're unfamiliar, consult a general reference
for the language of interest.
• Proper execution of some of the programs might require that you set certain envi‐
ronment variables. General syntax for doing so is covered in “Executing Programs
from the Command Line” on the companion website (see the
Preface
). For details
about environment variables that apply specifically to library file locations, see
Recipe 2.3
.
MySQL Client API Architecture
Each MySQL programming interface covered in this topic uses a two-level architecture:
• The upper level provides database-independent methods that implement database
access in a portable way that's the same whether you use MySQL, PostgreSQL, Ora‐
cle, or whatever.
• The lower level consists of a set of drivers, each of which implements the details for
a single database system.
This two-level architecture enables application programs to use an abstract interface
not tied to details specific to any particular database server. This enhances portability
of your programs: to use a different database system, just select a different lower-level
driver. However, perfect portability is elusive:
• The interface methods provided by the upper level of the architecture are consistent
regardless of the driver you use, but it's still possible to write SQL statements that
use constructs supported only by a particular server. For example, MySQL has
SHOW
statements that provide information about database and table structure, but using
SHOW
with a non-MySQL server likely will produce an error.
• Lower-level drivers often extend the abstract interface to make it more convenient
to access database-specific features. For example, the MySQL driver for Perl DBI
makes the most recent
AUTO_INCREMENT
value available as a database handle at‐
tribute accessible as
$dbh->{mysql_insertid}
. Such features make a program eas‐
ier to write, but less portable. To use the program with another database system will
require some rewriting.
