Universal Connectivity

1
Universal Connectivity

• Using database Application Programming Interfaces
  (APIs)

2
API Overview

• What is an API?

3
Power of APIs

• Various languages have their own API
• Each API provides universal connectivity to a
  wide variety of databases
• APIs simplify connections to databases by hiding
  complex details

4
Types of APIs

• Pre-compiled
• Embedded SQL using C and ProC
• Limited to Oracle
• Object based
• Each language provides objects to handle
  connectivity to various databases

5
Examples of Object-based APIs

• Java uses JDBC
• Perl uses DBI
• Visual Basic uses ADO, DAO and ODBC

6
Embedded SQL using ProC

• ProC connects C to Oracle
• SQL statements entered directly into C using
  EXEC SQL
• C program saved as a .pc which ProC
  pre-compiles into a C program

7
Setting up C for Pre-compilation

• Must include ltsqlcpr.hgt ltsqlca.hgt
• Host variable for records in Oracle
• Routines to connect and disconnect from Oracle
• Commands for committing the updates to Oracle

8
Sample Query in C

• EXEC SQL declare student_cur cursor for select
  from students
• EXEC SQL fetch student_cur into srec

9
Details of SQL Query

• Start with EXEC SQL
• Declare the cursor
• Form the SQL query
• Put results into struct srec

10
What Is JDBC?

• A Java API for executing SQL statements
• A mechanism for Java to talk to different
  databases
• JDBC extends what can be done in Java

11
What Does JDBC Do?

• Establish a connection with a database
• Send SQL statements
• Process the results

12
Being Both a Low-Level API and a Base for
Higher-level APIs

• Invoke SQL commands directly
• user-friendly high level API is finally
  translated into JDBC
• Two high-level APIs
• 1. Embedded SQL for Java
• 2. Direct mapping of relations to Java classes

13
JDBC versus ODBC and other APIs

• JDBC is built on ODBC
• Retains the basic design features of ODBC (based
  on X/Open SQL CLI)
• Why JDBC?
• Difference JDBC is easy to use!
• ODBC uses C interface
• Literal translation not desirable
• ODBC is hard to learn

14
JavaSoft Framework

• The JDBC driver manager
• The JDBC driver test suite
• The JDBC-ODBC bridge

15
JDBC Architecture
Java Application
JDBC API
JDBC Driver Manager
JDBC-ODBC Bridge Driver
JDBC-Net Driver
Driver A
Driver B

ODBC and DB Drivers
JDBC Drivers
JDBC Middleware protocol
Proprietary database access protocols
16
JDBC Driver Types

• JDBC-ODBC bridge plus ODBC driver
• Native-API partly-Java Driver
• JDBC-Net pure Java driver
• Native-protocol pure Java driver

17
JDBC Basics

• Import the necessary class
• Load the JDBC driver
• Allocate Connection and Statement objects
• Execute a query using the Statement object
• Retrieve data from the returned ResultSet object

18
JDBC Component Interaction
DriverManager
Statement
ResultSet
creates
creates
access
Database
19
Why Perl DBI

• DBI defines a set of functions, variables and
  conventions that provide a consistent database
  interface independent of the actual database
  being used.
• An interface , glue, between application and
  one or more databases drivers.
• Users are sheltered from the complexity of
  operating different databases.
• The powerful text processing ability of Perl is
  fully utilized.

20
Architecture of a DBI Application and data flow

• 
  
  

Scope of DBI
Perl Script Using DBI API Methods
S W I T C H
A P I
Oracle driver
Oracle Eng.
Sybase driver
Sybase Eng.
Other drivers
21
General Interface Rules

• Every session is processed by an specified handle
  returned from a connect method.
• The handle obj. is used to invoke database
  related methods.
• No concept of current session.
• Data is transferred in string format, no data
  loss. Binary data is also supported.
• Non-sequential record reads are not supported.
• No support for positioned updates and deletes.

22
DBI handles

• Handles hierarchal objects to interact with
  DBs.
• Driver handles
• created when the driver is loaded and
  initialized by the DBI , representing the loaded
  drivers.
