Client 3Processing Queries

Client 3Processing Queries

Now, let's turn our attention to query processing. DBI treats SELECT commands and non- SELECT commands differently. Commands other than SELECT require less-complex processing, so let's look at those first. Listing 14.6 shows the source code for client3a :

Listing 14.6. client3a.pl

1 #!/usr/bin/perl -W
 2 #
 3 #  Filename: client3a.pl
 4 #
 5
 6 use strict;
 7 use DBI;
 8
 9 my $dbh = DBI->connect( "dbi:Pg:", undef, undef, {PrintError => 0} )
10   or die "Can't connect to PostgreSQL: $DBI::errstr ($DBI::err)\n";
11
12 my $rows = $dbh->do( $ARGV[0] );
13
14 if( !defined( $rows )) {
15     print( $dbh->errstr."(".$dbh->err().")\n" );
16 }
17 else {
18     print( "Ok: $rows rows affected\n" );
19 }
20
21 $dbh->disconnect();

After successfully connecting to the database (lines 9 and 10), you can use the $dbh->do() method to execute a command. In this example, the command that client3 a executes is specified on the command line. The do() method executes a single SQL command and returns something . I know that sounds a little vague, but do() encodes a lot of information in its return valuelet's see what kinds of information you can discern from the return code.

If the command fails, do() returns undef , and you can interrogate the $dbh->errstr and $dbh->err values to find out what went wrong.

If you execute a command such as CREATE TABLE , ANALYZE , or GRANT , do() will return -1 to indicate success.

If you use do() to execute a command such as DELETE or UPDATE , do() will return the number of rows affected by the command. However, if the command affects zero rows, do() will return the string 0E0 . I'll tell you why in just a moment. First, let's run this program and see what happens when you execute a few commands:

$ chmod a+x ./client3a.pl
$ ./client3a.pl "GRANT SELECT ON tapes TO bruce"
Ok: -1 rows affected

That behaves as advertised. No data rows were affected, so do() returns -1 .

If you are following along with me, be sure you have a backup before you execute the next commandit deletes all rows from the tapes table.

./client3a.pl "DELETE FROM tapes"
Ok: 5 rows affected

In this case, you deleted five rows from the tapes table, so do() returned 5 . Now, let's see what happens when an error occurs:

./client3a.pl "DELETE FROM ship"
ERROR:  Relation "ship" does not exist(7)

This time, the table name is misspelled , so the do() method returned undef . We catch this condition at line 14 of client3a.pl , and print the error message (and error code) at line 15.

Now, let's see what the 0E0 business is all about:

./client3a.pl "DELETE FROM tapes where tape_id <> tape_id" 
Ok: 0E0 rows affected

This time, I've fed do() a command that can't possibly affect any rows (it is impossible for tape_id to not be equal to tape_id in any given row). It is not considered an error for a DELETE command (or an UPDATE command) to affect zero rows, so we don't want do() to return undef . Instead, do() returns the mysterious string 0E0 . If you haven't figured it out yet, 0E0 is the same thing as 0x10 . In other words, 0E0 is written in Perl's dialect of exponential notation. Why doesn't do() just return ? Because the string is interpreted as False in a logical expression. If you wrote code like this:

...
$row_count = $dbh->do("DELETE * FROM tapes WHERE tape_id <> tape_id");

if( $row_count ) {
  print( "Ok, $row_count rows affected\n" );
}
else {
  print( "Yeow! Something bad just happened\n" );
}
...

you would be reporting an error if the command affected zero rows. So instead, do() returns 0E0 , which is not interpreted as False . In this way, do() returns False only when an error occurs. Perl programmers think a little differently?

It's easy to translate the 0E0 into a more palatable : just add . For example:

...
$row_count = $dbh->do("DELETE * FROM tapes WHERE tape_id <> tape_id");

if( $row_count ) {
  print( "Ok, " . $row_count+0 . " rows affected\n" );
}
else {
  print( "Yeow! Something bad just happened\n" );
}
...

Be sure that you add after checking for undef ( undef+0 equals ).

Enough of that. Let's move on to SELECT execution now.

Executing a SELECT command is more complex than executing other commands because you need a way to process the result set. The DBI package uses a two-step, pre pare/execute model for processing SELECT commands. Listing 14.7 shows the basic steps required to process a SELECT command.

Listing 14.7. client3b.pl

