Beefy Boxes and Bandwidth Generously Provided by pair Networks
Syntactic Confectionery Delight
 
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 cooling their heels in the Monastery: (10)
As of 2014-09-22 09:06 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    How do you remember the number of days in each month?











    Results (185 votes), past polls