第15章 数据库应用结构与数据访问接口_2

ODBC
ODBC（ Open DataBase Connectivity,开放的数据 库互连）is a call level interface that is database independent. Related to SQL/CLI, part of SQL:1999 Software architecture with driver manager and drivers. ODBC passes SQL statements to the underlying DBMS. Can be interfaced to any DBMS that has a ODBC driver Not object oriented. Low-level: application must specifically allocate and deallocate storage
Non-interactive SQL: SQL statements are included in an application program written in a host language, like C, Java, COBOL
Application Program
Host language: A conventional language (e.g., C, language Java) that supplies control structures, computational capabilities, interaction with physical devices SQL: supplies ability to interact with database. Using the facilities of both: the application program can act as an intermediary between the user at a terminal and the DBMS
Call-Level Interfaces
A more modern approach to the hostlanguage/SQL connection is a call-level interface, in which the C (or other language) program creates SQL statements as character strings and passes them to functions of the library. Major approaches: SQL/CLI (standard of ODBC = open database connectivity) , OLEDB (Object Linked and Embedded DataBase), ADO (Active Data Object), and JDBC (Java DataBase Connectivity).
e.g., EXEC SQL SQL_statement
Precompiler scans program and translates SQL statements into calls to host language library procedures that communicate with DBMS Host language compiler then compiles program
Cursors
cursor
SELECT
application
Result set (or pointers to it) Base table
Cursors
EXEC SQL DECLARE GetEnroll INSENSITIVE CURSOR FOR SELECT T.StudId, T.Grade FROM Transcript T WHERE T.CrsCode = :crscode AND T.Semester = ‘S2000’; ……… Reference resolved at compile time, EXEC SQL OPEN GetEnroll; Value substituted at OPEN time if ( !strcmp ( SQLSTATE, “00000”)) {... fail exit... }; ………. EXEC SQL FETCH GetEnroll INTO :studid, :grade; while ( SQLSTATE = “00000”) { … process the returned row... EXEC SQL FETCH GetEnroll INTO :studid, :grade; } if ( !strcmp ( SQLSTATE, “02000”)) {... fail exit... }; ………. EXEC SQL CLOSE GetEnroll;
EXEC SQL CONNECT TO :dbserver; if ( ! strcmp (SQLSTATE, “00000”) ) exit (1); ….. EXEC SQL DELETE FROM Transcript T WHERE T.StudId = :studid AND T.Semester = ‘S2000’ AND T.CrsCode = :crscode; if (! strcmp (SQLSTATE, “00000”) ) EXEC SQL ROLLBACK; else { EXEC SQL UPDATE Course C SET C.Numenrolled = C.Numenrolled – 1 WHERE C.CrsCode = :crscode; if (! strcmp (SQLSTATE, “00000”) ) EXEC SQL ROLLBACK; else EXEC SQL COMMIT; }
Cursors (游标)
Result set – set of rows produced by a SELECT statement Cursor – pointer to a row in the result set. Cursor operations:
Declaration Open – execute SELECT to determine result set and initialize pointer Fetch – advance pointer and retrieve next row Close – deallocate cursor
Similar to dynamic SQL
Application uses string variables as arguments of library routines that communicate with DBMS
e.g. executeQuery(“SQL query statement”)
Introducing SQL Into the Application
SQL statements can be incorporated into an application program in two different ways:
Statement Level Interface (SLI): Application program is a mixture of host language statements and SQL statements and directives (embedded SQL). Call Level Interface (CLI): Application program is written entirely in host language
Embedded SQL
EXEC SQL SELECT C.NumEnrolled INTO :num_enrolled FROM Course C WHERE C.CrsCode = :crs_code; if ( !strcmp (SQLSTATE, “00000”) ) { printf ( “statement failed” ) };
Call Level Interface
Application program written entirely in host language (no precompiler)
Examples: JDBC, ODBC,OLEDB,ADO,
SQL statements are values of string variables constructed at run time using host language
Chapter 15 Database Interface
Chapter 15 - Objectives
Introduce to the methods of access database in applications:
Embedded SQL Database Interface
ODBC OLE DB and ADO JDBC
SQL statements are values of string variables that are passed as arguments to host language (library) procedures
Statement Level Interface
SQL statements and directives (embedded SQL) in the application have a special syntax that sets them off from host language constructs
Out ห้องสมุดไป่ตู้arameter
In parameter
Connections
To connect to an SQL database, use a connect statement