MySQL Cookbook, 2nd Edition

Issuing Statements and Retrieving Results

Problem

You want your program to send an SQL statement to the MySQL server and retrieve whatever result it produces.

Solution

Some statements return only a status code; others return a result set (a set of rows). Some APIs provide different methods for issuing each type of statement. If so, use the method that’s appropriate for the statement to be executed.

Discussion

There are two general categories of SQL statements that you can execute. Some statements retrieve information from the database; others make changes to that information. These two types of statements are handled differently. In addition, some APIs provide several different routines for issuing statements, which complicates matters further. Before we get to the examples demonstrating how to issue statements from within each API, I’ll show the database table that 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. To maintain a profile about each person, we can use the following table definition:

CREATE TABLE profile ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, name CHAR(20) NOT NULL, birth DATE, color ENUM('blue','red','green','brown','black','white'), ...