DBI: adding columns to table

[Bigus]

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]

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).

 

[rahul]

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.

 

[phaylon]

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

 

[rahul]

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

sincere apologies! I should've tested this myself.

-rahul

 

[xhoster]

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

 

[phaylon]

sincere apologies! I should've tested this myself.

What? Was it the case sensitiveness?

 

[Bigus]

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

 

[xhoster]

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

 

[Bigus]

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