Database Handles — Preparation

There are two groups of database handle methods. The methods in the first group help prepare SQL statement handles or check that they can be prepared. The methods in the second group work with SQL statement executions or clean up afterward. In this section, we'll work through the first group, those that help prepare the SQL statement handles. Later in this chapter, after covering the statement handles themselves, we'll discuss the second group of database handle methods. Think of these two groups as being like artillery troops and assault troops. They're all wearing the same uniform, but some prepare the ground while others go in and do the real work.

ping

The ping method checks to see if the target database server is still running. It is useful for batch programs. If this function fails to return true, there is little point trying to use the handle. You need to reconnect to the database:

$rc = $dbh->ping;

prepare

The prepare method creates and prepares a statement handle for later execution:

$sth = $dbh->prepare("SELECT SYSDATE FROM DUAL");

prepare_cached

The prepare_cached method is similar in concept to connect_cached, as described earlier:

$sth = $dbh->prepare_cached($statement);

If the same parameters are re-sent to prepare_cached, it tries to use a cached $sth statement handle instead of creating a new one.

quote

The quote method provides an excellent utility for preparing DML statements:

$quoteString = $dbh->quote("Let's buy O'Reilly books!");  # :-)
print "quoteString >", $quoteString, "<\n";

This produces the following output:

quoteString >'Let''s buy O''Reilly books!'<

Notice the doubled single-quotes which make this string ideal for inserting into an Oracle table. Here's one we created earlier:

create table test_table (message_col varchar2(50));

The following code is now possible:

$dbh->{AutoCommit} = 0; # Turning AutoCommit off! :)
eval {
   $quoteString = $dbh->quote("Let's buy O'Reilly books!");
   $sth = $dbh->prepare("INSERT INTO test_table
                         VALUES ( $quoteString )");
   $sth->execute;
   $dbh->commit;
};
if ($@) {
   warn "Transaction failed: $@";
   $dbh->rollback;
}

The row inserts neatly into the database:

SQL> SELECT * from test_table;
  
MESSAGE_COL
--------------------------------------------------
Let's buy O'Reilly books!
  
SQL>

An undefined value, such as $dbh->quote($an_undefined_value), will be returned as the string NULL (without single quotation marks) to match how NULLs are represented in SQL.