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?

In reply to finding field infomation with DBI and ODBC by thunders

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.