EXAMPLES

Example 1 (fetchrow_hashref):
Example 2 (fetchrow_arrayref):
Example 3 (concatenation, fetchrow_hashref and substring comparison)

CHANGES
TO-DO
ACKNOWLEDGEMENTS
AUTHOR
COPYRIGHT

NAME

DBIx::FetchLoop - Fetch with change detection and aggregates

SYNOPSIS

  use DBIx::FetchLoop;

  $lph = DBIx::FetchLoop->new($sth, $dbi_method);

  $hash_ref = $lph->fetch_current_data;

  $lph->set_aggregate($new_field, $field);
  $lph->reset_aggregate($new_field);

  $lph->set_concatenate($new_field, $field);
  $lph->reset_concatenate($new_field);

  $boolean = $lph->pre_loop($field);
  $boolean = $lph->post_loop($field);

  $boolean = $lph->pre_loop_substr($field,$offset,$length);
  $boolean = $lph->post_loop_substr($field,$offset,$length);

  $boolean = $lph->is_first;
  $boolean = $lph->is_last;

  $count = $lph->count;

DESCRIPTION

DBIx::FetchLoop is a supplemental approach for data retrieval with DBI. Result rows are queued with hash references to previous, current and next rows. Utility functions allow for simplified comparison of a field between previous and current or current and next rows. Additional functions allow you automatically create new fields for aggregating or concatenating based on fields in the resulting dataset.

Note: This module was created with ease of use and performance in mind. This module is intended to eliminate the need for temporary variables for loop detection as well as aggregation and concatenation. The reason that not all DBI methods for data retrieval are not implemented (such as selectall_arrayref) is that the module's design for performance would be defeated.

In essence you can write cleaner looking, more efficient code minus a few hassles.

METHODS

Instantiating a DBIx::FetchLoop object:

DBIx::FetchLoop requires two arguements when creating an object: a dbi statement handle, and a scalar identifying the DBI data retrieval method to utilize. Supported DBI methods are: fetchrow_arrayref fetchrow_hashref

The module automatically handles calling the $sth->execute and $sth->finish functions of DBI, therefore you only need to create the statement handle and pass it along.

Instantiating an object would look like this:

  use DBI;
  use DBIx::FetchLoop;

  $dbh = DBI->connect($connect_string);
  $sth = $dbh->prepare($sql);
  $lph = DBIx::FetchLoop->new($sth,'fetchrow_hashref');

If $dbi_method is not supplied, the module will default to using fetchrow_hashref.

Retrieving data:

  $d = $lph->fetch_current_data;

$d is a hashref with elements to previous, current and next datasets as available.

eg (fetchrow_hashref)

  $d->{previous}->{field} 
  $d->{current}->{field} 
  $d->{next}->{field}

eg (fetchrow_arrayref)

  $d->{previous}->[1] 
  $d->{current}->[1] 
  $d->{next}->[1]

Conditional testing:

These functions exist to make the code necessary for detecting a new loop a little cleaner.

  $lph->pre_loop($field);  - compares $field between previous and current rows, returns true if different
  $lph->post_loop($field); - compares $field between current and next rows, returns true if different


  $lph->pre_loop_substr($field,$offset,$length);  - compares substring of $field between previous and current rows, returns true if different
  $lph->post_loop_substr($field,$offset,$length);- compares substring of $field between current and next rows, returns true if different


  $lph->is_first; - returns true if current record is first record
  $lph->is_last;  - returns true if current record is last record

Data Utilities:

  $lph->set_aggregate($new_field, $field);
  $lph->set_concatenate($new_field, $field);

These functions allow you to create new fields in the resulting dataset that are aggregates or concatenates of an original field in the data set. They must be called before the first time you call $lph->fetch_current_data.

  $lph->reset_aggregate($new_field);
  $lph->reset_concatenate($new_field);

These functions reset the value of the specified field to undef in the current dataset. They can be called anytime during the running of the program.

  $lph->count; - return the number of the current row returned (starts at 1)

EXAMPLES

