DBI: adding columns to table

Discussion in 'Perl Misc' started by Bigus, May 18, 2005.

  1. Bigus

    Bigus Guest

    Has anyone had probs adding columns to a table using DBI and the ALTER TABLE
    command?

    I've been sending the command in a loop that processes some key/value pairs,
    checks if the column already exists and if not attempts to create it:

    foreach my $k(keys %keyvals){
    my $result = $db->do("DESCRIBE list_headers '$k'");
    logit("Failed to describe column $k ".$db->errstr,0) if $db->errstr ;
    if($result != 1){
    $db->do("ALTER TABLE list_headers ADD '$k' TEXT");
    logit("Failed to add column $k ".$db->errstr,1) if $db->errstr ;
    }
    }

    The error that's returned is:

    DBD::mysql::db do failed: You have an error in your SQL syntax; check
    the manual
    that corresponds to your MySQL server version for the right syntax to
    use near
    ''owner' TEXT' at line 1 at header.pl line 44.

    That's where $k = "owner". If I remove the single quotes round $k in teh
    ALTER TABLE line then the owner column is created fine, but of course it
    then falls over when $k = "reply-to" because of the hyphen.

    When I use PHPMyadmin to create the owner column it works fine and tells me
    it's used the syntax:

    ALTER TABLE `list_headers` ADD `owner` TEXT

    I've tried using the prepare & execute method but it falls over with
    exactly the same error.

    This sounds like a MySQL problem to me but if that's the case why does
    PHPMyadmin work? Anyone had this prob and got round it?

    Bigus
    Bigus, May 18, 2005
    #1
    1. Advertising

  2. Bigus

    Bigus Guest

    Re: adding columns to table

    Hmm, fixed it... A flash of inspiration just caused me to try using
    back-ticks, ie:

    $db->do("ALTER TABLE list_headers ADD `$k` TEXT");

    and it worked! Can't say I understand it at all because I've used
    single-quotes for everything else for the past 2 years and there doesn't
    appear to be any mention of it in the DBI manual (and prepare should have
    used backticks if that was what was needed anyway).



    "Bigus" <> wrote in message
    news:d6fk9t$s6j$...
    > Has anyone had probs adding columns to a table using DBI and the ALTER
    > TABLE command?
    >
    > I've been sending the command in a loop that processes some key/value
    > pairs, checks if the column already exists and if not attempts to create
    > it:
    >
    > foreach my $k(keys %keyvals){
    > my $result = $db->do("DESCRIBE list_headers '$k'");
    > logit("Failed to describe column $k ".$db->errstr,0) if $db->errstr ;
    > if($result != 1){
    > $db->do("ALTER TABLE list_headers ADD '$k' TEXT");
    > logit("Failed to add column $k ".$db->errstr,1) if $db->errstr ;
    > }
    > }
    >
    > The error that's returned is:
    >
    > DBD::mysql::db do failed: You have an error in your SQL syntax; check
    > the manual
    > that corresponds to your MySQL server version for the right syntax to
    > use near
    > ''owner' TEXT' at line 1 at header.pl line 44.
    >
    > That's where $k = "owner". If I remove the single quotes round $k in teh
    > ALTER TABLE line then the owner column is created fine, but of course it
    > then falls over when $k = "reply-to" because of the hyphen.
    >
    > When I use PHPMyadmin to create the owner column it works fine and tells
    > me it's used the syntax:
    >
    > ALTER TABLE `list_headers` ADD `owner` TEXT
    >
    > I've tried using the prepare & execute method but it falls over with
    > exactly the same error.
    >
    > This sounds like a MySQL problem to me but if that's the case why does
    > PHPMyadmin work? Anyone had this prob and got round it?
    >
    > Bigus
    >
    >
    Bigus, May 18, 2005
    #2
    1. Advertising

  3. Bigus

    rahul Guest

    Re: adding columns to table

    Bigus wrote:
    > Hmm, fixed it... A flash of inspiration just caused me to try using
    > back-ticks, ie:
    >
    > $db->do("ALTER TABLE list_headers ADD `$k` TEXT");
    >
    > and it worked! Can't say I understand it at all because I've used
    > single-quotes for everything else for the past 2 years and there

    doesn't
    > appear to be any mention of it in the DBI manual (and prepare should

    have
    > used backticks if that was what was needed anyway).


    Single quotes would make '$k' a string and the value contained in it
    will not be interpolated into your SQL statement.
    rahul, May 18, 2005
    #3
  4. Bigus

    phaylon Guest

    Re: adding columns to table

    rahul wrote:

    > Single quotes would make '$k' a string and the value contained in it will
    > not be interpolated into your SQL statement.


    Inside doublequotes? Here's my outcome:

    phaylon@gaia:~> perl -Mwarnings -Mstrict
    my $k = 'Test';
    my $sql = "ALTER TABLE list_headers ADD '$k' TEXT";
    print $sql, "\n";^D
    ALTER TABLE list_headers ADD 'Test' TEXT

    So this shouldn't be a problem. I would guess the database treats object
    names w/o single-quotes as case-insensitive, and with single-quotes,
    case-sensitive.

    But it's just a guess :D

    --
    http://www.dunkelheit.at/
    sapere aude.
    phaylon, May 18, 2005
    #4
  5. Bigus

    rahul Guest

    Re: adding columns to table

    phaylon wrote:
    > rahul wrote:
    >
    > > Single quotes would make '$k' a string and the value contained in

    it will
    > > not be interpolated into your SQL statement.

    >
    > Inside doublequotes? Here's my outcome:
    >
    > phaylon@gaia:~> perl -Mwarnings -Mstrict
    > my $k = 'Test';
    > my $sql = "ALTER TABLE list_headers ADD '$k' TEXT";
    > print $sql, "\n";^D
    > ALTER TABLE list_headers ADD 'Test' TEXT
    >
    > So this shouldn't be a problem. I would guess the database treats

    object
    > names w/o single-quotes as case-insensitive, and with single-quotes,
    > case-sensitive.
    >
    > But it's just a guess :D


    sincere apologies! I should've tested this myself.

    -rahul
    > --
    > http://www.dunkelheit.at/
    > sapere aude.
    rahul, May 18, 2005
    #5
  6. Bigus

    Guest

    "Bigus" <> wrote:
    ....
    > That's where $k = "owner". If I remove the single quotes round $k in teh
    > ALTER TABLE line then the owner column is created fine, but of course it
    > then falls over when $k = "reply-to" because of the hyphen.
    >
    > When I use PHPMyadmin to create the owner column it works fine and tells
    > me it's used the syntax:
    >
    > ALTER TABLE `list_headers` ADD `owner` TEXT


    If PHPMyadmin told you that it used backticks, and that seemed to work,
    then I would suggest that you try using backticks in your Perl program.

    Xho

    --
    -------------------- http://NewsReader.Com/ --------------------
    Usenet Newsgroup Service $9.95/Month 30GB
    , May 18, 2005
    #6
  7. Bigus

    phaylon Guest

    Re: adding columns to table

    rahul wrote:

    > sincere apologies! I should've tested this myself.


    What? Was it the case sensitiveness?

    --
    http://www.dunkelheit.at/
    That is not dead, which can eternal lie,
    and with strange aeons even death may die.
    -- H.P. Lovecraft
    phaylon, May 18, 2005
    #7
  8. Bigus

    Bigus Guest

    <> wrote in message
    news:20050518132317.407$...
    > "Bigus" <> wrote:
    > ...
    >> That's where $k = "owner". If I remove the single quotes round $k in teh
    >> ALTER TABLE line then the owner column is created fine, but of course it
    >> then falls over when $k = "reply-to" because of the hyphen.
    >>
    >> When I use PHPMyadmin to create the owner column it works fine and tells
    >> me it's used the syntax:
    >>
    >> ALTER TABLE `list_headers` ADD `owner` TEXT

    >
    > If PHPMyadmin told you that it used backticks, and that seemed to work,
    > then I would suggest that you try using backticks in your Perl program.


    It must have registered at a subconscious level and sown the seed. I didn't
    notice PHPmyadmin using backticks until you just pointed it out.. still, the
    DBI prepare method is supposed to deal with quoting automatically but when I
    tried that and it didn't work.

    Bigus
    Bigus, May 19, 2005
    #8
  9. Bigus

    Guest

    "Bigus" <> wrote:
    > <> wrote in message
    > news:20050518132317.407$...
    > > "Bigus" <> wrote:
    > > ...
    > >> That's where $k = "owner". If I remove the single quotes round $k in
    > >> teh ALTER TABLE line then the owner column is created fine, but of
    > >> course it then falls over when $k = "reply-to" because of the hyphen.
    > >>
    > >> When I use PHPMyadmin to create the owner column it works fine and
    > >> tells me it's used the syntax:
    > >>
    > >> ALTER TABLE `list_headers` ADD `owner` TEXT

    > >
    > > If PHPMyadmin told you that it used backticks, and that seemed to work,
    > > then I would suggest that you try using backticks in your Perl program.

    >
    > It must have registered at a subconscious level and sown the seed. I
    > didn't notice PHPmyadmin using backticks until you just pointed it out..
    > still, the DBI prepare method is supposed to deal with quoting
    > automatically but when I tried that and it didn't work.


    Prepare is not supposed to deal with quoting. It is the execute
    (or equivalent) that deals with the quoting, and that is only when bind
    variables are used, which you didn't do. And bind variables are used for
    data, not for names of DB structures.

    Quotes are used to disambiguate things. If your SQLs weren't ambiguous,
    why would you need quotes in the first place? And if they are ambiguous,
    how is "prepare" supposed to magically figure out what you meant in order
    to apply the quotes properly?

    Xho

    --
    -------------------- http://NewsReader.Com/ --------------------
    Usenet Newsgroup Service $9.95/Month 30GB
    , May 19, 2005
    #9
  10. Bigus

    Bigus Guest


    >> It must have registered at a subconscious level and sown the seed. I
    >> didn't notice PHPmyadmin using backticks until you just pointed it out..
    >> still, the DBI prepare method is supposed to deal with quoting
    >> automatically but when I tried that and it didn't work.

    >
    > Prepare is not supposed to deal with quoting. It is the execute


    I meant the prepare/execute method...

    > (or equivalent) that deals with the quoting, and that is only when bind
    > variables are used, which you didn't do.


    .....which I mentioned in the OP that I tried

    > And bind variables are used for
    > data, not for names of DB structures.


    however that would explain it!

    Bigus
    Bigus, May 20, 2005
    #10
    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. ulloa
    Replies:
    1
    Views:
    525
    Juha Laiho
    Jul 22, 2004
  2. Jacksm
    Replies:
    2
    Views:
    847
    Paul Chalekian
    Nov 21, 2006
  3. Jerome Hauss
    Replies:
    0
    Views:
    167
    Jerome Hauss
    Oct 13, 2004
  4. Asby

    Mason, DBI, and DBI::Pg

    Asby, Jul 24, 2003, in forum: Perl Misc
    Replies:
    0
    Views:
    173
  5. Tim Haynes
    Replies:
    3
    Views:
    140
    Ron Reidy
    Sep 13, 2003
Loading...

Share This Page