数据库应用技术10.ppt

1、第十章 SQL SERVER高级程序设计,10.1 使用SQL-DMO管理SQL SERVER,SQL分布式管理对象(SQL Distributed Management objects, SQL-DMO)提供了用程序来进行数据库的管理功能，SQL-DMO针对的对象是数据库本身的结构，它并不是象ADO那样对数据访问感兴趣，它提供OLE自动化接口来完成数据库的管理任务，就象在程序中通过代码来完成Enterprise Manager的功能一样。,10.1.1 SQL-DMO 编程接口,OLE自动化是维护应用程序共享对象的标准和方法，是一种允许应用程序为进行管理或数据访问共享对象的编程方法。SQL-

2、DMO就是利用OLE结构开发的，它为应用程序提供了通向所有SQL Server管理功能的接口，当然这些应用程序必须是与OLE兼容的。SQL-DMO使软件开发者直接访问SQL Server数据对象变成可能。我们利用DELPHI或Visual Basic等编程工具就可以使用分布式管理对象实现对SQL Server的直接访问。,10.1.2 SQL-DMO 对象模型,在SQL-DMO的对象模型中，最主要的对象是SQL SERVER对象，它是SQL-DMO对象模型中大部分主要对象的父对象。在应用程序中首先要创建一个SQL SERVER对象，然后建立一个与数据库实例的连接，接下来就可以使用SQL-DMO

3、对象集合中的对象的属性、方法、事件来进行编程。,10.1.2 SQL-DMO 对象模型,Replication对象主要用来在不同SQL SERVER数据库之间进行复制，它包括Distributor对象、Publisher对象、Subscriber对象等。 JobServer对象主要执行SQL Server Agent的功能，如创建和调度任务，返回一个错误信息等。 RemoteServer对象用来管理远程SQL SERVER，可以远程登录到一个SQL SERVER，并且可以在远程SQL SERVER上执行Transact-SQL语句。 Database对象也是一个比较重要的对象，用它可以创建一个

4、数据库，增加表、角色、存储过程、视图、用户定义数据类型等操作，也可以重命名数据库、删除数据库以及对数据库进行备份恢复等操作。 Table对象主要完成对表的各种操作，例如创建一个表、增加表列或删除表列、导入导出数据以及删除一个表。 其他的对象如View、Rule等分别完成与对象相关的功能，即代替Transact SQL语句来完成数据库的管理功能，全部的SQL-DMO对象模型如图10.1所示。,10.1.2 SQL-DMO 对象模型,10.1.3 深入SQL-DMO 对象,SQL-DMO对象模型中的对象很多，不可能一一列举所有的对象进行讲解，本节给出几个常用的对象，介绍它们的使用方法。,10.1.

5、3 深入SQL-DMO 对象,Application对象 Application是在SQL SERVER上层的一个对象，它用于创建或返回一个SQL SERVER实例，返回SQL-DMO的版本信息以及管理在Applictation上建立的SQL SERVER对象。 Application对象的主要方法是ListAvailableSQLServers，用于列出网络上可用的SQL SERVER实例。 Quit方法断开Application中引用的所有SQL SERVER对象，并且释放所有SQL-DMO对象。,10.1.3 深入SQL-DMO 对象,例程10.1：在VB中利用SQL-DMO列出所有当前

6、可用的SQL SERVER服务器。 新建一个工程，选中【工程】菜单的【引用】子菜单，在引用列表中选中Microsoft SQL DMO Object Library完成对SQL-DMO对象的引用。在窗体上添加一个ListBox控件，在Form_Load事件中，添加如下代码： Dim objApp As SQLDMO.Application Dim objAvailableSvr As SQLDMO.NameList Dim objSvr As SQLDMO.SQLServer Set objApp = New SQLDMO.Application Set objAvailableSvr = o

7、bjApp.ListAvailableSQLServers If objAvailableSvr.Count 0 Then For Each objSvr In objAvailableSvr 列出所有可用的SQL SERVER服务器 List1.AddItem objSvr.NetName Next End If Set objSvr = Nothing Set objAvailableSvr = Nothing objApp.Quit,10.1.3 深入SQL-DMO 对象,SQL SERVER对象 SQL SERVER对象是SQL-DMO的最主要的对象，它包括一些实现SQL SERVER

