How to access MS Access from Perl?

Discussion in 'Perl' started by luican, Jun 1, 2004.

  1. luican

    luican Guest

    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, Jun 1, 2004
    #1
    1. Advertising

  2. Email to 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" <>
    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
     
    Aaron W. West, Jun 2, 2004
    #2
    1. Advertising

  3. luican

    luican Guest

    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" <> wrote in message
    news:...
    > Email to 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" <>
    > 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, Jun 3, 2004
    #3
  4. 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.

    "luican" <> wrote in message
    news:8vJvc.11950$OI5.2480@edtnps84...
    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" <> wrote in message
    news:...
    > Email to 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" <>
    > 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
    >
    >
    >
    >
     
    Aaron W. West, Jun 4, 2004
    #4
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. dpackwood
    Replies:
    3
    Views:
    1,861
  2. PerlFAQ Server

    FAQ 1.4 What are Perl 4, Perl 5, or Perl 6?

    PerlFAQ Server, Jan 23, 2011, in forum: Perl Misc
    Replies:
    0
    Views:
    332
    PerlFAQ Server
    Jan 23, 2011
  3. PerlFAQ Server
    Replies:
    0
    Views:
    725
    PerlFAQ Server
    Feb 3, 2011
  4. PerlFAQ Server

    FAQ 1.4 What are Perl 4, Perl 5, or Perl 6?

    PerlFAQ Server, Feb 27, 2011, in forum: Perl Misc
    Replies:
    0
    Views:
    322
    PerlFAQ Server
    Feb 27, 2011
  5. PerlFAQ Server
    Replies:
    0
    Views:
    734
    PerlFAQ Server
    Apr 4, 2011
Loading...

Share This Page