Integrating Web & DBMS

(1)

Integrating Web & DBMS

Gianluca Ramunno < [email protected] >

li h i t d b

english version created by Marco D. Aime < [email protected] >

Politecnico di Torino Dip. Automatica e Informatica

Open Database Connectivity (ODBC)

technology for DBMS access that is part of Microsoft Data Access Components (MDAC)

C++ low level interface

included in Microsoft O.S.

allows accessing data from any application in DBMS-independent way

inserts a middleware (the ( database driver) between ) application and DBMS

uses SQL as data access & manipulation language

used by DAO (application level interface)

(2)

Open Database Connectivity (ODBC)

application

driver driver

driver

driver manager

data source _{data source} data source

SqlServer oracle xls

ODBC

Open Database Connectivity (ODBC)

application

driver manager (DLL)g ( )

loads the drivers upon application request

driver (DLL)

executes the ODBC calls received by the Driver Manager

the application sees the driver manager and the the application sees the driver manager and the

drivers as a single component

data source

DBMS, network, OS hosting the DBMS

(3)

Driver in single-tier configuration

the same node hosts:

applicationpp driver manager driver

software for data access (data access software) data storage

t ibl fi ti

two possible configurations:

stand alone network

ODBC

Driver in single-tier configuration

ODBC

(4)

Driver in multiple-tier configuration

the client node hosts:

applicationpp driver manager driver

the server node hosts:

software for data access (data access software) d t t

data storage

ODBC

Driver in multiple-tier configuration

(5)

Driver multiple-tier with gateway

features an additional gateway node

forward the requests to the data access softwareq

ODBC

OLE DB

technology (MDAC) included in Microsoft OSes

evolution of ODBC

COM (Component Object Model) interface to provide applications with uniform access to data from different sources

relational DBMSes

not relational DBMSes (new)( )

file system, mail system, spreadsheets, directory, …

not limited to SQL

(6)

OLE DB

consumer

applications accessing data through the OLE DB pp g g interface

provider

intermediary providing the data implements OLE DB methods

not all providers have the same capabilities; it not all providers have the same capabilities; it

depends on which OLE DB methods they implement

OLE DB consumer

OLE DB

li ti t l application or tool OLE DB interface OLE DB OLE DB

ODBC File Native

(7)

application or tool OLE DB interface OLE DB interface OLE.ODBC interface ODBC Driver Manager OLE DB interface ODBC provider

simple provider native provider Text File database database OLE DB ODBC Driver

OLE DB provider example

Jet 4.0 is the Access engine

native OLE DB providerp

J t 4 0 application OLE DB native provider OLE DB Jet 4.0 database proprietary interface

(8)

ADO (ActiveX Data Object)

middleware with object oriented interface

evolution of DAO

makes the OLE DB interface accessible to other languages than C++

it is an application level wrapper for OLE DB can be used with different languages by

complied user programs (VisualBasic)

macros inside applications like MS-Office (VisualBasic for Application - VBA)

WSH: script console (Jscript, VBscript)

ASP pages in IIS (Jscript, VBscript) ADO

ADO application or tool OLE DB interface OLE DB interface OLE.ODBC interface ODBC driver manager OLE DB inteface text ADO ODBC provider

simple provider native provider

database

ODBC driver

text file

(9)

ADO: object model

Connection Recordset Fi ld Fields Field Command Parameters Parameter Properties Property Properties Property Errors Error

ADO objects

Connection object

represents a connection to a specific data sourcep p

Command object

used to define a specific command, like a SQL query, to a data source

Recordset object

represents the set of records belonging to a whole represents the set of records belonging to a whole

table or resulting by a query

composed by rows (record) and columns (field)

(10)

ADO objects

Record object

represents a data rowp

corresponds to a single record of a recordset

Field object

represents a data column

corresponds to a column of a recordset

P t bj t

Parameter object

represents a parameter associated with command object (e.g. parameters for a stored procedure)

ADO

ADO objects & collections

Error object

details on an error occurred during a failed g operation with the provider

Fields collection

contains all the Field objects of a recordset

Errors collection

contains all the Error objects generated during a contains all the Error objects generated during a

failed operation with the provider

Parameters collection

contains all the Parameter objects of a command ADO

(11)

Connection

Connection Open Provider ConnectionString Execute Close Mode methods properties ADO

