Re: How can I fetch column names?

[Brian P. Millett]

This is a function that works for informix.  You mileage will vary.

# This function queries the database about a table.  Returns three values:
#  1) The strings that represent how to reconstruct the
#     table.  IE: "biblio_id     CHAR    (7),"
#  2) The number of columns in the table.
#  3) The array of column names.
sub get_columns_defs {
    my ($sth, $table) = @_;
    my @row,$col_name,$type_name,$col_len,@results,$i,@variableArray,$nulls;
        @results = ();
        $i=0;
        #print "Getting table schema for $table\n";
    $sth->execute($table);
    while (@row = $sth->fetchrow_array ) {
                $i++;
                $nulls = ($row[1] > 255) ? " NOT NULL" : "";
        $row[1] -= 256 if ($row[1] > 255);
        $col_name = $row[0];
        $type_name = $typ{$row[1]};
        $col_len = $row[2];
                if ($type_name eq 'CHAR' ||
                    $type_name eq 'VARCHAR'||
                    $type_name eq 'NCHAR'||
                    $type_name eq 'NVARCHAR') {
                push @results, sprintf "\t%-30s\t%-8s(%d)%s"
                                , $col_name,$type_name,$col_len,$nulls ;
                } else {
                        push @results, sprintf "\t%-30s\t%-8s%s"
                                , $col_name,$type_name,$nulls ;
                }
                push @variableArray, $col_name;
    }
        return (join(",\n", @results),$i,@variableArray);
}
my $dbh = DBI->connect("dbi:Informix:$database", $user, $pass);

$dbh->{PrintError}=1;
$dbh->{RaiseError}=1;
$dbh->{ChopBlanks} = 1;
# Query the database for column names, types & length of each
# column in a specific table.
my $stmnt = $dbh->prepare("
SELECT colname col_name, coltype type_name, collength col_len
FROM systables, syscolumns
WHERE systables.tabid = syscolumns.tabid
AND tabname = ?
");
#This function call returns three arguments:
#  1) schema = the strings that represent how to reconstruct the
#              table.  IE: "biblio_id     CHAR    (7),"
#  2) numberOfVariables = the number of columns in the table.
#  3) variableArray = the array of column names.
($schema,$numberOfVariables,@variableArray) = &get_columns_defs($stmnt, $table);
--
Brian Millett
Enterprise Consulting Group   "Shifts in paradigms
(314) 205-9030               often cause nose bleeds."
[EMAIL PROTECTED]                           Greg Glenn