1 #!/usr/bin/perl -W
 2 #
 3 #  Filename: client3b.pl
 4 #
 5 use strict;
 6 use DBI;
 7
 8 my $dbh = DBI->connect("dbi:Pg:", undef, undef, {PrintError => 1})
 9  or die "Can't connect to PostgreSQL: $DBI::errstr ($DBI::err)\n";
10 
11 my $sth = $dbh->prepare( $ARGV[0] );
12 
13 if( defined( $sth )) {
14     if( $sth->execute()) {
15         $sth->dump_results();
16     }
17 }
18 
19 $dbh->disconnect();

Line 11 prepares a command for execution (the command is taken from the first command-line argument). The prepare() method returns a statement handle, or undef if an error is encountered . Note that I have enabled PrintError in this example to simplify the code a little. If the command is successfully prepared, client3b calls the $sth->execute() method to actually carry out the query. At line 15, client3b takes a real short shortcut. The dump_results() method prints the result set associated with your statement handle. I call this a shortcut because you probably won't want to use this method except in quick-and-dirty programs or as an aid to debugging. If you run this application, I think you'll see what I mean:

$ chmod a+x client3b.pl
$ ./client3b.pl "SELECT * FROM customers"
'1', 'Jones, Henry', '555-1212', '1970-10-10'
'2', 'Rubin, William', '555-2211', '1972-07-10'
'3', 'Panky, Henry', '555-1221', '1968-01-21'
'4', 'Wonderland, Alice N.', '555-1122', '1969-03-05'
'5', 'Funkmaster, Freddy', '555-FUNK', undef
'7', 'Gull, Jonathan LC', '555-1111', '1984-02-05'
'8', 'Grumby, Jonas', '555-2222', '1984-02-21'
7 rows

All the data shows up, but dump_results() didn't do a very nice job of formatting the results. I'll show you how to fix that a little later. For now, let's go back and talk about some of the things that you can do between the call to prepare() and the call to execute() .

The Prepare/Execute Model

In earlier chapters, I explained that the prepare/execute model is useful for two different reasons: performance and simplicity.

Some database systems (including recent versions of PostgreSQL) gain a performance boost by using prepare/execute. In the prepare phase, the client application constructs a query (or other command) that includes placeholders ^[4] for actual data values. For example, the command INSERT INTO tapes VALUES( ?,? ) contains two placeholders (the question marks). This parameterized command is sent to the server. The server parses the command, prepares an execution plan, and returns any error messages to the client.

^[4] Placeholders are also known as parameter markers.

Before a prepared command can be executed, you must bind each placeholder. Binding a parameter creates a connection between a placeholder and a valuein other words, binding gives a value to a placeholder. After all the placeholders have been bound, you can execute the command.

The performance gain is realized from the fact that you can execute a prepared command over and over again, possibly providing different placeholder values each time. The server may not have to parse the command and formulate an execution plan once the command has been prepared.

The second advantage offered by the prepare/execute model is applicable to PostgreSQL. By splitting command processing into multiple pieces, you can factor your code for greater simplicity. For example, you may want to place the code that generates a command into one method, the code to compute and bind parameter values in a second method, and the code to process results in a third methodfor example:

...

prepare_insert_tapes_command( $sth );

while( defined( $line = <STDIN> )) {
  bind_tape_values( $sth, chomp( $line ));
  execute_insert_tapes( $sth );
}
...

In this code snippet, you prepare an INSERT command once, and bind and execute it multiple times.

Listing 14.8 shows client3c.pl . When you run this client, you can include a parameterized command on the command line, and it will prompt you to supply a value for each placeholder.

Listing 14.8. client3c.pl

1 #!/usr/bin/perl -W
 2 #
 3 #  Filename: client3c.pl
 4 #
 5 use strict;
 6 use DBI;
 7
 8 my $dbh = DBI->connect("dbi:Pg:", undef, undef, {PrintError => 1})
 9   or die "Can't connect to PostgreSQL: $DBI::errstr ($DBI::err)\n";
