Lecture 8 Accessing Database

1
Lecture 8 Accessing Database

• Database server
• Application access to the database
• Open Database Connectivity
• Accessing Database using Perl Script

2
1. Web Architecture

• Three-tier architecture
• Presentation clients contains both the
  presentation and application logic components.
• Content web server provides interactive view of
  information form a data store.
• Data and service level provides data for the web
  server.

3
1.1. Database

• Database
• Integrated collection of data
• Database Management System (DBMS)
• Has mechanisms for storing and organizing data
• Allows sophisticated queries and manipulations of
  data
• Database applications
• Banking all transactions
• Airlines reservations, schedules
• Universities registration, grades

4
1.1. Database

• Architecture for a database system

5
1.1. Database

• Architecture for a database system
• Physical level describes how a record is stored.
• single file MS Access, Lotus Approach, MS Fox
  Pro, Paradox
• true data server (multiple files) MySQL, Oracle,
  Sybase
• Logical level describes data stored in database,
  and the relationships among the data.
• View level application programs hide details of
  data types.
• Views can hide information (e.g. salary) for
  security purposes.

6
1.1.1. Relational Database Model

• Relational Database Model
• Most popular database system in use today
• Logical representation of the data
• Consider relationships between data without
  worrying about physical implementation
• Relational Database
• Composed of tables
• Any row of the table is called a record
• The first field is used as the primary key for
  referencing
• Records are normally unique (by primary key)
• Primary key can be composed of more than one
  field or column
• Each column represents a different field (or
  attribute)

7
1.1.1. Relational Database Model

• Example of Relational Database Structure

8
1.1.1. Relational Database Model

• Example of Relational Database Structure
• Physical level Microsoft Access - banking.mdb
• Logical level
• Three tables Customer, Account and Balance
• The primary key in Customer table is customer-id.

Table Customer
9
1.1.1. Relational Database Model

• Relationships among the three tables
• Lines between tables represent relationships
• Example Line between Customer and Account
• One-to-many relationship
• Every customer can have a number of accounts.

10
1.1.1. Relational Database Model

• View level join Customer and Account tables.

11
1.1.1. Relational Database Model

• Different users interested in different parts of
  the table
• Needs a data manipulation or query language - to
  access and manipulate the data from the
  table/database
• Structured Query Language (SQL pronounced
  sequel)
• The most widely used query language.
• Provides complete set of keywords
• Allows smaller databases to be combined to form
  larger ones
• Results of a query called result sets (or record
  sets)
• e.g. Result set to show the customers who stay in
  Kowloon

12
1.1.2. Structured Query Language

• Structured Query Language (SQL) keywords used to
• Query a database
• Insert records into a database
• Update existing records in a database

(Note there are other keywords not included in
this table)
13
1.1.2 SELECT Query

• SELECT FROM TableName
• TableName specifies table in database where data
  is located
• selects all rows and fields from TableName
• SELECT FROM Customer
• Selects the entire contents of the customer table
• SELECT customer-id, customer-name FROM Customer
• To select fields from table, replace with
  comma-separated list of field names to select
• Returns the following

14
1.1.2 SELECT Query

• WHERE clause
• Most cases, only necessary to locate records that
  satisfy certain selection criteria
• SQL uses the WHERE clause to specify the
  selection criteria
• Can contain operators
• lt, gt, lt, gt, , ltgt and LIKE
• Simplest form
• SELECT fieldName1, fieldName2, FROM TableName
  WHERE Criteria
• e.q. SELECT customer-id, district FROM customer
  WHERE district KOWLOON return the followings

15
1.1.2 SELECT Query

• ORDER BY clause
• Sorts results of query into ascending or
  descending order
• SELECT fieldName1, fieldName2, FROM TableName
  ORDER BY fieldName ASC
• SELECT fieldName1, fieldName2, FROM TableName
  ORDER BY fieldName DESC
• Example
• SELECT customer-id, customer-name FROM Customer
• ORDER BY customer-name ASC
• Returns customers sorted by customer name in
  ascending order

16
1.1.2 Inserting a Record

• INSERT INTO operation
• Inserts data into the table (e.g adds a record)
• Simplest form
• The single quote character should be used as a
  delimiter for strings to be inserted into the
  database

17
1.1.2 Inserting a Record

• Sample insert operation
• INSERT INTO Customer ( customer-id,
  customer-name, district, phone-no )
• Values (10006, Kwok, Charles, NT,
  9342-9432)
• Results

Newly added record
18
1.1.2 Updating a Record

• Modify a record with the UPDATE operation
• UPDATE TableName
• SET fieldName1 value1, fieldName2 value2, ,
  
• fieldNameN valueN
• WHERE criteria
• Example
• UPDATE Customer
• SET phone-no 9434-4321
• WHERE customer-id 10003
• Change phone number value for the customer id
  10003 from 2344-0979 to 9434-4321.

19
1.1.2 Updating a Record

• Old table
• New table

20
2 Application Access to the Database

• Microsoft Universal Data Access (UDA)
• Architecture designed for high-performance data
  access to different data sources.

21
2. Application Access to the Database

• UDA consists of three primary components
• OLE DB (Object Linking and Embedding DB)
• Core of UDA architecture
• Provides low-level access to any data source
• ODBC (Open Database Connectivity)
• API (Application Programming Interface) or
  programming language library written in C
• Developed by Microsoft to allow Windows
  applications to communicate in a uniform manner
  with relational databases