Connection

Mode DB opening mode p g read-only is default

for read-write con.Mode = adModeReadWrite

Execute (stringSQL)

returns a read-only RS with forward-only cursor

if th ti d d O i t d

if other properties are needed, use rs.Open instead of con.Execute

(12)

Recordset

AddNew Delete Open Close proprietà Recordset EOF Index Delete MoveFirst MoveNext MoveLast metodi p p MovePrevious Update ADO

Recordset: open method

 opening a recordset

ObjRS O ( S A ti C ti

 Source: SQL command, table name, stored procedure

 ActiveConnection: an object of type Connection or

ObjRS.Open ( Source, ActiveConnection, CursorType, LockType, option );

 ActiveConnection: an object of type Connection, or a connection string

 CursorType: type of cursor on the resulting RS

 Locktype: type of lock on data

(13)

CursorType

adOpenUnspecified Unspecified type of cursor. adOpenForwardOnly

(default)

This improves performance when only one pass through a RS is needed. (default) one pass through a RS is needed.

adOpenKeyset

Like a dynamic cursor, except that you can't see records that other users add, although records that other users delete are inaccessible from your RS. Data changes by other users are still visible.

dO D i

Additions, changes, and deletions by

th i ibl d ll t f

adOpenDynamic other users are visible, and all types of movement through the RS are allowed. adOpenStatic

A static copy of a set of records that you can use to find data or generate reports. Additions, changes, or deletions by other users are not visible.

Recordset: AddNew method

 adding a record

// if the table is not accessible ... objRS.Open("users", conString,

adOpenDynamic, adLockPessimistic); // adding the record

objRS.AddNew();

objRS("Name") = "Alberto"; objRS("Surname") = "Rossi"; objRS.Update();

(14)

ADO operation model (query)

1. create an ADO connection object 2. open the connection towards a DBMSp 3. create an ADO recordset object

4. open the recordset

5. manipulate data inside the recorset 6. close the recordset

l th ti

7. close the connection 8. remove the objects

ADO

ADO: ASP/JScript example (query)

creating and opening the connection

% @LANGUAGE "JS i t" % <% @LANGUAGE="JScript" %>  <% var con; var conString; con = Server.CreateObject("ADODB.Connection"); conString = "Provider=Microsoft.Jet.OLEDB.4.0; " ADO g

+ "Data Source= c:\\webdata\\mydb.mdb "; con.Open(conString);

(15)

ADO: ASP/JScript example (query)

creating and opening the recordset

<%

var rst;

var sSQLString;

rst = Server.CreateObject("ADODB.Recordset"); sSQLString = "Select * from Customers"; rst = con.Execute(sSQLString);

%>

ADO

ADO: ASP/JScript example (query)

extracting data from the recordset rst

<% while (!rst.EOF) { Response.write(rst("Name") + "<BR>"); rst.MoveNext( ); } } %> ADO

(16)

ADO: ASP/JScript example (query)

closing the recordset & connection

<% rst.Close( ); con.Close( ); rst = null; con = null; %> ADO

DB access with relative path

with the method Server.MapPath

<% @LANGUAGE="JScript" %> <%

var con;

var conString;

con = Server.CreateObject("ADODB.Connection"); conString = "Provider = Microsoft.Jet.OLEDB.4.0; " + "Data Source = " + Server.MapPath("./mydb.mdb"); con.Open(conString);

(17)

ADO: example of update via SQL

creating and opening the connection

<% @LANGUAGE="JScript" %>  <% var con; var conString; con = Server.CreateObject("ADODB.Connection"); St i "P id Mi ft J t OLEDB 4 0 " ADO conString = "Provider=Microsoft.Jet.OLEDB.4.0; " + "Data Source= c:\\webdata\\mydb.mdb "; con.Open(conString);

%>

ADO: example of update via SQL

adding records and closing the connection

<% <%

var sSQLString;

sSQLString =

"INSERT INTO tBooks (Title, Description, Category) VALUES ('DB Design','How to design a database', 3);" con.Execute(sSQLString);

ADO

con.Close( ); con = null; %>

(18)

Reserved keywords

errors may occur if table or column names

corresponds with keywords of ASP or the scripting

l ( l )

language (e.g. value)

to avoid these errors, enclose the name within square brackets (e.g. [ value ] )