Handles (Programming the Perl DBI)

4.2. Handles

The DBI defines three main types of objects that you may use to interact with databases. These objects are known as handles. There are handles for drivers, which the DBI uses to create handles for database connections, which, in turn, can be used to create handles for individual database commands, known as statements. Figure 4-1 illustrates the overall structure of the way in which handles are related, and their meanings are described in the following sections.

Figure 4-1. DBI handles

4.2.1. Driver Handles

Driver handles represent loaded drivers and are created when the driver is loaded and initialized by the DBI. There is exactly one driver handle per loaded driver. Initially, the driver handle is the only contact the DBI has with the driver, and at this stage, no contact has been made with any database through that driver.

The only two significant methods available through the driver handle are data_sources() , to enumerate what can be connected to, and connect() , to actually make a connection. These methods are more commonly invoked as DBI class methods, however, which we will discuss in more detail later in this chapter.

Since a driver handle completely encapsulates a driver, there's no reason why multiple drivers can't be simultaneously loaded. This is part of what makes the DBI such a powerful interface.

For example, if a programmer is tasked with the job of transferring data from an Oracle database to an Informix database, it is possible to write a single DBI program that connects simultaneously to both databases and simply passes the data backwards and forwards as needed. In this case, two driver handles would be created, one for Oracle and one for Informix. No problems arise from this situation, since each driver handle is a completely separate Perl object.

Within the DBI specification, a driver handle is usually referred to as $drh .

Driver handles should not normally be referenced within your programs. The actual instantiation of driver handles happens ``under the hood'' of DBI, typically when DBI->connect() is called.

4.2.2. Database Handles

Database handles are the first step towards actually doing work with the database, in that they encapsulate a single connection to a particular database. Prior to executing SQL statements within a database, we must actually connect to the database. This is usually achieved through the DBI's connect() method:

$dbh = DBI->connect( $data_source, ... );

The majority of databases nowadays tend to operate in a multiuser mode, allowing many simultaneous connections, and database handles are designed accordingly. An example might be if you wanted to write a stock-monitoring program that simultaneously monitored data in tables within different user accounts in the database. A DBI script could make multiple connections to the database, one for each user account, and execute SQL statements on each. Database handles are completely encapsulated objects, meaning that transactions from one database handle cannot ``cross-over'' or ``leak'' into another.

Database handles are children of their corresponding driver handle, which supports the notion that we could also make multiple simultaneous connections to multiple database types, as well as multiple simultaneous connections to databases of the same type. For example, a more complicated DBI script could make two connections to each of an Oracle and an Informix database to perform the above-mentioned monitoring. Figure 4-1, shown earlier, illustrates the capability of having multiple database handles connecting through a driver handle to an Oracle database.

Keep in mind that had the monitoring program been written in C, two copies of code would be required, one for Oracle's programming interface and one for Informix's. DBI levels the playing field.

Within the DBI specification and sample code, database handles are usually referred to as $dbh.

4.2.3. Statement Handles

Statement handles are the final type of object that DBI defines for database interaction and manipulation. These handles actually encapsulate individual SQL statements to be executed within the database.

Statement handles are children of their corresponding database handle. Since statement handles are objects in their own right, data within one statement is protected from tampering or modification by other statement handles.

For a given database handle, there is no practical limit to the number of statement handles that can be created and executed.[35] Multiple statements can be created and executed within one script, and the data can be processed as it returns. A good example of this might be a data-mining robot that connects to a database, then executes a large number of queries that return all sorts of different types of information. Instead of attempting to write convoluted SQL to correlate the information within the database, the Perl script fetches all the data being returned from the many statements and performs analysis there, using the fully featured text and data manipulation routines that Perl has to offer.

[35]In reality, the number of concurrent statement handles is dependent on the underlying database. For information on how many concurrent statement handles your database can support, see Appendix B, "Driver and Database Characteristics ".

Within the DBI specification and sample code, statement handles are generally referred to as $sth.