8、管理任务的对象和集合，允许SQL-DMO应用程序连接到一个SQL SERVER实例。 SQL SERVER对象完成的主要功能有： 连接到一个SQL SERVER实例、返回它的安装配置以及运行时参数。 增加或删除一个SQL SERVER对象，如Database对象、备份设备对象等。 在服务器上执行一个Transact SQL 语句或操作系统命令。 禁止一个SQL SERVER实例 捕获一个SQL SERVER事件或SQL SERVER对象事件，返回状态信息给SQL-DMO应用程序用户或返回调试信息给SQL-DMO应用程序开发者。,10.1.3 深入SQL-DMO 对象,SQL SERVER对象的

9、主要属性、方法： BackupDevices方法 返回对BackupDevices集合的引用，提供当前运行SQL SERVER的服务器上备份设备的说明。 Databases方法 返回对databases集合的引用，表示当前服务器上的可用的数据库名称。 Jobserver方法 返回对Jobserver的引用，包括SQL SERVER代理提供的功能。 LinkedServers方法 返回OLE DB数据提供者的属性。 Logins方法 返回对Logins集合的引用，管理用户登录的记录，这是SQL SERVER安全管理的一部分。 RemoteServers方法 提供远程SERVER的属性，包括识别一

10、个远程SERVER，移除一个远程SERVER的命名，重命名远程SERVER。 Replication方法 返回对Replication的引用，提供对数据库复制功能的支持。 ServerRoles方法 返回对ServerRoles集合的引用，通过设置权限来提供对数据库的安全管理功能的支持,10.1.3 深入SQL-DMO 对象,例程10.2：在VB中用SQL DMO登录到SQL SERVER，新建一个VB工程，引用Microsoft SQL DMO Object Library，在窗体上添加三个文本框控件：txtServer、txtUserName、txtPassword，分别对应用三个文本标签

11、：服务器名称、登录名、密码。添加一个按钮控件【连接】，在Click事件中写如下代码： Dim objSQLSERVER As SQLDMO.SQLServer Set objSQLSERVER = New SQLDMO.SQLServer On Error GoTo ErrorHandler objSQLSERVER.LoginTimeout = -1 objSQLSERVER.LoginSecure = False objSQLSERVER.AutoReConnect = False objSQLSERVER.Connect txtServer.Text, txtUserName.Text,

12、 txtPassword.Text MsgBox objSQLSERVER.Login, vbExclamation, 登录成功 Exit Sub ErrorHandler: MsgBox 登录错误： Database=test;UID=sa;PWD=; cn.Open ProvStr Set Session(cnn) = cn 用session传递对数据库的连接 END Sub 当会话结束时，要关闭数据库对象： Sub Session_OnEnd 当会话结束时关闭数据对象 cn.Close END Sub,10.2.2 实现动态页面发布数据,在Company.asp文件中，显示所有的单位，并

13、提供增加、删除、修改单位的功能。定义两个过程用于增加和删除选中的单位： Sub DeleteConfirm Set Form = Document.RemoveForm removeconfirmed = MsgBox (真的要删除选中的记录吗?, vbYesNo) IF removeconfirmed = 6 THEN document.RemoveForm.submit 提交页面，删除单位 END IF End Sub sub AddNew 转向添加新单位页面 document.location.href=CompanyAdd.asp End Sub,10.2.2 实现动态页面发布数据,在

14、CompanyAdd页面，需要完成对单位代码和单位名称的录入，提交时执行的关键代码如下： Set cn = Session(cnn) sCompanyID=Request.Form(CompanyID) sCompanyName=Request.Form(CompanyName) set rs=server.CreateObject(ADODB.Recordset) rs.Open select * from company where companyid= EXEC SQL CONNECT TO red.test as con2 USER sa; EXEC SQL DISCONNECT co

15、n1 EXEC SQL DISCONNECT con2,10.3.2 在C语言中使用嵌入式SQL,使用宿主变量 宿主变量作为标准的C变量在ESQL/C中定义，使用宿主变量可以在SQL语句中指定输入参数或接收输出参数。定义宿主变量的语句写在BEGIN DECLARE SECTION和END DECLARE SECTION语句之间，例如： EXEC SQL BEGIN DECLARE SECTION; int CompanyID; char CompanyName30; EXEC SQL END DECLARE SECTION; 使用宿主变量时，要在宿主变量前加一个冒号作为引用宿主变量的标志，如“

