Try Catch Block

[macchisp]

Is there a way of doing a try catch block in perl?

I am wrote a regular expression that removes the <br> tags of certain fields. I have a process that searches the database and if there are 3 or more <br> tags in a row, it will update those fields with the <br> tags removed.

A couple of fields in the database are too long and wrap to the next line. Once the regular expression hits this field, my SQL statement that updates the field does not execute. I'm guessing it fails because the field that I am updating is too large.

So what I am thinking, is to put it in a try catch block. If the execute fails, show the ID of which one failed and go to the next one.

Is this possible and if so, how would I go about doing this? Thanks.

[rwedge]

The length of a line should not effect your regular expresions ability, it is more likely another issue that causes the SQL statement not to execute.

[macchisp]

Thanks! Would anyone have any ideas as to what this issue might be?

[FishMonger]

Impossible to say without seeing your code.

[macchisp]

It is not impossible to see without the code, someone may have already run into an issue like this and know from experience. Thank you for looking at it and trying to help me resolve this issue though.

Here is the DB connection code

my $dbh = DBI->connect(qq{DBI:CSV:csv_eol=\n;csv_sep_char=\\~});
$dbh->{'csv_tables'}->{'JPDAR'} = {'file' => 'data/objectives.txt','col_names' => ["Obj","PosNbr","Year","Div","AVP","Dept","SDescr","Major","Methods","Results","Accompl","JanUpd","LDescr","Changes","Chars","Goals","Budget"]};
# Setup error variables
$dbh->{'RaiseError'} = 1;
$@ = '';

Below is getting all the fields and if there are more than 3 <br> tags in a row, it will replace it with one <br> tag. It will then check to make sure a value is not undefined and then it will update the DB.

my $original = "SELECT * FROM JPDAR";
my $sth = $dbh->prepare($original);
$sth->execute();

