Harald Fuchs wrote:
> In article <[EMAIL PROTECTED]>,
> "Adams, Bill TQO" <[EMAIL PROTECTED]> writes:
>
> >> I need to read the fields of a table dynamically using
> >> DBI.pm
> >>
> >> The resulting cgi script should be a form that has a
> >> checkbox for each field...the user will then create a
> >> temporary table based on which fields are checked in
> >> the form.
>
> > my $dbh = DBI->connect( ... );
> > my $sth = $dbh->prepare( "SHOW COLUMNS FROM $table" ) or die;
> > $sth->execute( ) or die;
> > my @columns;
> > while( my( $column_name ) = $sth->fetchrow_array( )){
> > push @columns, $column_name;
> > }
> > $sth->finish( );
>
> This is complicated and not portable. "perldoc DBI" shows a better method:
>
> my @columns = @{$sth->{NAME}};
>
That only works if you have already done a select. I think the original
poster wanted to get the column names before a query was run so that the user
could choose via the web page which columns to download. The full code for
your portable solution would be:
my $dbh = DBI->connect( ... );
my $sth = $dbh->prepare( "SELECE * FROM $table" ) or die;
$sth->execute( ) or die;
my @columns = @{$sth->{NAME}};
$sth->finish( );
Which could be bad if the table was really big and the alternative database in
question was not smart enough to just start returning data. (And, of course,
"LIMIT" in MySQL is not portable either.) Or the table was locked for some
reason.
No, mine is not portable. (I am thinking of writing an addition to DBI to
abstract some of the non-portable things e.g. getting the column names from a
table, getting the table names, getting the ID of an auto-increment column,
etc..) But it also will not suffer from locking problems. And one could
surround the code with:
if( $dbh->{Driver}{Name} eq 'mysql' ){
...
}elsif( $dbh->{Driver}{Name} eq 'Informix' ){
...
} else {
die "I do not know how to get column names from ",
$dbh->{Driver}{Name};
}
For reference here is how you do it in Infomix and InterBase:
my @list = $dbh->func( $table, '_columns' );
foreach my $column (@list) {
$column->[3] =~ s/\s+//g; #remove trailing space.
push @columns, $column->[3];
}
And PostgreSQL:
my @list = $dbh->func( $table, 'table_attributes' );
foreach my $x (@list) {
foreach my $column (@$x) {
$column->{NAME} =~ s/\s+//g; #remove trailing space.
push @columns, $column->{NAME};
}
}
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
