Transcript How to using MS Access database in Perl/CGI Web Programming

How to using
MS Access database
in Perl/CGI Web Programming
Wei Sun
Introduction
• ODBC stands for:
Open DataBase Connectivity
• The ODBC standard was designed to work
on any platform and has been ported to
win32, unix, macintosh, os/2 and others.
Introduction
ODBC was
designed by:
•
•
•
•
•
•
•
•
•
•
•
•
X/Open
SQL Access Group
ANSI
ISO
Microsoft
Digital
Sybase
IBM
Novell
Oracle
Lotus
and others.
Introduction
Program calls an ODBC
Module.
Perl Program
ODBC Module call the
ODBC
Win32:ODBC
ODBC Manager
determines what to do.
ODBC Manager
ODBC Driver performs
actual processing.
ODBC Driver
The database file is opened
by the driver and data is
manipulated.
Database
Access file
Install win32::odbc module
• step one: (perl 5.XXX need)
Download win32-ODBC.zip from
http://www.activestate.com/PPMPackages/zips/5xx-buildsonly/Win32-ODBC.zip
Readme Win32-ODBC.ppd Win32-ODBC.tar.gz
• step two:
In dos windows, in the fold in which you unziped win32-odbc:
ppm install Win32-ODBC.ppd
or
ppm install win32-odbcbeta -location=http://www.roth.net/perl/packages (if you use
new version perl)
Build a Database and Configure in ODBC
• Step one
creat a access file and
add some tables and
data in: (file name is
test.mdb, table name
is Address)
Build a Database and Configure in ODBC
• step two
a) open Start->Setting->Contrl Panel->data source(ODBC),
select MS Access Database in User DSN .
Data Source Name
Database Information
Userid
Password
Connection Information
User DSN only can be
accessed by who create it.
If you want the database
to be used by network
user, configure it in
System DSN
You can add/change DSN
name as you wish, not
must be "MS Access 97
Database".
Build a Database and Configure in ODBC
• b)select your database file:
In the Advanced,
you can set the ID
and password to
access the
database.
Test your database in Perl/CGI program
• A) load odbc module
#!perl
use Win32::ODBC;
Test your database in Perl/CGI program
• B) Connect database
$DSN = "MS Access Database"; #data base name
if (!( $db = new Win32::ODBC($DSN) )){
#$db is handle,ID and PW can be input here
print "connect to database failed.</br>";
print "Error:".win32::ODBC::Error()."<br>\n";
exit;
}
else{
print "connected to database (connected number", $db->Connection(),
")<br>";
}
Test your database in Perl/CGI program
• C)show the table list in the database
@tables = $db->TableList;
print @tables;
• D) select a table(submit a query)
if (! $db->Sql("SELECT * FROM [address] Where
age>=20") ){ //this is SQL language
@FieldNames = $db->FieldNames();
}
Test your database in Perl/CGI
program
• E)insert, update, delete records
#insert
$sqlinsert = “ INSERT INTO address VALUES ('Euler',
'[email protected]', 28, '021-345689') ";
#updata
$sqlupdate = “ UPDATE address SET age = age+10 ";
#del
$sqldelete = “ DELETE FROM address WHERE
name='jimtyan' ";
Test your database in Perl/CGI
program
• 3.6 close the database
$db->Close();
Test your database in Perl/CGI program
• example
• http://stoneskin.dns2go.com:888/db/
• http://stoneskin.dns2go.com:888/p/login.
html
More Refence
• for detail infromation: please read w32::ODBC
•
documintation
http://www.roth.net/perl/odbc/docs/
some samples and source codes
http://multiweb.lib.calpoly.edu/odbc/
Thanks.