while(my $row = $sth->fetchrow_hashref)
{
$found_results = 1;
$obj= $row->{'Obj'};
$year = $row->{'Year'};
$posnbr= $row->{'PosNbr'};
$div= $row->{'Div'};
$avp= $row->{'AVP'};
$dept= $row->{'Dept'};
$sdecr = $row->{'SDescr'};
$major = $row->{'Major'};
$methods = $row->{'Methods'};
$results = $row->{'Results'};
$accompl = $row->{'Accompl'};
$janupd = $row->{'JanUpd'};
$ldescr = $row->{'LDescr'};
$changes = $row->{'Changes'};
$chars = $row->{'Chars'};
$goals = $row->{'Goals'};
$budget = $row->{'Budget'};

for($i=15; $i>3;$i--){
if($obj =~ s/(<br>){$i}/<br>/) { $obj =~ s/(<br>){$i}/<br>/; }
if($year =~ s/(<br>){$i}/<br>/) { $year =~ s/(<br>){$i}/<br>/; }
if($posnbr =~ s/(<br>){$i}/<br>/) { $posnbr =~ s/(<br>){$i}/<br>/; }
if($div =~ s/(<br>){$i}/<br>/) { $div =~ s/(<br>){$i}/<br>/; }
if($avp =~ s/(<br>){$i}/<br>/) { $avp =~ s/(<br>){$i}/<br>/; }
if($sdecr =~ s/(<br>){$i}/<br>/) { $sdecr =~ s/(<br>){$i}/<br>/; }
if($major =~ s/(<br>){$i}/<br>/) { $major =~ s/(<br>){$i}/<br>/; }
if($methods =~ s/(<br>){$i}/<br>/) { $methods =~ s/(<br>){$i}/<br>/; }
if($results =~ s/(<br>){$i}/<br>/) { $results =~ s/(<br>){$i}/<br>/; }
if($accompl =~ s/(<br>){$i}/<br>/) { $accompl =~ s/(<br>){$i}/<br>/; }
if($janupd =~ s/(<br>){$i}/<br>/) { $janupd =~ s/(<br>){$i}/<br>/; }
if($ldescr =~ s/(<br>){$i}/<br>/) { $ldescr =~ s/(<br>){$i}/<br>/; }
if($changes =~ s/(<br>){$i}/<br>/) { $changes =~ s/(<br>){$i}/<br>/; }
if($chars =~ s/(<br>){$i}/<br>/) { $chars =~ s/(<br>){$i}/<br>/; }
if($goals =~ s/(<br>){$i}/<br>/) { $goals =~ s/(<br>){$i}/<br>/; }
if($budget =~ s/(<br>){$i}/<br>/) { $budget =~ s/(<br>){$i}/<br>/; }
}
$Nobj = $obj;
$Nyear = $year;
$Nposnbr = $posnbr;
$Ndiv = $div;
$Navp = $avp;
$Ndept = $dept;
$Nsdecr = $sdecr;
$Nmajor = $major;
$Nmethods = $methods;
$Nresults = $results;
$Naccompl = $accompl;
$Njanupd = $janupd;
$Nldescr = $ldescr;
$Nchanges = $changes;
$Nchars = $chars;
$Ngoals = $goals;
$Nbudget = $budget;

if($Nobj ne $obj) { $countobj++; }
if($Nyear ne $year) { $countyear++; }
if($Nposnbr ne $posnbr) { $countposnbr++; }
if($Ndiv ne $div) { $countdiv++; }
if($Navp ne $avp) { $countavp++; }
if($Ndept ne $dept) { $countdept++; }
if($Nsdecr ne $sdecr) { $countsdecr++; }
if($Nmajor ne $major) { $countmajor++; }
if($Nmethods ne $methods) { $countmethods++; }
if($Nresults ne $results) { $countresults++; }
if($Naccompl ne $accompl) { $countaccompl++; }
if($Njanupd ne $janupd) { $countjanupd++; }
if($Nldescr ne $ldescr) { $countldescr++; }
if($Nchanges ne $changes) { $countchanges++; }
if($Nchars ne $chars) { $countchars++; }
if($Ngoals ne $goals) { $countgoals++; }
if($Nbudget ne $budget) { $countbudget++; }

# VALIDATE DATA
if (defined($Nobj)){$Nobj=~ s/\n||\r||~||\f//g};
if ((!defined($Nobj))||($Nobj eq "")) {$Nobj ="-";}
if (defined($Nyear)){$Nyear=~ s/\n||\r||~||\f//g};
if ((!defined($Nyear))||($Nyear eq "")) {$Nyear ="-";}
if (defined($Nposnbr)){$Nposnbr=~ s/\n||\r||~||\f//g};
if ((!defined($Nposnbr))||($Nposnbr eq "")) {$Nposnbr ="-";}
if (defined($Ndiv)){$Ndiv=~ s/\n||\r||~||\f//g};
if ((!defined($Ndiv))||($Ndiv eq "")) {$Ndiv ="-";}
if (defined($Navp)){$Navp=~ s/\n||\r||~||\f//g};
if ((!defined($Navp))||($Navp eq "")) {$Navp ="-";}
if (defined($Ndept)){$Ndept=~ s/\n||\r||~||\f//g};
if ((!defined($Ndept))||($Ndept eq "")) {$Ndept ="-";}
if (defined($Nsdecr)){$Nsdecr=~ s/\n||\r||~||\f//g};
if ((!defined($Nsdecr))||($Nsdecr eq "")) {$Nsdecr ="-";}
if (defined($Nmajor)){$Nmajor=~ s/\n||\r||~||\f//g};
if ((!defined($Nmajor))||($Nmajor eq "")) {$Nmajor ="-";}
if (defined($Nmethods)){$Nmethods=~ s/\n||\r||~||\f//g};
if ((!defined($Nmethods))||($Nmethods eq "")) {$Nmethods ="-";}
if (defined($Nresults)){$Nresults=~ s/\n||\r||~||\f//g};
if ((!defined($Nresults))||($Nresults eq "")) {$Nresults ="-";}
if (defined($Naccompl)){$Naccompl=~ s/\n||\r||~||\f//g};
if ((!defined($Naccompl))||($Naccompl eq "")) {$Naccompl ="-";}
if (defined($Njanupd)){$Njanupd=~ s/\n||\r||~||\f//g};
if ((!defined($Njanupd))||($Njanupd eq "")) {$Njanupd ="-";}
if (defined($Nldescr)){$Nldescr=~ s/\n||\r||~||\f//g};
if ((!defined($Nldescr))||($Nldescr eq "")) {$Nldescr ="-";}
if (defined($Nchanges)){$Nchanges=~ s/\n||\r||~||\f//g};
if ((!defined($Nchanges))||($Nchanges eq "")) {$Nchanges ="-";}
if (defined($Nchars)){$Nchars=~ s/\n||\r||~||\f//g};
if ((!defined($Nchars))||($Nchars eq "")) {$Nchars ="-";}
if (defined($Ngoals)){$Ngoals=~ s/\n||\r||~||\f//g};
if ((!defined($Ngoals))||($Ngoals eq "")) {$Ngoals ="-";}
if (defined($Nbudget)){$Nbudget=~ s/\n||\r||~||\f//g};
if ((!defined($Nbudget))||($Nbudget eq "")) {$Nbudget ="-";}

# Setup query
my $where = "Obj = \'$obj\'";
my $set = "Obj = \'$Nobj\',PosNbr = \'$Nposnbr\',Year = \'$Nyear\',Div = \'$Ndiv\',AVP = \'$Navp\',Dept = \'$Ndept\',SDescr = \'$Nsdecr\',Major = \'$Nmajor\',Methods = \'$Nmethods\',Results = \'$Nresults\',Accompl = \'$Naccompl\',JanUpd = \'$Njanupd\',LDescr = \'$Nldescr\',Changes = \'$Nchanges\',Chars = \'$Nchars\',Goals = \'$Ngoals\',Budget = \'$Nbudget\'";
my $sqlquery = "UPDATE JPDAR SET $set WHERE $where";
my $updsth=$dbh->prepare($sqlquery);
$updsth->execute( );
$updsth->finish();
}

Any ideas on if a Try Catch block similar to this: try SQL Statement, if doesn't work, skip and do process all over again?

Thanks

[FishMonger]

There are several portions of your code that can be simplified/shortened, but before we do that, can you post a few example lines that are having this problem? If you can, it would be best to post the file as an attachment, so we can run a couple tests.

A couple of fields in the database are too long and wrap to the next line. Once the regular expression hits this field, my SQL statement that updates the field does not execute. I'm guessing it fails because the field that I am updating is too large.How do you know that it's not executing as apposed to execute but fail to update? How large/long is the field that you're updating?

[KevinADC]

these seems strange:

if($obj =~ s/(<br>){$i}/<br>/) { $obj =~ s/(<br>){$i}/<br>/; }

first you check to see if the substitution was successful, then you do it again. Is that really necessary?

[FishMonger]

Ya, I saw that too, infact doing that hole loop is strange when it could be accomplished with: s/(<br>){4,}/<br>/ for values %$row;

But that's not the only strange part. This also makes no sense.
$Nobj = $obj;
if($Nobj ne $obj) { $countobj++; }

This part can also be cleaned up.
if (defined($Nobj)){$Nobj=~ s/\n||\r||~||\f//g};
if ((!defined($Nobj))||($Nobj eq "")) {$Nobj ="-";}

[FishMonger]

You can even do column binding and drop the individual var assignments in the while loop.
my ($obj, $posnbr, $year, $div, $avp, $dept, etc, etc);
$sth->bind_columns($obj, $posnbr, $year, $div, $avp, $dept, etc, etc);

while($sth->fetch)
{

[FishMonger]

macchisp, are you still with us? Do you still want our help?

This regex can be simplified.
s/\n||\r||~||\f//g

to this
s/[\n\r\f]//g

But it also leads to this question. Since \n is your record separator, why are you searching for it within each field? If you actually have embedded \n in the fields, that would explain (the root cause) why you think "my SQL statement that updates the field does not execute".

When I look over your code, it appears to me that it's a band-aid patch to get around the real problem, which is the data wasn't formatted properly when it was inserted into the csv database. Maybe you should look into fixing the code for the original insertion of the data. Once that is done, you can reduce this code to a single line executed from the command line.

I'd need to see a sample of your actual data, but based on your code, this should do the trick.perl -pi.bak -e "s/(<br>){4,}/<br>/g; s/[\r\f]//g; s/~~/~-~/g;" data/objectives.txt

[macchisp]

Yeah Fish and Kevin, I'm still with you both. Other things have been keeping me busy and I just want to let you know that I will post the necessary information tomorrow to help me out with this problem of mine.

Thank you both so far for your help.

[macchisp]

can you post a few example lines that are having this problem? If you can, it would be best to post the file as an attachment, so we can run a couple tests.

The attached file is just a couple of records. The second record, beginning with "00000487~2004" is the one in which it fails. It was much longer but the file size had to be below 50KB.

these seems strange:
if($obj =~ s/(<br>){$i}/<br>/) { $obj =~ s/(<br>){$i}/<br>/; }first you check to see if the substitution was successful, then you do it again. Is that really necessary?

To answer your question, apparently not.

How do you know that it's not executing as apposed to execute but fail to update? How large/long is the field that you're updating?

I know that is is not executing because it goes through 105 records and actually updates the affected fields in the database. I've checked the database afterwards to see if it made any changes. Once it hits the 106th record, I think the problem is because one field is too large and that's why it fails.

Since \n is your record separator, why are you searching for it within each field?

I'm searching for it within each field because if by chance it got into a field, it would throw off the database. It's a little error checking before the data gets updated into the database.

Thanks for everything so far.

[KevinADC]

certainly this section can be eliminated entirely as it appears to do nothing:

$Nobj = $obj;
$Nyear = $year;
$Nposnbr = $posnbr;
$Ndiv = $div;
$Navp = $avp;
$Ndept = $dept;
$Nsdecr = $sdecr;
$Nmajor = $major;
$Nmethods = $methods;
$Nresults = $results;
$Naccompl = $accompl;
$Njanupd = $janupd;
$Nldescr = $ldescr;
$Nchanges = $changes;
$Nchars = $chars;
$Ngoals = $goals;
$Nbudget = $budget;

if($Nobj ne $obj) { $countobj++; }
if($Nyear ne $year) { $countyear++; }
if($Nposnbr ne $posnbr) { $countposnbr++; }
if($Ndiv ne $div) { $countdiv++; }
if($Navp ne $avp) { $countavp++; }
if($Ndept ne $dept) { $countdept++; }
if($Nsdecr ne $sdecr) { $countsdecr++; }
if($Nmajor ne $major) { $countmajor++; }
if($Nmethods ne $methods) { $countmethods++; }
if($Nresults ne $results) { $countresults++; }
if($Naccompl ne $accompl) { $countaccompl++; }
if($Njanupd ne $janupd) { $countjanupd++; }
if($Nldescr ne $ldescr) { $countldescr++; }
if($Nchanges ne $changes) { $countchanges++; }
if($Nchars ne $chars) { $countchars++; }
if($Ngoals ne $goals) { $countgoals++; }
if($Nbudget ne $budget) { $countbudget++; }

[FishMonger]

When I run your code without the adjustments/corrections that we've suggested, I receive the following error.
SQL ERROR: Incomplete SET clause!

DBD::CSV::db prepare failed: SQL ERROR: Incomplete SET clause!

[for Statement "UPDATE JPDAR SET Obj = '00000487',PosNbr = '2004',Year = '-<br><br>'
.....
.....
.....
.....
WHERE Obj = '00000487'"] at C:\testing\macchisp.pl line 131.
DBD::CSV::db prepare failed: SQL ERROR: Incomplete SET clause!

This is most likely caused by a syntax issue where there are “special characters” that are not being escaped. It also appears to me that your vars for the columns are offset.

[macchisp]

Kev,
Now how can you tell me that section
can be eliminated entirely as it appears to do nothing:

to me, the first part is redundant and it has already been removed. Now the second part, where if the two values aren't equal a counter adds one, is that really doing "nothing?" Can you elaborate to me how it "appears to do nothing." Thanks.


Fish, thanks. I'll take a look at the special characters and how the columns can be offset. Thanks a bunch.

[KevinADC]

Kev,
Now how can you tell me that section

to me, the first part is redundant and it has already been removed. Now the second part, where if the two values aren't equal a counter adds one, is that really doing "nothing?" Can you elaborate to me how it "appears to do nothing." Thanks.


If you removed the first part then the second part will be doing something. When you had both parts the counters would never increment.

[FishMonger]

Kevin and I both mentioned and/or suggested that portions of your code is not needed and/or illogical. You need to look at and have a clear understanding of what your code is actually doing and the logic behind it.

Beginning with a “clean slate”, as you loop through your query results, your data is being put into a hash. Lets focus in on the 'year' field for the first record of your sample data (and I'll adjust for the column offset).

$row->{'year'} is assigned 2003 from the db query

You then copy that hash value to a scalar with this assignment$year = $row->{'Year'};

A little later, you then make another copy with this statement$Nyear = $year;

So, now you have 3 separate copies of the exact same data and you've repeated that process for each of the vars. That's a big waste of effort and resources.

Now, lets look at this statement:if($Nyear ne $year) { $countyear++; }After making a copy of the var with a direct assignment, you then test and increment a counter if they're not equal (which won't happen). It doesn't make any sense doing that test. If that direct assignment fails, then you have a much BIGGER problem; Perl itself is corrupt and needs to be recompiled and installed.

This set of statements is questionableif (defined($Nyear)){$Nyear=~ s/\n||\r||~||\f//g};Since \n is the record separator, it won't be found within each field, so searching for it is a waste. ~ is the field separator is just as illogical to search for as the \n record separator. \f is a form feed and I don't see any logical reason why you would need to search for it within each (or any) field. \r is the only one that you may want to search, but even that one is questionable.

[macchisp]

touche, thanks for explaining, I was blind. Thank you very much.

So now that we got the illogical programming out of the way, any idea on if there is a way to do a try catch block? I looked through all the posts and I didn't see if anything addressed that question.

Something to the syntax of

Try SQL statement, if doesn't execute, skip, go to next statement

Is something like this possible? If so, how would I go about doing it?

[FishMonger]

In order to do your try catch block, you need to add an or clause to the prepare statement. It is also a good/best practice to check the status of each of your sql (select, prepare, update, and execute) statements.

Here's a cleaned up version that takes care of most of the issues, but it still could be improved.use strict;
use warnings;
use DBI;

my $dbh = DBI->connect(qq{DBI:CSV:csv_eol=\n;csv_sep_char=\\~});
$dbh->{'csv_tables'}->{'JPDAR'} = {'file' => 'data/objectives.txt','col_names' => ["Obj","PosNbr","Year","Div","AVP","Dept","SDescr","Major","Methods","Results","Accompl","JanUpd","LDescr","Changes","Chars","Goals","Budget"]};
# Setup error variables
$dbh->{'RaiseError'} = 0;
$dbh->{'PrintError'} = 0;


my $original = "SELECT * FROM JPDAR";
my $sth = $dbh->prepare($original);
$sth->execute();

while(my $row = $sth->fetchrow_hashref)
{
s/(<br>){4,}/<br>/ for values %$row;

my $obj= $row->{'Obj'};
my $year = $row->{'Year'};
my $posnbr= $row->{'PosNbr'};
my $div= $row->{'Div'};
my $avp= $row->{'AVP'};
my $dept= $row->{'Dept'};
my $sdecr = $row->{'SDescr'};
my $major = $row->{'Major'};
my $methods = $row->{'Methods'};
my $results = $row->{'Results'};
my $accompl = $row->{'Accompl'};
my $janupd = $row->{'JanUpd'};
my $ldescr = $row->{'LDescr'};
my $changes = $row->{'Changes'};
my $chars = $row->{'Chars'};
my $goals = $row->{'Goals'};
my $budget = $row->{'Budget'};


# Setup query
my $where = "Obj = '$obj'";
my $set = qq/Obj='$obj', PosNbr='$posnbr', Year='$year', Div='$div', AVP='$avp', Dept='$dept', SDescr='$sdecr', Major='$major', Methods='$methods', Results='$results', Accompl='$accompl', JanUpd='$janupd', LDescr='$ldescr', Changes='$changes', Chars='$chars', Goals='$goals', Budget='$budget'/;
my $sqlquery = "UPDATE JPDAR SET $set WHERE $where";
my $updsth=$dbh->prepare($sqlquery) || error($obj, $dbh->errstr) and next;
$updsth->execute;
}


sub error {
my ($obj, $reason) = @_;
my $line = (caller)[2];
print "prepare statement failed for $obj at $0 line $line $/reason: $reason\n\n";
}