Statement Handle Methods

Statement Handle Methods

Before we execute prepared statements, we can bind IN and OUT parameters using the methods described in this section.

bind_param

The bind_param method binds parameters to SQL statements. Placeholders are numbered from 1 upwards. For example:

$sth = $dbh->prepare("SELECT * FROM emp 
                      WHERE ename LIKE ? OR ename LIKE ?");
  
$sth->bind_param(1, "MILLER");
$sth->bind_param(2, "K%");
  
$sth->execute;

You can also use named parameters, described at the end of the next section.

bind_param_inout

The bind_param_inout method, available under DBD::Oracle, helps us with PL/SQL. This method allows us to call procedures with OUT parameters. Let's see how it works.

First of all, let's create a simple Oracle procedure:

CREATE OR REPLACE PROCEDURE oracle_power (in_base IN NUMBER, 
                                          in_power IN NUMBER,
                                          out_result OUT NUMBER) IS
BEGIN
   out_result := POWER(in_base, in_power);
END;

Avoiding Perl's own 9**4 notation, we can now run the following code to work out 9 to the power of 4:

$sth = $dbh->prepare("BEGIN oracle_power(?, ?, ?); END;");
  
$sth->bind_param(1, 9); # 1st parameter, value 9
$sth->bind_param(2, 4); # 2nd parameter, value 4
  
$sth->bind_param_inout( 3, \$got_the_power, 50); # Notice reference! :)
$sth->execute;
  
print "got_the_power >", $got_the_power, "<\n";

We must supply bind_param_inout with an extra parameter, in this case 50, which is the maximum length of data we're expecting to receive back. If in doubt, opt for an XXL size here, as long as you have sufficient memory. The Perl code shown above returns the following result:
```
got_the_power >6561<
```

As an alternative to using question marks, you can also use named bound parameters with DBD::Oracle. In the following example, the procedure has been predefined like this:

CREATE OR REPLACE PROCEDURE 
   squarer ( in_number IN NUMBER, inout_result IN OUT NUMBER ) IS 
BEGIN
   inout_result := POWER(in_number, 2);
END;

Perl DBI code to use this procedure would look something like this:

my $in_number = 3;
my $inout_result;
  
$sth = $db->prepare(q{
  BEGIN
      squarer(:in_number, :inout_result);
  END;
                     });
  
# We bind here, then execute the call.
  
$sth->bind_param(":in_number", $in_number);
$sth->bind_param_inout(":inout_result", \$inout_result, 1);
  
$sth->execute;
print $inout_result;

execute

We've already strewn the execute method liberally throughout this chapter. It's used to execute prepared statements. It returns an undef on failure, or the number of rows affected if this information can be determined. Otherwise, it returns -1.

$rv = $sth->execute;

If bind variables are being used, we can send these in as an array, rather than binding them all explicitly with separate bind_param calls:

$rv = $sth->execute( @bound_values );

fetchrow_array

The fetchrow_array method fetches rows from an array. A popular FETCH command is illustrated in Figure B-2.

Figure B-2. A simple fetchrow_array example

Let's see what's going on here.

Working through Figure B-2, we first create our object with the connect method. (The diagram displays internal objects, but we never need to concern ourselves with these in our coding work.)
Next we prepare our statement handle.
We're then able to loop around the statement handle object using the fetchrow_array method, which creates a copy of each data row selected.
We finally disconnect the database handle, which leads to the destruction of associated DBI objects by the garbage memory collector.

fetchrow_arrayref

Going a step beyond fetchrow_array, the fetchrow_arrayref method is more efficient because instead of copying data rows into a local array, it provides a reference to the row of data already stored within the driver. It's a fairly straightforward operation to drill down from the reference into this remote array, as shown in Figure B-3.

Figure B-3. fetchrow_arrayref

fetchrow_hashref

In a similar manner, the fetchrow_hashref method (shown in Figure B-4) returns a reference to an anonymous hash for each row. This time the data is keyed on column name, rather than numeric index. It's best to give this method an optional string parameter of either NAME_uc or NAME_lc to ensure that column names are always in a preferred case; otherwise, they may be in mixed case, especially if you're using Perl DBI in a portable manner across different database types.

Figure B-4. fetchrow_hashref

fetchall_arrayref

Instead of accessing data one row at a time, you can get a single reference to access all of the data in one go (if you have the available memory). Use the fetchall_arrayref method, as shown in Figure B-5, which demonstrates the default use of fetchall_arrayref. A single key accesses a first-level array composed of reference keys to second-level arrays. There is one of these for each row of data found by the SELECT statement.

Figure B-5. fetchall_arrayref with array batches

Figure B-6 demonstrates a variation on this theme. An optional hash marks out the various column names required. This transforms the second-level arrays into hashes, accessed by column name rather than numeric index.

Figure B-6. fetchall_arrayref with hashes

fetchall_hashref

The fetchall_hashref method can only be used with SELECT statements containing unique key data combinations, such as single-column primary keys. This method creates a first-level hash possessing as many index keys as there are uniquely keyed rows. We can then drill down from this hash into second-level hashes, which contain all of the row data by column name. Figure B-7 illustrates the use of this method.

Figure B-7. fetchall_hashref with its key requirement

finish

The finish method deactivates an active SELECT statement handle, thus causing any pending unfetched data to be discarded. This can free up resources in the server, especially for large GROUP BY or ORDER BY queries. It is rarely used, however, because statement handles deactivate anyway after a last row is fetched. Nevertheless, you may want to use this method if you're only fetching a fixed number of rows from a statement (see the selectrow_array method mentioned later). You should also finish active statements before disconnecting from a database:

$rc  = $sth->finish;

rows

The rows method holds the number of rows processed by the statement; it returns -1 if it is unable to determine a figure. When you use this method with a SELECT statement, wait until all of the rows have been fetched before calling it, to get accurate results:

$rv = $sth->rows;

bind_col

For a more efficient way to access data, you can use the bind_col method and its partner, bind_columns. We simply bind in the variables we need to associate selected columns with, before calling a simple fetch command:

Let's examine the following code:

$sth = $dbh->prepare(" SELECT deptno, dname, loc FROM dept ");
$sth->execute;
  
$sth->bind_col(1, \$deptno); # Notice the use of referencing! :)
$sth->bind_col(2, \$dname);
$sth->bind_col(3, \$loc);
while ( $sth->fetch ) {                              # Simple fetch
   printf "%2d %14s %13s \n", $deptno, $dname, $loc;
}

When executed, this prints the following:

10     ACCOUNTING      NEW YORK
20       RESEARCH        DALLAS
30          SALES       CHICAGO
40     OPERATIONS        BOSTON

bind_columns

Using the bind_columns method, you can shrink those three bind_col lines into a single bind_columns call. Simply ensure that you have the same number of variables in the bind_columns call as there are fields to be selected, and also make sure you then get them in the right order. For example:

$sth->bind_columns(\$deptno, \$dname, \$loc);   # List of references

dump_results

This prototyping method fetches all the selected rows out from a statement, as comma-separated values, and prints them to STDOUT:

$sth->dump_results;