Beefy Boxes and Bandwidth Generously Provided by pair Networks
Keep It Simple, Stupid
 
PerlMonks  

DBI false error

by kyledba2013 (Initiate)
on Sep 18, 2014 at 16:34 UTC ( #1101082=perlquestion: print w/replies, xml ) Need Help??

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

First off, if you are reading this you have already given some of your time to help solve the problem. Therefore, thank you.

Secondly, to put to give you some background of my Perl knowledge, I know enough to be dangerous but not enough to be known as an expert or Perl developer for that matter. So please don't judge too harshly if you see an obvious mistake. Also you may have to explain something a little more than usual.

I have a script that forks process to create multiple tables at once. The script gets the DDL needed for a table and then runs a $dbh->do command. The script works well for the most part. However, there are 4 tables out of 5000 that consistently fail. No error string is in $dbh->errstr, $dbh->err, or DBI::errstr. My connect code is:

my $dbh = DBI->connect("$connection", "$user", "$pass", {PrintError => 0, PrintWarn => 0, RaiseError => 1, LongReadLen => 2000000100, # add 100 to the longest possible long +, as per DBI man page ora_module_name => $base_scriptname # set module name for this s +ession to the name of this script. }) or error_check('N','Y','N',"Could Not Connect To Database: ". $DBI +::errstr);

my do script is:

$rows = $source_dbh->do($ctas_sql) or { print sample_table_log "Create table failed at " . get_date() +. "\n" and print sample_table_log "Value for rows is: [$rows]\n" and print sample_table_log "\nCannot run $ctas_sql \n" and print sample_table_log 'DBI::errstr = ' . DBI::errstr . "\n" a +nd print sample_table_log '$source_dbh->errstr = ' . $source_dbh- +>errstr . "\n" and print sample_table_log '$source_dbh->err = ' . $source_dbh->er +r . "\n" and update_sample_table_driver_tb($t_owner,$t_name,'FAILED') and exit 1 };

I am in debugging mode right now, that is why you see so many print error strings. Here is the log generated:

Create table failed at 09/18/14 10:58:07 Value for rows is: [] Cannot run CREATE TABLE "sampler_user"."table" SEGMENT CREATION IMMEDIATE PCTFREE 0 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 214748364 +5 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "S_BCM_DATA" PARALLEL 32 as select a.* from BCM.RESPONSE_CAMP_TB a DBI::errstr = $source_dbh->errstr = $source_dbh->err =

However, i can go into the database and see the table created. My biggest question is, why does the script run fine for all the other tables but not for these. I can run the sql in sqlplus just fine as well in Toad.

I just ran this outside of my sampler script. Here is a full code that you can test with:

#!/usr/bin/perl use strict; use DBI; use Getopt::Long; use File::Basename; require "/home/fdsprod/bin/setoraenv.pl"; ###build connection strings my $HOST=`hostname`; my $sid="$ENV{ORACLE_SID}"; my $source_connection_string=qq(dbi:Oracle:host=$HOST;sid=$sid); my $source_dbh = connect_to_db($source_connection_string); my $rows; create_table(); ###################################################################### +############### # Connect To the source Database. ###################################################################### +############### sub connect_to_db { my $errormessage; # get the base filename for this script my $base_scriptname = basename($0); my $user = 'sampler_fdsglobal'; my $pass = 'sample'; # Just connect to the db. my $connection = $_[0]; my $dbh = DBI->connect("$connection", "$user", "$pass", {PrintError => 0, PrintWarn => 0, RaiseError => 1, LongReadLen => 2000000100, # add 100 to the longest possible long +, as per DBI man page ora_module_name => $base_scriptname # set module name for this s +ession to the name of this script. }) or error_check('N','Y','N',"Could Not Connect To Database: ". $DBI +::errstr); return $dbh; } ###################################################################### +############### # Connect To the source Database. ###################################################################### +############### sub create_table{ my $t_owner = 'BCM'; my $t_name = 'RESPONSE_CAMP_TB'; my $sample_tblspc = 'S_BCM_DATA'; my $t_multi = 'NO'; my $t_condition = ''; my ($sql, $sth, $rows); my $ctas_sql; ##get ddl for table. The below will remap to new schema and table +s $sql = qq(select SYS.sampler_return_ddl_test(upper('$t_owner'),upp +er('$t_name'),'NO','$sample_tblspc') FROM DUAL); $sth = $source_dbh->prepare($sql) or { print "Prepare Error: $sql \n" . $source_dbh->errstr . "\n" an +d exit 1 }; $sth->execute() or { print "Execute Error: $sql \n". $source_dbh->errstr ." \n" and + exit 1 }; $ctas_sql = $sth->fetchrow_array(); ##send email failure if $ctas is null. if (!$ctas_sql){ error_check('N','Y','N',"Create table as sql varabie is null\n +") }; ##in oder to run a 'create table as' statement. you must delete t +he column definitions ##the below regular expression deletes the first instance of a par +enthesis group and its contents, ##which is the column definition when pulled from dbms_metadata. +The dbms_metadata is the ##foundation of the sys.sampler_return_ddl function. DO NOT DELET +E THE FOLLOWING REGULAR EXPRESSION $ctas_sql =~ s/\((?>[^()]|(?R))*\)//; $ctas_sql = $ctas_sql . qq(\nas \nselect a.* from $t_owner.$t_name + a $t_condition); print "Begin Create table at ". get_date() ."\n\n"; print "Value for ctas_sql is:\n$ctas_sql\n\n\n"; $rows = $source_dbh->do($ctas_sql) or { print "Create table failed at " . get_date() . "\n" and print "Value for rows is: [$rows]\n" and print "\nCannot run $ctas_sql \n" and print 'DBI::errstr = ' . DBI::errstr . "\n" and print '$source_dbh->errstr = ' . $source_dbh->errstr . "\n" a +nd print '$source_dbh->err = ' . $source_dbh->err . "\n" and print '$! = ' . $! . "\n" and exit 1 }; print "Completed create table at ". get_date() ." Rows Created = [ +$rows]\n"; #if(defined $rows){ # print sample_table_log "Completed create table at ". get_date +() ." Rows Created = [$rows]\n"; #} #else { # print sample_table_log "\nCannot run $ctas_sql \n". $source_d +bh->errstr; # update_sample_table_driver_tb($t_owner,$t_name,'FAILED'); # exit 1; #} } ###################################################################### +############### # Get Date + # ###################################################################### +############### sub get_date { # Needed an easy way to get the system date. my $tmp_date= `/usr/bin/date "+%D %T"`; chomp($tmp_date); return $tmp_date; }

Replies are listed 'Best First'.
Re: DBI false error
by hippo (Chancellor) on Sep 18, 2014 at 16:59 UTC

    The documentation for DBI is pretty clear on the return value of do and it says:

    Prepare and execute a single statement. Returns the number of rows affected or "undef" on error. A return value of "-1" means the number of rows is not known, not applicable, or not available.

    But you are not testing the defined-ness or the negativity of the return value, you are testing the truthfulness of it. So, if zero rows are affected the return value will be zero, but your code in its present form takes this to be a failure. Instead of your current test, perhaps something like this might be better:

    $rows = $source_dbh->do($ctas_sql); unless (defined $rows) { # handle error condition here }

    Caveat: Your comments and code suggest you are using Oracle which I haven't touched for years so there may be something specific to that DB at play here as well.

    Update: That's probably cobblers. do should return 0E0 on zero rows and your output clearly shows that rows is '' or undef. Sorry for the noise.

Re: DBI false error
by mje (Curate) on Sep 19, 2014 at 09:02 UTC

    There are a few things that strike me I think you should fix first. 1. add use warnings 2. you use "$dbh->prepare(xx) or {}" and $sth->execute() or {} but you cannot put a block after or, it looks like a hash (if you enabled warnings you'd have seen that) 3. RaiseError => 1 negates having to do all those xxx or error tests 4. as RaiseError is enabled if your script did not die then there must be something wrong with your code testing for an error.

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others contemplating the Monastery: (5)
As of 2020-11-29 02:43 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found

    Notices?