http://www.perlmonks.org?node_id=11112569


in reply to Re^3: Problem Running More than Two Successive (I think) Sql SELECT statements, DBI, DBD::Oracle
in thread RESOLVED - Problem Running More than Two Successive (I think) Sql SELECT statements, DBI, DBD::Oracle

A thousand upvotes upon you, choroba. This at least seems to make it run correctly and consistently... I'm not above doing cringe worthy stuff if it works correctly and consistently. :-)

I fiddled a bit with the same basic idea, and alternatively I can also disconnect and reconnect with a bit of delay like the following prior to each prepare and it seems to work correctly, consistently, and cringe-ly. I had previously seen issues with a lot of connecting/disconnecting, but it hasn't complained yet when I do it like below with @connection = ($dsn, $user, $pass, {InactiveDestroy => 1, PrintError => 0, RaiseError => 1, AutoCommit => 0}).

$dbh->disconnect or warn "Disconnection failed: $DBI::errstr\n"; sleep(1); $dbh = DBI->connect_cached(@connection) or die;

I'll take anything that even remotely resembles a win at this point, like I said in an earlier post on this same basic problem, I seem to be frequently muddling the line between winning and losing.

Just another Perl hooker - My clients appreciate that I keep my code clean but my comments dirty.

Replies are listed 'Best First'.
Re^5: Problem Running More than Two Successive (I think) Sql SELECT statements, DBI, DBD::Oracle
by 5mi11er (Deacon) on Feb 07, 2020 at 19:23 UTC
    A google search against "perl dbi finish" brought be to this post:
    DBI and finish

    Maybe just remove those finish lines...

      I've actually already tried it with and without them, and I have completely removed them now. The original code I wrote when I started down this adventure didn't have them, I added them later to try and fix the issues I was seeing. The official DBI docs recommend against them unless you know you are only retrieving a part of the data set you queried.

      Another of my Oracle DBAs was actually very helpful and switched me to a different service and tweaked some other options in my connection string a bit. Now it's a lot more stable, albeit seemingly a bit slower for both connect/disconnect and data retrieval. I'm still doing the silly repeated disconnect/reconnect with some long waits, but now the worst I've seen is the occasional error thrown that says:

      DBD::Oracle::db disconnect failed: ORA-03113: end-of-file on communica +tion channel

      But it's rare, and I can probably just write some error handling/retry x times code around it and make it reliable enough for my purposes. Once again, and mostly just for posterity's sake (and my own, but hey, someone else may find it useful), I'll provide the full script, SQL, and sanitized tnsnames.ora file as I currently have them set since it seems to be mostly working (famous last words, probably regret them shortly) now.

      use utf8; use strict; use warnings; use Data::Dumper; use DBI; my $note_filter = '%'; # filter for WHERE LIKE statement in sql for "r +db_main.dk_lov_header_rec.note" my $name_filter = '%'; # filter for WHERE LIKE statement in sql for "r +db_main.dk_lov_header_rec.global_section_name" # bland credentials open(my $access_fh, '<', "$ENV{USERPROFILE}".'\Documents\Perl\DW_acces +s.txt') or die "Cannot open \"DW_access.txt\": $!."; chomp(my $user = <$access_fh>); chomp(my $pass = <$access_fh>); close($access_fh); # read in sql statement from "lov_table_lookup.sql" my $lov_table_sql; open(my $lov_table_sql_fh, '<', 'lov_table_lookup.sql') or die "Cannot + open \"lov_table_lookup.sql\": $!."; $lov_table_sql .= $_ while(<$lov_table_sql_fh>); close($lov_table_sql_fh); # read in sql statement from "lov_value_lookup.sql" my $lov_value_sql; open(my $lov_value_sql_fh, '<', 'lov_value_lookup.sql') or die "Cannot + open \"lov_value_lookup.sql\": $!."; $lov_value_sql .= $_ while(<$lov_value_sql_fh>); close($lov_value_sql_fh); # establish database connection my $dsn = 'dbi:Oracle:DWAPRD'; my @connection = ($dsn, $user, $pass, {InactiveDestroy => 1, PrintErro +r => 0, RaiseError => 1}); my $dbh = DBI->connect_cached(@connection) or die; # check that data will be returned my $tables_count = $dbh->selectrow_hashref('SELECT COUNT(*) as count F +ROM rdb_main.dk_lov_header_rec WHERE note LIKE ? AND global_section_n +ame LIKE ?', undef, $note_filter, $name_filter); print Dumper($tables_count); die "\nNO TABLE NUMBERS FOUND\n" if (!$tables_count->{'COUNT'}); # execute "lov_table_lookup.sql" $dbh->disconnect or warn "Disconnection failed: $DBI::errstr\n"; sleep(5); $dbh = DBI->connect_cached(@connection) or die; my $sth1 = $dbh->prepare($lov_table_sql) or die; $sth1->execute($note_filter, $name_filter) or die; # store table numbers in array print "\n----------\n"; my $print_format = '%10s %12s %-32s %-80s'; printf("$print_format\n", 'ROW_NUMBER', 'TABLE_NUMBER', 'GLOBAL_SECTIO +N_NAME', 'NOTE'); my @table_numbers; my $row_counter = 1; while(my $row = $sth1->fetchrow_hashref) { push(@table_numbers, $row->{'TABLE_NUMBER'}); foreach (keys(%$row)) {$row->{$_} = '' if (!defined($row->{$_}))}; + # change all NULLs to empty strings printf("$print_format\n", $row_counter, $row->{'TABLE_NUMBER'}, $r +ow->{'GLOBAL_SECTION_NAME'}, $row->{'NOTE'}); $row_counter++; } print "----------\n"; # adjust $lov_value_sql for variable number of bind parameters my $bind_inserts; $bind_inserts .= '?, ' foreach (@table_numbers); $bind_inserts =~ s/, $//; # discard hanging comma $lov_value_sql =~ s/LIST_OF_LOV_TABLE_NUMBERS:\?/$bind_inserts/; # execute "lov_value_lookup.sql" $dbh->disconnect or warn "Disconnection failed: $DBI::errstr\n"; sleep(5); $dbh = DBI->connect_cached(@connection) or die; my $sth2 = $dbh->prepare($lov_value_sql) or die; print "\n----------\n"; $print_format = '%10s %12s %15s %-15s %-80s'; printf("$print_format\n", 'ROW_NUMBER', 'TABLE_NUMBER', 'LOV_INTEGER_K +EY', 'LOV_DISPLAY_KEY', 'NOTE'); $row_counter = 1; $sth2->execute(@table_numbers) or die; while(my $row = $sth2->fetchrow_hashref) { foreach (keys(%$row)) {$row->{$_} = '' if (!defined($row->{$_}))}; + # change all NULLs to empty strings printf("$print_format\n", $row_counter, $row->{'TABLE_NUMBER'}, $r +ow->{'LOV_INTEGER_KEY'}, $row->{'LOV_DISPLAY_KEY'}, $row->{'NOTE'}); $row_counter++; } print "----------\n"; $dbh->disconnect or warn "Disconnection failed: $DBI::errstr\n"; sleep(5);

      -- Find the table number(s) that have the codes you wish to decode SELECT table_number , TRIM(CAST(global_section_name as CHAR(32))) as global_section_n +ame , TRIM(CAST(note as CHAR(80))) as note FROM rdb_main.dk_lov_header_rec WHERE note LIKE ? AND global_section_name LIKE ? ORDER BY global_section_name

      -- List all the codes SELECT table_number , lov_integer_key , TRIM(CAST(lov_display_key as CHAR(40))) as lov_display_key , TRIM(CAST(note as CHAR(80))) as note FROM rdb_main.dk_lov_detail_rec WHERE table_number IN (LIST_OF_LOV_TABLE_NUMBERS:?) ORDER BY table_number, note

      DWAPRD = (DESCRIPTION_LIST= (LOAD_BALANCE=off) (FAILOVER=on) (DESCRIPTION= (ADDRESS_LIST= (LOAD_BALANCE=on) (ADDRESS = (PROTOCOL=TCP)(HOST=XXX)(PO +RT = XXX)))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = XXX)) ) (DESCRIPTION= (ADDRESS_LIST= (LOAD_BALANCE=on) (ADDRESS = (PROTOCOL=TCP)(HOST=XXX)(PO +RT = XXX)))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = XXX)) ) )

      Just another Perl hooker - My clients appreciate that I keep my code clean but my comments dirty.
        I am curious as to why you are using DBI->connect_cached instead of just DBI->connect?

        Why this?:

        $dbh->disconnect or warn "Disconnection failed: $DBI::errstr\n"; sleep(5); $dbh = DBI->connect_cached(@connection) or die;
        I read the following:

        connect_cached is like connect, except that the database handle returned is also stored in a hash associated with the given parameters. If another call is made to connect_cached with the same parameter values, then the corresponding cached $dbh will be returned if it is still valid. The cached database handle is replaced with a new connection if it has been disconnected or if the ping method fails.

        Caching can be useful in some applications, but it can also cause problems and should be used with care. The exact behavior of this method is liable to change, so if you intend to use it in any production applications you should discuss your needs on the dbi-users mailing list.

        I don't see any need to drop your db connection and then re-connect. I recommend to ditch this connect_cached method. Connect once without caching and stay connected. There could be some glitch with this connect_cached method that is screwing things up? Stuff like "but it can also cause problems and should be used with care." are red flags to me.