Chapter 3: SQL（2）

Embedded SQL, ODBC and JDBC Application Architectures ODBC OLE DB、ADO JDBC

Embedded SQL

Embedded SQL The SQL standard defines embeddings of SQL in a variety of programming languages such as Pascal, PL/I, Fortran, C, and Cobol. A language to which SQL queries are embedded is referred to as a host language(宿主语言), and the SQL structures permitted in the host language comprise embedded SQL（嵌入式）. EXEC SQL statement is used to identify embedded SQL request to the preprocessor EXEC SQL <embedded SQL statement > END-EXEC Note: this varies by language. E.g. the Java embedding uses # SQL { …. } ;

嵌入SQL语句 负责操纵数据库 负责控制程序流程 将SQL嵌入到高级语言中混合编程，程序中会含有两种不同计算模型的语句 SQL语句 说明性语句 嵌入SQL语句 数据定义 可执行语句 数据控制 数据操纵 允许出现可执行的高级语言语句的地方，都可以写可执行SQL语句 允许出现说明语句的地方，都可以写说明性SQL语句 将SQL嵌入到高级语言中混合编程，程序中会含有两种不同计算模型的语句 SQL语句 描述性的面向集合的语句 负责操纵数据库 高级语言语句 过程性的面向记录的语句 负责控制程序流程

工作单元之间的通信方式 SQL通信区 主变量 游标（解决集合性操作语言与过程性操作语言的不匹配） 向主语言传递SQL语句的执行状态信息 主语言能够据此控制程序流程 主变量 作用 主语言向SQL语句提供参数 将SQL语句查询数据库的结果交主语言进一步处理 使用方法 这些变量由宿主语言的程序定义，并用SQL的DECLARE语句说明。 引用时，这些变量前必须加冒号“：”作为前缀标识，以示与数据库中变量有区别。 游标（解决集合性操作语言与过程性操作语言的不匹配） 游标定义语句（DECLARE） 游标打开语句（OPEN） 游标推进语句( FETCH ) 游标关闭语句（CLOSE）

SQL通信区 描述系统当前工作状态 描述运行环境 SQLCA： SQL Communication Area SQLCA是一个数据结构 SQL语句执行后，DBMS反馈给应用程序信息 描述系统当前工作状态 描述运行环境 这些信息将送到SQL通信区SQLCA中 应用程序从SQLCA中取出这些状态信息，据此决定接下来执行的语句

SQLCA的使用方法 定义SQLCA 用EXEC SQL INCLUDE SQLCA加以定义 使用SQLCA SQLCA中有一个存放每次执行SQL语句后返回代码的变量SQLCODE 如果SQLCODE等于预定义的常量SUCCESS，则表示SQL语句成功，否则表示出错 应用程序每执行完一条SQL 语句之后都应该测试一下SQLCODE的值，以了解该SQL语句执行情况并做相应处理

主变量 什么是主变量 主变量的用途 将数据库中的数据修改为指定值 指定执行的操作 指定WHERE子句或HAVING子句中的条件 嵌入式SQL语句中可以使用主语言的程序变量来输入或输出数据 在SQL语句中使用的主语言程序变量简称为主变量（Host Variable） 主变量的用途 输入主变量 指定向数据库中插入的数据 将数据库中的数据修改为指定值 指定执行的操作 指定WHERE子句或HAVING子句中的条件 输出主变量 获取SQL语句的结果数据 获取SQL语句的执行状态

主变量（续） 在SQL语句中使用主变量的方法 BEGIN DECLARE SECTION ......... 1) 说明主变量 BEGIN DECLARE SECTION ......... ......... (说明主变量) END DECLARE SECTION 2) 使用主变量 说明之后的主变量可以在SQL语句中任何一个能够使用表达式的地方出现 为了与数据库对象名（表名、视图名、列名等）区别，SQL语句中的主变量名前要加冒号（:）作为标志 在SQL语句之外(主语言语句中)使用主变量的方法 可以直接引用，不必加冒号

Example Query From within a host language, find the names and cities of customers with more than the variable amount dollars in some account. Specify the query in SQL and declare a cursor for it EXEC SQL BEGIN DECLARE SECTION； Int amount； EXEC SQL END DECLARE SECTION； EXEC SQL declare c cursor for select customer-name, customer-city from depositor, customer, account where depositor.customer-name = customer.customer-name and depositor account-number = account.account-number and account.balance > :amount END-EXEC

