How to access MS Access from Perl?

[luican]

Hi,

Good day! I am writing some Perl script on the server side for HTML forms
that can create/update an MS Access database. Does anyone know of examples
how this can be done?

Your help is greatly appreciated.

-lc

 

[Aaron W. West]

Email to (e-mail address removed) bounces.

Anyway, try DBI and DBD::ODBC. Or maybe DBD::ADO, but I've never tried it.

----------------------------------------

http://cpan.org

http://search.cpan.org/~jurl/DBD-ODBC-1.09/ODBC.pm

...
Connect without DSN The ability to connect without a full DSN is introduced
in version 0.21.

Example (using MS Access): my $DSN = 'driver=Microsoft Access Driver
(*.mdb);dbq=\\\\cheese\\g$\\perltest.mdb'; my $dbh =
DBI->connect("dbi:ODBC:$DSN", '','') or die "$DBI::errstr\n";

The above sample uses Microsoft's UNC naming convention to point to the
MSAccess file (\\\\cheese\\g$\\perltest.mdb). The dbq parameter tells the
access driver which file to use for the database.

...
----------------------------------------
One thing you might try is setting the default bind type to varchar as I did
for MS SQL Server, if you have date conversion errors. But it may not be
relevant to MS Access:

# This is needed to avoid needless sql_describe_params,
# and errors inserting datetime values
# It emulates old DBI 0.28 behavior:
$dbh->{odbc_default_bind_type} = 12; # SQL_VARCHAR; #12

----------------------------------------

http://search.cpan.org/~sgoeldner/DBD-ADO-2.91/lib/DBD/ADO.pm

...
The DBD::ADO module supports ADO access on a Win32 machine.
...


----- Original Message -----
From: "luican" <[email protected]>
Newsgroups: comp.lang.perl
Sent: Tuesday, June 01, 2004 12:07 PM
Subject: How to access MS Access from Perl?


Hi,

Good day! I am writing some Perl script on the server side for HTML forms
that can create/update an MS Access database. Does anyone know of examples
how this can be done?

Your help is greatly appreciated.

-lc

 

[luican]

Thanks Aaron. Here's an example I modified using Win32::ODBC and an ODBC
data source to the Northwind database.
What benefits will DBI provide?

lc


use Win32::ODBC;
$db = new Win32::ODBC( "northwind" ) || die "Error connecting: " .
Win32::ODBC::Error();

if( ! $db->Sql( "SELECT CustomerID FROM customers" ) ) {
while( $db->FetchRow() ) {
%Data = $db->DataHash();
@key_entries = keys(%Data);
$Row++;
print "$Row)";
foreach $key ( keys( %Data ) )
{
print "\t'$key' = '$Data{$key}'\n";
}
}
}
else
{
print "Unable to execute query: " . $db->Error() . "\n";
}
$db->Close();

 

[Aaron W. West]

Correct me if I'm wrong, anyone, but...

I see no way to bind parameters in Win32::ODBC, which means if you wish to
insert or update values in text fields, you need to build a SQL statement
while doubling all single-quotes within the fields. Such an approach may not
be unreasonable, but I think it's preferable to use bound parameters. Also,
if you were to later migrate to (a heavily loaded) MS SQL Server or Oracle
as the database, it would be much preferable to use bound parameters, to
minimize your load on the database server's compiled procedure cache.

On the other hand, if startup time for your perl script is an issue, and
performance is less of one, a lightweight module such as this may be
slightly preferable to the bulkier DBI + DBD::ODBC. In my testing startup
time is 54 to 70 ms less for use Win32::ODBC; than for use DBI; use
DBD::ODBC. (Less than the startup time difference between Cygwin Perl and
ActiveState Perl ; Cygwin perl is faster by around 100 ms with these
modules.)

I found that there is a "quote" function in the module DBI::W32ODBC v11.2,
which also escapes \r and \n characters for Access, so that is apparently
also necessary. The quote function seems not to be used anywhere in that
module, so must be provided just for convenience for the user of the module
for building SQL queries (INSERT or UPDATE statements, in particular.)

I think DBI and DBD::ODBC are far more mature, as well...

I really think it would be helpful if the authors of these modules stated
more of the advantages and disadvantages of using their module right in the
description of their module. Perhaps it's hard for an author to be objective
about it. But the module docs for Win32::ODBC don't even give an example of
an INSERT or UPDATE statement (perhaps a clue that it may be problematic for
those usages.)

DBI is admittedly, to me, a little annoying in that it provides too many
ways to do the same thing. Do I really need all those ways? I'm sure some
perform better than others, or are better in some situations... Of course,
there's nothing stopping you from using prepare, optional bind_param for
each parameter (but do it for all if you do it for any, I think, to be
consistent), and execute for every query, which is how I did it at first,
just to minimize the ways I'd have to learn.

Thanks Aaron. Here's an example I modified using Win32::ODBC and an ODBC
data source to the Northwind database.
What benefits will DBI provide?

lc


use Win32::ODBC;
$db = new Win32::ODBC( "northwind" ) || die "Error connecting: " .
Win32::ODBC::Error();

if( ! $db->Sql( "SELECT CustomerID FROM customers" ) ) {
while( $db->FetchRow() ) {
%Data = $db->DataHash();
@key_entries = keys(%Data);
$Row++;
print "$Row)";
foreach $key ( keys( %Data ) )
{
print "\t'$key' = '$Data{$key}'\n";
}
}
}
else
{
print "Unable to execute query: " . $db->Error() . "\n";
}
$db->Close();