IS 4506 Database Connectivity

1
IS 4506Database Connectivity
2
Overview

• Two and Three-Tier C/S Architecture
• ASP Database Connection
• ODBC - Connection to DBMS
• Overview of transaction processing

3
Client /Server Architecture
PCs
Middleware

• Database Server

4
Web Client/Server Architecture
Client Web Browser
INTERNET
INTRANET
HTTP Server
5
Multi-Tier Client/Server Architecture
Client Web Browser
INTERNET
INTRANET
HTTP Server

• Database
• Server

6
(No Transcript)
7
(No Transcript)
8
(No Transcript)
9
(No Transcript)
10
(No Transcript)
11
(No Transcript)
12
ODBC Errors a) A user tries to connect to a
database via your companys web site and receives
the error "ODBC" Login failure. What is the
possible cause? The user has insufficient
permission to access the database. b) A user
tries to connect to a database via your companys
web site and receives the error "ODBC" General
network error. What is the possible cause? Due to
heavy use, the database was recently moved to
another server.
13
ODBC Errors
c) A user tries to connect to a database via your
companys web site and receives the error "ODBC"
MS OLE ODBC provider for ODBC driver error
80004005. MS ODBC driver manager. Data source
name not found and no default driver specified.
What is the possible cause? Data source name is
configured incorrectly. d) A user tries to
connect to a database via your companys web site
and receives the error "ODBC" Unable to find
resources. What is the possible cause? ODBC
driver not configured.
14
ASP Code

• lt_at_ LANGUAGE "VBSCRIPT"gt
• lt
• Set OBJdbConnection Server.CreateObject("ADODB.C
  onnection")
• OBJdbConnection.Open "is4506","", "
• strSQL "INSERT INTO Students (FName,LName,Email)
  VALUES
• ('" Request.Form("first") "', '"
  Request.Form("last") "','" Request.Form("email
  ") "')"
• OBJdbConnection.Execute(strSQL)
• OBJdbConnection.Close
• gt

15
Database Connection - Input

• Set OBJdbConnection Server.CreateObject("ADODB.C
  onnection")
• OBJdbConnection.Open "is4506","", ""
• strSQL "INSERT INTO Students(fname,lname)
  VALUES ('" Request.Form("fname") "', '"
  Request.Form("lname") "')"
• OBJdbConnection.Execute(strSQL)
• OBJdbConnection.Close

16
Database Connection - Update

• Set OBJdbConnection Server.CreateObject("ADODB.C
  onnection")
• OBJdbConnection.Open "is4506","", ""
• ifname Request.Form("fname")
• 'example of using a variable in the SQL
  expression
• strSQL "UPDATE Students SET fname ('"
  ifname "'), lname ('" Request.Form("lname")
  "') WHERE ID (" Request.Form("ID") ")"
• OBJdbConnection.Execute(strSQL)
• OBJdbConnection.Close

17
Database Connection - Delete

• Set OBJdbConnection Server.CreateObject("ADODB.C
  onnection")
• OBJdbConnection.Open "is4506","", ""
• 'Note that the single apostrophe is missing from
  the ID expression when using numbers vice
  strings.
• strSQL "DELETE FROM Students WHERE ID ("
  Request.Form("ID") ")"
• OBJdbConnection.Execute(strSQL)
• OBJdbConnection.Close

18
Overview of Transaction Processing

• Transaction
• A unit of work that succeeds or fails as a whole
• Method for coordinating a series of changes made
  to a resource or sets of resources
• ACID Properties
• Atomicity
• Consistency
• Isolation
• Durability

19
ACID Properties

• Atomicity - Either all changes happen or none
  happen.
• Consistency - Actions taken as a group do not
  violate any integrity.
• Isolation - For actions that execute
  concurrently, one is either executed before or
  after the other, but not both.
• Durability - Changes survive failures of process,
  network, operating system, and others.

20
Three-Tier Architecture

• Presentation
• Business/Data components
• Data access

21
Components of a Transaction
Client
Network
Receiver
Queue
Connections
Security
Context
Configuration
DB Manager
Thread Pool
Service Logic
Synchronization
Shared Data
22
The Bank Example
Transaction
Transaction
Account
Account
Update Receipt
Move Money
Receipt
Client
23
MSDE

• MSDE is a stripped down version of SQL Server
  that is included with Access 2000 and Access
  2002.
• The Access 2000 version is named Microsoft Data
  Engine. It is a stripped down version of SQL
  Server 7. It can be installed directly from the
  Office 2000 CD from the following location
  SQL\x86\SETUP\SETUPSQL.EXE
• The Access 2002 version is named Microsoft
  Desktop Engine. It is a stripped down version of
  SQL Server 2000. It can also be installed
  directly from the CD from the following location
  MSDE\SETUP.EXE
• Both servers are fully functional SQL Servers but
  they do not include a user interface like
  Enterprise Manager.