Nikhil Hegde Nikhil Hegde - 1 month ago 13
MySQL Question

UPDATE query is not working in DBI

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

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);
Comments