dbi mysql problem with placeholders and the default keyword

D

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
 
F

Frank Seitz

david said:
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
 
X

xhoster

david said:
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.
 
M

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
 
F

Frank Seitz

Marc said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,928
Messages
2,570,068
Members
46,513
Latest member
JacklynMcC

Latest Threads

Top