Programming the Perl DBI

Preparing SQL Statements

The first stage of the cycle to retrieve data from your database is to prepare the statement handle from an SQL statement. This stage generally corresponds to the parse stage that occurs internally within your database engine.

What typically occurs is that the SQL statement is sent as a string of characters via a valid database handle to the database. This string is then parsed by the database itself to ensure that it is valid SQL, both in terms of syntax and also in terms of entities referred to within the database (e.g., to make sure you aren’t referring to tables that don’t exist and that you have permission to refer to those that do).

Provided that the database swallows this statement without any complaints, it will return some sort of database-specific data structure that encapsulates that parsed statement. It is this database-specific data structure that the DBI further encapsulates as a statement handle. Figure 5.1 shows this process more clearly.

Figure 5-1. Statement preparation data flow via DBI

It is through this DBI statement handle that you perform the remainder of the data-fetching cycle.

In DBI terms, the way to prepare a statement is to use the prepare() method, which is executed via a database handle. For example, a simple DBI program that creates a statement handle can be written as follows:

#!/usr/bin/perl -w # # ch05/prepare/ex1: Simply creates ...