Metadata

There are several metadata-related method calls associated with the main database handle. These are summarized in Table B-5. (There will be an increasing amount of metadata in future versions of DBI. Check perldoc DBI for the latest details.)

Table B-5. Database handle metadata methods

Method	Description
table_info	Lists schemas, tables, and other object metadata.
tables	A simpler interface to the tables_info method.
primary_key_info	Provides primary key metadata.
primary_key	A simpler interface to the primary_key_info method.
type_info_all	Returns a reference to a read-only array used to drill down on all type information in the database.
type_info	Returns information on a particular data type.
foreign_key_info	Returns foreign key information.
column_info	Returns column information.

Statement Handle Metadata

There are many read-only attributes you can access via the statement handle. Rather than describe all of them individually, we've provided the following piece of code as a good guide to the main ones used. Note that some are straightforward string values, whereas others are array references to columnar information:

use DBI;
  
my $dbh = DBI->connect('dbi:Oracle:orcl', 'scott', 'tiger',
                       {RaiseError => 1, AutoCommit => 0} );
  
my $sth = $dbh->prepare("SELECT empno, hiredate
                           FROM emp
                          WHERE ename = ? ");
  
$sth->bind_param(1, 'MILLER');  # SQL uses 1 bound parameter
$sth->execute;
  
print "Number of Fields : ", $sth->{NUM_OF_FIELDS}, "\n";
print "Bound parameters : ", $sth->{NUM_OF_PARAMS}, "\n\n";
  
for $column (0..($sth->{NUM_OF_FIELDS} - 1)) {  # Columns, 0 - N
  
   print "Column Name      : ", $sth->{NAME}->[$column], "\n",
         "SQL Data Type    : ", $sth->{TYPE}->[$column], "\n",
         "Precision        : ", $sth->{PRECISION}->[$column], "\n",
         "Scale            : ", $sth->{SCALE}->[$column], "\n",
         "Nullable? (1=yes): ", $sth->{NULLABLE}->[$column], "\n\n";
}
print "SQL Statement    : ", $sth->{Statement}, "\n";

When the above code is run, it generates the following listing:

Number of Fields : 2
Bound parameters : 1
  
Column Name      : EMPNO
SQL Data Type    : 3
Precision        : 4
Scale            : 0
Nullable? (1=yes):
  
Column Name      : HIREDATE
SQL Data Type    : 9
Precision        : 75
Scale            : 0
Nullable? (1=yes): 1
  
SQL Statement    : SELECT empno, hiredate
                           FROM emp
                          WHERE ename = ?

You can see that some figures included above are unreliable when used with irrelevant data types, such as 75 for the Precision of the HIREDATE column.