10
11 $dbh->do( "SET TRANSFORM_NULL_EQUALS TO ON" );
12
13 my $sth = $dbh->prepare( $ARGV[0] );
14
15 if( defined( $sth )) {
16
17     get_params( $sth );
18
19     if( $sth->execute()) {
20         $sth->dump_results();
21     }
22 }
23 
24 $dbh->disconnect();
25
26 #
27 #  subroutine: get_params( $sth )
28 #
29 sub get_params
30 {
31     my $sth             = shift;
32     my $parameter_count = $sth->{NUM_OF_PARAMS};
33     my $line            = undef;
34
35     for( my $i = 1; $i <= $parameter_count; $i++ ) {
36         print( "Enter value for parameter $i: " );
37
38         chomp( $line = <STDIN> );
39
40         if( length( $line )) {
41             $sth->bind_param( $i, $line );
42         }
43         else {
44             $sth->bind_param( $i, undef );
45         }
46     }
47 }

After connecting to the database, client3c executes the command SET TRANSFORM__NULL_EQUALS TO ON . This command allows you to write WHERE ... = NULL when you should really write WHERE ... IS NULL . I know that sounds a little mysterious right now, but I'll show you why you want to do that in a moment. At line 13, client3c prepares the statement entered on the command line. If that succeeds, you can call the get_params() method (described next) to prompt the user for parameter values. Then, client3c wraps up by executing the prepared command and dumping the results.

The get_params() method (line 29) prompts the user for a value for each placeholder in the command. How do you know how many placeholders appear in the command? The statement handle has a number of attributes that you can query once the command has been prepared. One of these attributes ( NUM_OF_PARAMS ) contains the number of placeholders on the command. The for loop starting at line 35 executes once for each placeholder. After printing a prompt, get_params() reads one line from STDIN and strips off the terminator ( new-line ). If the user enters something, get_params() calls bind_param() to bind the string entered by the user to the current parameter. If the user doesn't enter anything (that is, he just presses the Return key), get_params() binds undef to the current parameter. When you bind undef to a placeholder, you are effectively setting the parameter to NULL .

Let's run this script a few times. First, execute a command that does not include any placeholders:

$ chmod a+x client3c.pl
$ $ ./client3c.pl "SELECT * FROM customers WHERE customer_id = 2"
'2', 'Rubin, William', '555-2211', '1972-07-10'
1 rows

Now, try one that includes a parameter marker:

$ ./client3c.pl "SELECT * FROM customers WHERE customer_id = ?"
Enter value for parameter 1: 2
'2', 'Rubin, William', '555-2211', '1972-07-10'
1 rows

Finally, see what happens when you don't enter a parameter value:

$ ./client3c.pl "SELECT * FROM customers WHERE birth_date = ?"
Enter value for parameter 1: 
'5', 'Funkmaster, Freddy', '555-2132', undef
1 rows

Because get_params() binds undef to this parameter (see line 44), you are executing the command SELECT * FROM customers WHERE birth_date = NULL . Normally, that would not be considered a valid command ( NULL is never equal to anything), but at the beginning of this script, you enabled PostgreSQL's TRANSFORM_NULL_EQUALS runtime parameter.

Metadata and Result Set Processing

Now, I'd like to revisit the issue of result set processing. In earlier examples, you've been using dump_results() to avoid dealing with too many details at once.

After you call the execute() method, you can access the result set and metadata about the result set through the statement handle.

You can use any of three methods to process individual rows within the result set: fetchrow_arrayref() , fetchrow_array() , or fetchrow_hashref() . A fourth method, fetchall_arrayref(), returns a reference to an array that contains a reference to each row.

Let's look at each of these methods in detail.

fetchrow_arrayref() returns a reference to an array containing the values for the next row in the result set. When you reach the end of the result set, fetchrow_arrayref() returns undef . fetchrow_arrayref() will also return undef if an error occursyou have to check $sth->err() to distinguish between an error and the end of the result set.

Each element of the array returned by fetchrow_arrayref() contains a value that corresponds to a column in the result set. If a row contains NULL values, they are represented by undef values in the array. Listing 14.9 shows a script that processes a result set using the fetchrow_arrayref() method.

Listing 14.9. client3d.pl

1 #!/usr/bin/perl
 2 #
 3 #  Filename: client3d.pl
 4 #
 5 use strict;
 6 use DBI;
 7
 8 my $dbh = DBI->connect("dbi:Pg:", undef, undef, {PrintError => 1})
 9   or die "Can't connect to PostgreSQL: $DBI::errstr ($DBI::err)\n";
