INE1020 Lecture Notes
Download
Report
Transcript INE1020 Lecture Notes
Lecture 8 Accessing Database
Database server
Application access to the database:
Open Database Connectivity
Accessing Database using Perl Script
INE1020: Introduction to Internet Engineering
5: Web-based Applications
1
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.
HTTP request
HTTP response
Database
server
Internet
Client computers
with web browsers
Web server
Presentation level
Content level
INE1020: Introduction to Internet Engineering
Data and service
level
5: Web-based Applications
2
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
INE1020: Introduction to Internet Engineering
5: Web-based Applications
3
1.1. Database
Architecture for a database system
view level
view 1
view 1
view n
logical
level
physical
level
INE1020: Introduction to Internet Engineering
5: Web-based Applications
4
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.
INE1020: Introduction to Internet Engineering
5: Web-based Applications
5
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)
INE1020: Introduction to Internet Engineering
5: Web-based Applications
6
1.1.1. Relational Database Model
Example of Relational Database Structure
Attributes
A record
customer-id
10001
10002
10003
10004
10005
customer-name
Zhang, Stephen
Ho, Ricky
Chau, Anthony
Wong, Michael
Tsang, Daniel
Primary Key
INE1020: Introduction to Internet Engineering
district
Kowloon
NT
HK
Kowloon
NT
phone-no
9843-9893
9873-1341
2344-0979
2878-4323
2134-4532
account-no
A-201
A-217
A-222
A-103
A-234
A column
5: Web-based Applications
7
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: Account
Table: Customer
customer-id
10001
10002
10003
10004
10005
customer-name
Zhang, Stephen
Ho, Ricky
Chau, Anthony
Wong, Michael
Tsang, Daniel
district
Kowloon
NT
HK
Kowloon
NT
phone-no
9843-9893
9873-1341
2344-0979
2878-4323
2134-4532
INE1020: Introduction to Internet Engineering
customer-id account-no
10001
A-201
10002
A-217
10002
A-300
10003
A-222
10003
A-239
10004
A-103
10005
A-109
10005
A-234
Table: Balance
account-no
A-103
A-109
A-201
A-217
A-222
A-234
A-239
A-300
balance
850
400
700
300
100
1200
450
500
5: Web-based Applications
8
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.
INE1020: Introduction to Internet Engineering
5: Web-based Applications
9
1.1.1. Relational Database Model
View level: join Customer and Account tables.
Attributes
A record
customer-id
10001
10002
10003
10004
10005
customer-name
Zhang, Stephen
Ho, Ricky
Chau, Anthony
Wong, Michael
Tsang, Daniel
Primary Key
INE1020: Introduction to Internet Engineering
district
Kowloon
NT
HK
Kowloon
NT
phone-no
9843-9893
9873-1341
2344-0979
2878-4323
2134-4532
account-no
A-201
A-217
A-222
A-103
A-234
A column
5: Web-based Applications
10
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
customer-id district
10001
Kowloon
10004
Kowloon
INE1020: Introduction to Internet Engineering
5: Web-based Applications
11
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
SQL keyword
SELECT FROM
WHERE
ORDER BY
INSERT INTO
UPDATE
Description
Select (retrieve) fields from one or more tables.
Criteria for selection that determine the rows to be
retrieved.
Criteria for ordering (sorting) of records.
Insert values into one or more tables. [Note: Some
databases do not require the SQL keyword INTO.]
Update existing data in one or more tables.
(Note: there are other keywords not included in this table)
INE1020: Introduction to Internet Engineering
5: Web-based Applications
12
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:
customer-id customer-name
10001
10002
10003
10004
10005
INE1020: Introduction to Internet Engineering
Zhang, Stephen
Ho, Ricky
Chau, Anthony
Wong, Michael
Tsang, Daniel
5: Web-based Applications
13
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
• <, >, <=, >=, =, <> 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:
customer-id district
10001
Kowloon
10004
Kowloon
INE1020: Introduction to Internet Engineering
5: Web-based Applications
14
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
customer-id
10003
10002
10005
10004
10001
customer-name
Chau, Anthony
Ho, Ricky
Tsang, Daniel
Wong, Michael
Zhang, Stephen
INE1020: Introduction to Internet Engineering
5: Web-based Applications
15
1.1.2 Inserting a Record
INSERT INTO operation
Inserts data into the table (e.g; adds a record)
Simplest form:
Table into
which record
will be inserted
List of field names into
which to insert values
(not required if inserting
complete record)
INSERT INTO TableName ( fieldName1, fieldName2, …, fieldNameN )
Values ( value1, value 2, …, valueN )
KEYWORDS
Values to be inserted into fields
– in order of fields listed before
The single quote character should be used as a delimiter for
strings to be inserted into the database
INE1020: Introduction to Internet Engineering
5: Web-based Applications
16
1.1.2 Inserting a Record
Sample insert operation
INSERT INTO Customer ( customer-id, customername, district, phone-no )
Values (10006, ‘Kwok, Charles’, ‘NT’, ‘9342-9432’)
Results:
Newly added
record
customer-id
10001
10002
10003
10004
10005
10006
customer-name
Zhang, Stephen
Ho, Ricky
Chau, Anthony
Wong, Michael
Tsang, Daniel
Kwok, Charles
INE1020: Introduction to Internet Engineering
district
Kowloon
NT
HK
Kowloon
NT
NT
phone-no
9843-9893
9873-1341
2344-0979
2878-4323
2134-4532
9342-9432
5: Web-based Applications
17
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.
INE1020: Introduction to Internet Engineering
5: Web-based Applications
18
1.1.2 Updating a Record
Old table:
customer-id
10001
10002
10003
10004
10005
10006
customer-name
Zhang, Stephen
Ho, Ricky
Chau, Anthony
Wong, Michael
Tsang, Daniel
Kwok, Charles
district
Kowloon
NT
HK
Kowloon
NT
NT
phone-no
9843-9893
9873-1341
2344-0979
2878-4323
2134-4532
9342-9432
district
Kowloon
NT
HK
Kowloon
NT
NT
phone-no
9843-9893
9873-1341
9434-4321
2878-4323
2134-4532
9342-9432
New table:
customer-id
10001
10002
10003
10004
10005
10006
customer-name
Zhang, Stephen
Ho, Ricky
Chau, Anthony
Wong, Michael
Tsang, Daniel
Kwok, Charles
INE1020: Introduction to Internet Engineering
5: Web-based Applications
19
2 Application Access to the Database
Microsoft Universal Data Access (UDA)
Architecture
designed for high-performance data
access to different data sources.
Application or Browser
ADO
OLE DB
ODB
C
Relational data sources
Mainframe/legacy data
Non-relational data sources
INE1020: Introduction to Internet Engineering
5: Web-based Applications
20
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
INE1020: Introduction to Internet Engineering
5: Web-based Applications
21
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).
Application or Browser
ADO
OLE DB
ODB
C
Relational data sources
Mainframe/legacy data
Non-relational data sources
INE1020: Introduction to Internet Engineering
5: Web-based Applications
22
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
INE1020: Introduction to Internet Engineering
5: Web-based Applications
23
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.
INE1020: Introduction to Internet Engineering
5: Web-based Applications
24
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.
INE1020: Introduction to Internet Engineering
5: Web-based Applications
25
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.
INE1020: Introduction to Internet Engineering
5: Web-based Applications
26
2.1. Registering a Database
The Banking DSN is now listed.
INE1020: Introduction to Internet Engineering
5: Web-based Applications
27
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
INE1020: Introduction to Internet Engineering
5: Web-based Applications
28
3. Accessing Database using Perl Script
INE1020: Introduction to Internet Engineering
5: Web-based Applications
29
3. Accessing Database using Perl Script
Example of how to access database by using HTML
form and Perl scripts.
<HTML>
<HEAD>
<TITLE>Sample Database Query</TITLE>
</HEAD>
<BODY BACKGROUND = "images/back.gif">
<BASEFONT FACE = "ARIAL,SANS-SERIF" SIZE = 2>
<FONT SIZE = +2>
<STRONG>Querying an ODBC database.</STRONG>
</FONT><BR>
<FORM METHOD = "POST" ACTION = "cgi-bin/data.pl">
<INPUT TYPE = "TEXT" NAME = "QUERY" SIZE = 40
VALUE = "SELECT * FROM CUSTOMER"><BR><BR>
<INPUT TYPE = "SUBMIT" VALUE = "Send Query">
</FORM>
</BODY>
</HTML>
INE1020: Introduction to Internet Engineering
Insert and
define text
INPUT for
entering SQL
query
5: Web-based Applications
30
3. Accessing Database using Perl Script
Script output:
CUSTOMER
INE1020: Introduction to Internet Engineering
5: Web-based Applications
31
3. Accessing Database using Perl Script
Perl scripts:
# Program to query a database and send results to the client.
use Win32::ODBC;
use CGI qw (:standard);
$querystring = param(QUERY);
$DSN = "Banking";
print header;
if (!($Data = new Win32::ODBC($DSN))) {
print "Error connecting to $DSN\n";
print "Error: " . Win32::ODBC::Error() . "\n";
exit;
}
if ($Data->Sql($querystring)) {
print "SQL failed.\n";
print "Error: " . $Data->Error() . "\n";
$Data->Close();
exit;
}
INE1020: Introduction to Internet Engineering
Specifies that a new
instant of the object
is to be created
Query string sent to
the database
If fails, error message
is returned
5: Web-based Applications
32
3. Accessing Database using Perl Script
print "<BODY BACKGROUND = \"/images/back.gif\">";
print "<BASEFONT FACE = \"ARIAL,SANS-SERIF\" SIZE = 3>";
print "<FONT COLOR = BLUE SIZE = 4> Search Results </FONT>";
$counter = 0;
print "<TABLE BORDER = 0 CELLPADDING = 5 CELLSPACING = 0>";
while($Data->FetchRow()) {
%Data = $Data->DataHash();
@key_entries = keys(%Data);
print "<TR>";
foreach $key( keys( %Data ) ) {
print "<TD BGCOLOR = #9999CC>$Data{$key}</TD>";
}
print "</TR>";
$counter++;
Retrieves the fields in
a row from the record
set
Coding HTML in Perl
open using print header
close using print
end_html
}
print "</TABLE>";
print end_html;
$Data->Close();
INE1020: Introduction to Internet Engineering
5: Web-based Applications
33
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)
INE1020: Introduction to Internet Engineering
5: Web-based Applications
34
3. Accessing Database using Perl Script
Script output:
INE1020: Introduction to Internet Engineering
5: Web-based Applications
35
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/Pro
ducts/ActivePerl/site/lib/Win32/ODBC.html
http://aspn.activestate.com/
http://www.roth.net/perl/odbc/faq/
INE1020: Introduction to Internet Engineering
5: Web-based Applications
36