Re: how can one get the datatypes of columns in a table using Perl?

[Paul DuBois]

At 5:07 PM -0700 2000-05-18, Dana Powers wrote:
>On Thu, 18 May 2000, Sandeep Hulsandra wrote:
>>  Hi,
>>  I need to get the datatypes of columns in a table. I tried using the
>>  listfields function of perl. That is,
>>  $fields = $db->listfields($table);
>>  @array = $fields->type;
>>  Instead of getting the datatype (char, int, real, etc.), I am getting
>>  numbers.
>>  Please advice.
>
>I believe you can use $dbh->type_info_all() to get a reference to an 
>array with
>all of the datatypes. My guess is that the numbers you are getting should be
>used as indexes into this array. The type_info_all function is new but
>documented. Try
>perldoc DBI
>and scroll down to around line 1780. Hope this helps
>Dana


Here's another way that doesn't rely on using metadata:

# Return a hash of table column information structures.  The hash is
# indexed on column name.  Each structure is a hash with the following
# elements:
# type  column type
# len   column length (0 if length doesn't apply)

sub get_table_column_info
{
my ($dbh, $db_name, $tbl_name) = @_;
my ($sth);
my (%col_info) = ();
my ($name, $type_str, $type, $len, $rest);

         $sth = $dbh->prepare ("SHOW COLUMNS FROM $db_name.$tbl_name");
         $sth->execute ();
         while (my $ary_ref = $sth->fetchrow_arrayref ())
         {
                 $name = $ary_ref->[0];
                 $type_str = $ary_ref->[1];
                 die "Invalid type: $type_str\n" unless $type_str =~ /^(\w+)/;
                 $type = uc ($1);
                 $rest = $';
                 # if type was something like typename(len), pull out the len
                 $len = (($rest =~ /\((\d+)\)/) ? $1 : 0);
                 $col_info{$name} = {};  # create hash for column name
                 $col_info{$name}->{type} = $type;
                 $col_info{$name}->{len} = $len;
         }
         $sth->finish ();
         return (%col_info);
}

-- 
Paul DuBois, paul@stripped