10
11 my $sth = $dbh->prepare( $ARGV[0] );
12
13 if( defined( $sth )) {
14     if( $sth->execute()) {
15         print_results( $sth );
16     }
17 }
18
19 $dbh->disconnect();
20
21 #
22 #  subroutine: print_results( $sth )
23 #
24 sub print_results
25 {
26     my $sth = shift;
27
28     while( my $vals = $sth->fetchrow_arrayref()) {
29         foreach my $val ( @$vals ) {
30             print( $val . "\t" );
31         }
32         print( "\n" );
33     }
34 }

The interesting part of this script is the print_results() subroutine (lines 24 through 34). This method loops through the result set by calling fetchrow_arrayref() to retrieve one row at a time. print_results() loops through each value in the array and print the contents. When you run this script, you will see the result set printed in a format similar to that produced by the dump_results() method:

$ chmod a+x client3d.pl
$ ./client3d.pl "SELECT * FROM customers"
1       Jones, Henry    555-1212        1970-10-10
2       Rubin, William  555-2211        1972-07-10
3       Panky, Henry    555-1221        1968-01-21
4       Wonderland, Alice N.    555-1122        1969-03-05
7       Gull, Jonathan LC       555-1111        1984-02-05
8       Grumby, Jonas   555-2222        1984-02-21

It's important to understand that fetchrow_arrayref() does not return an array; it returns a reference to an array. In fact, fetchrow_arrayref() happens to return a reference to the same array each time you call it. This means that each time you call fetchrow_arrayref() , the values from the previous call are overwritten by the next row.

You can see this by modifying the print_results() subroutine to save each reference returned by fetchrow_arrayref() , as shown in Listing 14.10.

Listing 14.10. print_results_and_saved_references

...
sub print_results_and_saved_references
{
    my $sth = shift;
    my @saved_refs;

    while( my $vals = $sth->fetchrow_arrayref()) {
        foreach my $val ( @$vals ) {
            print( $val . "\t" );
        }
        print( "\n" );
        push( @saved_refs, $vals );
    }

    print( "Saved References:\n" );

    foreach my $vals ( @saved_refs ) {
        foreach my $val( @$vals ) {
            print( $val . "\t" );
        }
        print( "\n" );
    }
}
...

In this version of print_results() , you add each reference returned by fetchrow__arrayref() to your own @saved_refs array. After you finish processing the result set, go back and print the contents of @saved_refs . Now the output looks like this:

1       Jones, Henry    555-1212        1970-10-10
2       Rubin, William  555-2211        1972-07-10
3       Panky, Henry    555-1221        1968-01-21
4       Wonderland, Alice N.    555-1122        1969-03-05
7       Gull, Jonathan L        1984-02-05
8       Grumby, Jonas   555-2222        1984-02-21
Saved References:
8       Grumby, Jonas   555-2222        1984-02-21
8       Grumby, Jonas   555-2222        1984-02-21
8       Grumby, Jonas   555-2222        1984-02-21
8       Grumby, Jonas   555-2222        1984-02-21
8       Grumby, Jonas   555-2222        1984-02-21
8        Grumby, Jonas     555-2222         1984-02-21

You can see that there were six rows in this result set, so you saved six references in @saved_refs . When you print the contents of @saved_refs , you can see that all prior results have been overwritten by the last row in the result set. This is because fetchrow_arrayref() uses a single array per statement handle , no matter how many rows are in the result set.

In contrast, fetchrow_array() returns a new array each time you call it (except, of course, when you encounter an error or the end of the result set; then fetchrow_array() returns undef ). Listing 14.11 shows how to process a result set using the fetchrow_array() method.

Listing 14.11. print_results_using_fetchrow_array

...
sub print_results_using_fetchrow_array
{
    my $sth = shift;

    while( my @vals = $sth->fetchrow_array()) {
        foreach my $val ( @vals ) {
            print( $val . "\t" );
        }
        print( "\n" );
    }
}
...

In some circumstances, it's easier to work with a hash than with an array. The fetchrow_hashref() method fetches the next result set row into a hash and returns a reference to the hash. Listing 14.12 shows how to process a result set using fetchrow_hashref() .

Listing 14.12. print_results_using_fetchrow_hashref

...
sub print_results_using_fetchrow_hashref
{
    my $sth = shift;

    while( my $vals = $sth->fetchrow_hashref()) {
        foreach my $key ( keys( %$vals )) {   
            print( $vals->{$key} . "\t" );     
        }
        print( "\n" );
    }
}
...

