Transcript Week-9 - people

Interacting With Data
Week 8
Connecting to the database
Creating recordsets
Interacting with the database
Database - Data Table
Column
(Field)
Row
(Record)
Students
Table Name
Studentno
Name
Address
Phone
11462534
White
9 White St
783-5643
44376789
Brown
6 Brown Rd
656-2222
23390765
Smith
2 Smith St
222-5858
Information is added to a data table on a Row by Row basis
Table names and Column names are case sensitive
How Data Flows
ODBC (Open Database Connectivity)
(Translates client request so DBMS understands what the client wants)
Client Browser
Sends Recordset
request by way of
Connection Object
Sends and receives
Query request by way of
Recordset Object
Database Management System
Data Tables
(contains all information)
(Applies rules and searches for data)
Search for Data (SQL query)
Create recordset based
on data found
Recordsets
(contains copy of information requested by client)
How Data Flows
ODBC Connection Interpreter (DSN)
(Translates client request so DBMS understands what to do)
• Open Database Connectivity provides a standard software interface to different
database management systems (DBMS).
• Software applications can communicate with different DBMSs without altering
their applications by using drivers provided by Database system vendors
“a standard database access method developed by the SQL Access group
in 1992. The goal of ODBC is to make it possible to access any data
from any application, regardless of which database management system
(DBMS) is handling the data. ODBC manages this by inserting a middle
layer, called a database driver, between an application and the DBMS.
The purpose of this layer is to translate the application's data queries
into commands that the DBMS understands. For this to work, both the
application and the DBMS must be ODBC-compliant -- that is, the
application must be capable of issuing ODBC commands and the DBMS
must be capable of responding to them. Since version 2.0, the standard
supports SAG SQL.
http://www.ozekisms.com/high-performance-sms-gateway/terms-anddefinitions/index_p_php_q_ow_page_number_e_110opt.html
How Data Flows
• DSNs (data source name) are set up to use drivers,
either by;
1) accessing the ODBC applet in the control panel
or,
2) writing a DSN-less connection string (handy
when you do not have access to the control panel
or when the O/S does not have a control panel)
• A Connection Object is created which allows the
application to connect to the database System
How Data Flows
Creating The Connection Object
• The Connection Object can be created either as a DSN or DSN-less connection
DSN Connection
<%
‘create the object (DSN already set in the control panel) that will connect to the database
Set conn = Server.CreateObject (“ADODB.Connection”)
‘ open the connection
conn.Open “INT213con”
%> Note: I will discuss this topic in nest week.
DSN-less Connection
<%
‘create the object without DSN being setup in control panel
Set conn =Server. CreateObject (“ADODB.Connection”)
‘ create the connection
conn.ConnectionString="DRIVER={Microsoft Access Driver (*.mdb)};" &_
"DBQ=C:\Inetpub\wwwroot\INT213db.mdb“
‘Open the connection
conn.Open
%>
Recordset Object
Recordsets
(contains information requested by client)
• Recordset Object sends a query to the database system using the
Connection Object.
•A recordset is a data structure that consists of a group of database records, and can
either come from a base table or as the result of a query to the table.
http://en.wikipedia.org/wiki/Recordset
• The DBMS processes the query and returns the data requested in the
form of Rows and Columns (just like an Excel spreadsheet).
The Recordset and the data it contains is a copy (snapshot) of the data
from the data tables. It is held in RAM on the client system. This data can
be displayed, altered, deleted, etc. Any changes are sent back to the
server using the Recordset Object
• The actual data tables will not be affected by changes until the
Recordset is written back to the Data Tables.