Database Handles — SQL and Cleanup

To save coding, we often combine database and statement handles using the methods described in this section. When we are finished executing SQL statements, we clean up with a database disconnection, also described in this section.

do

The do method is typically used to prepare and execute DML statements in one call. We can also use it in combination with bind parameters, as shown in the following example:

$dbh->{AutoCommit} = 0; # Turn it off! :)
  
$sth = $dbh->do("DELETE FROM test_table");  # Binds unnecessary
$dbh->commit;
  
$sth = 
   $dbh->do("INSERT INTO test_table values (?)",
            undef,                          # <= Can be Attributes 
            "It's worse than that Jim");    # Binding this 1st value
$dbh->commit;

Let's just check that:

SQL> select * from test_table;
  
MESSAGE_COL
--------------------------------------------------
It's worse than that Jim
  
SQL>

If the statement will be executed several times, it is often more efficient to carry out a single prepare, followed by many execute commands, to avoid constantly re-preparing the same DML statement.

selectrow_array

The selectrow_array method is a super-method that combines the prepare, execute, and fetchrow_array entries — all in one go. It generates an array consisting of the first row found:

@row_ary = $dbh->selectrow_array($statement);

selectall_arrayref

The selectall_arrayref alternative method uses fetchall_arrayref and thereby generates a reference to a first-level array containing references to however many second-level arrays are necessary to hold every row returned by the SELECT statement. It's a head twister — but in a good way. Here's an example; see the earlier Figure B-5 for more details.

$ary_ref = $dbh->selectall_arrayref($statement);

selectall_hashref

We use the fetchall_hashref method, this time as the final link in the selection chain, with selectall_hashref. You must supply a column key, as illustrated in the following example. See the earlier Figure B-7 for more details.

$hash_ref = $dbh->selectall_hashref($statement, $key); # Use key! :)

selectcol_arrayref

The selectcol_arrayref method returns a reference to an array containing the first field from each row:

$ary_ref = $dbh->selectcol_arrayref($statement);

Other columns can be pushed into the array via the Columns attribute. Groovy!

$ary_ref = 
   $dbh->selectcol_arrayref($select_statement, { Columns => [1,2] });

commit

The commit method commits transactions when AutoCommit is set to false:

$rc = $dbh->commit;

rollback

The rollback method rolls back transactions:

$rc = $dbh->rollback;

begin_work

This method switches AutoCommit off until either a commit or a rollback is encountered, thus completing a single explicit transaction. The AutoCommit behavior then reverts back to what it was previously.

disconnect

The disconnect method is typically seen just before the end of a program. It neatly closes down the database connection.

$rc = $dbh->disconnect or warn $dbh->errstr;

If you're using transactions, it is good practice to explicitly call either commit or rollback before disconnecting in order to keep your code clean and reliable.