I've been working with DBI recently, and I've been attempting to automate some tedious tasks with it. For example from time to time I have to convert MSAccess database tables to Oracle tables. The Method my teacher gave the class(yes this is homework) involves too many repetitive steps for my tastes.

What I want to do is scan the access DB and create Oracle tables on the fly. So first thing that I needed was info about what datatypes I'm dealing with for each column.

#!/usr/bin/perl -w

use strict;
use DBI;

#connect to Access file via ODBC
my $accessDSN =    q(driver=Microsoft Access Driver (*.mdb);).
                q(dbq=D:\\Course 1 Case Study Files\\Order Entry Syste
+m.mdb);
my $dbhA = DBI->connect("dbi:ODBC:$accessDSN",'','') or die "$DBI::err
+str\n"; 

#prepare handles for each table.
my @tables = qw(Customers OrderLineItems Orders Products); 
my @tblSth = map {prepare($_,$dbhA)} @tables;    
  
#print column data for each table
  for my $sHandle(@tblSth){
    $sHandle->execute;
      my $index = (keys %{$sHandle->fetchrow_hashref("NAME")});

      for my $col(0..$index){
        print join " ",$sHandle->func($col,"DescribeCol"), "\n";
    }
   }
    
sub prepare{
    my ($table,$dHandle) = @_;
    return $dHandle->prepare("select * from $table");
}
[download]

This is a snippit of output of that script

CustId 4 10 0 0
FirstName 12 50 0 1
LastName 12 50 0 1
CompanyName 12 50 0 1
Address1 12 50 0 1
Address2 12 50 0 1
City 12 50 0 1
State 12 2 0 1
Zip 12 10 0 1
Phone 12 25 0 1
Fax 12 50 0 1
[download]

does any one know where the "DescribeCol" function is documented? I can tell that the first list item returned is of course column name, and the second is data type, and the third decribes size, but I do not know what the fourth and fifth columns describe. and I dont know how to decode the datatype...

Is there a better way to get the Datatype of a column?