Perl and Access 
Author Message
 Perl and Access



# > How can I work with an Access DataBase and Perl. Please, can you show
me
# > the most simple source code to open, close, read, and write into de
# > DataBase?
# > Thanks in advance.

# Here's a script I used to extract some stuff from an access DB, and grab
a load of files.
# If you ignore the stuff at the end (section 6), that I'm too lazy to
remove, then it should
# provide a reasonably generic script for setting up a DNS, and reading
from a DB file.
# Writing to the DB can be done at the same stage as reading, the hard part
was getting
# the DB file open!!!

# By the way, comp.lang.perl.misc is probably the best place for Perl
database / ODBC
# questions.

#!c:/Perl5.004/bin/perl

# Define environment

$inputdir = "\\input";
$outputdir = "\\output";

# Define DSN and driver type

$DBase = "$inputdir\\$ARGV[0]";
$DSN = "Translate";
$DriverType = "Microsoft Access Driver (*.mdb)";
$Desc = "Description=Temporary ODBC Source";

################ Main #################

# Open Access db and produce doc list #

use Win32::ODBC;

# 1) Create DSN

system "cls";
print "Adding DSN \"$DSN\" ...";
if (Win32::ODBC::ConfigDSN(ODBC_ADD_DSN, $DriverType, ("DSN=$DSN",
"Description=Temporary ODBC Source", "DBQ=$DBase", "DEFAULTDIR=$Dir",
"UID=", "PWD="))){
        print " done!\n\n";

Quote:
}else{

        print " failed!\n\nUnable to create \"$DSN\" source.\n";
        exit();

Quote:
}

# 2) Connect to DSN

if (!($odbc_object = new Win32::ODBC($DSN))){
        print "Unable to open connection to \"$DSN\" source.\n";
        exit();

Quote:
}else{

        print "Successfully connected to \"$DSN\" source.\n\n";

Quote:
}

# 3) Identifying table(s)

