DBI 1

[godspeed06]

Greetings,
I have a script that connects to a database, which $sth->fetchrow_array stores in an array. Then I walk through the array and run a application utility against each table found. Below is error when running the script:



ERROR:
DBI::db=HASH(0x311db4)->disconnect invalidates 1 active statement handle (either destroy statement handles or call finish on them before disconnecting) at ./dbscript.pl line 67.

Script:
this is line :67
********************
$dbh->disconnect( );

 

[Rieekan]

That error is saying that you're closing the database connection before getting all of the rows back from the database. Please post your code so we can take a look to see what the issue may be.

- George

 

[godspeed06]

George, please see the script below:

#!/usr/local/bin/perl -w

use strict;

use DBI;
use Data:umper;

# Connect to Oracle database, making sure AutoCommit is

# turned off and potential errors are raised.

#Configurations
my $log_dir = "tmp";
my $log_file = "err_user_log.txt";
$ENV{PATH} = '/bin:/usr/bin:/usr/local/bin';
my $system = "/opt/RDP/land/system";




my $dbh = DBI->connect( 'dbi:Oracle:INT20', 'user', 'pass',

{ RaiseError => 1, AutoCommit => 0 } );



my $sql = qq{SELECT sched_table FROM def_tables where user_daily='SYSTEM'};



my $sth = $dbh->prepare( $sql );

$sth->execute( );



my @found = $sth->fetchrow_array;

foreach my $table (@found){

system "sudo su \- user $system util -U foo2 -P foo1 -defexport -table $table -dcname FOO12 -file $log_dir/$log_file"


}

#while ( my($tables) = $sth->fetchrow_array) {

# my @found = $tables;


# Print out the result.


}

$dbh->disconnect( ); # Disconnect

 

[brigmar]

What Rieekan said:

Quick and Dirty solution:

$sth->finish();

right before your '$dbh->disconnect();' line.

 

[godspeed06]

George that worked! This time it run with no error, except my expect output was not written the file specified at the end of command line utility. Is there a way to get the results?

Thanks!

 

[Rieekan]

Try this (change in red):

#!/usr/local/bin/perl -w

use strict;

use DBI;
use Data::Dumper;

# Connect to Oracle database, making sure AutoCommit is

# turned off and potential errors are raised.

#Configurations
my $log_dir = "tmp";
my $log_file = "err_user_log.txt";
$ENV{PATH} = '/bin:/usr/bin:/usr/local/bin';
my $system = "/opt/RDP/land/system";

my $dbh = DBI->connect( 'dbi:Oracle:INT20', 'user', 'pass',

                        { RaiseError => 1, AutoCommit => 0 } );

my $sql = qq{SELECT sched_table FROM def_tables where user_daily='SYSTEM'};

my $sth = $dbh->prepare( $sql );

$sth->execute();
  
my @found = $sth->fetchrow_array;

foreach my $table (@found){
    
    system "sudo su \- user $system util -U foo2 -P foo1 -defexport -table $table -dcname FOO12 -file $log_dir/$log_file"
       
    [COLOR=red]print "$table\n";[/color]
    
}

[COLOR=red]$sth->finish();[/color]

$dbh->disconnect(  );  # Disconnect

 

[godspeed06]

Ok, here is the deal. I am able to run the system() command in the shell successfully. However, when running this via the script, the results are null, meaning there is no log file. Is there a way to tell perl to wait until system() command completes between each iteration?

 

[Rieekan]

Sure, just use backticks instead...

my $output = `sudo su \- user $system util -U foo2 -P foo1 -defexport -table $table -dcname FOO12 -file $log_dir/$log_file`;

 

[godspeed06]

Ha! Will run this first thing in the morning and let you know the outcome.

 

[godspeed06]

One last thing, how would I incorporate this into my script. I mean, these values are store in $output, whereas I was call this command via system()

Many Thanks!

 

[Rieekan]

$output is just capturing any output from the commands you're running, so it's just a safe way to verify that you have the results you want. You could test against it to make sure everything occurred as it should. This might be a better alternative to storing everything to a log file to look at later.

To incorporate it, just replace the system() line with this one.

- George

 

[godspeed06]

Thanks, George! I will let you know the results.