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
Statement Handle MetadataThere 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. |