Two ways you can do it that I can give an example of. One is to query the
system catalogue directly embedding this:
SELECT name FROM syscolumns WHERE id = object_id('<sometable>')
(of course, substituting the table name in the appropriate place).
or, if you are using DBI, you can pull a full list of columns in a statement
handle doing something like this (snipped out of a replication program I
have been working on).:
my $dbh = DBI->connect($database,$db_user,$db_password, {AutoCommit=>0,
LongReadLen => 4000});
my $dbh1 = DBI->connect($database,$db_user,$db_password, {AutoCommit=>0,
LongReadLen => 4000});
$dbh->do("USE Northwind");
$dbh1->do("USE Northwind_copy");
my $select = $dbh->prepare("SELECT * FROM Customers") || die qq{Can't
execute\n$select\n $dbh::errstr\n};
local($\, $,) = ("\n", "\t");
$columnlist = '['.join('], [', @{$select->{NAME}}).']'; #get the column
list from the dbi function.
print @{$select->{NAME}}[0..$#{$select->{NAME}}]; #for demo only. Shows the
array of column names
As you can see from the print statement, the column names are now
referencable in the @{$select->{NAME}}
array. (Notice that $select is the statement handle. If you used $sth your
array would be @{$sth->{NAME}} There are other properties that can be pulled
in similar manners from the statement handles. Once you get the hang of
using this, you can easily get the other properties listed in perldoc DBI.
Hope this helps,
Steve H.
-----Original Message-----
From: eric wang [mailto:[EMAIL PROTECTED]]
Sent: Thursday, July 05, 2001 2:58 PM
To: dave hoover; Eric Wang; [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Subject: RE: creating columns on the fly
Thanks for the tip,
But what if I am using Microsoft SQL server 7?
is there a way to do this?
eric
-----Original Message-----
From: dave hoover [mailto:[EMAIL PROTECTED]]
Sent: Thursday, July 05, 2001 12:43 PM
To: Eric Wang; [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Subject: Re: creating columns on the fly
Eric Wang wrote:
[snip]
> First, I decided that I will need to generate the
> SQL statements inside
> the perl script. (i.e. print SQL "insert...
> blah..").
If you use DBI, this is no problem, in fact, it's
expected. You will probably call
$sth->execute(SQL_HERE). You can plug in a previously
generated scalar variable at SQL_HERE.
> But, I need to know
> if this column I insert into has already existed or
> not? if not, create
> it, otherwise just insert it. So, the simple
> question is can I do this?
[snip]
Yes. You can query the database first to see if it's
there, then based on the results, construct your SQL
statement with UPDATE/INSERT accordingly.
=====
Dave Hoover
"Twice blessed is help unlooked for." --Tolkien
http://www.redsquirreldesign.com/dave
__________________________________________________
Do You Yahoo!?
Get personalized email addresses from Yahoo! Mail
http://personal.mail.yahoo.com/
