Beefy Boxes and Bandwidth Generously Provided by pair Networks
Syntactic Confectionery Delight
 
PerlMonks  

Re: Can I talk to an MS Access DB using Perl?

by Asim (Hermit)
on Feb 02, 2000 at 19:07 UTC ( #2732=note: print w/ replies, xml ) Need Help??


in reply to Can I talk to an MS Access DB using Perl?

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.


Comment on Re: Can I talk to an MS Access DB using Perl?
Select or Download Code
RE: Re: Can I talk to an MS Access DB using Perl?
by athomason (Curate) on May 22, 2000 at 12:49 UTC
    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: note [id://2732]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others browsing the Monastery: (9)
As of 2014-10-20 08:58 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    For retirement, I am banking on:










    Results (74 votes), past polls