游标 主语言是面向记录的，一组主变量一次只能存放一条记录 什么是游标 仅使用主变量并不能完全满足SQL语句向应用程序输出数据的要求 每个游标区都有一个名字 用户可以用SQL语句逐一从游标中获取记录，并赋给主变量，交由主语言进一步处理

Embedded SQL (Cont.) （1）定义 EXEC SQL DECLARE <游标名> CURSOR FOR <SELECT语句>; 例如： EXEC SQL DECLARE C1 CURSOR FOR SELECT tit_id, tit, roy FROM titles; （2）打开The open statement causes the query to be evaluated EXEC SQL open c END-EXEC （3）逐一取出记录The fetch statement causes the values of one tuple in the query result to be placed on host language variables. EXEC SQL fetch c into :cn, :cc END-EXEC Repeated calls to fetch get successive tuples in the query result A variable called SQLSTATE in the SQL communication area (SQLCA) gets set to ‘02000’ to indicate no more data is available （4）关闭The close statement causes the database system to delete the temporary relation that holds the result of the query. EXEC SQL close c END-EXEC Note: above details vary with language.

............ EXEC SQL INCLUDE SQLCA; /* (1) 定义SQL通信区 */ EXEC SQL BEGIN DECLARE SECTION; /* (2) 说明主变量 */ CHAR title_id(7); CHAR title(81); INT royalty; EXEC SQL END DECLARE SECTION; main() { EXEC SQL DECLARE C1 CURSOR FOR SELECT tit_id, tit, roy FROM titles; /* (3) 游标操作（定义游标）*/ /* 从titles表中查询 tit_id, tit, roy */ EXEC SQL OPEN C1; /* (4) 游标操作（打开游标）*/ for(;;) { EXEC SQL FETCH C1 INTO :title_id, :title, :royalty; /* (5) 游标操作（将当前数据放入主变量并推进游标指针）*/ if (sqlca.sqlcode <> SUCCESS) /* (6) 利用SQLCA中的状态信息决定何时退出循环 */ break; printf("Title ID: %s, Royalty: %d", :title_id, :royalty); printf("Title: %s", :title); /* 打印查询结果 */ } EXEC SQL CLOSE C1; /* (7) 游标操作（关闭游标）*/

Updates Through Cursors 步骤 DECLARE EXEC SQL DECLARE <游标名> CURSOR FOR <SELECT语句> FOR UPDATE OF <列名>; FOR <SELECT语句> FOR UPDATE (2) OPEN (3) FETCH 推进游标指针，并把当前记录从缓冲区中取出来送至主变量 (4) 检查该记录是否是要修改或删除的记录，是则处理之 <UPDATE语句> WHERE CURRENT OF <游标名> <DELETE语句> WHERE CURRENT OF <游标名> (5) 重复第(3)和(4)步，用逐条取出结果集中的行进行判断和处理 (6) CLOSE

例 对某个系（系名由主变量deptname指定）的学生信息，根据用户的要求修改其中某些人的年龄字段。 思路 查询某个系全体学生的信息 然后根据用户的要求修改其中某些记录的年龄字段

例题（续） gets(deptname); /* 为主变量deptname赋值 */ EXEC SQL INCLUDE SQLCA; EXEC SQL BEGIN DECLARE SECTION; ...... /* 说明主变量 deptname,HSno,HSname,HSsex,HSage,NEWAge等*/ EXEC SQL END DECLARE SECTION; gets(deptname); /* 为主变量deptname赋值 */ EXEC SQL DECLARE SX CURSOR FOR SELECT Sno, Sname, Ssex, Sage FROM Student WHERE SDept=:deptname 　 FOR UPDATE OF Sage; /* 说明游标 */ EXEC SQL OPEN SX /* 打开游标 */ WHILE(1) {/* 用循环结构逐条处理结果集中的记录 */ EXEC SQL FETCH SX INTO :HSno, :HSname, :HSsex, :HSage; /* 将游标指针向前推进一行，然后从结果集 中取当前行，送相应主变量*/ if (sqlca.sqlcode <> SUCCESS) break; /* 若所有查询结果均已处理完或 出现SQL语句错误，则退出循环 */ printf("%s, %s, %s, %d", Sno, Sname, Ssex, Sage); /* 显示该记录 */ printf("UPDATE AGE ? "); /* 问用户是否要修改 */ scanf("%c",&yn);

例题（续） if (yn='y' or yn='Y') /* 需要修改 */ { printf("INPUT NEW AGE: "); scanf("%d",&NEWAge); /* 输入新的年龄值 */ EXEC SQL UPDATE Student SET Sage=:NEWAge WHERE CURRENT OF SX; /* 修改当前记录的年龄字段 */ }; ...... EXEC SQL CLOSE SX; /* 关闭游标 */

例题（续） 例4 对某个系的学生信息，根据用户的要求删除其中某些人的记录。 EXEC SQL END DECLARE SECTION; 例4 对某个系的学生信息，根据用户的要求删除其中某些人的记录。 EXEC SQL INCLUDE SQLCA; EXEC SQL BEGIN DECLARE SECTION; /* 说明主变量 deptname,HSno,HSname,HSsex,HSage等*/ EXEC SQL END DECLARE SECTION; gets(deptname); /* 为主变量deptname赋值 */ EXEC SQL DECLARE SX CURSOR FOR SELECT Sno, Sname, Ssex, Sage FROM Student WHERE SDept=:deptname FOR UPDATE; /* 说明游标 */ EXEC SQL OPEN SX /* 打开游标 */

例题（续） WHILE(1){ /* 用循环结构逐条处理结果集中的记录 */ EXEC SQL FETCH SX INTO :HSno, :HSname, :HSsex, :HSage; /* 将游标指针向前推进一行，然后从结 果集中取当前行，送相应主变量*/ if (sqlca.sqlcode <> SUCCESS) break; 　 /* 若所有查询结果均已处理完或出现SQL语句错误，则退出循环 */ printf("%s, %s, %s, %d", Sno, Sname, Ssex, Sage); /* 显示该记录 */ printf("DELETE ? "); /* 问用户是否要删除 */ scanf("%c",&yn); if (yn='y' or yn='Y') /* 需要删除 */ EXEC SQL DELETE FROM Student WHERE CURRENT OF SX; /* 删除当前记录 */ .}; EXEC SQL CLOSE SX; /* 关闭游标 */

Dynamic SQL Allows programs to construct and submit SQL queries at run time. Example of the use of dynamic SQL from within a C program. char * sqlprog = “update account set balance = balance * 1.05 where account-number = ?” EXEC SQL prepare dynprog from :sqlprog; char account [10] = “A-101”; EXEC SQL execute dynprog using :account; The dynamic SQL program contains a ?, which is a place holder for a value that is provided when the SQL program is executed.

Application Architectures

Application Architectures Applications can be built using one of two architectures Two tier model Application program running at user site directly uses JDBC/ODBC to communicate with the database Three tier model Users/programs running at user sites communicate with an application server. The application server in turn communicates with the database Application program DB JDBC/ODBC Users/programs DB JDBC/ODBC application server

Two-tier Model E.g. Java code runs at client site and uses JDBC to communicate with the backend server Problems: Security: passwords available at client site, all database operation possible More code shipped to client Not appropriate across organizations, or in large ones like universities

Three Tier Model CGI Program Database Server Application/HTTP Server JDBC Servlets HTTP/Application Specific Protocol Network Client Client Client

Three-tier Model (Cont.) E.g. Web client + Java Servlet using JDBC to talk with database server Client sends request over http or application-specific protocol Application or Web server receives request Request handled by CGI program or servlets Security handled by application at server Better security

ODBC

ODBC Open DataBase Connectivity(ODBC) standard standard for application program to communicate with a database server. ODBC是一个调用层的接口 ODBC定义了标准的SQL语法 ODBC提供一个驱动程序管理器来管理并同时访问多个DBMS系统

应用程序的主要功能是：调用ODBC函数，递交SQL语句给DBMS，检索出结果，并进行处理。 驱动程序管理器是一个动态连接库（DLL），用于连接各种DBS的DBMS驱动程序（如Oracle、Foxpro、Sybase等驱动程序），管理应用程序和DBMS驱动程序之间的交互作用（通信）。 应用程序通过调用驱动程序所支持的函数来操纵数据库。驱动程序也是一个动态连接库（DLL）。 单层驱动 多层驱动 两层 三层 数据源

网络环境下基于单层驱动程序的ODBC结构

基于多层驱动程序的ODBC结构(两层结构)

基于网关机制的多层驱动程序的ODBC结构（三层结构）

ODBC (Cont.) ODBC program first allocates an SQL environment, then a database connection handle. Opens database connection using SQLConnect(). Parameters for SQLConnect: connection handle, the server to which to connect the user identifier, password Must also specify types of arguments: SQL_NTS denotes previous argument is a null-terminated string.

… …… □ henv hdbc hstmt 环境句柄 (定义DB应用环境) 连接句柄 (定义一个DB连接) 语句句柄 (定义一个SQL语句)

ODBC Code int ODBCexample() { RETCODE retcode; HENV env; /* environment */ HDBC conn; /* database connection */ HSTMT hstmt /* 语句 */ SQLAllocEnv(&env); SQLAllocConnect(env, &conn); SQLConnect(conn, "aura.bell-labs.com", SQL_NTS, "avi", SQL_NTS, "avipasswd", SQL_NTS); SQLAllocStmt(hdbc.&hstmt) retcode=SQLExecDirect(hstmt,”SELECT * FROM S”,SQL_NTS) …. SQLDisconnect(conn); SQLFreeStmt(hstmt,SQL_NTS) SQLFreeConnect(conn); SQLFreeEnv(env); }

有准备地执行SQL语句的函数（用于动态sql） (1) SQL语句预备函数 其格式如下：SQLPrepare（hstmt，szSqlStr，cbSqlStr）。其中，参数hstmt是一个有效的语句句柄，参数szSqlStr和cbSqlStr分别表示将要执行的SQL语句的字符串及其长度。 (2) SQL语句执行函数 其格式如下：SQLExecute（hstmt）。其中参数hstmt是一个有效的语句句柄。 while（RETCODE_IS_SUCCESSFUL（retcode） ｛ retcode=SQLFetch（hstmt）； /*推进光标*/ if（RETCODE_IS_SUCCESSFUL（retcode） ｛do ｛ rcGetData = SQLGetData（hstmt，1，SQL_C_CHAR， szBuffer，sizeof（szBuffer），&cbValue）；/*获取数据*/ DISPLAY_MEMO（szBuffer，cbValue）；／*显示*／ ｝ while（ rcGetData!=SQL_NO_DATA_FOUND）； ｝

ODBC Code (Cont.) Main body of program char branchname[80]; float balance; int lenOut1, lenOut2; HSTMT stmt; SQLAllocStmt(conn, &stmt); char * sqlquery = "select branch_name, sum (balance) from account group by branch_name"; error = SQLExecDirect(stmt, sqlquery, SQL_NTS); if (error == SQL_SUCCESS) { SQLBindCol(stmt, 1, SQL_C_CHAR, branchname , 80, &lenOut1); SQLBindCol(stmt, 2, SQL_C_FLOAT, &balance, 0 , &lenOut2); while (SQLFetch(stmt) >= SQL_SUCCESS) { printf (" %s %g\n", branchname, balance); } } SQLFreeStmt(stmt, SQL_DROP);

ODBC Code (Cont.) SQLBindCol() binds C language variables to attributes of the query result When a tuple is fetched, its attribute values are automatically stored in corresponding C variables. Arguments（参数） to SQLBindCol() ODBC stmt variable, attribute position in query result The type conversion from SQL to C. The address of the variable. For variable-length types like character arrays, The maximum length of the variable Location to store actual length when a tuple is fetched. Note: A negative value returned for the length field indicates null value Good programming requires checking results of every function call for errors; we have omitted most checks for brevity.

More ODBC Features Prepared Statement Metadata features SQL statement prepared: compiled at the database Can have placeholders（占位符）: E.g. insert into account values(?,?,?) Repeatedly executed with actual values for the placeholders Metadata features finding all the relations in the database and finding the names and types of columns of a query result or a relation in the database. By default, each SQL statement is treated as a separate transaction that is committed automatically. Can turn off automatic commit on a connection SQLSetConnectOption(conn, SQL_AUTOCOMMIT, 0)} transactions must then be committed or rolled back explicitly by SQLTransact(conn, SQL_COMMIT) or SQLTransact(conn, SQL_ROLLBACK)

ODBC Conformance Levels Conformance levels specify subsets of the functionality defined by the standard. Core Level 1 requires support for metadata querying Level 2 requires ability to send and retrieve arrays of parameter values and more detailed catalog information. 可以用SQLGetFunction函数来得到确定驱动程序是否支持某一特定函数 SQL符合性 最小级SQL DDL:CREATE/DROP TABLE 核心级SQL ALTER TABLE,CERETE/DROP INDEX,CREATE/DROP VIEW 扩展级SQL 可以用SQLGetInfo函数来得到SQL兼容性级别和所支持的SQL关键字

ODBC API与SQL CLI之间的协调 Microsoft公司于1991年11月提出了一个关于ODBC的体系结构，并在1992年发布了ODBC 1.0规范，随后以软件开发工具包（SDK）形式出版并发行了这种面向SQL的API。 数据库公司国际财团SQL Access Group（SAG）和标准化组织X／Open在1992年联合出版了一个规范，该规范定义了调用级界面（Call Level Interface），CLI）标准。 1993年，Microsoft公司推出了ODBC 2.0规范，并于1994年出版了ODBC 2.0 SDK。 Microsoft在1998年出版的ODBC 3.0规范中，作了一些修改，目的是更进一步接近SQL标准。 SQL3（即SQL99）的第三部分介绍了SQL／CLI。

OLE DB、ADO

Role of OBDC

Role of OLE DB

OLE DB breaks the features and functions of a DBMS into COM objects, making it easier for vendors to implement portions of functionality This characteristic overcomes a major disadvantage of ODBC With ODBC, a vendor must create an ODBC driver for almost all DBMS features and functions in order to participate in ODBC at all

OLE DB Goals Create object interfaces for DBMS functionality pieces Query, update, transaction management, etc. Increase flexibility Allow data consumers to use only the objects they need Allow data providers to expose pieces of DBMS functionality Providers can deliver functionality in multiple interfaces Interfaces are standardized and extensible Object interface over any type of data Relational and non-relational database, ODBC or native, VSAM and other files, Email, etc. Do not force data to be converted or moved from where it is

组 成 Data Providers 数据提供者 Data Consumers 数据使用者 Service Components 服务组件 组 成 Data Providers 数据提供者       凡是透过OLE DB 将数据提供出来的，就是数据提供者。例如SQL Server 数据库中的数据表，或是附文件名为mdb 的Access 数据库档案等，都是Data Provider。 Data Consumers 数据使用者       凡是使用OLE DB 提供数据的程序或组件，都是OLE DB 的数据使用者。换句话说，凡是使用ADO 的应用程序或网页都是OLE DB 的数据使用者。 Service Components 服务组件       数据服务组件可以执行数据提供者以及数据使用者之间数据传递的工作，数据使用者要向数据提供者要求数据时，是透过OLE DB 服务组件的查询处理器执行查询的工作，而查询到的结果则由指针引擎来管理。

OLE DB Data Providers OLE DB has two types of data providers Tabular data provider exposes data via rowsets Examples: DBMS, spreadsheets, 电子邮件 Service provider is a transformer of data through OLE DB interfaces It is both a consumer and a provider of transformed data Examples: query processors, XML document creator 服务提供者可以认为是数据的转换者。

Role of ADO

ADO ADO (Active Data Objects) characteristics Simple object model for OLE DB data consumers Can be used from VBScript, JScript, Visual Basic, Java, C#, C++ Single Microsoft data access standard Data access objects are the same for all types of OLE DB data

JDBC

JDBC JDBC is a Java API for communicating with database systems supporting SQL JDBC supports a variety of features for querying and updating data, and for retrieving query results JDBC also supports metadata retrieval, such as querying about relations present in the database and the names and types of relation attributes Model for communicating with the database: Open a connection Create a “statement” object Execute queries using the Statement object to send queries and fetch results Exception mechanism to handle errors

JDBC Code public static void JDBCexample(String dbid, String userid, String passwd) { try { Class.forName (“oracle.jdbc.driver.OracleDriver”); /装载合适的驱动程序 Connection conn = DriverManager.getConnection( "jdbc:oracle:thin:@aura.bell-labs.com:2000:bankdb", userid, passwd); Statement stmt = conn.createStatement(); … Do Actual Work …. stmt.close(); conn.close(); } catch (SQLException sqle) { System.out.println("SQLException : " + sqle);

JDBC Code (Cont.) Update to database try { stmt.executeUpdate( "insert into account values ('A-9732', 'Perryridge', 1200)"); } catch (SQLException sqle) { System.out.println("Could not insert tuple. " + sqle); } Execute query and fetch and print results ResultSet rset = stmt.executeQuery( "select branch_name, avg(balance) from account group by branch_name"); while (rset.next()) { System.out.println( rset.getString("branch_name") + " " + rset.getFloat(2));

JDBC Code Details Getting result fields: Dealing with Null values rs.getString(“branchname”) and rs.getString(1) equivalent if branchname is the first argument of select result. Dealing with Null values int a = rs.getInt(“a”); if (rs.wasNull()) Systems.out.println(“Got null value”);

Prepared Statement Prepared statement allows queries to be compiled and executed multiple times with different arguments PreparedStatement pStmt = conn.prepareStatement( “insert into account values(?,?,?)”); pStmt.setString(1, "A-9732"); pStmt.setString(2, "Perryridge"); pStmt.setInt(3, 1200); pStmt.executeUpdate(); pStmt.setString(1, "A-9733"); Beware: If value to be stored in database contains a single quote or other special character, prepared statements work fine, but creating a query string and executing it directly would result in a syntax error!（ prepared statements 不会检查语法）

Transactions in JDBC As with ODBC, each statement gets committed automatically in JDBC To turn off auto commit use conn.setAutoCommit(false); To commit or abort transactions use conn.commit() or conn.rollback() To turn auto commit on again, use conn.setAutoCommit(true);

Procedure and Function Calls in JDBC JDBC provides a class CallableStatement which allows SQL stored procedures/functions to be invoked.（存储过程的调用） CallableStatement cs1 = conn.prepareCall( “{call proc (?,?)}” ) ; CallableStatement cs2 = conn.prepareCall( “{? = call func (?,?)}” );

Result Set MetaData The class ResultSetMetaData provides information about all the columns of the ResultSet. Instance of this class is obtained by getMetaData( ) function of ResultSet. Provides Functions for getting number of columns, column name, type, precision, scale, table from which the column is derived etc. ResultSetMetaData rsmd = rs.getMetaData ( ); for ( int i = 1; i <= rsmd.getColumnCount( ); i++ ) { String name = rsmd.getColumnName(i);/*获得字段名*/ String typeName = rsmd.getColumnTypeName(i); }

Database Meta Data The class DatabaseMetaData provides information about database relations Has functions for getting all tables, all columns of the table, primary keys etc. E.g. to print column names and types of a relation DatabaseMetaData dbmd = conn.getMetaData( ); ResultSet rs = dbmd.getColumns( null, “BANK-DB”, “account”, “%” ); //Arguments: catalog, schema-pattern, table-pattern, column-pattern // Returns: 1 row for each column, with several attributes such as // COLUMN_NAME, TYPE_NAME, etc. while ( rs.next( ) ) { System.out.println( rs.getString(“COLUMN_NAME”) , rs.getString(“TYPE_NAME”); } There are also functions for getting information such as Foreign key references in the schema Database limits like maximum row size, maximum no. of connections, etc

Other SQL Features SQL sessions client connects to an SQL server, establishing a session executes a series of statements disconnects the session can commit or rollback the work carried out in the session An SQL environment contains several components, including a user identifier, and a schema, which identifies which of several schemas a session is using.

Schemas, Catalogs, and Environments Three-level hierarchy for naming relations. Database contains multiple catalogs each catalog can contain multiple schemas SQL objects such as relations and views are contained within a schema e.g. catalog5.bank-schema.account Each user has a default catalog and schema, and the combination is unique to the user. Default catalog and schema are set up for a connection Catalog and schema can be omitted, defaults are assumed Multiple versions of an application (e.g. production and test) can run under separate schemas

Embedded SQL, ODBC and JDBC Application Architectures ODBC OLE DB、ADO JDBC

End of Chapter