Someone kindly gave me an example of using DBI for MSAccess. It ran
initially. However, I have now, as part of my attempt to install bugzilla,
deinstalled perl 5.6 and perl 5.8 (which was incorrectly installed on top of
perl 5.6) and reinstalled perl 5.8+, DBI and DBD-Mysql and MySQL several
times and now the program below dies with the following error: "This
application has failed to start because Perl56.dll was not found."
The error message makes me wonder if I need to deinstall 5.8, install 5.6
and then install 5.8 on top of 5.6 without deinstalling 5.6 first.
Thanks,
Siegfried
#!c:/perl/bin/perl
use strict;
use DBI;
my $database = 'D:\Dodds\Nra\db1';
$database = 'D:\Dodds\Nra\Benefits\nra_tables2k.mdb'; # Access 2000 db
$database = 'c:/Program Files/Apache
Group/Apache2/cgi-bin/test_components02.mdb'; # Access 2000 db
$database =~ s|\\|\\\\|g; # create an escaped filename
$database =~ s|/|\\\\|g; # create an escaped filename
my $DSN = "driver={Microsoft Access Driver (*.mdb)};dbq=$database";
my $dbh = DBI->connect("dbi:ODBC:$DSN","","") or
die "$DBI::errstr\n";
#&show_tables;
#&count_rows("MoveIn", $dbh);
#&show_columns("tblCalls", $dbh);
#&show_columns("tblMemberCompanies", $dbh);
#&show_columns("tblContacts", $dbh);
#&show_columns("tblAdvertising", $dbh);
#&show_columns("tblConventions", $dbh);
&show_columns("BaseCaseTypes", $dbh);
&print_table("BaseCaseTypes", $dbh);
my $rc = $dbh->disconnect;
exit;
sub print_table {
my ($table,$dbh) = @_;
my $sql = qq~Select * from [$table] where [Booth#] = 5131~;
my $sql = qq~Select * from [$table] where Date = #5/17/2000#~;
my $sql = qq~Select TOP 2 * from [$table]~;
#print $sql . "\n";
my $sth = $dbh->prepare($sql);
$sth->execute() or die "DBI Execute error: " . $sth->errstr . "\n";
# Print columns names
my ($columns, $col_types) = &_get_columns($table,$dbh);
my $x;
my $results;
foreach (@{$columns}) {
$results .= $_ . "|";
$x++;
}
print $results . "\n";
# Print values
my $x;
while (my @columns = $sth->fetchrow_array) {
my $y = 0;
foreach (@columns) {
$x .= $columns[$y] . "|";
$y++;
}
$x .= "\n";
}
print $x;
}
sub count_rows {
my ($table,$dbh) = @_;
my $sql = qq~Select * from [$table]~;
#print $sql . "\n";
my $sth = $dbh->prepare($sql);
$sth->execute() or die "DBI Execute error: " . $sth->errstr . "\n";
my $x;
while (my @columns = $sth->fetchrow_array) {
$x++;
}
print $table . " = " . $x . "\n";
}
sub show_columns {
my ($table,$dbh) = @_;
my ($columns, $col_types) = &_get_columns($table,$dbh);
my $x;
my $results;
foreach (@{$columns}) {
$results .= $_ . ":\t" . $col_types->[$x] . "\n";
$x++;
}
print $results;
} # show_columns
sub _get_columns {
my ($table,$dbh) = @_;
my $sql = qq~Select * from [$table]~;
my $sth = $dbh->prepare($sql);
$sth->execute() or die "DBI Execute error: " . $sth->errstr . "\n";
my $x;
my @columns;
my @col_types;
foreach (@{$sth->{NAME}}) {
# if ($_ =~ /^s_/) {$x++; next;}; # skip system columns that begin
with "s_"
$columns[$x] = $sth->{NAME}->[$x];
# Get column type
my @type_info = $dbh->type_info($sth->{TYPE}->[$x]);
my $col_type;
my $y;
foreach (@type_info) {
$col_type .= $_->{TYPE_NAME};
$y++;
if ($y > 0) { $col_type .= ' ' };
}
chomp $col_type;
$col_types[$x] = $col_type;
$x++;
}
return ([EMAIL PROTECTED], [EMAIL PROTECTED]);
} # _get_columns
sub show_tables {
# Displays list of tables
my $display_sys_tables = 0;
my @tables = $dbh->tables;
my $x;
my $tn;
foreach $tn (@tables) {
if (($display_sys_tables) && ($tn =~ /^MSys/)) {
$x .= $tn . "\n";
} elsif (!($tn =~ /^MSys/)) {
$x .= $tn . "\n";
}
}
print $x;
}
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
<http://learn.perl.org/> <http://learn.perl.org/first-response>
