Scripting Access with Perl

Perl Scripting with Access Databases

General Scripting Information

The scripting information provided here is aimed at Espinda' customers. Much of the code is applicable to any Windows based Web Server. However some parameters, such as the methods used to derive file paths, may not be valid.
Our Perl use the same OLE objects as ASP.

The current version of ADO installed on our servers is MDAC 2.7. Detailed component information can be obtained from Microsoft or one of the many scripting sites on the Web. Search on google for "recordset.movenext" or something similar to find them.

Before looking at the code, the following points should be considered when using Access databases in a shared hositng environment:

* All scripts run on a shared server, and thus what happens on one site has an effect on the stability of certain components on other sites on the same server. Access ADO is such a component. It is a shared resource, and should be programmed correctly.

* The Jet access engine has a limited set of resources. Once these are exhausted, the script will generate errors. These are normally temporary in nature and can occur when server loading is very heavy. These are operating system limitations, and cannot be extended or isolated by us.

* You should use only Access 2000 or higher databases. If you use Access 97 you may receive the following error:-
[Microsoft][ODBC Microsoft Access Driver] Cannot open database '(unknown)'. It may not be a database that your application recognizes, or the file may be corrupt.
This is due to an issue in MDAC 2.5 and greater and is only resolved by using a newer version of Access.

* Use as few connections as possible. If you are using session and application based objects, use a single application based connection. Otherwise, use one per script, explicitly opening the connection at the start of the script, and then closing it at the end of the script.

Using ADO in Perl to connect to an Access Database on Your Site

Now to the code. First intialize the units and output the HTML header,

use Win32::OLE;
use Win32::OLE::Const ;

print "Content-type: text/html\n\n";

and get the web root folder so that we can get the path to the database in the private folder. Next set up the database connection. Always explicitly open the connection. Do not use a connection string with objects such as a command or a recordset object. These implicitly open a database connection which they then leave open to time out after the script closes. This connection accesses the Northwinds database which is stored in the Private directory below the web site root folder HTDOCS.

$webroot = substr ($ENV{'PATH_TRANSLATED'}, 0, length ($ENV{'PATH_TRANSLATED'}) - (length ($ENV{'PATH_INFO'})+(length("htdocs"))))."private";
print $webroot;

$DBFile = "$webroot\\northwind.mdb";
$connstr = "PROVIDER=MSDASQL;DRIVER={Microsoft Access Driver (*.mdb)};DBQ=$DBFile;UID=;PWD=;";
print $connstr;

# Create our ADO Connection object and open it
$cnn = Win32::OLE->new("ADODB.Connection");
$rec = Win32::OLE->new("ADODB.Recordset");
$cnn->Open($connstr);

Now open the resordset using the current connection. In this case we are querying the customer table.

$SQL ="SELECT top 10 (city) FROM customer";

$rec->open($SQL, $cnn, 1, 1);

With the recordset now open, we iterate through it and output the results to the page. Note that we don't need to use the movefirst method as the recordset points to the first record by default when opened. With an empty recordset, the loop exits immediately without errors.

until($rec->EOF)
{
$fields = $rec->Fields;
$firstname=$fields->Item("city")->value;
print $firstname;
$rec->MoveNext();
}

Finally we close the connection used.

$cnn->Close;