How to using How to using MS Access database MS Access database

in Perl/CGI Web Programmingin Perl/CGI Web Programming

Wei SunWei Sun

Introduction Introduction

• ODBC stands for:ODBC stands for:OOpenpen DDataataBBasease CConnectivityonnectivity

• The ODBC standard was designed to worThe ODBC standard was designed to work on any platform and has been ported tk on any platform and has been ported to win32, unix, macintosh, os/2 and othero win32, unix, macintosh, os/2 and others.s.

IntroductionIntroduction

ODBC was ODBC was designed designed by:by:

• X/Open X/Open

• SQL Access GroupSQL Access Group

• ANSIANSI

• ISOISO

• MicrosoftMicrosoft

• DigitalDigital

• SybaseSybase

• IBMIBM

• NovellNovell

• OracleOracle

• LotusLotus

• and others. and others.

IntroductionIntroduction

ODBC ManagerODBC Manager determines what to do.

ODBC DriverODBC Driver performs actual processing.

DatabaseAccess file

The database file is opened by the driver and data is manipulated.

Perl ProgramProgram calls an ODBC Module.

Win32:ODBCODBC Module call the ODBC

Install win32::odbc module Install win32::odbc module

• step one:step one: (perl 5.XXX need)(perl 5.XXX need)Download win32-ODBC.zip from Download win32-ODBC.zip from http://www.activestate.com/PPMPackages/zips/5xx-builds-only/http://www.activestate.com/PPMPackages/zips/5xx-builds-only/Win32-ODBC.zipWin32-ODBC.zip Readme Win32-ODBC.ppd Win32-ODBC.tar.gzReadme Win32-ODBC.ppd Win32-ODBC.tar.gz

• step two: step two: In dos windows, in the fold in which you unziped win32-odbc:In dos windows, in the fold in which you unziped win32-odbc:ppm install Win32-ODBC.ppdppm install Win32-ODBC.ppdoror ppm install win32-odbcbeta --location=http://www.roth.net/ppm install win32-odbcbeta --location=http://www.roth.net/perl/packagesperl/packages (if you use new version perl)(if you use new version perl)

Build a Database and Configure in Build a Database and Configure in

ODBCODBC

• Step one Step one

creat a access file ancreat a access file and add some tables and add some tables and data in: (file name id data in: (file name is test.mdb, table nas test.mdb, table name is Address)me is Address)

Build a Database and Configure in Build a Database and Configure in ODBCODBC• step two step two

Data Source NameDatabase InformationUseridPasswordConnection 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".

a) opena) open Start->Setting->Contrl Panel->data source(ODBC), Start->Setting->Contrl Panel->data source(ODBC),

select MS Access Database in User DSN .select MS Access Database in User DSN .

Build a Database and Configure in Build a Database and Configure in ODBCODBC

• b)select your database file: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 prograTest your database in Perl/CGI programm

• A) load odbc module A) load odbc module

#!perl #!perl use Win32::ODBC;use Win32::ODBC;

Test your database in Perl/CGI prograTest your database in Perl/CGI programm• B) Connect databaseB) Connect database $DSN = "MS Access Database"; #data base name$DSN = "MS Access Database"; #data base nameif (!( if (!( $db = new Win32::ODBC($DSN)$db = new Win32::ODBC($DSN) )){ )){ #$db is handle,ID and PW can be input here #$db is handle,ID and PW can be input here print "connect to database failed.</br>"; print "connect to database failed.</br>"; print "Error:".win32::ODBC::Error()."<br>\n";print "Error:".win32::ODBC::Error()."<br>\n";exit; exit; } } else{ else{

print "connected to database (connected number", $db->Connectiprint "connected to database (connected number", $db->Connection(), ")<br>"; on(), ")<br>"; } }

Test your database in Perl/CGI prograTest your database in Perl/CGI programm• C)show the table list in the database C)show the table list in the database

@tables = $db->TableList; print @tables;

• D) select a table(submit a query)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 prTest your database in Perl/CGI programogram• E)insert, update, delete records E)insert, update, delete records

#insert$sqlinsert = “ INSERT INTO address VALUES ('Euler', 'euler@21cn.com', 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 prTest your database in Perl/CGI programogram• 3.6 close the database3.6 close the database

$db->Close();$db->Close();

Test your database in Perl/CGI prograTest your database in Perl/CGI programm

• exampleexample

• http://stoneskin.dns2go.com:888/db/http://stoneskin.dns2go.com:888/db/

• http://stoneskin.dns2go.com:888/p/http://stoneskin.dns2go.com:888/p/login.htmllogin.html

More Refence More Refence

• for detail infromation: please read w32::ODBC documifor detail infromation: please read w32::ODBC documintationntationhttp://http://www.roth.net/perl/odbc/docswww.roth.net/perl/odbc/docs//

• some samples and source codessome samples and source codeshttp://multiweb.lib.calpoly.edu/odbc/http://multiweb.lib.calpoly.edu/odbc/

Thanks.Thanks.