Fetching results in Perl

The Perl DBI module provides methods for connecting to a database, preparing and issuing SQL statements, and fetching rows from result sets.

About this task

This procedure fetches results from an SQL query.

Restrictions

Because the Perl DBI module supports only dynamic SQL, you cannot use host variables in your Perl Db2® applications.

Procedure

To fetch results:

Create a database handle by connecting to the database with the DBI->connect statement.
Create a statement handle from the database handle.
For example, you can return the statement handle $sth from the database handle by calling the prepare method and passing an SQL statement as a string argument, as demonstrated in the Perl statement example:
```
   my $sth = $dbhandle->prepare( 
      'SELECT firstnme, lastname 
         FROM employee '
      );
```
Issue the SQL statement by calling the execute method on the statement handle. A successful call to the execute method associates a result set with the statement handle.
For example, you can run the statement prepared in the previous Perl statement by using the listed example:
```
   #Note: $rc represents the return code for the execute call
   my $rc = $sth->execute();
```
Fetch a row from the result set associated with the statement handle by calling the fetchrow method. The Perl DBI returns a row as an array with one value per column.
For example, you can return all of the rows from the statement handle in the previous example by using the listed Perl statement:
```
   while (($firstnme, $lastname) = $sth->fetchrow()) {
      print "$firstnme $lastname\n";
   }
```

Examples

The example shows how to connect to a database and issue a SELECT statement from an application written in Perl.

   #!/usr/bin/perl
   use DBI;

   my $database='dbi:DB2:sample';
   my $user='';
   my $password='';

   my $dbh = DBI->connect($database, $user, $password)
      or die "Can't connect to $database: $DBI::errstr";

   my $sth = $dbh->prepare( 
      q{ SELECT firstnme, lastname 
         FROM employee }
      )
      or die "Can't prepare statement: $DBI::errstr";

   my $rc = $sth->execute
      or die "Can't execute statement: $DBI::errstr";

   print "Query will return $sth->{NUM_OF_FIELDS} fields.\n\n";
   print "$sth->{NAME}->[0]: $sth->{NAME}->[1]\n";

   while (($firstnme, $lastname) = $sth->fetchrow()) {
      print "$firstnme: $lastname\n";
   }

   # check for problems that might have terminated the fetch early
   warn $DBI::errstr if $DBI::err;

   $sth->finish;
   $dbh->disconnect;