sub runQueries { my $LogFilePath = $ENV{"DATALOADFILEROOT"}."/files/"; my ($thingToDo, $fileHandle, $queriesToRunRef, $queryCodeRef, $dbh) = @_; # 'queries' is a list of queries we need to run in a certain order # 'queryCode' is a hash list of queries, keyed on query name. my @queries = @$queriesToRunRef; my %queryCode = %$queryCodeRef; my $sqlStatement = ""; my $sth; foreach my $query(@queries) { print $fileHandle "next query is $query\n"; if (defined $queryCode{$query}) { print "1\n"; my $queryLogFile = $LogFilePath . $query; my $sqlSuccess = 0; print "2\n"; if (defined $queryFiles{$query}) { print $fileHandle "writing to file - $queryLogFile\n"; open(queryLog, ">$queryLogFile") or return "Couldn't open $logFilePath . $queryLogFile - $!"; } print "3\n"; my $sqlStatement = $queryCode{$query}; my $rowCount = 0; print $fileHandle "code - $sqlStatement\n"; # We put these next statements into a loop because we might encounter an error # we can recover from. If so, the 'eval' will catch it and throw it to 'if ($@)' while ($sqlSuccess == 0) { print "4\n"; eval { print "5\n"; #$dbh->{RaiseError} = 1; print "6\n"; $sth = $dbh->prepare($sqlStatement); print "7\n"; $sth->execute(); print "8\n"; print $fileHandle "rows affected - " . $sth->rows . "\n"; #my $result = $sth->fetchall_arrayref({}); while (my @row=$sth->fetchrow_array()) { if (defined $queryFiles{$query}) { print queryLog "@row\n"; } $rowCount++; } $sqlSuccess = 1; }; if ($@) { print $fileHandle "caught error $@\n"; my $errString = $@; print $fileHandle "here err string is |$errString|\n"; if ($errString =~ m|Table '(.*)' already exists|) { print $fileHandle "got to here\n"; my $sql = "drop table " . $1; my $drop = $dbh->prepare($sql); $drop->execute(); } else { return "Error found - $errString"; } } } if ($rowCount > 0 && (defined $queryFiles{$query}) && $queryFiles{$query} eq "stop") { return "query '$query' wrote $rowCount rows - this needs investigating\n"; } if (defined $queryFiles{$query}) { print $fileHandle "Wrote $rowCount rows\n"; close querylog; } } else { print $fileHandle "******* NO CODE FOR THIS KEY ********\n"; return "Can't proceed - no code for query $query\n"; } $sqlStatement = "update dataLoadProgress set progress = '" . $query . "'"; $sth = $dbh->prepare($sqlStatement) or return $DBI::errstr; $sth->execute() or return $DBI::errstr; } $sqlStatement = "update dataLoadProgress set progress = '" . $thingToDo . ":Success'"; $sth = $dbh->prepare($sqlStatement) or return $DBI::errstr; $sth->execute() or return $DBI::errstr; return "Success"; }