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;
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!).
| [reply] [Watch: Dir/Any] [d/l] [select] |
|
| [reply] [Watch: Dir/Any] |
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();
| [reply] [Watch: Dir/Any] [d/l] |
Re: Mystified by why my UPDATE statement is not reflected in my database display
by nikosv (Deacon) 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')
| [reply] [Watch: Dir/Any] [d/l] |
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";?
| [reply] [Watch: Dir/Any] [d/l] [select] |
|
|