Clientserver database systems and ODBC l l l

Client-server database systems and ODBC l l l Client-server architecture and components More on reliability and security ODBC standard

Database processing functions

The client-server architecture The network “cut” is between applications and the DBMS l Database processing is on the server l

DBMS Driver Receive processing requests from applications l Format requests and deliver to DBMS l Receive responses from DBMS l Format responses for application l Communication layer on client and server to exchange messages between DBMS and driver l

Roles of client and server l Client l » Manage the user interface » Enforce business rules » Process application logic » Generate database requests (SQL) » Transmit database requests to server » Receive results from server » Format results Server » Accept database request from clients » Process database requests » Format results and transmit to client » Enforce business rules » Perform integrity checking » Maintain database overhead data » Provide concurrent access control » Provide recovery and security services

Advantages & disadvantages l Pro » Applications use client CPUs in parallel – More powerful applications » Network traffic is reduced l Con » Concurrency control » Multiple client OS’s

Multithreading

Client requests l Native library calls (API) » DBMS-specific l Embedded SQL » Static SQL – SQL structure fixed at compile time – Faster » Dynamic SQL – SQL structure determined at run time – More flexible

Business rules: client-enforcement

Business rules: server-enforcement

Enforcement of business rules l Client » Triggers » Duplication » What about using DML directly? l Server » All in one place » What if DBMS cannot enforce business rules?

Concurrency control l Pessimistic locking » Assume things will go wrong » Prevent problems all the time l Optimistic locking » Assume things will go well » If problem, do it over

Pessimistic locking: same page, different records

Optimistic locking: same page, different records What if same record?

Concurrency control, take 2 l Pessimistic versus optimistic locking, which is better? » Depends on application. . . » What is the common case? – If conflict is frequent, use pessimistic! – If conflict is rare, use optimistic!

ODBC Open Data. Base Connectivity l Industry-standard interface between client applications and server DBMS products l The idea is to write DBMS-independent applications, as long as they conform to the ODBC standard l To conform or not to conform? l

ODBC architecture

ODBC components l On the server: » Data source(s) - database, DBMS, OS, network platform l On the client: » DBMS drivers - one per data source – Receive ODBC request – Process if not standard SQL – Submit to specific data source » Driver manager - one per client – Receive ODBC request from application – Load appropriate driver and forward request

ODBC drivers l Single-tier: process both ODBC calls and SQL statements l Multiple-tier: process ODBC calls and pass SQL statements to data source

ODBC conformance levels l 3 levels of driver API conformance » Core (connect, commit/rollback, error. . . ) » Level 1 (partial results, catalog, driver gestalt. . . ) » Level 2 (browse, scrollable cursors. . . ) l 3 levels of SQL conformance » Minimum (simple select, create, drop, insert, update, delete, simple expressions. . . ) » Core (indices, views, grant, revoke, subqueries, aggregate functions. . . ) » Extended (outer joins, cursors, scalar functions, procedures. . . )