Beefy Boxes and Bandwidth Generously Provided by pair Networks
Just another Perl shrine
 
PerlMonks  

Re^3: Problem Running More than Two Successive (I think) Sql SELECT statements, DBI, DBD::Oracle

by choroba (Cardinal)
on Feb 07, 2020 at 15:17 UTC ( [id://11112568]=note: print w/replies, xml ) Need Help??


in reply to Re^2: 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

What happens if each prepare and execute run in a separate connection?

map{substr$_->[0],$_->[1]||0,1}[\*||{},3],[[]],[ref qr-1,-,-1],[{}],[sub{}^*ARGV,3]

Replies are listed 'Best First'.
Re^4: Problem Running More than Two Successive (I think) Sql SELECT statements, DBI, DBD::Oracle
by perldigious (Priest) on Feb 07, 2020 at 15:48 UTC

    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.
      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.

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: note [id://11112568]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others lurking in the Monastery: (4)
As of 2024-03-28 20:46 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found