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.

   


Perl for Oracle DBAs
Perl for Oracle Dbas
ISBN: 0596002106
EAN: 2147483647
Year: 2002
Pages: 137

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net