• Database Handles Database handles encapsulate a
  single connection to a particular database.
• Statement Handles database interaction and
  manipulation,they encapsulate individual SQL
  statements to be executed within the database.
  Children of Database handle, no limit on the of
  children dbh

23
Hierarchal handles
24
DBI class and functionalities

• Connect
• dbh DBI-gtconnect(data_source, username,
  password)
• dbh DBI-gtconnect(data_source,
  username, password, \attr)
• DB handle methods
• prepare
• sth dbh-gtprepare(statement) die
  dbh-gterrstr
• sth dbh-gtprepare(statement, \attr) die
  dbh-gterrstr

25
DBI class and functionalities (continue)

• commit rc dbh-gtcommit die dbh-gterrstr
• rollback rc dbh-gtrollback die
  dbh-gterrstr
• AutoCommit()
• disconnect rc dbh-gtdisconnect warn
  dbh-gterrstr
• Statement handle
• sth-gtexecute()

26
Other useful utilities

• DBI Dynamic Attributes error handlers
• rv h-gterr str h-gterrstr str
  h-gtstate h-gttrace(trace_level)
• neat, neat-list
• Do prepare and execute
• bind_param

27
ADO - Introduction

• What is it ?
• ADO - ActiveX Data Object
• application level interface to OLE DB.
• accesses different data sources
• used as an interface for 1 to n tier
  client/server and web-based data development
• ADO objects can be used in any language that
  supports COM and automation (i.e. Visual Basic,
  Visual C, Visual Fox Pro, VBScript, etc.).
• allows the program to access a database (i.e.
  Oracle, Access)
• can perform operations on the data source (edit,
  update, access, delete, and insert).

28
ADO -Programming Model
29
ADO - Connection Object

• Connection Object
• provides a connection to a data source
• can apply a number of operation, queries and
  transactions
• uses OPEN command to establish a link to the
  data source and CLOSE to end the connection
• uses a connection string for all necessary
  information (service provider, name of database,
  user name and password)
• conn "UIDklassenPWDB00124606DRIVERMicrosof
  t ODBC for Oracle" "SERVERCourses
• uses the Cursor service for OLE DB that enables
  batch updates for transaction management using
  the methods
• BeginTrans, CommitTrans, RollbackTrans

30
ADO - Command Object

• Command Object
• represents statements or queries that can process
  a data source.
• There are two main types of Commands
• Simple SQL Statements
• mdkcmd.CommandText "update sname from student
  where sno " Text3.Text " commit
• Calls to store procedures in the data source.
• mdkcmd.CommandText "unregister(" Text1.Text
  "," Text2.Text ")
• returns tuple(s) and handles parameters
• the EXECUTE command is used to execute a
  command or can create a RecordSet object and
  associated it with the command object to capture
  the tuples returned from an operation

31
ADO - RecordSet Object

• RecordSet Object
• provides the user with the ability to insert,
  update, delete and scroll through records
• updates can be done automatically or in batch
  form
• when created, a cursor is automatically created.
• An example of using a recordset with a cursor
  that holds all the records selected
• Private Sub getCoursesForStd() ' Lists the
  courses a student is taking
• Dim conn As String
• conn "UIDklassenPWDB00124606DRIVERMicro
  soft ODBC for Oracle" _
• "SERVERCourses"
• With Adodc3
• .ConnectionString conn
• .RecordSource "select cno, sec from
  register where sno " Text3.Text
• End With
• Adodc3.Refresh

32
ADO - Objects

• Parameter Object
• represents a parameter of a command
• provides additional information and data for a
  command object
• Field Object
• columns in a RecordSet
• accessible through the RecordSet object

33
ADO - Objects

• Error Object
• enables the user to retrieve provider errors
• can be generated by a command call, connection
  call or RecordSet object but only the connection
  object can retrieve errors
• it is optional.
• Property Object
• There are two types of properties
• Built in
• Dynamic

34
Database API Assignment Group 2

• For the website with these slides, download codes
  and assignment please go to
• http//borg.cs.dal.ca/wzheng