Each key in the hash is a column name. For example, if you execute the command SELECT * FROM customers, you will find the following keys:

customer_name
birth_date
id
phone

There are a couple of points to be aware of when using fetchrow_hashref() . First, the order of the column names returned by keys() is random ^[5] . If you feed the same result set to print_results_using_fetchrow_hashref() and print_results_using_fetchrow_array() , you will see the same values, but the columns are not likely to be displayed in the same left-to-right order. Second, if a result set contains two or more columns with the same name, all but one value will be discarded. This makes a lot of sense because a hash cannot contain duplicate keys. You might encounter this problem when a query includes computed columns and you forget to name the columns (using AS ). This problem can also occur when you join two or more tables and SELECT the common columns. For example:

^[5] Random, but consistent. It is extremely likely that the column names will appear in the same order during the processing of the entire result set. If the ordering is important, you should really be using an array in the first place, not a hash.

./client3d_hashref.pl "
> SELECT
>   datname, blks_read*8192, blks_hit*8192
> FROM
>   pg_stat_database"
0          perf
0          template1
0          template0
235732992  movies

Notice that you requested three values, but you see only two of them. The column name for blks_read*8192 and blks_hit*8192 is the same:

?column?

So, one of the columns is discarded by fetchrow_hashref() , and you can't predict which one will be thrown out. If you give a unique name to each column, you will see all three results:

./client3d_hashref.pl "
> SELECT
>   datname, blks_read*8192 AS Read, blks_hit*8192 AS Hit
> FROM
>   pg_stat_database"
perf    0       0
template1       0       0
template0       0       0
movies  243728384       3661824

That fixes one bug, but now you have a new problem. This table is difficult to read; it doesn't have column headers and there is no vertical alignment. Let's fix both of those problems.

Listings 14.13 through 14.18 show the client3e.pl script. This client is (almost) capable of executing an arbitrary query and printing a nicely formatted result set. There's still one problem left in this client, and I'll show you how to fix it in a moment.

Listing 14.13 shows the mainline code for client3e.pl :

Listing 14.13. client3e.pl

1 #!/usr/bin/perl
 2 #
 3 #  Filename: client3e.pl
 4 #
 5 use strict;
 6 use DBI;
 7
 8 my $dbh = DBI->connect("dbi:Pg:", undef, undef, {PrintError => 1})
 9   or die "Can't connect to PostgreSQL: $DBI::errstr ($DBI::err)\n";
10
11 my $sth = $dbh->prepare( $ARGV[0] );
12
13 if( defined( $sth )) {
14   if( $sth->execute()) {
15     my($widths, $row_values) = compute_column_widths( $sth );
16     print_column_headings( $sth, $widths );
17     print_results( $row_values, $widths );
18   }
19 }
20
21 $dbh->disconnect();

After connecting to the database, preparing the command, and executing it, you are ready to print the results. First, call compute_column_widths() (see Listing 14.14) to figure out how wide each column should be. Next, print the column headings, and finally print the results.

Listing 14.14. client3e.pl-compute_column_widths

23 #
24 #  subroutine: compute_column_widths( $sth )
25 #
26 sub compute_column_widths
27 {
28   my $sth   = shift;
29   my $names = $sth->{NAME};
30   my @widths;
31
32   for( my $col = 0; $col < $sth->{NUM_OF_FIELDS}; $col++ ) {
33     push( @widths, length( $names->[$col] ));
34   }
35
36   my $row_values = $sth->fetchall_arrayref();
37
38   for( my $col = 0; $col < $sth->{NUM_OF_FIELDS}; $col++ ) {
39     for( my $row = 0; $row < $sth->rows(); $row++ ) {
40       if( defined( $row_values->[$row][$col] )) {
41         if( length( $row_values->[$row][$col] ) > $widths[$col] ) {
42           $widths[$col] = length( $row_values->[$row][$col] );
43         }
44       }
45     }
46   }
47
48   return( \@widths, $row_values );
49 }

