Connecting to an MS Access database.

[gmccammon]

I am working on a class assignment where I have to connect to an MS Access database. The perl script is contained in a separate file *.pl

I went into Control panel/ODBC/ and selected Microsoft Access driver and identified the source name.

I was looking at a previous post here and many things appear to be similar to my requirements.

1. Add dbi inclusion. (pretty straight forward, like an “include” in C or “import” in Java

use DBI;
$::db_host = 'localhost';
$::db_name = 'KTC.mdb';
$::db_user = 'root';
 

My database is titled KTC.mdb and is located in the apache2\database directory
I’m not sure what the db_user=’root’ statement does?

2.Add database connection hardcoding the user name and password. (use the DBI connect call and store the handle)
This is somewhat confusing because there are no passwords on our database. I am also using access not mysql. DBI:mysql:host= ??

# $::db = DBI->connect(
# "DBI:mysql:host=$::db_host;database=$::KTC.mdb",
# $::db_user) || die $DBI::errstr;
 

3.Add a query to select the product name, description, units in stock, and price. (use products table and only select one product, where “ProductID = $productID”)

my $stm= $::db->prepare("SELECT ProductName, ProductDescription, UnitsInStock, UnitPrice FROM Products WHERE ProductID = $productID ORDER BY ProductID");
$stm->execute || die $::db->errstr;
my $LST = $stm->fetchall_hashref('ProductID');
 

4. Add statement handle definition and execution.
5. Code the loop by fetching an array from the statement handle. (use “fetchrow_array()”, will return records from database into an array “@product”, there are also other ways to do this, could return a hash)
6. Display the records. (students often get confused with this. You need to access the indices of the array to display in the table. Each index corresponds to the column name in the select statement of your query. The syntax to grab an index of an array for this project in perl is (“$product[x]” where x is a number pertaining to the column name, will map to product information)
I haven’t even looked at these requirements yet.

7. Disconnect database.

$rc = $dbh->disconnect();
 

Do I need more than this?

[vssp]

=**************************************
= Name: Create Table/MS Access/ADO
= Description:This is a simple script th
= at will create a
new table in a Access Database. This is to show
how to connect to an Access Database thru Perl
by using DBI and the ADO driver. You can also
connect by using ODBC, by replacing where you
ADO. dbi:ADO:$DSN
= By: Randy McCleary
=
=This code is copyrighted and has = limited warranties.Please see http://w
= ww.Planet-Source-Code.com/vb/scripts/Sho
= wCode.asp?txtCodeId=457&lngWId=6 =for details. =**************************************

#!/usr/bin/perl -w
################################################## #
## This is a simple script that will create a
## new table in a Access Database. This is to show
## how to connect to an Access Database thru Perl
## by using DBI and the ADO driver. You can also
## connect by using ODBC, by replacing where you
## ADO. dbi:ADO:$DSN
################################################## #
use POSIX;
use strict;
use CGI qw(:standard);
use CGI::Carp qw(fatalsToBrowser);
use DBI;
use Time::Local
print header;

######################################
## Set DSN-Less Connection
######################################
my $DSN = 'driver=Microsoft Access Driver (*.mdb);dbq=C:\WINDOWS\perl.mdb';
my $dbh = DBI->connect("dbi:ADO:$DSN", '','')
or die "$DBI::errstr\n";

######################################
## Generate the SQL Statement
######################################
my $sql = <<"EndOfSQL";
CREATE TABLE tblContacts2 (
ID COUNTER,
LastName CHAR(40),
FirstName CHAR(40),
MiddleName CHAR(20);
HomePhone CHAR(40),
WorkPhone CHAR(40),
CellPhone CHAR(40);
BirthDay CHAR(20);
Fax CHAR(40),
Email CHAR(40),
Address1 CHAR(40),
Address2 CHAR(40),
City CHAR(30),
State CHAR(10),
ZipCode CHAR(20),
CONSTRAINT ID_PK PRIMARY KEY(ID)
)
EndOfSQL


################################
## Execute the SQL Statement
################################
$dbh->do($sql)
or die "Execution problem: $DBI::errstr";
print "Table was Created";

######################################
## Close the connection when finished:
######################################
$dbh->disconnect;



I hpe this helpfull for u

vssp

[gmccammon]

Thanks, I finally got it to connect, I just have a data fiormatting problem now.