• Uses SQL to access data
• ADO (ActiveX Data Objects)
• Simple object model
• Provides uniform access to any data source by
  interacting with OLE DB

22
2. Application Access to the Database

• Two common methods
• ODBC approach - e.g. using Perl scripts (red
  line).
• ADO approach - e.g. using ASP (blue line).

23
2.1. ODBC Approach

• ODBC (Open Database Connectivity)
• API (Application Programming Interface) or
  programming language library written in C
• Uses SQL to access data
• ODBC Driver written by vendors
• Uses ODBC API to provide uniform access to the
  database
• To execute an SQL query
• Program must be able to access the database
• Database must be given a System Data Source Name
  (DSN) on the server
• Database must be registered as an ODBC source

24
2.1. Registering a Database

• Specific instructions for Windows 98/2000
• double-click the ODBC Data Sources (32 bit) icon
  in the Windows Control Panel to display the ODBC
  Data Source Administrator dialog.

• Click the System DSN tab to view a list of all
  systems DSNs.
• Select the name to the right of Microsoft Access
  Driver (.mdb) from the list.

25
2.1. Registering a Database

• Click Add to display the Create New Data Source
  dialog. Select Microsoft Access Driver (.mdb)
  and click Finish to display the ODBC Microsoft
  Access Setup dialog.

26
2.1. Registering a Database

• Enter the Data Source Name and Description field.
  Click the Select button to display the Select
  Database dialog. Select the database that you
  want. Click OK to close this dialog.

27
2.1. Registering a Database

• The Banking DSN is now listed.

28
3. Accessing Database using Perl Script

• After registering database as ODBC source, we can
  access it using Perl scripts.
• Perl package Win32-ODBC
• Enables Perl programs to connect to ODBC data
  sources
• Example From Web browser
• 1. Client enters SQL query string
• 2. String sent to Web server
• 3. Perl script executed
• Database queried
• 4. Record set in HTML form sent back to client
• Use tables to output fields in a database
• Organizes information neatly

29
3. Accessing Database using Perl Script
30
3. Accessing Database using Perl Script

• Example of how to access database by using HTML
  form and Perl scripts.

ltHTMLgt ltHEADgt ltTITLEgtSample Database
Querylt/TITLEgt lt/HEADgt ltBODY BACKGROUND
"images/back.gif"gt ltBASEFONT FACE
"ARIAL,SANS-SERIF" SIZE 2gt ltFONT SIZE
2gt ltSTRONGgtQuerying an ODBC database.lt/STRONGgt
lt/FONTgtltBRgt ltFORM METHOD "POST" ACTION
"cgi-bin/data.pl"gt ltINPUT TYPE "TEXT" NAME
"QUERY" SIZE 40 VALUE "SELECT FROM
CUSTOMER"gtltBRgtltBRgt ltINPUT TYPE "SUBMIT" VALUE
"Send Query"gt lt/FORMgt lt/BODYgt lt/HTMLgt
Insert and define text INPUT for entering SQL
query
31
3. Accessing Database using Perl Script

• Script output

32
3. Accessing Database using Perl Script

• Perl scripts

Program to query a database and send results to
the client. use Win32ODBC use CGI qw
(standard) querystring param(QUERY) DSN
"Banking" print header if (!(Data new
Win32ODBC(DSN))) print "Error connecting
to DSN\n" print "Error " .
Win32ODBCError() . "\n" exit if
(Data-gtSql(querystring)) print "SQL
failed.\n" print "Error " . Data-gtError() .
"\n" Data-gtClose() exit
Specifies that a new instant of the object is to
be created
Query string sent to the database If fails, error
message is returned
33
3. Accessing Database using Perl Script
print "ltBODY BACKGROUND \"/images/back.gif\"gt"
print "ltBASEFONT FACE \"ARIAL,SANS-SERIF\" SIZE
3gt" print "ltFONT COLOR BLUE SIZE 4gt Search
Results lt/FONTgt" counter 0 print "ltTABLE
BORDER 0 CELLPADDING 5 CELLSPACING
0gt" while(Data-gtFetchRow()) Data
Data-gtDataHash() _at_key_entries
keys(Data) print "ltTRgt" foreach
key( keys( Data ) ) print "ltTD BGCOLOR
9999CCgtDatakeylt/TDgt" print
"lt/TRgt" counter print "lt/TABLEgt" print
end_html Data-gtClose()
Retrieves the fields in a row from the record set
Coding HTML in Perl open using print header close
using print end_html
34
3. Accessing Database using Perl Script

• Error() - Returns the last encountered error
• FetchRow() - Retrieves the next record from the
  keyset.
• DataHash() - Returns the contents for the entire
  row from the record set
• keys() - Returns an unordered array containing
  all keys in a hash
• Close() - Closes the ODBC connection
• new - Creates a new ODBC connection based on DSN
  (Data Source Name)

35
3. Accessing Database using Perl Script

• Script output

36
Further Readings

• Note This topic is designed with the objective
  of providing an introduction to accessing
  database using perl scripts.
• Students who wish to invest more time on studying
  advanced features and topics of ODBC and
  accessing databases are referred to the following
  resources
• Deitel Chapter 22
• http//aspn.activestate.com/ASPN/Reference/Product
  s/ActivePerl/site/lib/Win32/ODBC.html
• http//aspn.activestate.com/
• http//www.roth.net/perl/odbc/faq/