Transcript Database Connectivity PowerPoint Slides

IS 4506
Database Connectivity
 Overview

Two and Three-Tier C/S Architecture

ASP Database Connection

ODBC - Connection to DBMS

Overview of transaction processing
Client /Server Architecture
PCs
Middleware
Database Server
Web Client/Server Architecture
Client
Web Browser
INTERNET
INTRANET
HTTP
Server
Multi-Tier Client/Server Architecture
Client
Web Browser
INTERNET
INTRANET
HTTP
Server
Database
Server
ODBC Errors
a) A user tries to connect to a database via your company’s
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 company’s
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.
ODBC Errors
c) A user tries to connect to a database via your
company’s 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
company’s web site and receives the error "ODBC…"
Unable to find resources. What is the possible cause?
ODBC driver not configured.
ASP Code
<%@ LANGUAGE = "VBSCRIPT"%>
<%
Set OBJdbConnection = Server.CreateObject("ADODB.Connection")
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
%>
 Database Connection - Input
Set OBJdbConnection =
Server.CreateObject("ADODB.Connection")
OBJdbConnection.Open "is4506","", ""
strSQL = "INSERT INTO Students(fname,lname) VALUES ('" &
Request.Form("fname") & "', '" & Request.Form("lname") &
"')"
OBJdbConnection.Execute(strSQL)
OBJdbConnection.Close
 Database Connection - Update
Set OBJdbConnection =
Server.CreateObject("ADODB.Connection")
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
 Database Connection - Delete
Set OBJdbConnection =
Server.CreateObject("ADODB.Connection")
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
 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
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.
Three-Tier Architecture

Presentation

Business/Data components

Data access
Components of a Transaction
Client
Network
Receiver
Connections
Context
Security
Thread Pool
Service Logic
Synchronization
Shared Data
Configuration
DB Manager
Queue
The Bank Example
Transaction
Transaction
Four
Components
 Account
Modifies an account

Move Money
Debit, credit, or
transfer

Receipt
Get unique #

Update receipt
Allocation 100
receipt #s
Account
Update
Receipt
Account
Move Money
Receipt
Client
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.