This code snippet solved a problem I had where I needed to update a series of rows in a purchase order database with numbers being stored in cgi variables based on oid (object identification) numbers.

The cgi variables were named via oid's which was convenient at the time. I don't know if databases other than PostgreSQL have oids, but I wouldn't be surprised if they did. Some of the variable and placeholders may be unecessary, but that's why I am posting this, to see other right ways to do this.

  $sth = $dbh->prepare(q{SELECT oid from items where orderid = ?}) || 
+die $dbh->errstr;
  $sth->execute($orderid) || die $dbh->errstr;
  my @oids;
  my $oid;
  $sth->bind_columns(\$oid);  # Binds each column to $oid, through ref
+erence
  my $i;
  my $max;
  for ($i = 0; $sth->fetch; $i++) { # The fetch updates $oid to the ne
+xt one.
    $oids[$i] = $oid;               
    $max = $i;
  }
  for ($i = 0; $i <= $max; $i++) {
    my $price = $q->param("item_" . $oids[$i] . "_price"); # Gets pric
+e from cgi
    my $curoid = $oids[$i]; # unecessary...?
    $sth = $dbh->prepare(q{UPDATE items set price = (?) WHERE oid = (?
+)}) || die $dbh->errstr;
    $sth->execute($price, $curoid) || die $dbh->errstr;
  }

[download]

Replies are listed 'Best First'.
RE: DBI Select and Update
by Anonymous Monk on May 31, 2000 at 18:38 UTC
    this isnt tested but how about : 
      while ($sth->fetch) { 
    push @oids, $oid;
  }

  for (@oids) {
    my $price = $q->param("item_" . $_ . "_price");
    $sth = $dbh->prepare(q{UPDATE items set price = ? WHERE oid = ?});
    $sth->execute($price, $_);
  }

    [download]
    no need for $max or $i just a hack on a bored day
[reply]
[d/l]
      Why are you preparing each time with placeholders? Should be more like:

      my $sth_udt = $dbh->prepare(qq{UPDATE items set price = ? WHERE oid = 
+?});

while (my $val = $sth->fetchrow) {
    my $price = param("item_${val}_price");
    $sth_udt->execute($price, $val);
    # You could probably just do:
    # $sth_udt->execute(param("item_${val}_price"), $val);
}

      [download]

      Cheers,
      KM

[reply]
[d/l]

        Now that's a good call. Good syntax as well. Thanks a ton. It defeats the purpose of the placeholders to call them every loop

        My problem was my inability to realize i could make more than one statement handle, that makes everything easier, even though the man pages no doubt explicitly state that you can. Go go perlmonks.

        Ok, now thanks for all the help, here is the finished product:

        Note that i stayed with the bind_columns/fetch idea, which should be faster. I suppose, though, that a bind_col might speed it up even more... 

        
  $sth = $dbh->prepare(q{SELECT oid from items where orderid = ?}) || 
+die $dbh->errstr;
  $sth->execute($orderid) || die $dbh->errstr;
  $sth->bind_columns(\$oid);  # Binds each column to $oid, through ref
+erence

  my $sth_udt = $dbh->prepare(qq{UPDATE items set price = ? WHERE oid 
+= ?});
  while ($sth->fetch) {
      my $price = param("item_${oid}_price");
      $sth_udt->execute($price, $oid);
      # You could probably just do this, but it didn't work for me...?
+!?!:
      # $sth_udt->execute(param("item_${oid}_price"), $oid);
  }

        [download]
[reply]
[d/l]
        duh, missed that.....
[reply]
      whoops, didnt mean to go ANON
[reply]
        hmm
[reply]

Back to Cool Uses for Perl