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

Can someone tell me how to fix this code.
I am not understanding I am supposed to pass the in the update statement? 
my $sql = "select max(Number) from Volunteer where Date =? and Volunte
+er = 'TBD'";
   my $number = $dbh->selectcol_arrayref($sql, undef, $date);

   my $id = "Date = '$date' and Volunteer = 'TBD' and Number = $number
+";
   my $content = "Volunteer";
   my $status = "$name";
   my $sth=$dbh->prepare('UPDATE Volunteer set Volunteer =?, WHERE =?'
+);
   my $rv=$sth->execute($status, $id);

        print $query->h1({-align=>'center'}, "You signed up for this d
+ate $date");
$sth->finish ();
$dbh->disconnect ();

[download]

Replies are listed 'Best First'.
Re: Updating a database
by dws (Chancellor) on Jan 15, 2002 at 10:20 UTC
    Can someone tell me how to fix this code.

    First, you need to understand that you can't assemble a WHERE clause by plugging an expression into a placeholder (except in certain obscure cases where the underlying database doesn't really support prepare). You can only safely bind discrete values.

    Try something like this: 

      my $str = $dbh->prepare(
      "UPDATE Volunteer " .
      "SET Volunteer = ? " .
      "WHERE Date = ? AND Volunteer = 'TBD' AND Number = ?"
  );
  my $rv = $sth->execute($status, $date, $id);

    [download]
[reply]
[d/l]
[select]
Re: Updating a database
by grep (Monsignor) on Jan 15, 2002 at 10:04 UTC
    my $sth=$dbh->prepare('UPDATE Volunteer set Volunteer =?, WHERE =?');

    You forgot the field to test for in your WHERE clause and you do not need that comma.

    my $sth=$dbh->prepare('UPDATE Volunteer set Volunteer = ? WHERE field_name = ?');

    I would recommend not using a field name the same as a table name it tends to cause confusion. I also generally use leading upper case for tables and all lower case for fields, makes it easier to distinguish between fields, tables and SQL statements.

    There is an excellent SQL course at http://sqlcourse.com and http://sqlcourse2.com.

    grep 
    grep> cd pub
grep> more beer

    [download]
[reply]
[d/l]
[select]
Re: Updating a database
by Zaxo (Archbishop) on Jan 15, 2002 at 11:02 UTC

    You are attempting to sock an entire WHERE clause into a single placeholder. I think you want:

    my $sth=$dbh->prepare("UPDATE Volunteer set Volunteer =?, WHERE $id");

    [download]
    Note double quotes, they allow $id to be interpolated.

    What does 'TBD' indicate? Are your Volunteers unidentified until they attain max(Number)? I'm a little puzzled by what your code seems to try to do. I should think:

    my $sth = $dbh->prepare("select Date from Volunteer where Volunteer=?"
+);
$sth->execute($user);

    [download]
    would be enough. I mistrust having the table and column have the same name.

    After Compline,
    Zaxo

[reply]
[d/l]
[select]
Re: Updating a database
by screamingeagle (Curate) on Jan 15, 2002 at 10:37 UTC
    Or...
    $sql = <<EOF;
    update Volunteer set volunteer_id = $new_id and Number = $newnumbe
+r where id = 2
EOF
    ;
    $dbh->do($sql) || return "ERROR: '$sql'";

    [download]

    ...where $dbh is the database handle
[reply]
[d/l]
      The problem with this approach is you do not get the proper quoting that placeholders provide. You sql would break if

      $new_id = "I'm tired"

      grep 
      grep> cd pub
grep> more beer

      [download]
[reply]
[d/l]
[select]
      As grep. Also, if you don't use placeholders then there's no need to assign the query to a var before doing it. And the DBI error string would be interesting if it dies. If I wasn't going to use place holders I'd do 
      $dbh->do("update Volunteer set volunteer_id = $new_id and Number = $ne
+wnumber where id = 2") or die $dbh->errstr;

      [download]
      ... except for grep's reason I *would* use placeholders, so I'd use a slight tweak on dws: 
      

  my $sth = $dbh->prepare("UPDATE Volunteer " .
      "SET Volunteer = ? " .
      "WHERE Date = ? AND Volunteer = 'TBD' AND Number = ?"
  ) or die $dbh->errstr;
  my $rv = $sth->execute($status, $date, $id) or die $dbh->errstr;

      [download]


      § George Sherston
[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom