dbi mysql problem with placeholders and the default keyword

Discussion in 'Perl Misc' started by david, Feb 12, 2009.

  1. david

    david Guest

    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
     
    david, Feb 12, 2009
    #1
    1. Advertising

  2. david

    Frank Seitz Guest

    david wrote:
    >
    > 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
    --
    Dipl.-Inform. Frank Seitz; http://www.fseitz.de/
    Anwendungen für Ihr Internet und Intranet
    Tel: 04103/180301; Fax: -02; Industriestr. 31, 22880 Wedel
     
    Frank Seitz, Feb 12, 2009
    #2
    1. Advertising

  3. david

    Guest

    david <> wrote:
    > 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.
     
    , Feb 12, 2009
    #3
  4. david

    Marc Lucksch Guest

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


    > 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
     
    Marc Lucksch, Feb 12, 2009
    #4
  5. david

    Frank Seitz Guest

    Marc Lucksch wrote:
    > Frank Seitz schrieb:
    >> david wrote:
    >>>
    >>> my $sth = $dbh->prepare('UPDATE A SET B = ? WHERE C = ?');
    >>> and then
    >>> $sth->execute('DEFAULT',1);

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

    >
    > 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
    --
    Dipl.-Inform. Frank Seitz; http://www.fseitz.de/
    Anwendungen für Ihr Internet und Intranet
    Tel: 04103/180301; Fax: -02; Industriestr. 31, 22880 Wedel
     
    Frank Seitz, Feb 12, 2009
    #5
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Paul Vudmaska

    dbi:mysql mysql has gone away

    Paul Vudmaska, Apr 21, 2004, in forum: Ruby
    Replies:
    0
    Views:
    125
    Paul Vudmaska
    Apr 21, 2004
  2. Jerome Hauss
    Replies:
    0
    Views:
    189
    Jerome Hauss
    Oct 13, 2004
  3. Tim Haynes
    Replies:
    3
    Views:
    158
    Ron Reidy
    Sep 13, 2003
  4. iain
    Replies:
    2
    Views:
    197
  5. Ulrich Herbst

    SQL Injection and DBI placeholders

    Ulrich Herbst, Jun 15, 2004, in forum: Perl Misc
    Replies:
    4
    Views:
    143
    Vetle Roeim
    Jun 16, 2004
Loading...

Share This Page