|
Re: DBI, add fields to existing table?
by jZed (Prior) on Aug 13, 2005 at 20:10 UTC
|
Like most things, SQL, it depends on which DBMS/DBD you are using. But update is definitely not the right command -- that's for updating values. In standard SQL the command to change the column structure of a table would be ALTER TABLE, but see the docs for your DBMS.
(updated typo, thanks bmann) | [reply] |
|
|
Just to extend a little bit. Basically there are two types of database "languages" - DDL and DML. DML (Data Manipulate Language) is a set of statements that can be used to operate on the data, for example insert/delete/update. DDL (Data Definition Language) is a set of statements used to operate on the definition of database components, for example create/alter.
| [reply] |
Re: DBI, add fields to existing table?
by Corion (Patriarch) on Aug 13, 2005 at 20:14 UTC
|
This is an SQL question. Please take a step back and read some good introductory texts on SQL.
To bring this back to a Perl discussion, there happens to be this module that I'm writing, which makes dynamically adding columns to a table easy, or at least strives to. It's not yet in a releasable state, but that doesn't prevent me from posting it here - maybe somebody finds it useful.
The tentative name is Table::Denormalized because that's what I use it for, but maybe Table::DynamicColumns or something like that would fit better - I'm really bad at giving my modules good names.
Part of the module is a tiny reimplementation of Class::DBI, which I found easier to do than finding the spots I need to override to make Class::DBI itself do my bidding. I'm not posting the (trivial) RowSet, which implements an array-object that knows the query it came from.
| [reply]
[d/l]
[select] |
Re: DBI, add fields to existing table?
by saintmike (Vicar) on Aug 13, 2005 at 20:11 UTC
|
| [reply]
[d/l]
[select] |
|
|
UPDATE changes contents of an existing table. INSERT adds them.
C.
| [reply]
[d/l]
[select] |
|
|
| [reply] |
Re: DBI, add fields to existing table?
by tomazos (Deacon) on Aug 14, 2005 at 17:22 UTC
|
I'll bet you are using MySQL.
The answer is:
$dbh->do("ALTER TABLE $dbtable ADD COLUMN (Name text)");
$dbh->do("ALTER TABLE $dbtable ADD COLUMN (Address text)");
-Andrew.
| [reply]
[d/l] |
|
|
ALTER TABLE was correct which was suggested before but the syntax I ended up using was:
my $cmd = "ALTER TABLE $dbTable ADD (NAME text, ADDRESS text)";
my $cmd_prep = $db->prepare($cmd);
unless($cmd_prep->execute()) {
push(@error, "Database insertion error: ".DBI::errstr);
}
$cmd_prep->finish();
prepare() and execute() is preferred over do()
| [reply]
[d/l] |
|
|
The finish() in your snippet isn't needed. It is only for fetching data when the fetch is not completed. It's true that prepare() & execute() is preferable when you are executing the same statement many times (prepare once, execute many), but if you are only executing a single statement and especially a statement like ALTER (which can't have any placeholders in it), there is no reason at all to prefer prepare() and execute() over do().
| [reply] |