1、Chapter 3: SQL（2）,Embedded SQL, ODBC and JDBC,Embedded SQLApplication ArchitecturesODBC OLE DB、ADOJDBC,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 i
2、s 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 preprocessorEXEC SQL END-EXECNote: this varies by language. E.g. the Java embedding uses # SQL . ;,嵌入SQL语句,说明性语句
3、嵌入SQL语句 数据定义 可执行语句 数据控制 数据操纵 允许出现可执行的高级语言语句的地方，都可以写可执行SQL语句允许出现说明语句的地方，都可以写说明性SQL语句将SQL嵌入到高级语言中混合编程，程序中会含有两种不同计算模型的语句SQL语句 描述性的面向集合的语句 负责操纵数据库高级语言语句 过程性的面向记录的语句 负责控制程序流程,工作单元之间的通信方式,SQL通信区向主语言传递SQL语句的执行状态信息主语言能够据此控制程序流程主变量作用 主语言向SQL语句提供参数将SQL语句查询数据库的结果交主语言进一步处理使用方法这些变量由宿主语言的程序定义，并用SQL的DECLARE语句说明。引用
4、时，这些变量前必须加冒号“：”作为前缀标识，以示与数据库中变量有区别。游标（解决集合性操作语言与过程性操作语言的不匹配）游标定义语句（DECLARE） 游标打开语句（OPEN）游标推进语句( FETCH ) 游标关闭语句（CLOSE）,SQL通信区,SQLCA： SQL Communication AreaSQLCA是一个数据结构SQLCA的用途SQL语句执行后，DBMS反馈给应用程序信息 描述系统当前工作状态 描述运行环境这些信息将送到SQL通信区SQLCA中应用程序从SQLCA中取出这些状态信息，据此决定接下来执行的语句,SQLCA的使用方法,定义SQLCA 用EXEC SQL INCLU
5、DE SQLCA加以定义使用SQLCASQLCA中有一个存放每次执行SQL语句后返回代码的变量SQLCODE如果SQLCODE等于预定义的常量SUCCESS，则表示SQL语句成功，否则表示出错应用程序每执行完一条SQL 语句之后都应该测试一下SQLCODE的值，以了解该SQL语句执行情况并做相应处理,主变量,什么是主变量嵌入式SQL语句中可以使用主语言的程序变量来输入或输出数据在SQL语句中使用的主语言程序变量简称为主变量（Host Variable）主变量的用途输入主变量 指定向数据库中插入的数据 将数据库中的数据修改为指定值 指定执行的操作 指定WHERE子句或HAVING子句中的条件输出
6、主变量 获取SQL语句的结果数据 获取SQL语句的执行状态,主变量（续）,在SQL语句中使用主变量的方法1) 说明主变量BEGIN DECLARE SECTION. . (说明主变量).END DECLARE SECTION2) 使用主变量说明之后的主变量可以在SQL语句中任何一个能够使用表达式的地方出现为了与数据库对象名（表名、视图名、列名等）区别，SQL语句中的主变量名前要加冒号（:）作为标志在SQL语句之外(主语言语句中)使用主变量的方法可以直接引用，不必加冒号,Example Query,Specify the query in SQL and declare a cursor for
7、 itEXEC SQL BEGIN DECLARE SECTION；Int amount；EXEC SQL END DECLARE SECTION；EXEC SQLdeclare c cursor for select customer-name, customer-cityfrom depositor, customer, accountwhere depositor.customer-name = customer.customer-name and depositor account-number = account.account-numberand account.balance :
8、amountEND-EXEC,From within a host language, find the names and cities of customers with more than the variable amount dollars in some account.,游标,主语言是面向记录的，一组主变量一次只能存放一条记录仅使用主变量并不能完全满足SQL语句向应用程序输出数据的要求嵌入式SQL引入了游标的概念，用来协调这两种不同的处理方式什么是游标游标是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果每个游标区都有一个名字用户可以用SQL语句逐一从游标中获取记录，并赋
9、给主变量，交由主语言进一步处理,Embedded SQL (Cont.),（1）定义 EXEC SQL DECLARE CURSOR FOR ;例如： EXEC SQL DECLARE C1 CURSOR FOR SELECT tit_id, tit, roy FROM titles;（2）打开The open statement causes the query to be evaluatedEXEC SQL open c END-EXEC（3）逐一取出记录The fetch statement causes the values of one tuple in the query resu
10、lt to be placed on host language variables.EXEC SQL fetch c into :cn, :cc END-EXECRepeated calls to fetch get successive tuples in the query resultA 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 t
11、he database system to delete the temporary relation that holds the result of the query.EXEC SQL close c END-EXECNote: 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 EN
12、D 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 SUCC
13、ESS) /* (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 FOR UPDATE OF ;EXEC SQL DECLARE CURSOR FOR FOR UPDATE(2) O
14、PEN (3) FETCH推进游标指针，并把当前记录从缓冲区中取出来送至主变量(4) 检查该记录是否是要修改或删除的记录，是则处理之 WHERE CURRENT OF WHERE CURRENT OF (5) 重复第(3)和(4)步，用逐条取出结果集中的行进行判断和处理(6) CLOSE,例 对某个系（系名由主变量deptname指定）的学生信息，根据用户的要求修改其中某些人的年龄字段。思路 查询某个系全体学生的信息 然后根据用户的要求修改其中某些记录的年龄字段,例题（续）,EXEC SQL INCLUDE SQLCA; EXEC SQL BEGIN DECLARE SECTION; . /*
15、 说明主变量 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) /* 用循环结构逐条处理结果集中的记录
16、*/ 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, .,例题（续）,if (yn=y or yn=Y) /*
17、 需要修改 */ printf(INPUT NEW AGE: ); scanf(%d, /* 关闭游标 */ . .,例题（续）,例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 S
18、no, 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语句错误，则退出
19、循环 */ printf(%s, %s, %s, %d, Sno, Sname, Ssex, Sage); /* 显示该记录 */printf(DELETE ? ); /* 问用户是否要删除 */ scanf(%c, /* 关闭游标 */,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 =
20、 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,App
21、lication Architectures,Applications can be built using one of two architecturesTwo tier modelApplication program running at user site directly uses JDBC/ODBC to communicate with the databaseThree tier modelUsers/programs running at user sites communicate with an application server. The application s
22、erver in turn communicates with the database,Two-tier Model,E.g. Java code runs at client site and uses JDBC to communicate with the backend serverProblems:Security: passwords available at client site, all database operation possibleMore code shipped to clientNot appropriate across organizations, or
23、 in large ones like universities,Three Tier Model,Application/HTTPServer,Servlets,JDBC,Network,HTTP/Application Specific Protocol,Three-tier Model (Cont.),E.g. Web client + Java Servlet using JDBC to talk with database serverClient sends request over http or application-specific protocolApplication
24、or Web server receives requestRequest handled by CGI program or servletsSecurity handled by application at serverBetter security,ODBC,ODBC,Open DataBase Connectivity(ODBC) standard standard for application program to communicate with a database server.ODBC是一个调用层的接口 ODBC定义了标准的SQL语法 ODBC提供一个驱动程序管理器来管理
25、并同时访问多个DBMS系统,应用程序的主要功能是：调用ODBC函数，递交SQL语句给DBMS，检索出结果，并进行处理。 驱动程序管理器是一个动态连接库（DLL），用于连接各种DBS的DBMS驱动程序（如Oracle、Foxpro、Sybase等驱动程序），管理应用程序和DBMS驱动程序之间的交互作用（通信）。应用程序通过调用驱动程序所支持的函数来操纵数据库。驱动程序也是一个动态连接库（DLL）。 单层驱动多层驱动两层三层数据源,网络环境下基于单层驱动程序的ODBC结构,基于多层驱动程序的ODBC结构(两层结构),基于网关机制的多层驱动程序的ODBC结构（三层结构）,ODBC (Cont.),O
26、DBC 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 connectthe user identifier, password Must also specify types of arguments:SQL_NTS denotes previous argume
27、nt is a null-terminated string.,ODBC Code,int ODBCexample() RETCODE retcode; HENV env; /* environment */ HDBC conn; /* database connection */ HSTMT hstmt /* 语句 */ SQLAllocEnv( ,有准备地执行SQL语句的函数（用于动态sql）(1) SQL语句预备函数其格式如下：SQLPrepare（hstmt，szSqlStr，cbSqlStr）。其中，参数hstmt是一个有效的语句句柄，参数szSqlStr和cbSqlStr分别表示将
28、要执行的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
29、）；*显示* while（ rcGetData!=SQL_NO_DATA_FOUND）；,ODBC Code (Cont.),Main body of program char branchname80;float balance;int lenOut1, lenOut2;HSTMT stmt; SQLAllocStmt(conn, ,ODBC Code (Cont.),SQLBindCol() binds C language variables to attributes of the query result When a tuple is fetched, its attribute
30、values are automatically stored in corresponding C variables.Arguments（参数） to SQLBindCol()ODBC stmt variable, attribute position in query resultThe 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
31、 to store actual length when a tuple is fetched.Note: A negative value returned for the length field indicates null valueGood programming requires checking results of every function call for errors; we have omitted most checks for brevity.,More ODBC Features,Prepared StatementSQL statement prepared:
32、 compiled at the databaseCan have placeholders（占位符）: E.g. insert into account values(?,?,?)Repeatedly executed with actual values for the placeholdersMetadata featuresfinding all the relations in the database andfinding the names and types of columns of a query result or a relation in the database.B
33、y default, each SQL statement is treated as a separate transaction that is committed automatically.Can turn off automatic commit on a connectionSQLSetConnectOption(conn, SQL_AUTOCOMMIT, 0) transactions must then be committed or rolled back explicitly by SQLTransact(conn, SQL_COMMIT) orSQLTransact(co
34、nn, SQL_ROLLBACK),ODBC Conformance Levels,Conformance levels specify subsets of the functionality defined by the standard.CoreLevel 1 requires support for metadata queryingLevel 2 requires ability to send and retrieve arrays of parameter values and more detailed catalog information.可以用SQLGetFunction
35、函数来得到确定驱动程序是否支持某一特定函数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）和
36、标准化组织XOpen在1992年联合出版了一个规范，该规范定义了调用级界面（Call Level Interface），CLI）标准。 1993年，Microsoft公司推出了ODBC 2.0规范，并于1994年出版了ODBC 2.0 SDK。 Microsoft在1998年出版的ODBC 3.0规范中，作了一些修改，目的是更进一步接近SQL标准。SQL3（即SQL99）的第三部分介绍了SQLCLI。,OLE DB、ADO,Role of OBDC,Role of OLE DB,OLE DB breaks the features and functions of a DBMS into CO
37、M objects, making it easier for vendors to implement portions of functionalityThis characteristic overcomes a major disadvantage of ODBCWith 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 interfac
38、es for DBMS functionality piecesQuery, update, transaction management, etc.Increase flexibilityAllow data consumers to use only the objects they needAllow data providers to expose pieces of DBMS functionalityProviders can deliver functionality in multiple interfacesInterfaces are standardized and ex
39、tensibleObject interface over any type of dataRelational 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 数据提供者 凡是透过OLE DB 将数据提供出来的，就是数据提供者。例如SQL Server 数据库中的数据表，或是附文件名为mdb 的Access 数据库档案等，都是Da
40、ta 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 d
41、ata provider exposes data via rowsetsExamples: DBMS, spreadsheets, 电子邮件Service provider is a transformer of data through OLE DB interfacesIt is both a consumer and a provider of transformed dataExamples: query processors, XML document creator,Role of ADO,ADO,ADO (Active Data Objects) characteristics
42、Simple object model for OLE DB data consumersCan be used from VBScript, JScript, Visual Basic, Java, C#, C+Single Microsoft data access standardData 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 SQLJDBC suppor
43、ts a variety of features for querying and updating data, and for retrieving query resultsJDBC also supports metadata retrieval, such as querying about relations present in the database and the names and types of relation attributesModel for communicating with the database:Open a connectionCreate a “
44、statement” objectExecute queries using the Statement object to send queries and fetch resultsException 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-:2000:bankdb, userid, passwd); Statement stmt = conn.createStatement(); Do Actual Work . stmt.close(); conn.close(); catch (SQLException sqle) System.out.println(SQLException : + sqle); ,
