dbi mysql problem with placeholders and the default keyword

[david]

Hi all,

I have the following question:
in my sql i write
UPDATE A SET B = DEFAULT
WHERE C = 1;

In mysql i write:
my $sth = $dbh->prepare('UPDATE A SET B = ? WHERE C = ?');
and then
$sth->execute('DEFAULT',1);

The problem is that when column B is a text the word DEFAULT will be
inserted instead the default of the column.

Has someone a solution for the problem ?

Thanks,
David

 

[Frank Seitz]

my $sth = $dbh->prepare('UPDATE A SET B = ? WHERE C = ?');
and then
$sth->execute('DEFAULT',1);

The problem is that when column B is a text the word DEFAULT will be
inserted instead the default of the column.

Has someone a solution for the problem ?

You need another statement handle to achieve this:

my $sth2 = $dbh->prepare('UPDATE A SET B = DEFAULT WHERE C = ?');
$sth2->execute(1);

A SQL placeholder stands for a value, not an expression.

Frank

 

[xhoster]

Hi all,

I have the following question:
in my sql i write
UPDATE A SET B = DEFAULT
WHERE C = 1;

In mysql i write:
my $sth = $dbh->prepare('UPDATE A SET B = ? WHERE C = ?');
and then
$sth->execute('DEFAULT',1);

The problem is that when column B is a text the word DEFAULT will be
inserted instead the default of the column.

DEFAULT is syntax, not data.

Has someone a solution for the problem ?

Don't use place holders for syntax, use them for data.

$dbh->prepare('UPDATE A SET B = DEFAULT WHERE C = ?');


Xho

--
-------------------- http://NewsReader.Com/ --------------------
The costs of publication of this article were defrayed in part by the
payment of page charges. This article must therefore be hereby marked
advertisement in accordance with 18 U.S.C. Section 1734 solely to indicate
this fact.

 

[Marc Lucksch]

A SQL placeholder stands for a value, not an expression.

Why not:

my $sth = $dbh->prepare('UPDATE A SET B = ".($default?"DEFAULT:"")."
WHERE C = ?');

Or on a per line base (I don't know it this works, someone test it (mysql)

my $sth = $dbh->prepare('UPDATE A SET B = IFNULL(?,DEFAULT) WHERE C = ?');
$sth->execute(undef,1); #undef instead of 'DEFAULT'

# Shouldn't SQL do this anyway using DEFAULT on NULL? (for
# NOTNULL columns)

or with 2 queries, calling the first on DEFAULT and the second on VALUE

my $sth = $dbh->prepare('UPDATE A SET B = ? WHERE C = ?');
my $sthdefault = $dbh->prepare('UPDATE A SET B = DEFAULT WHERE C = ?');

....
$value=1; #or whatever.
if ($default) {
$sthdefault->execute($value);
}
else {
$sthdefault->execute($someothervalue,$value);
}

Marc "Maluku" Lucksch

 

[Frank Seitz]

Why not:

my $sth = $dbh->prepare('UPDATE A SET B = ".($default?"DEFAULT:"")." WHERE C = ?');

Looks strange.

Or on a per line base (I don't know it this works, someone test it (mysql)

my $sth = $dbh->prepare('UPDATE A SET B = IFNULL(?,DEFAULT) WHERE C = ?');
$sth->execute(undef,1); #undef instead of 'DEFAULT'

Nice idea but not allowed:

UPDATE [LOW_PRIORITY] [IGNORE] table_reference
SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]

Frank