$Num = 0;
foreach  $Temp ($odbc_object->TableList("", "", "%", "TABLE, VIEW,
SYSTEM_TABLE")){
        $Table = $Temp;
        print "\t", ++$Num, ".) \"$Temp\"\n";

Quote:
}

print "\nTotal number of tables : $Num";

# 4) Do SQL query and print tables out

if (! $odbc_object->Sql("SELECT * FROM [$Table] ORDER BY \"Id\"")){

        open (OUTPUT, ">$inputdir\\output.txt") || die "Unable to create output
file :\n$!\n";
        while($odbc_object->FetchRow()){
                undef %Data;
                %Data = $odbc_object->DataHash();
                foreach $field (keys %Data) {
                        if ($field =~ /Document Number/) {
                                $docnum = $Data{$field};
                        }
                        if ($field =~ /Year of Publication/) {
                                $docyear = $Data{$field};
                                $docyear = translate_docyear($docyear);
                        }
                        if ($field =~ /Document Type/) {
                                $doctitle = $Data{$field};
                                $doctitle = translate_doctitle($doctitle);
                        }
                        if ($field =~ /ID/) {
                                $docid = $Data{$field};
                                printf OUTPUT "%s\\%s%4.4d00\n", $doctitle, $docyear, $docnum;
                        }
                }
        }
        print "\nNo more records available.\n";
        close OUTPUT;

Quote:
}

# 5) Close and remove DSN

$odbc_object->Close();

print "\nRemoving the temporary DSN :\n";
print "\n\tDSN = \"$DSN\"\n\tDriver = \"$DriverType\"\n";

if (Win32::ODBC::ConfigDSN(ODBC_REMOVE_DSN, $DriverType, "DSN=$DSN")){
        print "\nSuccessful!\n";

Quote:
}else{

        print "\nFailed to remove temporary DSN \"$DSN\".\nPlease do it by
hand.\n";
Quote:
}

sleep 2;

# 6) Find files and move them to input directory

system "cls";

$sourcedir = "\\\\ti_sgml_serv\\celex\\cen\\files";
$log_open = 0;

open (OUTPUT, "<$inputdir\\output.txt") || die "Unable to open output file
:\n$!\n";
print "Searching for files to process ...\n";
while (<OUTPUT>) {
        if (/(\d[A-Z]{1,2})\\([A-Z]{2}\d{6})/) {
                $subdir = $1;
                $filename = $2;
                $return_value = system "copy $sourcedir\\$subdir\\$filename\.\*
$inputdir\\";
                if ($return_value != 0) {
                        if ($log_open == 0) {
                                open (LOGFILE, ">$inputdir\\error.log") || die "Unable to create error
log :\n$!\n";
                                $log_open++;
                                print LOGFILE "Log of documents not
found.\n---------------------------\n\n";
                                $unfound = 0;
                        }
                        $subdir = translate_subdir($subdir);
                        $filename = translate_filename($filename);
                        $unfound_file = "$subdir $filename";
                        print LOGFILE "Cannot find : $unfound_file\n";
                        $unfound++;
                }
                undef $return_value;
        }

Quote:
}

close OUTPUT;
if ($log_open > 0) {
        print LOGFILE "\nTotal : $unfound\n";
        close LOGFILE;
Quote:
}

#system "del $inputdir\\output.txt";
print "\n... done!\n";

############### Subs ################

sub translate_docyear {
        my $self = shift;
        if ($self =~ /(\d)(\d)/) {
                $decade = $1;
                $year = $2;
                $decade =~ tr/5-9/A-E/;
                $year =~ tr/0-9/A-J/;
                $self = "$decade$year";
        }
        return $self;

Quote:
}

sub translate_doctitle {
        my($phrase) = shift;
        $phrase =~ s/^Commission Decision$/3D/ig;
        $phrase =~ s/^Commission Directive$/3L/ig;
        $phrase =~ s/^Commission Recommendation$/3K/ig;
        $phrase =~ s/^Commission Regulation$/3R/ig;
        $phrase =~ s/^Council Decision$/3D/ig;
        $phrase =~ s/^Council Directive$/3L/ig;
        $phrase =~ s/^Council Regulation$/3R/ig;
        $phrase =~ s/^Proposal for Council Directive$/5PC/ig;
        $phrase =~ s/^Proposal for Council Regulation$/5PC/ig;
        $phrase =~ s/^Written Question$/9E/ig;
        return $phrase;

Quote:
}

sub translate_filename {
        my $self = shift;
        if ($self =~ /([A-E])([A-J])0*(.+)/) {
                $decade = $1;
                $year = $2;
                $actual_number = $3;
                $decade =~ tr/A-E/5-9/;
                $year =~ tr/A-J/0-9/;
                $actual_number =~ s/00$/$1/;
                $self = "$decade$year $actual_number";
        }
        return $self;

Quote:
}

sub translate_subdir {
        my($phrase) = shift;
        $phrase =~ s/3D/Commission Decision/i;
        $phrase =~ s/3L/Commission Directive/i;
        $phrase =~ s/3K/Commission Recommendation/i;
        $phrase =~ s/3R/Commission Regulation/i;
        $phrase =~ s/3D/Council Decision/i;
        $phrase =~ s/3L/Council Directive/i;
        $phrase =~ s/3R/Council Regulation/i;
        $phrase =~ s/5PC/Proposal for Council/i;
        $phrase =~ s/9E/Written Question/i;
        return $phrase;

Quote:
}

################### End ###################


Tue, 07 Dec 1999 03:00:00 GMT  
 Perl and Access

How can I work with an Access DataBase and Perl. Please, can you show me

the most simple source code to open, close, read, and write into de
DataBase?

Thanks in advance.



Wed, 08 Dec 1999 03:00:00 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. perl to access MS Access database

2. Using Perl To Access Microsoft Access Databases???

3. Using Perl to access a MS Access DB from Web

4. Perl to access a MS Access database

5. Linux (Apache) Web server, Perl accessing Access dbase?

6. Perl MySQL access problems

7. Perl and Access

8. Perl SQL Access

9. Perl to access POP3,IMAP and LDAP

10. perl problem, accessing file blocks my script

11. Perl / DB Access

12. Perl to access MS SQL on NT from Unix web server

 

 
Powered by phpBB® Forum Software