ClientServer

1

• Client/Server

2
  Historically

• Single CPU, time-shared computing
• DB can be accessed remotely via dumb terminals
• Data and DBMS reside at a single site

3
 Client-Server Architectures

• Specialized servers - file server, printer
  server, etc.
• DBMS stored on specific server machine, available
  to multiple clients
• Client machines need client SW

4
Client-Server Architectures

• Client
• formulates SQL queries
• provides interface and programming language
  interface
• Precompiles/compiles functions
• sends query to server for processing
• Server
• processes query
• sends result back to client

5
Client/Server architecture

• Since SQL is standard - different SQL servers can
  accept SQL commands
• Server - back-end machine or DB engine
• Client - front-end machine or application program
  (AP)
• Some machines run either or both client and
  server
• DB2, ORACLE, MS SQL Server

6
CS457 Oracle

• In our installation of Oracle, Oracle server
  software is stored on machine XE
• Client machines, (e.g. in lab, your laptop) need
  Oracle client software
• Must set place information into a file tnsnames
  so can find server (XE)

7
Why client/server?

• Cost effective - use lots of cheap PCs to
  interact with server
• Optimize use of HW and SW
• but
• DB applications can be complex
• Need client SW, server SW, communication SW
• Need lots of storage
• Fat clients vs. thin clients - 2-tier vs. m-tier

8
Open Database Connectivity (ODBC)

• An open standard application programming (API)
  for accessing a database
• Defines a set of function calls, error codes and
  data types for developing database independent
  applications
• Microsoft is the main proponent

9
ODBC drivers

• Open Database Connectivity ODBC
• Used to connect front-end SW to backend DB
• Defines a standard interface between the
  front-end and
• the back-end so you can be flexible in
  your choice of DB

10
 Drivers   

• DB applications call functions in ODBC interface,
  that are implemented in DB specific modules
  called drivers
• Driver isolates application from DB specific
  calls          
• Driver maps DB to ODBC data types
• Only need to add a new driver to access a new
  DBMS (no recompile, etc. needed)

11
Drivers

• You can read and update any DB which conforms to
  the ODBC standard once you have the appropriate
  driver
• ODBC standard used by both the client and the
  server
• if client conforms to ODBC standard, maps to ODBC
• Drivers are readily available (e.g. in Access, at
  ORACLE's web site)

12
ODBC  

• ODBC is a specification for a DB API independent
  of any DBMS or OS
• Used to describe SQL, SQL is API to DBMS
• CLI (call level interface) based on
  specifications from X/Open and ISO/IEC for DB
  API's
• Anybody can write ODBC applications and drivers -
  SDK from MS

13
Drivers allow you to

• Access multiple DBMS using same source code
  without recompiling
• Access multiple DBMS simultaneously
• Can even access an OODB using ODBC
• Driver maps objects, etc. to relations, etc.

14
Driver Tasks

• Connect/disconnect to data source
• Check for function errors
• Initiate transactions
• Submit SQL statements to data source for
  execution
• Send data to and retrieve data from data source

15
ODBC Architecture on Client

• Application
• Driver Manager
• Driver1 Driver 2 ... Driver n
• data data data
•             source source source
•            

16

• Client needs an ODBC driver for the DB you are
  going to connect to (ODBC data source)
• Client needs Driver Manager (support files)
• Driver manager - determines which driver to load
  based on data source

17
Restrictions

• Any one driver imposes its own restrictions 
• (e.g. MS ODBC limits what can be done)

18
ODBC Applications

• Three fundamental objects in an ODBC program
• Environments
• Connections
• Statements

19
ODBC Application Structure

• Initialization
• Allocation Environments SQLAllocEnv()
• Allocation Connection SQLAllocConnect()
• Connect SQLConnect() or SQLDriverConnect()

20
ODBC Application Structure (cont)

• Transaction Processing
• Termination
• Disconnect SQLDisconnect()
• Free Connection SQLFreeConnect()
• Free Environment SQLFreeEnv()

21
Sample program

• include ltsqlclil.hgt
• int main ()
• SQLHENV henv
• SQLHDBC hdbc
• SQLRETURN rc
• SQLCHAR server20 XE
• SQLCHAR uid20 sv9999
• SQLCHAR pwd20 pw
• // initialization
• SQLAllocEnv(henv)
• SQLAllocConnect(henv, hdbc)
• rc SQLConnect(hdbc, server, SQL_NTS, uid,
  SQP_NTS, pwd, SQL_NTS)

22

• if (rc ! SQL_SUCCESS)
• cout ltlt Connection failure ltlt endl
• return SQL_ERROR
• // Perform desired task
• // Termination
• SQLDisconnect(hdbc)
• SQLFreeConnect(hdbc)
• SQLFreeEnv(henv)
• return SQL_SUCCESS
• Complete program