Listing 14.14 shows the compute_column_widths() subroutine. There's a lot of new stuff going on in this subroutine. First, you use the statement handle to retrieve two pieces of metadata. At line 29, compute_column_widths() uses the {NAME} attribute to find column names. {NAME} is a reference to an array of column names ^[6] . DBI also provides the {NAME_lc} and {NAME_uc} attributes, in case you want the column names to appear in lowercase or uppercase, respectively. The {NUM_OF_FIELDS} attribute returns the number of columns (or fields, if you prefer) in the result set. {NUM_OF_FIELDS} will return 0 for commands other than SELECT.

^[6] Some database drivers may include undef column names in the {NAME} array. The DBD::Pg never includes undefined column names.

At lines 32 through 34, compute_columns_widths() loops through each column in the result set and inserts the length of the column name into the widths array. When it finishes the loop, you have an array with {NUM_OF_FIELDS} entries, and each entry in this array contains the length of the corresponding column name.

I mentioned earlier that there are four methods that you can use to walk through a result set. The first three, fetchrow_array() , fetchrow_arrayref() , and fetchrow_hashref() , process a result set one row at a time. The fourth method, fetchall_arrayref() , gives us access to the entire result set at once. We use fetchall_arrayref() at line 36. This method returns a reference to an array of references: one reference for each row in the result set. Think of fetchall__arrayref() as returning a two-dimensional array. For example, to get the value returned in the fourth column of the third row, you can use the syntax $row__values->[3][4] .

After you have a reference to the entire result set, compute_column_widths() loops through every row and every column (lines 38 through 46), finding the widest value for each column.

There's another piece of metadata buried in this loop. At line 39, this script calls $sth->rows() method to determine how many rows are in the result set.

compute_column_widths() returns two values. The first value is a reference to the @widths array. The second value returned by this method is the reference to the result set.

You may be thinking that it's kind of silly to return the result set reference from this subroutine; why not just call fetchall_arrayref() again when you need it? You can't. After a command has been executed, you can fetch the results only once. Of course, you can access the result set as many times as you like; you just can't fetch any given row more than once.

Now, let's look at the pad() subroutine (see Listing 14.15).

Listing 14.15. client3e.pl-pad

51 #
52 #  subroutine: pad( $val, $col_width, $pad_char )
53 #
54 sub pad
55 {
56   my( $val, $col_width, $pad_char ) = @_;
57   my $pad_len;
58
59   $val      = "" if ( !defined( $val ));
60   $pad_char = " " if( !defined( $pad_char ));
61   $pad_len  = $col_width - length( $val );
62
63   return( $val . $pad_char x $pad_len . " " );
64
65 }

The pad() subroutine simply pads the given value ( $val ) to $col_width characters . If the given value is undef , meaning that it is a NULL value from the result set, pad() translates it into an empty string for convenience. The optional $pad_char parameter determines the pad character. If the caller does not provide a $pad_char , pad() will pad with spaces.

Listing 14.16 shows the print_column_headings() subroutine.

Listing 14.16. client3e.pl-print_column_headings

67 #
68 #  subroutine: print_column_headings( $sth )
69 #
70 sub print_column_headings
71 {
72   my $sth    = shift;
73   my $widths = shift;
74   my $names  = $sth->{NAME};
75
76   for( my $col = 0; $col < $sth->{NUM_OF_FIELDS}; $col++ ) {
77     print( pad( $names->[$col], $widths->[$col] ));
78   }
79
80   print( "\n" );
81
82   for( my $col = 0; $col < $sth->{NUM_OF_FIELDS}; $col++ ) {
83     print( pad( "-", $widths->[$col], "-" ));
84   }
85
86   print( "\n" );
87 }

The print_column_headings() subroutine prints properly aligned column headings. The first loop (lines 76 through 78) prints each column name, padded with spaces to the width of the column. The second loop (lines 82 through 84) prints a string of dashes under each column name.

The print_results() subroutine is shown in Listing 14.17.

Listing 14.17. client3e.pl-print_results

89 #
 90 #  subroutine: print_results( )
 91 #
 92 sub print_results
 93 {
 94   my( $rows, $widths ) = @_;
 95 
 96   for( my $row = 0; $row < $sth->rows(); $row++ ) {
 97     for( my $col = 0; $col < $sth->{NUM_OF_FIELDS}; $col++ ) {
 98       print( pad( $rows->[$row][$col], $widths->[$col] ));
 99     }
100     print( "\n" );
101   }
102 }

Finally, print_results() prints the entire result set. Use the widths array ( constructed by compute_column_widths() ) to pad each value to the appropriate width.

