Problem with INSERT statement

sasrs99 has asked for the wisdom of the Perl Monks concerning the following question:

I have this insert subroutine that keeps giving me this warning: Issuing rollback() for database handle being DESTROY'd without explicit disconnect() at promo.pl line 1106. and I can't seem to get rid of it. Can someone give me an idea of what to do?

sub insert_preSOF_records
{
    my ($dbh, $planner, $sum, $output_array) = @_; 
    
  my $id                                     = "1.0";
  my $msg                                 = "PROMO ORDER";
  
  my @bind_params = ();
    
  for my $i ( 0 .. $#output_array )       
  {                                                                   
+                  
          my $row = [ @{$output_array[$i]} ];     

        #
        # $row->[0]    = LOC
        # $row->[1]    = ITEM
        # $row->[2]    = QTY
        # $row->[3]    = P_DLRNET
        # $row->[4]    = P_DLRSPLRCODE
        
        push @bind_params, ( $id, $row->[4], $row->[0], $row->[1], $ro
+w->[2], $row->[3], $msg, $planner, $sum );
    
        #print Dumper(@bind_params);
    
    $dbh = DBI->connect("dbi:Oracle:$instance", "pac", "pac", \%attr) 
+or die "Can't connect to Oracle!", $DBI::errstr, "\n";
    
        my $insert_handle = $dbh->prepare("INSERT INTO pac.presof (id
                                                                 , dlr
+splrcode
                                                                 , loc
                                                                 , ite
+m
                                                                 , qty
                                                                 , dlr
+net
                                                                 , mes
+sage
                                                                 , pla
+nnercode
                                                                 , sum
+) 
                                                            VALUES (?,
+?,?,?,?,?,?,?,?)"); 

         die "Couldn't prepare queries; aborting" unless defined $inse
+rt_handle;
    
         $insert_handle->execute( @bind_params ) or die LogMsg("There 
+is a problem with the insert into PAC.PRESOF " . $dbh->errstr);
  
      #$dbh->commit;
      
      $insert_handle->finish;
      
      @bind_params = ();    
    
  }
          
    $dbh->disconnect();
          
  return 1;   # Success
}
[download]

Comment on Problem with INSERT statement Download Code

Replies are listed 'Best First'.
Re: Problem with INSERT statement by imp (Priest) on Dec 19, 2006 at 21:23 UTC
You initialize $dbh using the first argument to the function, but you then overwrite that variable with a new connection for every loop iteration. Do one of these: Pass the handle in, do not disconnect in the subroutine Connect at the top of the subroutine, disconnect at the bottom of the subroutine Connect at the top of the loop, disconnect at the bottom of the loop I would recommend #1 or #2, as #3 is only appropriate if you are connecting to dynamic sources. Very inefficient otherwise.	[reply]
Re^2: Problem with INSERT statement by sasrs99 (Acolyte) on Dec 19, 2006 at 21:46 UTC
#2 worked like a charm. Thanks!	[reply]
Re: Problem with INSERT statement by bart (Canon) on Dec 19, 2006 at 20:49 UTC
You may need the `commit` (or turn `autocommit` on in DBI), but the `finish` is unnecessary. Perhaps you're actually starting a new transaction by using it. Try removing it...?	[reply] [d/l] [select]
Re^2: Problem with INSERT statement by sasrs99 (Acolyte) on Dec 19, 2006 at 21:16 UTC
No luck. Here's how the bottom section looks now but I still get the error: `my $insert_handle = $dbh->prepare("INSERT INTO pac.presof (id , dlr +splrcode , loc , ite +m , qty , dlr +net , mes +sage , pla +nnercode , sum +) VALUES (?, +?,?,?,?,?,?,?,?)"); die "Couldn't prepare queries; aborting" unless defined $inse +rt_handle; $insert_handle->execute( @bind_params ) or die LogMsg("There +is a problem with the insert into PAC.PRESOF " . $dbh->errstr); $dbh->commit; @bind_params = (); } $dbh->disconnect(); return 1; # Success` [download]	[reply] [d/l]
Re: Problem with INSERT statement by perrin (Chancellor) on Dec 19, 2006 at 21:04 UTC
Is it reaching your disconnect() call? It doesn't sound like it is.	[reply]