Beefy Boxes and Bandwidth Generously Provided by pair Networks
Clear questions and runnable code
get the best and fastest answer
 
PerlMonks  

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

by perldigious (Priest)
on Feb 07, 2020 at 22:06 UTC ( #11112598=note: print w/replies, xml ) Need Help??


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

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.
  • Comment on Re^6: Problem Running More than Two Successive (I think) Sql SELECT statements, DBI, DBD::Oracle
  • Select or Download Code

Replies are listed 'Best First'.
Re^7: Problem Running More than Two Successive (I think) Sql SELECT statements, DBI, DBD::Oracle
by Marshall (Abbot) on Feb 08, 2020 at 23:58 UTC
    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.

      Oh, I'm not a big fan of how I'm doing this either. For me it's a kludge that seems to fix another problem I had with this.

      So are the all the connect/disconnects, I had another different problem without them. I had also tried adding finish method calls instead with no luck.

      I'm not savvy enough at this stuff to be that effective on debugging, but I know if I do go back to just a single connect that isn't cached and/or remove the explicit disconnects I still see the issues. I'm sort of relying on my Oracle DBAs at this point, and since one of them switched the service I connected to and added a few other connection options things seem far more stable than they have ever been previously.

      I'm still holding out hope I can figure out what's really going on as I work with my employer's database from Perl more, but for now I'm way behind at work and I have to let this one go.

      Just another Perl hooker - My clients appreciate that I keep my code clean but my comments dirty.
        I don't understand why you are getting connection timeouts. The number of connections is limited, but usually a big number. Rather than attempting this connection_cached method, I would recommend a re-try of the basic connection method. The docs show enough red flags about connection_cached that I would stay away from that. The answer to a problem with a common simple method is seldom to increase complexity. Here is some code showing a retry for a web page fetch which you can adapt to your situation or some Monk can supply a better way:
        my $success=0; my $tries=0; while (! $success and $tries++ < 10) { eval { $m2->get($fullurl); }; if (! $@) { $success = 1; } else { print STDERR "Error: Retry Attempt $tries of 10\n"; print LOG "Error: Retry Attempt $tries of 10\n"; sleep (3); } } die "aborted Web Site Error: $!" unless $success; #ultimate f +ailure!! PROGRAM ABORT !!!!
        In general I would connect once at the beginning of your script and then disconnect after all SQL work is done. You could add some statements to log how often connect succeeds vs fails. DB Admins (and Monks) like to see data. From the above code, I can tell you for example that this website fails about once per 2,000 requests. Why, I don't know. But it is infrequent enough that I don't worry about it.

        This stuff where the query result fetch hangs is weird. That basically "shouldn't happen", but alas it does happen - such is computing at times! Figuring out what is going on there probably involves writing some specific test code to reproduce the problem with a known dataset. And sounds like that will take more time than you have at the moment. It is possible to implement your own timeout to prevent the program hang, but there are some "yeah but's" with that and we are dealing with an error that is so rare that most folks have never seen it before.

        Check back in when you have time to work on your project again. In general the Perl DBI is very robust and much easier to use than similar code in some other popular languages. I don't think we've come close to exhausting the various testing possibilities.

        Update: The most common method that I use for DB results is fetchall_arrayref. I am not sure what the DBI does with fetching results row by row. Most of my result sets are "small" meaning less than 2,000 rows. There is an equivalent fetchall_hashref. Perhaps some other Monk can explain the difference in communication between the DBI and DB for these array result situations and row by row fetching?

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others wandering the Monastery: (4)
As of 2020-06-01 23:34 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    Do you really want to know if there is extraterrestrial life?



    Results (12 votes). Check out past polls.

    Notices?