Example 1 (fetchrow_hashref):

  use DBI;
  use DBIx::FetchLoop;

  $dbh = DBI->connect(...);
  $sth = $dbh->prepare('select company, department, bank_account, balance from account_table");
  $lph = DBIx::FetchLoop->new($sth,'fetchrow_hashref');

  $lph->set_aggregate('department_rollup','balance');
  $lph->set_aggregate('company_rollup','balance');

  while (my $d = $lph->fetch_current_data) {

    if ($lph->pre_loop('company')) {
      print "Company: " . $d->{current}->{company} . "\n";
    }

    if ($lph->pre_loop('department')) {
      print "Department: " . $d->{current}->{department} . "\n";
    }

    print "Account: " . $d->{current}->{bank_account} . " : " . $d->{current}->{balance} . "\n";

    if ($lph->post_loop('department')) {
      print "Department Balance: " . $d->{current}->{department_rollup} . "\n";
      $lph->reset_aggregate('department_rollup');
    }

    if ($lph->post_loop('company')) {
      print "Company Balance: " . $d->{current}->{company_rollup} . "\n\n";
      $lph->reset_aggregate('company_rollup');
    }
  }

  $dbh->disconnect;

Example 2 (fetchrow_arrayref):

  use DBI;
  use DBIx::FetchLoop;

  $dbh = DBI->connect(...);
  $sth = $dbh->prepare('select company, department, bank_account, balance from account_table");
  $lph = DBIx::FetchLoop->new($sth,'fetchrow_arrayref');

  $lph->set_aggregate(4,3);
  $lph->set_aggregate(5,3);

  while (my $d = $lph->fetch_current_data) {


    if ($lph->pre_loop(0)) {
      print "Company: " . $d->{current}->[0] . "\n";
    }

    if ($lph->pre_loop(1)) {
      print "Department: " . $d->{current}->[1] . "\n";
    }

    print "Account: " . $d->{current}->[2] . " : " . $d->{current}->[3] . "\n";

    if ($lph->post_loop(1)) {
      print "Department Balance: " . $d->{current}->[4] . "\n";
      $lph->reset_aggregate(4);
    }

    if ($lph->post_loop(0)) {
      print "Company Balance: " . $d->{current}->[5] . "\n\n";
      $lph->reset_aggregate(5);
    }
  }

  $dbh->disconnect;

Example 3 (concatenation, fetchrow_hashref and substring comparison)

  use DBI;
  use DBIx::FetchLoop;

  $dbh = DBI->connect(...);
  $sth = $dbh->prepare('select news_group, message_header, message_part from news");
  $lph = DBIx::FetchLoop->new($sth,'fetchrow_hashref');

  $lph->set_concatenate('whole_message','message_part');

  while (my $d = $lph->fetch_current_data) {

    if ($lph->is_first) {
      print "News Viewing App\n";
    }

    if ($lph->pre_loop('news_group')) {
      print "Group: " . $d->{current}->{news_group} . "\n";
    }

    if ($lph->pre_loop_substr('message_header',4,10)) { 
      print "Title: " . substr($d->{current}->{message_header},4,10) . "\n";
      print "Author: " . substr($d->{current}->{message_header},14,10) . "\n";
      print "Result #" . $lph->count . "\n";
    }

    if (i$lph->post_loop_substr('message_header',4,10)) { 
      print "Message: \n" . $d->{current}->{whole_message} . "\n\n";
      $lph->reset_concatenate('whole_message');
    }

    if ($lph->is_last) {
      print "All done\n";
    }

  $dbh->disconnect;

CHANGES

Please see the CHANGES file in the module distribution.

TO-DO

 - Spend more time on the documentation.

 - More in-depth examples (with comments)

ACKNOWLEDGEMENTS

Thanks to Tim Bunce for a lesson in the finer points of module naming. :)

Thanks to Ron M'Sadoques and Tom (d'Oh) Sullivan for listening to my ideas and constant promises to get around to writing this module.

AUTHOR

Brendan L. Fagan <[email protected]>. Comments, bug reports, patches and flames are appreciated.