Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2005
    Posts
    220

    Unanswered: Help with subroutine

    I have the following subroutine that 99.99% of the time will return a single value. My concern the how do I build in error trapping for the .01% in case there happens to be more than two distinct values. I want to bail.

    Code:
    sub what_category_do_we_process
    {
      my $planner = shift;
      my ( $cur, $sql );
    		
      $sql = "SELECT DISTINCT category 
      				  from promo_params 
      				 WHERE plnrcode = ? 
      				   AND processflag = 'N'";
                   
      $cur = $dbh->prepare($sql);
        
      $cur->execute($planner);
    	
    	$cur->bind_columns(undef, \$category);
    
    	while($cur->fetch()) 
    	{	
       return $category;
    	} 
    
    }

  2. #2
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    What about:
    Code:
    sub what_category_do_we_process
    {
      my $planner = shift;
      my $sql = "SELECT DISTINCT category 
    		  FROM   promo_params 
      		  WHERE  plnrcode = ? 
      		    AND  processflag = 'N'";
                   
      my $cur = $dbh->prepare($sql);
      $cur->execute($planner);
      $cur->bind_columns(undef, \$category);
      $cur->fetch();
      my $cat = $category;
      if ($cur->fetch()) { die "Multiple rows returned"; }
      else { return $cat; }
    }
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  3. #3
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697

    Thumbs up

    Just to add to what Peter posted, even if you're going to bail, you might need to clean up database connections or something.

    Code:
    eval {
       blah;
       blah;
       do_stuff_with(what_category_do_we_process($blah));
       # 99.9% of the time it just works, and continues here...
       blah;
       blah;
    }
    if($@ eq "Multiple rows returned") {
       handle_multiple_row_exception;
    } elsif($@) {
       die $@;
    }
    See perldoc perlvar for an explanation of $@ and perldoc -f eval for more info.

  4. #4
    Join Date
    Dec 2006
    Posts
    6
    How about this? You can wrap the call to "what_categroy_do_we_process" in and eval as suggested before to trap the die when that happens.

    Jim

    Code:
    sub what_category_do_we_process
    {
      my $planner = shift;
    	
      my $sql = "SELECT DISTINCT category 
        from promo_params 
        WHERE plnrcode = ? 
        AND processflag = 'N'";
    
      my $cats = $dbh->selectcol_arrayref($sql,undef,$planner);
    
      die "More that one category found" if ( @{$cats} > 1 );
      die "No categories found" unless ( @{$cats} );
    
      return($cats->[0]);
    }

  5. #5
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    How about this?
    Yup. Now, if you are doing a lot of stuff that requires all this cleanup, you might want to reuse your cleanup code. Here's how:

    Code:
    sub errorcheck_db_action(&) {
        my($func) = @_;
        eval {
            &$func();
        }
        if($@) {
            if($@ eq "Multiple rows returned") {
                whatever_cleanup();
            } elsif(...) {
            ...
            } else {
                die $@; # if we don't know what kind of exception it is, pass it along
            }
        }
    }
    
    errorcheck_db_action {
        do_database_stuff();
    };
    If you're confused as to what sub foo(&) { ... } does, check perldoc perlsub for prototypes. Basically, the stuff inside the curlies becomes an anonymous subroutine without the "sub" keyword. It even gets its own @_ and everything, and if you have values outside it, it sees them. (It's a thing computer scientists call closures... it just works.)

    All you need to iron out is where you're going to stash your DBI object. If you subclass DBI and use it as a method, you lose the prototype, so calling it would look like $dbi->errorcheck_action(sub { block_of_code; }). The syntax wouldn't be as nice, but it'd work.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •