MS Access with Perl

[Precision]

Does anyone know how I can connect to a MS Access database using PERL? I'm not sure if i have the connection string correct or not, I have set up my ODBC Tool like this:

DSN: precisionse_Articles
Path: \database\Lightscatter.mdb

and the PERL script:

my $dbfile = "e:\web\precisionse\htdocs\database\Lightscatter.md b";
$DBIConn = "dbi:ODBC:precisionse_Articles";
my $dbh = DBI->connect($DBIConn) or die "$DBI::errstr\n";

If anyone could help I would really appreciate it! Thanks for your time!

Simon

[Precision]

Ok, so now I can connect to the database [:)]. Now, how can I get my SQL Statement to work. I have this:

# Prepare SQL Statement
$sqlstatement = "SELECT * FROM Articles $where ORDER BY Article";

$sth = $dbh->prepare($sqlstatement);</font id="blue">

but the problem is the $dbh is from my old connection way:
my $dbh = DBI->connect("$DBIConn") or die "Opps, $DBI::errstr\n";

what am I supposed to do with the $dbh? What I am supposed to allocate it with? Thanks very much for your time and I hope I make some sense here! If not please tell me and I'll reword this. Thank you very much again!

[bruce]

This is the test code i use to test if perl can connect to access


use Win32::ODBC;

#-- Depending on your version of Win32::ODBC, you may need to do
#-- something like the following to suppress -w warnings.
$Win32::ODBC::ODBCPackage = $Win32::ODBC::ODBCPackage;
$ODBCPackage::Version = $ODBCPackage::Version;

my $Message = '';
my $Column = '';
my $Value = '';
my %HashRow = ();
my $db = '';
my $Source = 'DSN=<dsnname>;';

if ($db = new Win32::ODBC($Source)) {
print STDERR "\nODBC connection successful for data source $Source.\n\n";
} else {
$Message = Win32::ODBC::Error(); #-- use when no $db object ref.
print STDERR "\nMessage: $Message\n";
print STDERR "ERROR: ODBC error during connection\n";
exit;
}

my $Sql = "SELECT * FROM sometable";

if ( $db->Sql($Sql) ) {
#-- Error on Sql call returns error code else UNDEF for success.
#-- Note reverse of error handling on "new" method.
$Message = $db->Error();
print STDERR "\nERROR: $Message\n";
exit;
}

#-- Establish loop to process each row in the result set for the SELECT.
while ($db->FetchRow()) {
#-- Get one row from result set for SELECT statement and
#-- then store that row's columns as one hash. Hash key is
#-- column name and hash value is the column value.
%HashRow = $db->DataHash();

#-- Process each column in a row. In this case, just print them.
while ( ($Column, $Value) = each(%HashRow) ) {
print STDERR "Column '$Column' = '$Value'\n";
}
print STDERR "\n";
}

$db->Close() || die Win32::ODBC::Error();

exit;</font id="Courier New">

Hope this help


[b]quote:Originally posted by Precision

Does anyone know how I can connect to a MS Access database using PERL? I'm not sure if i have the connection string correct or not, I have set up my ODBC Tool like this:

DSN: precisionse_Articles
Path: \database\Lightscatter.mdb

and the PERL script:

my $dbfile = "e:\web\precisionse\htdocs\database\Lightscatter.md b";
$DBIConn = "dbi:ODBC:precisionse_Articles";
my $dbh = DBI->connect($DBIConn) or die "$DBI::errstr\n";

If anyone could help I would really appreciate it! Thanks for your time!

Simon

</blockquote id="quote"></font id="quote">