Now let's run this script a few times:

$ chmod a+x ./client3e.pl
$ ./client3e "SELECT * FROM customers";
id customer_name        phone    birth_date 
-- -------------------- -------- ---------- 
1  Jones, Henry         555-1212 1970-10-10 
2  Rubin, William       555-2211 1972-07-10 
3  Panky, Henry         555-1221 1968-01-21 
4  Wonderland, Alice N. 555-1122 1969-03-05 
8  Grumby, Jonas        555-2222 1984-02-21 
7  Gull, Jonathan LC             1984-02-05

That looks much better; all the columns line up nicely and you can finally see the column names.

Now how does this client react when you give it a bad table name?

$ ./client3e.pl "SELECT * FROM ship"     
DBD::Pg::st execute failed: ERROR:  Relation "ship" does not 
exist at ./client3e.pl line 14.

That's not the prettiest error message, but it certainly does tell you what's wrong and even where in your code the error occurs.

What happens if you try to execute a command other than SELECT ?

$ ./client3e.pl "INSERT INTO tapes VALUES( 'JS-4820', 'Godzilla' )"
DBD::Pg::st fetchall_arrayref failed: no statement executing at
 ./client3e.pl line 36.

That's not so good. You can't use fetchall_arrayref() or any of the fetch() methods, unless the command that you execute returns a result set. Notice that you got all the way to line 36 before you ran into an error. That's an important pointyou can still use prepare() and execute() to executed non- SELECT commands, you just can't fetch from a nonexistent result set.

Listing 14.18 presents a new version of the client3e.pl mainline that fixes the problem.

Listing 14.18. client3e.pl-modified mainline

1 #!/usr/bin/perl -W
 2 #
 3 #  Filename: client3e.pl
 4 #
 5 use strict;
 6 use DBI;
 7
 8 my $dbh = DBI->connect("dbi:Pg:", undef, undef, {PrintError => 1})
 9   or die "Can't connect to PostgreSQL: $DBI::errstr ($DBI::err)\n";
10
11 my $sth = $dbh->prepare( $ARGV[0] );
12
13 if( defined( $sth )) {
14   if( $sth->execute()) {
15     if( $sth->{NUM_OF_FIELDS} == 0 ) {
16         print($sth->{pg_cmd_status} . "\n" );
17     }
18     else {
19       my($widths, $row_values) = compute_column_widths( $sth );
20       print_column_headings( $sth, $widths );
21       print_results( $row_values, $widths );
22     }
23   }
24 }
25
26 $dbh->disconnect();

You distinguish between SELECT commands and other commands by interrogating $sth->{NUM_OF_FIELDS} . If {NUM_OF_FIELDS} returns , you can safely assume that you just executed some command other than SELECT . If {NUM_OF_FIELDS} returns anything other than , you know that you just executed a SELECT command.

You can't use $sth->rows() to determine the command type. When you execute a SELECT command, $sth->rows() returns the number of rows in the result set. When you execute an INSERT , UPDATE , or DELETE command, $sth->rows() returns the number of rows affected by the command. For all other command types, $sth->rows() will return -1 .

If you've just executed an INSERT or UPDATE command, you can call $dbh->last_insert_id() to retrieve the value of the sequence assigned to the new row ( assuming that the row does in fact contain a sequence column). $dbh->last_insert_id() comes in two flavors. Use the first variant if you know the name of the sequence:

$lastID = $dbh->last_insert_id( undef, undef, undef, undef, {sequence=>

name

} );

The first four arguments are ignored by DBD::Pg (but may be used by other drivers). The last argument must be an attribute that specifies the name of the sequence.

Use the second variant when you know the name of the table but not the name of the sequence:

$lastID = $dbh->last_insert_id( undef, $schema, $table, undef );

The first and last arguments are always ignored by DBD::Pg. The $schema and $table arguments ($schema by be undef ) specify the table that you're interested in. DBD::Pg tries to find a UNIQUE , NOT NULL column in that table whose default value is based on a sequence. If it finds such a column, last_insert_id() returns the value of the sequence. If the given table contains two or more sequences, last_insert_id() returns the sequence used by the PRIMARY KEY (assuming that it can find a sequence-based PRIMARY KEY ).

In general, you should use the first form of last_insert_id() when you can. The second form forces DBD::Pg to search through the PostgreSQL dictionary to find the correct sequence and that can really slow things down (the second form can also produce an unexpected result when it encounters a table that contains two or more sequence-based columns).

