Programming the Perl DBI

Neat and Tidy Formatting

The DBI features a couple of utility functions that can be used to tidy up strings into a form suitable for easy reading. These two functions are neat() and neat_list(), the former operating on a single scalar value, the latter operating on a list of scalar values.

For example, to use neat() to tidy up some strings, you could write:

#!/usr/bin/perl -w
#
# ch04/util/neat1: Tests out the DBI::neat() utility function.
#

use DBI;

### Declare some strings to neatify
my $str1 = "Alligator's an extremely neat() and tidy person";
my $str2 = "Oh no\nhe's not!";

### Neatify this first string to a maxlen of 40
print "String: " . DBI::neat( $str1, 40 ) . "\n";

### Neatify the second string to a default maxlen of 400
print "String: " . DBI::neat( $str2 ) . "\n";

### Neatify a number
print "Number: " . DBI::neat( 42 * 9 ) . "\n";

### Neatify an undef
print "Undef: " . DBI::neat( undef ) . "\n";

exit;

which generates the output of:

String: 'Alligator's an extremely neat() and...'
String: 'Oh no
he's not!'
Number: 378
Undef:  undef

demonstrating that string values are quoted,[10] whereas values known to be numeric are not. The first string has been truncated to the desired length with ... added. Undefined values are recognized and returned as the string undef without quotes.

While the neat() function is handy for single values, the neat_list() function is handy for lists. It simply calls neat() on each element of the referenced list before joining the list of values together with the desired separator string. For example:

#!/usr/bin/perl -w
#
# ch04/util/neat2: Tests out the DBI::neat_list() utility function

use DBI qw( neat_list );

### Declare some strings to neatify
my @list = ( 'String-a-string-a-string-a-string-a-string',
              42, 0, '', undef );

### Neatify the strings into an array
print neat_list( \@list, 40, ", " ), "\n";

exit;

which generates the output of:

'String-a-string-a-string-a-string-a...', 42, 0, '', undef

This example also shows that the utility functions can be imported into your package so you can drop the DBI:: prefix.

DBI uses neat() and neat_list() internally to format the output generated by tracing. That's important to know if you're wondering why the trace output is truncating your huge SQL statements down to 400 characters.[11]

Numeric Testing

The final utility function supplied by DBI that we'll look at is quite a curious one called looks_like_number(). This function quite simply tells you whether or not a value looks like a number or not.

looks_like_number() operates by taking a list of values as an argument and returns a new array signifying whether or not the corresponding value within the original array was a number, not a number, or undefined.

This may seem rather a curious thing to want to do, but in the case of handling large quantities of data, it's useful for working out which values might need to have their quotes escaped via the quote() method.

The returned array will contain the same number of values as the original data array, with the elements containing one of three values signifying the following:

true        The original value is a number.
false       The original value is not a number.
undef       The original value is empty or undefined.

The following example illustrates how this process works:

#!/usr/bin/perl -w
#
# ch04/util/lookslike1: Tests out the DBI::looks_like_number()
                        function.
#

use DBI;

### Declare a list of values
my @values = ( 333, 'Choronzon', 'Tim', undef, 'Alligator',
               1234.34, 'Linda', 0x0F, '0x0F', 'Larry Wall' );

### Check to see which are numbers!
my @areNumbers = DBI::looks_like_number( @values );

for (my $i = 0; $i < @values; ++$i ) {

    my $value = (defined $values[$i]) ? $values[$i] : "undef";

    print "values[$i] -> $value ";

    if ( defined $areNumbers[$i] ) {
        if ( $areNumbers[$i] ) {
            print "is a number!\n";
        }
        else {
            print "is utterly unlike a number and should be
                   quoted!\n";
        }
    }
    else {
        print "is undefined!\n";
    }
}

exit;

The results from this program illustrate how the values are treated and shows that hexadecimal values are not treated as numbers:

values[0] -> 333 is a number!
values[1] -> Choronzon is utterly unlike a number and should
             be quoted!
values[2] -> Tim is utterly unlike a number and should
             be quoted!
values[3] -> undef is undefined!
values[4] -> Alligator is utterly unlike a number and should
             be quoted!
values[5] -> 1234.34 is a number!
values[6] -> Linda is utterly unlike a number and should
             be quoted!
values[7] -> 15 is a number!
values[8] -> 0x0F is utterly unlike a number and should
             be quoted!
values[9] -> Larry Wall is utterly unlike a number and should
             be quoted!

The first 0x0F in the list of values is reported as looking like a number because Perl converted it into one (15) when the script was compiled. The second is not reported as looking like a number because the looks_like_number() function only looks for integers and floating-point numbers.

And that brings us to the end of the introduction to DBI and its architecture. We'll be talking more on how to actually do stuff with DBI in the next chapter.

---

Page 7 of 8