Beefy Boxes and Bandwidth Generously Provided by pair Networks
Pathologically Eclectic Rubbish Lister
 
PerlMonks  

Can I talk to an MS Access DB using Perl?

by Anonymous Monk
on Feb 01, 2000 at 07:50 UTC ( #2641=perlquestion: print w/ replies, xml ) Need Help??
Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Just want to do something simple, query Access DB and provide action from there, just want to know if this is possible

Comment on Can I talk to an MS Access DB using Perl?
Re: Can I talk to an MS Access DB using Perl?
by dlc (Acolyte) on Feb 01, 2000 at 18:39 UTC

    try installing DBD::ODBC, which you can get from CPAN. from the readme for version 0.22

      Win32 ODBC drivers:
      http://www.microsoft.com/support/products/backoffice/odbc/
      Follow "Microsoft ODBC Desktop Database Drivers 3.5 For 32-Bit Programs"
      and "ODBC Drivers for Win95 Applications".
      For Access use version "Access ODBC driver 3.40.2111 27/03/96" or later.
      If your using Activestates Perl build for Windows, you can use Win32::ODBC. Its very easy, and was very robust for the project I did in it. www.roth.net has more information and a tutorial. It comes preloaded in recent activestate builds as well. _Drew
Re: Can I talk to an MS Access DB using Perl?
by Asim (Hermit) on Feb 02, 2000 at 19:07 UTC

    Here's two snippets of code to get you started, as well. I've been working on this very issue recently! The first connects using DBI::ADO, but works the same for DBI::ODBC with one caveat, as noted below. Also, I'm doing something fairly complex, with making a array of hashs holding the data. The docs for both modules can tell you how to get simpler forms of the data, if you wish:

    use strict; use DBI; $| =1; my @sites; #This below can be changed to 'dbi:ODBC:<name>' #provided you have DBI::ODBC installed #I'm testing both, and have both installed #They "feel" fairly interchangable my $dbh = DBI->connect('dbi:ADO:Name', undef, undef, {PrintError => 1, + RaiseError => 1}); #The two lines below are for use with DBI::ODBC #with BLOB, aka MEMO fields with large amounts of data. #Look in the docs for the modules for more on them #$dbh->{LongReadLen} = 65534; #$dbh->{LongTruncOk} = 1; my $sth = $dbh->prepare('SELECT * FROM tbldata'); $sth->execute; #dump_results is very good for testing, #and even for simple apps -- you don't need to #loop the results, just print out $results #my $results = DBI::dump_results($sth); #One of the nice things about DBI is the wide #variety in gives you in retrieving data while (my $results = $sth->fetchrow_hashref) { push @sites, $results; }

    Now for the Win32::ODBC version -- less commentary, as it's a little less flexible, but more obvious (IMHO):

    use Win32::ODBC; my @rows; my $DSN = "Name"; if (!($db = new Win32::ODBC($DSN))){ print "error connecting to $DSN\n"; print "error: " . Win32::ODBC::Error() . "\n"; exit; } die qq(SQL failed: ), $db->Error(), qq(\n) if ($db->Sql("SELECT * FROM + tbldata")); while ($db->FetchRow()) { my %data = $db->DataHash(); push @rows, {%data}; }

    In general, I'd use DBI unless you have need of Win32::ODBC's features, but please look over both.

    ----Asim

    Edited by footpad, ~Sun Jan 6 04:48:47 2002 (GMT): Added <CODE> and <P> tags.

      No disrespect intended, but here's a cleaner version (same code as above, but within code tags).
      use strict; use DBI; $| =1; my @sites; #This below can be changed to 'dbi:ODBC:<name>' #provided you have DBI::ODBC installed #I'm testing both, and have both installed #They "feel" fairly interchangable my $dbh = DBI->connect('dbi:ADO:Name', undef, undef, {PrintError => 1, + RaiseError => 1}); #The two lines below are for use with DBI::ODBC #with BLOB, aka MEMO fields with large amounts of data. #Look in the docs for the modules for more on them #$dbh->{LongReadLen} = 65534; #$dbh->{LongTruncOk} = 1; my $sth = $dbh->prepare('SELECT * FROM tbldata'); $sth->execute; #dump_results is very good for testing, #and even for simple apps -- you don't need to #loop the results, just print out $results #my $results = DBI::dump_results($sth); #One of the nice things about DBI is the wide #variety in gives you in retrieving data while (my $results = $sth->fetchrow_hashref) { push @sites, $results; } ==================================================== Now for the Win32::ODBC version -- less commentary, as it's a little l +ess flexiable, but more obvious (IMHO): use Win32::ODBC; my @rows; my $DSN = "Name"; if (!($db = new Win32::ODBC($DSN))){ print "error connecting to $DSN\n"; print "error: " . Win32::ODBC::Error() . "\n"; exit; } die qq(SQL failed: ), $db->Error(), qq(\n) if ($db->Sql("SELECT * FROM + tbldata")); while ($db->FetchRow()) { my %data = $db->DataHash(); push @rows, {%data}; }
        I use the above code but the results prined from @rows...is like HASH(01b2b69c)..... & so on...instead of the the fields in the table....can u tell whts going wrong.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://2641]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others chanting in the Monastery: (13)
As of 2015-07-01 21:16 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









    Results (22 votes), past polls