Update statement with Perl DBI for MySQL 5.0

[sam]

Hi,
Here is my sample code:
$sql ="update transaction set salesvolume=$salesvol, netsales=$netsales,
transtype=\"$transtype\",
returnreason=\"$returnreason\"
where prodcode=\"$prodcode\" and
custcode=\"$custcode\" and date=$transdate";
$udpate_sql = qq {$sql;};
$sth = $dbh->prepare($update_sql) || die "prepare: $update_sql:
$DBI::errstr";
$sth->execute || die "Unable to execute query: $dbh->errstr\n";
$sth->finish;
print $sql;

Currently I found somethingn strange with mysql(perhaps with perl dbi as
well), after setup 1 or 2 test data, I tried to make a change to one of
the fiield in a table, but after update is completed, I don't see the
corresponding field in a record is updated by the new value.

If I copy the update statement exactly from the output of the print
statement ( print $sql), and paste it to the mysql login prompt to
execute it, the update statement update the record instantly. I don't
know what is happening here, why perl dbi does not do what the update
statement supposed to do? Have I missed a commit statement? but I don't
have idea how to place a commit statement to perl dbi.

Thanks
Sam

[Sherm Pendley]

Post by sam
$sql ="update transaction set salesvolume=$salesvol, netsales=$netsales,
transtype=\"$transtype\",
returnreason=\"$returnreason\"
where prodcode=\"$prodcode\" and
custcode=\"$custcode\" and date=$transdate";
$udpate_sql = qq {$sql;};
$DBI::errstr";
$sth->execute || die "Unable to execute query: $dbh->errstr\n";
$sth->finish;
print $sql;
Currently I found somethingn strange with mysql(perhaps with perl dbi as
well), after setup 1 or 2 test data, I tried to make a change to one of
the fiield in a table, but after update is completed, I don't see the
corresponding field in a record is updated by the new value.

It sounds like a quoting problem - your example is just *begging* for
placeholders anyway. Here's how you'd use placeholders instead:

my $sql = 'update transaction
set salesvolume=?, netsales=?,
transtype=?, returnreason=?,
where prodcode=? and custcode=? and date=?';

my $sth = $dbh->prepare($sql) || die "prepare: $sql: $DBI::errstr";

$sth->execute($salesvol, $netsales, $transtype, $returnreason,
$prodcode, $custcode, $transdate) ||
die "Unable to execute query: $dbh->errstr";
$sth->finish;

Using placeholders does two things for you: First, it handles all the
quoting automagically. That's handy. And second, if you're inserting more
than one row, it's far more efficient: Instead of repeatedly building up a
series of SQL strings that you prepare() and execute() one at a time, you
need only call prepare() once, and then make as many calls to execute() as
you need to.

sherm--

[J. Gleixner]

Post by sam
Hi,

[...]

Post by sam
$udpate_sql = qq {$sql;};
$DBI::errstr";

If this truly is your code.. update_sql is undefined.

use strict;

Would likely make the typo more obvious. Also, you could also use single
quotes, making your SQL statement much simpler, placeholders would be
the best solution.