Beefy Boxes and Bandwidth Generously Provided by pair Networks
"be consistent"
 
PerlMonks  

Mystified by why my UPDATE statement is not reflected in my database display

by OfficeLinebacker (Chaplain)
on Jul 05, 2013 at 18:22 UTC ( #1042754=perlquestion: print w/ replies, xml ) Need Help??
OfficeLinebacker has asked for the wisdom of the Perl Monks concerning the following question:

Greetings, esteemed monks!

I have this page which is to display the contents of a database, and, if desired, edit one of the records. The database consists of a table which is a resource, which is defined as a name, a link, and availability, and a table which indicates which matrix locations the resource goes in.

For some reason, when editing one of the rows, DBI returns 1, meaning one row is affected in the UPDATE statement, but in the table print out below, the change isn't being reflected. I've gone over this several times to no avail. Can anyone with a fresh perspective take a look and help?

Apologies for the long code, you pretty much have to see the whole thing.

#!/usr/bin/perl -w use strict; use CGI; use CGI::Carp qw(warningsToBrowser fatalsToBrowser); use DBI; use autodie; #Total hack to use a module surrepetitiously installed on a GoDaddy ac +count use lib 'SOMEDIR'; use Text::CSV; use lib 'SOMEOTHERDIR'; use Regexp::Common; #use Data::Table; $\="\n"; my $db="SOMEDB"; my $dbun="SOMEUN"; my $dbpw = 'SOMEPW'; my $dbhost="SOMEHOST.hostedresource.com"; my $res_tbl="resource_list"; my $loc_tbl="matrix_locations"; my $drh = DBI->install_driver("mysql") || print "install_driver failed +: $DBI::errstr <br/>"; my $dsn = "DBI:mysql:database=$db;host=$dbhost"; my $dbh = DBI->connect($dsn,$dbun,$dbpw) || print "DB connect failed: +$DBI::errstr <br/>"; my $res_select_stmt = "Select * from $res_tbl order by id"; my $loc_select_stmt = "Select location from $loc_tbl where resource_id + = ?"; my @data_elements = ('id','name','link','availability','locations'); my @avail_options = ('Free','Free w/Membership','Available for Purchas +e'); my $q = CGI->new; # create new CGI object print $q->header; print $q->start_html(-dtd=>'html',-title=>'Procedural Harm Affinity Gr +oup Resource Data Base Display and Edit'); # start the HTML #Here we are going to have two steps: #1. UPDATE the master resource table with the new data #2. Delete everything out of the locations table, and re-insert the n +ew values. #Note: I know this isn't very efficient (we should check if anything +changed before doing either) #But this is to limit my coding time, which is at a premium. #we'll also be using this as a flag my $id_to_update=0; my %params = $q->Vars; #Have to be careful here, there should only ever be one param that mat +ches this. foreach my $param (keys %params){ if ($param =~ /^Submit(\d+)/){ $id_to_update=$1; } } #OK I totally forgot that I didn't just concatenate the ID to the end +of the field name, I put an underscore in there. if ($id_to_update){ #first, update the resources table my $update_res_stmt = "UPDATE $res_tbl SET name=?, link=?, availab +ility=? WHERE id=?"; my $updated_name = $params{"name_$id_to_update"}; my $updated_link = $params{"link_$id_to_update"}; my $updated_avail = $params{"availability_$id_to_update"}; if ($updated_link =~ $RE{URI}){ print $q->p("Updating resource list item with name=$updated_na +me, link=$updated_link, availability=$updated_avail for id=$id_to_upd +ate"); my $sth = $dbh->prepare($update_res_stmt) or warn "prepare UPD +ATE statement failed: $dbh->errstr"; my $rv = $sth->execute($updated_name,$updated_link,$updated_av +ail,$id_to_update) || warn "Execute update failed: $dbh->errstr"; $sth->finish; print $q->p("Resources table updated with return value '$rv'." +); }else{ print $q->p("Error: Please make sure resource_link is a valid + URL. This includes the http:// part as well as the domain and path. +"); } #next, insert the locations #oh man, I forgot the id for this param. BAD CODER print $q->p("Locations param is '",$params{"locations_$id_to_updat +e"},"'"); my @locations = split /,/ , $params{"locations_$id_to_update"}; my $locs_legit_flag=1; my $offending_loc; foreach my $loc (@locations){ #print "Checking matrix location $loc"; if ($loc !~ m/^([A-Za-z])(\d+)$/){ $locs_legit_flag = 0; $offending_loc = $loc; } } if (! scalar @locations){ print $q->p("Somehow, the list of matrix locations has no loca +tions in it. Please try again."); }elsif ($locs_legit_flag){ #clear out the locations related to that resource ID from the +locations table my $delete_loc_stmt = "DELETE from $loc_tbl where resource_id= +?"; print $q->p("Clearing out location table records with resource +_id=$id_to_update"); my $sth = $dbh->prepare($delete_loc_stmt) or warn "prepare DEL +ETE statement failed: $dbh->errstr"; $sth->execute($id_to_update) || warn "Execute DELETE failed: $ +dbh->errstr"; print $q->p("Locations table cleared."); $sth->finish; #OK time to insert now my $loc_insert_stmt = "insert into $loc_tbl (resource_id, loca +tion) values (?,?)"; $sth = $dbh->prepare($loc_insert_stmt) or warn "prepare INSERT + statement failed: $dbh->errstr"; @locations = map( uc($_), @locations ); #trying to use a hash to remove duplicates #this one liner is from Effective Perl Programming via PerlMon +ks @locations = sort keys %{ { map { $_, 1 } @locations } }; foreach my $loc (@locations){ $sth->execute($id_to_update,$loc) || print "execute statem +ent failed: $DBI::errstr <br/>"; } $sth->finish; }else{ #This means that $locs_legit_flag was cleared #We did this whole escapeHTML thing in data_entry.pl so re-doi +ng it here #try to build the string ahead of time rather than interpolati +ng, so that the default call by CGI on escapeHTML runs properly my $msg = "One of the matrix locations you entered, namely '$o +ffending_loc', is invalid. All matrix locations must be a letter plus a 1-2 digit number, e.g. N9 or B19." +; #well, maybe it's not default and has to be called explicitly. $msg = CGI::escapeHTML($msg); print $q->p($msg); } } print $q->start_form; print $q->h3("Aggregate view of Procedural Harm database edit"); my $select_sth = $dbh->prepare($res_select_stmt) || print "prepare sta +tement failed: $DBI::errstr <br/>"; $select_sth->execute() || print "execute statement failed: $DBI::errst +r <br/>"; my $results = $select_sth->fetchall_arrayref(); $select_sth->finish(); my $tblstring= join '</th><th>', @data_elements; print "<table border ='1'><tr><th>$tblstring</th></tr>"; #print '<tr><td>'; foreach my $row (@$results){ print '<tr><td>'; my $id = $row->[0]; #set baseline so we get the ID my @newrow = @$row; my $i; if ( $q->param("Edit$id") ){ #start at 1 so as to skip the ID field taken care of properly for ($i = 1; $i < ((scalar @data_elements) - 1); $i++ ){ #if it's availability, let's keep it a radio group if ($data_elements[$i] eq 'availability'){ $newrow[$i] = $q->radio_group(-name=>"$data_elements[$ +i]_$id", -values=>\@avail_options, -default=>$avail_options[0], -linebreak=>'true', #-labels=>\%labels, #-attributes=>\%attributes ); }else{ $newrow[$i] = $q->textarea(-name=>"$data_elements[$i]_ +$id", -default=>$row->[$i], -rows=>5, -columns=>50); } } #whoops, was modifying $row when I needed the original values +later. #BAD Coder! BAD! #@$row = @newrow; print join '</td><td>' , @newrow; }else{ print join '</td><td>' , @$row; } print '</td><td>'; $select_sth = $dbh->prepare($loc_select_stmt) || print "prepare st +atement failed: $DBI::errstr <br/>"; $select_sth->execute($id) || print "execute statement failed: $DBI +::errstr <br/>"; my @locs; while (my $ref = $select_sth->fetchrow_arrayref()){ push @locs , $ref->[0]; } $select_sth->finish(); my $locs_for_cell = join ',' , @locs; if ( $q->param("Edit$id") ){ #dafuq was I thinking here? This is clearly the locations tex +tarea #I had it as $data_elements[$i+1 but I guess $i got incremente +d one last time to fail the conditional in the loop #I guess I'll leave it in case I add fields to the table/data +elements to the array? print $q->textarea(-name=>"$data_elements[$i]_$id", -default=> +$locs_for_cell); print $q->submit(-name=>"Submit$id",-value=>"Submit edits for +record $id"); }else{ print $locs_for_cell; print $q->submit(-name=>"Edit$id",-value=>"Edit row $id"); } print '</td></tr>'; } print '</table>'; print $q->end_form; #$select_sth->finish(); $dbh->disconnect(); print $q->end_html;

Comment on Mystified by why my UPDATE statement is not reflected in my database display
Download Code
Re: Mystified by why my UPDATE statement is not reflected in my database display
by moritz (Cardinal) on Jul 05, 2013 at 19:48 UTC

    For debugging purposes it's usually a good idea to pass the RaiseError => 1 option to the connect call, just in case you forget to print the DBI->errstr somewhere.

    Also DBI->trace(1) might provide some insight.

    Finally it is recommended that you explicitly pass AutoCommit => 1 or AutoCommit => 0 to the connect call (and in the latter case don't forget to commit your changes!).

      Ah, thanks. Setting RaiseError and AutoCommit to 1 didn't change anything (either output or behavior).

      I'll have to pipe the output of the trace to a file and examine the file, since this is a CGI script.

      I guess the Carping of stuff to the browser doesn't work for trace from DBI, because I tried and I get no output to the browser.
Re: Mystified by why my UPDATE statement is not reflected in my database display
by nikosv (Hermit) on Jul 05, 2013 at 20:32 UTC
    I haven't checked your code but 'execute' will always return a true value if it run successfully regardless if it actually updated,in this case, or not.

    Most drivers will return '0E0' , and mysql's in particular, when the statement runs without errors but has updated 0 rows

    so it's best to use :
    if ($rv eq '0E0')
Re: Mystified by why my UPDATE statement is not reflected in my database display
by 5mi11er (Deacon) on Jul 05, 2013 at 21:33 UTC
    I attempted to reduce your code to something that just set information on a static entry, then printed all the entries. From this, I was able to see that the update worked just fine. So, I'd be inclined to think that the problem might be in the complicated stuff near the end of your code, and not in the actual database update code.

    -Scott

    #!/usr/bin/perl -w use strict; use DBI; #use autodie; $\="\n"; my $res_tbl="SOMETABLE"; my $loc_tbl="OTHERTABLE"; my $res_select_stmt = "Select * from $res_tbl order by id"; my $loc_select_stmt = "Select location from $loc_tbl where resource_id + = ?"; my $db="SOMEDB"; my $dbun="SOMEUN"; my $dbpw = 'SOMEPW'; my $dbhost="SOMEHOST.hostedresource.com"; my $res_tbl="resource_list"; my $loc_tbl="matrix_locations"; my $drh = DBI->install_driver("mysql") || print "install_driver failed +: $DBI::errstr <br/> +"; my $dsn = "DBI:mysql:database=$db;host=$dbhost"; my $dbh = DBI->connect($dsn,$dbun,$dbpw) || print "DB connect failed: +$DBI::errstr <br/>" my $res_select_stmt = "Select * from $res_tbl order by id"; my $loc_select_stmt = "Select location from $loc_tbl where resource_id + = ?"; my $update_res_stmt = "UPDATE $res_tbl SET name=?, link=?, availabilit +y=? WHERE id=?"; print "Updating $res_tbl item with stuff \n"; my $sth = $dbh->prepare($update_res_stmt) or warn "prepare UPDATE stat +ement failed: $dbh->errstr"; my $rv = $sth->execute("test","link test", "test availability", 7) || +warn "Execute update failed: $dbh->errstr"; $sth->finish; print "$res_tbl updated with return value '$rv'.\n"; my $select_sth = $dbh->prepare($res_select_stmt) || print "prepare sta +tement failed: $DBI::errstr <br/>"; $select_sth->execute() || print "execute statement failed: $DBI::errst +r <br/>"; my $results = $select_sth->fetchall_arrayref(); $select_sth->finish(); foreach my $row (@$results){ print join ',' , @$row; print '\n'; } $dbh->disconnect();
Re: Mystified by why my UPDATE statement is not reflected in my database display
by OfficeLinebacker (Chaplain) on Jul 08, 2013 at 18:55 UTC

    I found the error. Thanks to the suggestion to use the trace feature. I configured it to print the trace at level 4 to a file which I examined. The problem was I used the wrong column names for name and link.

    "UPDATE $res_tbl SET name=?, link=?, availability=? WHERE id=?";

    should have been

    "UPDATE $res_tbl SET resource_name=?, resource_link=?, availability=? WHERE id=?";

    However, I'm trying to understand why

    $sth->execute($updated_name,$updated_link,$updated_avail,$id_to_update) || warn "Execute update failed: $dbh->errstr";

    did not print an error to the browser; I guess because it returned a 1, even though it failed? I guess it should have been

    $sth->execute($updated_name,$updated_link,$updated_avail,$id_to_update) && warn "Execute update failed: $dbh->errstr";?

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others pondering the Monastery: (8)
As of 2014-09-20 07:18 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    How do you remember the number of days in each month?











    Results (157 votes), past polls