Other Statement and Database Handle Attributes

At line 16, client3e uses a nonstandard extension to the DBI statement handle: pg_cmd_status . The PostgreSQL DBI driver adds four PostgreSQL-specific attributes to the statement handle. pg_cmd_status returns the standard PostgreSQL command status. For example, when you INSERT a new row, the command status is the word INSERT , followed by the OID of the new row, and then the number of rows affected:

$ psql -d movies
movies=# INSERT INTO tapes VALUES 
movies-# ( 
movies(#   'KL-24381', 'The Day The Earth Stood Still'
movies(# );
INSERT 510735 1

Now, when you run client3e.pl (with the new code in place), you see that non- SELECT commands are handled properly:

$ ./client3e.pl "INSERT INTO tapes VALUES( 'JS-4820', 'Godzilla' )"
INSERT 510736 1

$ ./client3e.pl "DELETE FROM tapes WHERE tape_id = 'JS-4820'"
DELETE 1

The other three statement handle extensions are pg_size , pg_type , and pg_oid_status .

The pg_size attribute returns a reference to an array that contains the size of each column in the result set. The size of a variable-length column is returned as -1 . In most cases, this information is not terribly useful because it represents the size of each column on the server , not the actual amount of data sent to the client. If you need to know the width of a column, you'll have to compute it by hand as you did in the compute_column_widths() function.

pg_type is a little more useful than pg_size . pg_type returns a reference to an array that contains the name of the data type of each column in the result set. Note that pg_type does not understand user-defined data types and will return the string " unknown " for such columns.

The pg_oid_status attribute returns the OID (object-ID) of the new row after an INSERT command is executed. This attribute uses the libpq PQoidstatus() function and has the same limitations (namely, pg_oid_status returns a meaningful value only when an INSERT command creates a single new row).

The DBI API supports a few more statement handle attributes that are not well-supported (or not supported at all) by the PostgreSQL driver.

The {TYPE} attribute returns a reference to an array containing data type codes (one entry per result set column). The values returned by {TYPE} are intended to provide database-independent data type mappings. Currently, the DBD::Pg module maps PostgreSQL data types into the symbolic values shown in Table 14.2. All other PostgreSQL data types map to a numberthe OID (object id) for the type as defined in the pg_type system table. For example, the OID for the BOX data type is 603 the {TYPE} value for a BOX column is 603 .

Table 14.2. {TYPE} Mappings

PostgreSQL Data Type	Symbolic Name
BYTEA	SQL_BINARY
INT8	SQL_DOUBLE
INT2	SQL_SMALLINT
INT4	SQL_INTEGER
FLOAT4	SQL_NUMERIC
FLOAT8	SQL_REAL
BPCHAR	SQL_CHAR
VARCHAR	SQL_VARCHAR
DATE	SQL_DATE
TIME	SQL_TIME
TIMESTAMP	SQL_TIMESTAMP

{% if main.adsdop %}{% include 'adsenceinline.tpl' %}{% endif %}

The {PRECISION} , {SCALE} , and {NULLABLE} attributes are not supported by DBD::Pg. {PRECISION} returns the same value as {pg_size} , {SCALE} will return undef , and {NULLABLE} will return 2 (meaning unknown ).

Another statement handle attribute not supported by DBD::Pg is {CursorName} . Other drivers return the name of the cursor associated with statement handle (if any): the {CursorName} attribute in DBD::Pg returns undef . You can use cursors with the PostgreSQL driver, but you must do so explicitly by executing the DECLARE ... CURSOR , FETCH , and CLOSE commands.

As you know, PostgreSQL cursors can be used only within a transaction block. By default, a DBI database handle starts out in AutoCommit mode. When the {AutoCommit} attribute is set to 1 (meaning true ), all changes are committed as soon as they are made. If you want to start a transaction block, simply set {AutoCommit} to (meaning false ), and the DBD::Pg driver will automatically execute a BEGIN command for you. When you want to complete a transaction block, you can call $dbh->commit() or $dbh->rollback() . You should not try to directly execute COMMIT or ROLLBACK commands yourselfthe DBD::Pg driver will intercept those commands and reward you with an error message. The next client ( client4.pl ) lets you explore DBI transaction processing features interactively.