UPDATE query is not working in DBI

Question

use DBI;

my $jobID = 1;

$dbh = DBI->connect("dbi:mysql:$database:$db_server", $user, $password) or die "Connection ERROR!";
$dbh->do('USE MultiRunScheduler');
$dbh->do('UPDATE Scheduler SET RequestStatus="CANCELED" WHERE ID="$jobID";')
print "Scheduled Jobs deleted";

I'm trying to change the RequestStatus field of a database based on ID but it's not working through the UPDATE statement. It prints out Scheduled Jobs deleted and the entire program runs successfully but it doesn't update the database RequestStatus field. If I remove ID="$jobID" and change it to ID=1 then the RequestStatus entry in the database changes.

Any idea how to get around this?

Answer 1

Perl does not expand variables inside single-quoted strings.

http://perldoc.perl.org/perldata.html#Scalar-value-constructors says in part:

String literals are usually delimited by either single or double quotes. They work much like quotes in the standard Unix shells: double-quoted string literals are subject to backslash and variable substitution; single-quoted strings are not (except for \' and \ ).

Also you should use single-quotes for string delimiters inside your SQL.

So this:

$dbh->do('UPDATE Scheduler SET RequestStatus="CANCELED" WHERE ID="$jobID";')

Should be this:

$dbh->do("UPDATE Scheduler SET RequestStatus='CANCELED' WHERE ID='$jobID'");

If you want to really use best practices, use bind parameters in your SQL instead of putting variables inside strings. Then you don't need to worry about what kind of quotes you use. Even if the parameter is a string type, you don't put the parameter placeholder in quotes in SQL.

Example:

$dbh->do("UPDATE Scheduler SET RequestStatus='CANCELED' WHERE ID=?", undef, $jobID);