16、WHERE CompanyID=:CompanyID” 注意：如果从数据库中传给宿主变量的值是NULL，那么宿主变量将会把NULL值转换为-1处理，例如虽然给宿主变量Address赋了NULL值，以下SQL语句还是不会返回Address为NULL值的记录： EXEC SQL SELECT FROM Company WHERE Address = :Address;,10.3.2 在C语言中使用嵌入式SQL,使用SQL语句 在ESQL/C中有两种SQL语句，一种是静态SQL语句，一种是动态SQL语句。静态SQL语句是一个完整的SQL语句，而动态SQL语句需要在运行时指定条件变量。 可以用PREP

17、ARE来定义一个动态SQL语句，语法如下： PREPARE stmt_name FROM :hvar 执行一个动态SQL语句可以用EXECUTE或EXECUTE IMMEDIATE，EXECUTE IMMEDIATE指的是执行一个包含在宿主变量中的SQL语句。 可以用SELECT INTO来把查询结果赋给指定的宿主变量，只用于返回一条记录的查询语句，如果查询语句返回多行记录，可以使用游标来处理。 例程10.10：构造一个动态SQL语句，插入一条记录到Company表。 EXEC SQL BEGIN DECLARE SECTION; char stmtcon = INSERT INTO Comp

18、any VALUES (?, ?); char CompanyID30; char CompanyName30; EXEC SQL END DECLARE SECTION; /提示用户输入数据 / printf(输入单位代码: ); scanf(%s, CompanyID); printf(输入单位名称: ); scanf(%s, CompanyName); EXEC SQL PREPARE stmt FROM :stmtcon; EXEC SQL EXECUTE stmt USING :CompanyID, :CompanyName; 例程10.11：把单位代码为”0001”的单位名称赋给变

19、量CompanyName： EXEC SQL BEGIN DECLARE SECTION; char CompanyName30; EXEC SQL END DECLARE SECTION; EXEC SQL SELECT CompanyName INTO :CompanyName FROM Company WHERE CompanyID=0001;,10.3.2 在C语言中使用嵌入式SQL,使用游标 当SQL查询语句只返回一行时，可以用SELECT INTO语句把返回值赋给宿主变量，但是当查询语句返回多行时，就不能用SELECT INTO，此时就需要游标来处理。 游标分为静态游标和动态游标，

20、静态游标包含完整的SQL语句，动态游标则需要在运行时指定参数。 静态游标可以包含宿主变量，当打开它时，宿主变量就被读到SQL语句中，形成一个完整的SQL语句。 动态游标中的参数用？标记符来指定，当打开游标时，根据提供的？标记符所代表的数据来构造SQL语句。 在使用游标之前必面要定义游标，定义游标的语法如下： DECLARE cursor_name INSENSITIVE SCROLL CURSOR FOR select_stmt | prepared_stmt_name FOR READ ONLY | UPDATE OF column_list 打开游标使用OPEN语句，用FETCH语句移动打

21、开的记录集。对于打开的游标，可以用UPDATE来更新数据，DELETE来删除数据。游标使用完后，用CLOSE语句来关闭它。,10.3.2 在C语言中使用嵌入式SQL,例程10.12：定义一个游标，返回单位信息表的所有记录，并输出单位信息到屏幕上。 /定义游标，并打开/ EXEC SQL DECLARE c1 CURSOR FOR SELECT CompanyID,CompanyName FROM Company FOR BROWSE; EXEC SQL OPEN c1; /定义变量/ EXEC SQL BEGIN DECLARE SECTION; char CompanyID30; char

22、CompanyName30; EXEC SQL END DECLARE SECTION; /输出信息/ while (SQLCODE = 0) EXEC SQL FETCH c1 INTO :CompanyID,:CompanyName; printf(%s,%s,CompanyID,CompanyName); 例程10.13：定义一个动态游标，查询单位名称为“北京怡神工贸公司”的单位，返回其单位代码。 EXEC SQL BEGIN DECLARE SECTION; char szCommand = SELECT CompanyID FROM Company WHERE CompanyName

23、 = ?; char szCompanyName = 北京怡神工贸公司; char szCompanyID30; EXEC SQL END DECLARE SECTION; /定义动态游标/ EXEC SQL DECLARE con_cursor CURSOR FOR select_statement; /准备SQL语句/ EXEC SQL PREPARE select_statement FROM :szCommand; /打开游标，输出到变量中/ EXEC SQL OPEN con_cursor USING :szCompanyName; EXEC SQL FETCH con_cursor INTO :szCompanyID;,10.3.2 在C语言中使用嵌入式SQL,使用事务 ESQL/C象T-SQL一样完全支持事务管理，打开一个事务用BEGIN TRANSACTION，提交事务用COM