MYSQL $dbh->do

andrew has asked for the wisdom of the Perl Monks concerning the following question:

ermm, this is really annoying Im trying to update a table called settings in my database, and I need to use placeholders on this, I tried this buti t ain no placeholer and I get an error.

    $key = param('key');
    $des = param('des');
   $dbh->do("UPDATE settings SET key='$key', des='$des'") or die $dbh-
+>errstr;
[download]

Error: 

You have an error in your SQL syntax near 'key='sss', des='sss' WHERE 
+1' at line 1 at admin.cgi line 177.
[download]

Comment on MYSQL $dbh->do Select or Download Code

Replies are listed 'Best First'.
Re: MYSQL $dbh->do by dws (Chancellor) on Sep 28, 2002 at 20:20 UTC
I tried this buti t ain no placeholer and I get an error. Did you really manage to create a table with a column named 'key'? I kinda doubt it. 'KEY' is a reserved word (in ANSI92 SQL as well as in MySQL). Consult the reserved word table in your MySQL doc.	[reply]
Re: MYSQL $dbh->do by Juerd (Abbot) on Sep 28, 2002 at 20:23 UTC
Update - What dws says makes sense. You can use backticks to still be able to use those names as column names (in all other SQL it's double quotes, but they work like single quotes (creating string values) in MySQL). I added them to my example of good placeholder usage. Best is probably to rename your column. (Untested) $dbh->do('UPDATE settings SET `key` = ?, des = ?', undef, $key, $des) +or die $dbh->errstr; [download] DBI perlstyle strict - Yes, I reinvent wheels. - Spam: Visit eurotraQ.	[reply] [d/l]
Re: Re: MYSQL $dbh->do by andrew (Acolyte) on Sep 28, 2002 at 20:41 UTC
`You have an error in your SQL syntax near '' at line 1 at admin.cgi line 207.` Doesnt work!!!	[reply] [d/l]
Re: Re: Re: MYSQL $dbh->do by Juerd (Abbot) on Sep 28, 2002 at 20:49 UTC
Doesnt work!!! Oh, right. Stupid DBI again. I forgot to add undef. `use DBIx::Simple; my $db = DBIx::Simple->connect(...); $db->query('UPDATE ... SET foo=?, bar=?', $foo, $bar);` [download] There, a lot better :) - Yes, I reinvent wheels. - Spam: Visit eurotraQ.	[reply] [d/l]
Re: Re: Re: Re: MYSQL $dbh->do by andrew (Acolyte) on Sep 28, 2002 at 20:54 UTC
Re: MYSQL $dbh->do by andrew (Acolyte) on Sep 28, 2002 at 20:40 UTC
I changed key to keyword and works good but im still stuck on the placeholder issue!	[reply]
Re: Re: MYSQL $dbh->do by JupiterCrash (Monk) on Sep 29, 2002 at 18:17 UTC
What do you mean by placeholder issue? What is the placeholder that you refer to? Is your 'key' column a primary key by any chance? The update statement that you run does not have a WHERE clause attached to it, and will therefore try to update every row. (is that what you want?) Could you be violating some primary key constraint?	[reply]