CODEWITHSUNDEEP
mysqlperldbi
user1664433
user1664433

Reputation: 63

Perl DBI mysql update

namekey values . . . . . . username to be updated

#!/usr/bin/perl -w
# Use the DBI module

use  strict;
use warnings;
use DBI;

# CONFIG VARIABLES
my $platform = "mysql";
my $database = "prod";
my $host     = "localhost";
my $username = "root";
my $password = "admin";

# DATA SOURCE NAME
my $dsn = "dbi:$platform:$database:$host";

# PERL DBI CONNECT
my $connect = DBI->connect($dsn, $username, $password);

# VARS for Examples
my $query;
my $query_handle;
my $id;
my $demo;

# Example 2 using do() UPDATE   

# SAMPLE VARIABLE AND VALUES TO PASS INTO SQL STATEMENT
$id   = "username";
$name = "Arty";

# do() THE UPDATE
$query        = "UPDATE jos_config SET values = '$name' WHERE namekey = $id;";
$query_handle = $connect->prepare($query);

# EXECUTE THE UPDATE
$query_handle = $connect->do($query);

print STDERR "ERROR: $DBI::errstr";
print STDERR "INFO: $query_handle rows updated";

undef $query;

error message: 

DBD::mysql::db do failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'values = 'Arty' WHERE namekey = smtp_username' at line 1 at /home/arty/Documents/SmtpDbchange2.pl line 40.
ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'values = 'Arty' WHERE namekey = smtp_username' at line 1Use of uninitialized value $query_handle in concatenation (.) or string at /home/arty/Documents/SmtpDbchange2.pl line 43.

please assist

Upvotes: 1

Views: 7163

Answers (4)

user3045873
user3045873

Reputation: 21

You can try this:

my $sthSetid = $dbh->prepare("UPDATE $table SET nameOfColumnToChange = ? WHERE id = ?");
$sthSetid->execute($newData, $refGetid[0]);
$sthSetid->finish;

Upvotes: 0

pilcrow
pilcrow

Reputation: 58589

Your syntax error comes from the fact that "values" is a reserved word in SQL. You need to quote that token when it functions as an identifier:

UPDATE jos_config SET `values` = ...

Generally, MySQL shows you just where the syntax error is at the beginning of the query excerpt, as it did for you.

Your next problem will be that you have not properly quoted the literal value you compare against namekey, and that literal value will look like an identifier rather than a string to MySQL. The solution here is to forget about interpolating variables and use parameterized queries:

$connect->do('UPDATE jos_config SET `values`=? WHERE namekey=?', undef, $name, $id);

Upvotes: 6

ysth
ysth

Reputation: 98398

If you get an error like this, print out the offending statement:

print $query;

which shows:

UPDATE jos_config SET values = 'Arty' WHERE namekey = username;

Then stare at it till you see the missing quotes.

Then stop doing it that way and use placeholders:

$query = "UPDATE jos_config SET values=? WHERE namekey=?;";
$query_handle = $connect->prepare($query);
my $result = $connect->do($query, {}, $name, $id);

Upvotes: 0

mmertel
mmertel

Reputation: 397

Looks like you are missing some quote marks around the namekey value $id:

$query = "UPDATE jos_config SET values = '$name' WHERE namekey = '$id';";

Upvotes: 0

Related Questions