RE: getting table structure out of MS-Access
It worked just fine for me with no changes.
Dennis
-Original Message-
From: David N Murray [mailto:[EMAIL PROTECTED]
Sent: Thursday, 25 March 2004 2:38 PM
To: Bart Lateur
Cc: [EMAIL PROTECTED]
Subject: Re: getting table structure out of MS-Access
I thought this worked, but when I test it now, I can't get it to work.
YMMV. -- Dave
#!/usr/bin/perl -w
# this does a 'describe' for MS access
use DBI;
use strict;
$|++;
#DBI->trace(8);
die "usage: desc.pl DSN table_name\n" if $#ARGV != 1;
my $dbh = DBI->connect("dbi:ODBC:$ARGV[0]", '', '',
{ PrintError => 1, RaiseError => 1 });
die "connect failed: " . $DBI::errstr . "\n" if ! $dbh;
my $sth = $dbh->column_info("", "", $ARGV[1], "");
my @ar;
print "$ARGV[0].$ARGV[1]\nColumn Type
Size Prec\n";
while (@ar = $sth->fetchrow_array()) {
$ar[8] = 0 if !defined($ar[8]);
printf("%-40s %-10s %4d %4d\n", $ar[3], $ar[5], $ar[6], $ar[8]); }
$sth->finish; $dbh->disconnect;
On Mar 25, Bart Lateur scribed:
> I'd like to automate exporting data out of an existing Access
> database, using DBI/DBD::ODBC, I guess. I need a proper description of
> each column in a table, in order to reconstruct a complete CREATE
> TABLE statement.
>
> My guess is that the func() method in DBD::ODBC could help. I've
> experimented a little with
>
> $sth->func($col, 'DescribeCol')
>
> but the results don't mean too much to me. I'm stuck. Help?
>
> --
> Bart.
>
Re: getting table structure out of MS-Access
On Wed, 24 Mar 2004 22:37:37 -0500 (EST), David N Murray wrote: >I thought this worked, but when I test it now, I can't get it to work. It works for recent enough versions of DBD::ODBC. More or less. Except that column_info returns NULLABLE true for columns that can't be NULL, doubles (float) have a COLUMNS_SIZE of 53, which is the number of bits in the mantissa, not the number of digits; also for doubles the number of DECIMAL_DIGITS is undefined... (using DBI 1.37, DBD::ODBC 1.06, and Access '97) At least it gets the data type for the columns right, that and the length for VARCHAR fields. It's a start... (meager, but a start anyway) -- Bart.
Re: getting table structure out of MS-Access
I thought this worked, but when I test it now, I can't get it to work.
YMMV. -- Dave
#!/usr/bin/perl -w
# this does a 'describe' for MS access
use DBI;
use strict;
$|++;
#DBI->trace(8);
die "usage: desc.pl DSN table_name\n" if $#ARGV != 1;
my $dbh = DBI->connect("dbi:ODBC:$ARGV[0]", '', '',
{ PrintError => 1, RaiseError => 1 });
die "connect failed: " . $DBI::errstr . "\n" if ! $dbh;
my $sth = $dbh->column_info("", "", $ARGV[1], "");
my @ar;
print "$ARGV[0].$ARGV[1]\nColumn Type Size
Prec\n";
while (@ar = $sth->fetchrow_array()) {
$ar[8] = 0 if !defined($ar[8]);
printf("%-40s %-10s %4d %4d\n", $ar[3], $ar[5], $ar[6], $ar[8]);
}
$sth->finish;
$dbh->disconnect;
On Mar 25, Bart Lateur scribed:
> I'd like to automate exporting data out of an existing Access database,
> using DBI/DBD::ODBC, I guess. I need a proper description of each column
> in a table, in order to reconstruct a complete CREATE TABLE statement.
>
> My guess is that the func() method in DBD::ODBC could help. I've
> experimented a little with
>
> $sth->func($col, 'DescribeCol')
>
> but the results don't mean too much to me. I'm stuck. Help?
>
> --
> Bart.
>
getting table structure out of MS-Access
I'd like to automate exporting data out of an existing Access database, using DBI/DBD::ODBC, I guess. I need a proper description of each column in a table, in order to reconstruct a complete CREATE TABLE statement. My guess is that the func() method in DBD::ODBC could help. I've experimented a little with $sth->func($col, 'DescribeCol') but the results don't mean too much to me. I'm stuck. Help? -- Bart.
