You aren't quoting the column values for DBI. The simplest way to force that is to rewrite the query with placeholders:
sub db_update_field {
my $table = shift;
my $record = shift;
my $field = shift;
my $value = shift;
my $dbh = dbconnect();
my $db_update_field = $dbh->prepare (
"UPDATE $table SET $field = ? WHERE id = ?"
);
$db_update_field->execute ($value, $record);
}
The alternative is to call $dbh->quote on the values. I also corrected the connect call to provide the db handle.
Does your dbconnect() routine set up the RaiseError flag?
After Compline,
Zaxo | [reply]
[d/l] |
I want to place my most common SQL queary formats in a subroutine and pass the parameters to it in sub calls. 90% of the time my results are perfect, 10% I get quirky errors.
Given your approach, 90/10 is about right. The thing that's tripping you up is quoting, and given the approach you're using, you're going to either have a sub that can handle about 90%, or you're going to push some real ugliness out to clients of the sub.
To insert "now()" into the query, you don't want the <quote>db_update_field</code> to be adding quotes. But to insert "t", you do. See the problem? For both to work, you need to push quoting out to the client, and do
db_update_field("users", $current_id, "last_seen_time", "now()");
db_update_field("users",$current_id,"last_seen_area","'$stuff'");
The second problem with your approach is that when you have a pair of values to update, you need to either invoke db_update_field twice (within a transaction), or you need to implement db_update_fields2, then db_update_fields3, then ... it keeps going downhill.
Instead a brittle, plug-in-the-pieces-to-run-a-query approach, build a set of routines that reflect the semantic actions you're performing.
| [reply]
[d/l]
[select] |
Seems to me you're creating SQL statements directly, but consider this:
UPDATE table SET foo = bar WHERE baz = boo
That probablyl isn't a valid SQL statement. However, this following might be:
UPDATE table SET foo = 'bar' WHERE baz = 'boo'
Yep. Quotation. To overcome this, you should NOT try to quote these things by yourself. Instead, use placeholders:
my $sth = $dbh->prepare(
qq|UPDATE $table SET $field = ? WHERE id = ?|
);
$sth->execute( $value, $record );
Of course, this still wouldn't work if $field or $table contained some bad chars. So I personally REALLY REALLY am against using this type of dynamic SQL generation. Might as well write each one of them out, cause you're bound get fewer mistakes | [reply]
[d/l]
[select] |
Well, you don't give us a lot to go on. What are the "quirky errors" that you get, for starters?
I'm also forced to wonder how sure you are that the datatypes are correct. I say this because of the examples you give. "13" works but "t" doesn't. "now()" works but "text" doesn't... Those examples seem to suggest a datatype issue even if it isn't one.
-sauoq
"My two cents aren't worth a dime.";
| [reply] |
Something that may help out in uncovering your problem is to die if you $db_update_field->execute(); fails.
Try something like this:
$db_update_field->execute() or die $dbh->errstr;
| [reply]
[d/l]
[select] |