Nikhil Hegde Nikhil Hegde - 3 months ago 19
MySQL Question

PERL to SQL DBI Module not working properly

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
field of a database based on
but it's not working through the
statement. It prints out
Scheduled Jobs deleted
and the entire program runs successfully but it doesn't update the database
field. If I remove
and change it to
then the
entry in the database changes.

Any idea how to get around this? Thanks!


Perl does not expand variables inside single-quoted strings. 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.


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