Retrieving Data from Microsoft Access Database using Perl

Thomas Rawley · Jul 21, 2003

I am on a Windows machine and I need to acess some data in a MS Access
database so I can display it on a website. Both the perl script and the
database file are currently in the same directory on the same computer
(Windows XP). I have ActivePerl installed and downloaded and installed both
the DBI and DBD::ODBC modules using ppm.

When I try to run the script, I get and error saying the data source name
was not found and no default driver specified. The code is below. Any help
on this would be wonderful. Thanks in advance.

use DBI;

$DSN = 'driver=Mircosoft Access Driver (*.mdb);dbq=MyEasinetEvents.mdb';
$dbh = DBI->connect("dbi:ODBC:$DSN",'','');

$sth = $dbh->prepare("SELECT * FROM Events");

$sth->execute();

print "Content-type:text/html\n\n";
print "<html><body><h1>Fingerprint Scanner Records</h1>";
print "<table>\n";

print

SubAddr</th><th>UserID</th><th>CardNo</th><th>Details</th><th>LinkedEvent</

th><th>Archived</th></tr>\n";

while( @row = $sth->fetchrow_array )
{
print "<tr>";

foreach $field (@row)
{
print "<td>$field</td>";
}

print "</tr>";
}

print "</table>\n";
print "</body></html>";

$sth->finish;
$dbh->disconnect;

Thomas

Bob Walton · Jul 21, 2003

Thomas said:
I am on a Windows machine and I need to acess some data in a MS Access
database so I can display it on a website. Both the perl script and the
database file are currently in the same directory on the same computer
(Windows XP). I have ActivePerl installed and downloaded and installed both
the DBI and DBD::ODBC modules using ppm.

When I try to run the script, I get and error saying the data source name
was not found and no default driver specified. The code is below. Any help
on this would be wonderful. Thanks in advance.

use DBI;

$DSN = 'driver=Mircosoft Access Driver (*.mdb);dbq=MyEasinetEvents.mdb';

-------------------^^

Also, you might need the full path to your .mdb file, even if it is in
the same directory as your program -- especially since you seem to be
running this as a CGI script. Probably a better way is to define a
system DSN using the ODBC tool in the Control Panel (don't use a user
DSN if you want to hook it to your web server). You just use the name
you assigned to that entry as your DSN in your connect statement. If
you called it "foo", $DSN='foo'; would do the trick. That prevents the
sort of snafu indicated above, and removes the worry about where the
database file is located, typos in the path, etc.

$dbh = DBI->connect("dbi:ODBC:$DSN",'','');

Always test to see if things are successful. If you don't, you'll never
know where they went wrong. This goes for just about every call to DBI
methods, unless you use the RaiseError flag in your connect statement.
Something like:

die "horribly because of $DBI::errstr" unless $dbh;

$sth = $dbh->prepare("SELECT * FROM Events");

$sth->execute();

print "Content-type:text/html\n\n";

If you are doing CGI, then for heavens sake:

use CGI;

and take full advantage of it.

print "<html><body><h1>Fingerprint Scanner Records</h1>";
print "<table>\n";

print

th><th>Archived</th></tr>\n";

while( @row = $sth->fetchrow_array )
{
print "<tr>";

foreach $field (@row)
{
print "<td>$field</td>";
}

print "</tr>";
}

print "</table>\n";
print "</body></html>";

$sth->finish;
$dbh->disconnect;

Thomas

General comment: By the time you have connected to your Access database
in a CGI script, you'll wish you'd used something else (that is, the
connection process uses a lot of time). A dbm-type hash-key "database"
would be far faster.

Thomas Rawley · Jul 21, 2003

Thanks for your quick response. I am very new to Perl and the code comes
from a book I have. I tried the ODBC in the Control Panel and it seemed to
work fine. Now I just have to figure out how to get this up and running on
the internet (it runs fine on the local machine). I appreciate your help.

Thomas

Bob Walton · Jul 21, 2003

Thomas said:
Thanks for your quick response. I am very new to Perl and the code comes
from a book I have. I tried the ODBC in the Control Panel and it seemed to
work fine. Now I just have to figure out how to get this up and running on
the internet (it runs fine on the local machine). I appreciate your help.

Thomas

You're welcome. Two things:

First, don't top-post (that's when you put your reply ahead of the text
you are responding to, like you just did).

Second, regarding getting your program to work as a CGI script, I
strongly recommend that you start with a "hello world" type CGI script
and get it working. Then add parameters, test database pieces, and so
forth to it one at a time. When you have that working, then go for your
full-blown application. For a nice reference of stuff to look out for, see:

perldoc -q 500

The references given there are tedious, but that's what CGI is about --
lots of little details, most of which are not intuitively obvious the
first time you see them, and all of which have to be exactly right. And
also note that CGI problems are generally not Perl problems (you would
have the same troubles no matter what language the CGI programs were
written in), hence they are off-topic for this newsgroup. They are
on-topic for comp.infosystems.www.authoring.cgi, but, as usual, make
sure you've exhausted their FAQs before posting.

Note also that the CGI module and in particular:

use CGI::Carp qw(fatalsToBrowser);

is a godsend for CGI work.

.... <stuff after top-post deleted>

Bart Lateur · Jul 23, 2003

Thomas said:
$DSN = 'driver=Mircosoft Access Driver (*.mdb);dbq=MyEasinetEvents.mdb';

^^
This has to be a typo...

Checking dynamically populated data using ajax with user entered value	5	Apr 11, 2020
Fail extracting table from .mdb file using DBI module	6	Feb 10, 2008
Perl to SQLite bridge is not working, database connect fails ....	3	Apr 8, 2014
yet another question about numbers and strings	3	Mar 15, 2012
DBI and Microsoft Access	3	Mar 9, 2005
mysql -- cannot connect (can in Perl)	6	Mar 7, 2011
Login page perl/CGI	2	Apr 27, 2007
Searching database with perl script	2	Jun 19, 2006

Retrieving Data from Microsoft Access Database using Perl

Thomas Rawley

Bob Walton

Thomas Rawley

Bob Walton

Bart Lateur

Ask a Question

Similar Threads

Members online

Forum statistics

Latest Threads