Re: How do you get Perl::DBI to do a desc table

You can try the NAME and the TYPE DBI statement handle objects.

Here's how you can grab column names:

my $names = $sth->{NAME};
#$names is an array reference
for (@$names) {
   print $_, "\n";
}
[download]

Here's how you can grab the datatype of the columns:

my $types = $sth->{TYPE};
#$types is an array reference
for (@$types) {
   print $_, "\n";
}
[download]

The last example returns an array of integer values, the value indicates the datatype (see ANSI X3.135 for a description).

Comment on Re: How do you get Perl::DBI to do a desc table_name? Select or Download Code

Replies are listed 'Best First'.
Re: Re: How do you get Perl::DBI to do a desc table_name? by lachoy (Parson) on Mar 28, 2002 at 13:23 UTC
Two minor notes: First, you might want to be careful about the values returned by `$sth->{NAME}` if you care about case-sensitivity. The DBI doesn't make them consistent or modify them at all -- they're whatever the database returns. And what the database returns can be vendor specific. The `NAME_uc` and `NAME_lc` properties do what you'd expect. Second, I think the integer values returned from `$sth->{TYPE}` correspond to the DBI SQL type constants. So you can do: `use DBI qw( :sql_types ); ... my $types = $sth->{TYPE}; foreach my $type ( @{ $types } ) { print "VARCHAR" if ( $type == SQL_VARCHAR ); print "DATETIME" if ( $type == SQL_DATETIME ); print "BOOLEAN" if ( $type == SQL_BIT ); print "FLOAT" if ( $type == SQL_FLOAT ); ... }` [download] Conveniently, most of them also map to the ODBC CLI specifications. :-) You can find the type-to-constant mapping in the 'dbi_sql.h' header file found in your architecture-dependent perl library tree -- for example, mine is in `site_perl/5.6.1/i686-linux/auto/DBI/dbi_sql.h` Chris M-x auto-bs-mode	[reply] [d/l] [select]

Replies are listed 'Best First'.

Re: Re: How do you get Perl::DBI to do a desc table_name?
by lachoy (Parson) on Mar 28, 2002 at 13:23 UTC

Two minor notes:

First, you might want to be careful about the values returned by $sth->{NAME} if you care about case-sensitivity. The DBI doesn't make them consistent or modify them at all -- they're whatever the database returns. And what the database returns can be vendor specific. The NAME_uc and NAME_lc properties do what you'd expect.

Second, I think the integer values returned from $sth->{TYPE} correspond to the DBI SQL type constants. So you can do:

use DBI qw( :sql_types );
...
my $types = $sth->{TYPE};
foreach my $type ( @{ $types } ) {
  print "VARCHAR"  if ( $type == SQL_VARCHAR );
  print "DATETIME" if ( $type == SQL_DATETIME );
  print "BOOLEAN"  if ( $type == SQL_BIT );
  print "FLOAT"    if ( $type == SQL_FLOAT );
  ...
}
[download]

Conveniently, most of them also map to the ODBC CLI specifications. :-)

You can find the type-to-constant mapping in the 'dbi_sql.h' header file found in your architecture-dependent perl library tree -- for example, mine is in site_perl/5.6.1/i686-linux/auto/DBI/dbi_sql.h

Chris
M-x auto-bs-mode

[reply]
[d/l]
[select]