微软数据库访问接口（ODBC，OLEDB，ADO）

目录

• 数据库访问接口发展历史
• ODBC、OLEDB和ADO之间的关系
• VB脚本下的数据库接口代码示例
  • OLEDB连接
  • ODBC连接
  • 执行SQL语句
  • 游标操作
  • 读取数据
  • 添加数据
  • 修改数据
  • 常用属性
  • 关闭连接
• 在SQL Server中通过链接服务器查询OLE DB数据源
• 在SQL Server中直接查询OLE DB数据源

 

微软数据库访问接口发展历史

ODBC历史

ODBC（Open Database Connectivity，开放数据库互连）。要了解ODBC是什么，先了解一下数据库连接的相关知识。在最开始连接数据库时，由于数据库种类繁多，各种数据库连接有不同的需求，这个时期，数据库连接主要依靠各种API函数来进行连接。在这种背景下，微软于1992年发表了ODBC， ODBC就是将这些API函数封装起来形成统一的接口。当SQL语句进入接口后，驱动器管理程序将它们送入对应的驱动器(driver)，由驱动器将SQL语句送入各种不同的数据库。

OLE DB历史

OLE DB（Object Linking and Embedding, Database，对象链接嵌入数据库）是微软为以统一方式访问不同类型的数据存储设计的一种应用程序接口。OLE DB 是建立在 ODBC 功能之上的一个开放规范。ODBC 是为访问关系型数据库而专门开发的，OLE DB 则用于访问关系型和非关系型信息源。OLE DB可以通过ODBC连接到数据库，也可以直接连接到数据库，并且直接连接的访问速度更快。可以知道，只要支持ODBC的数据源是一定能够支持OLEDB的，但是反过来则不一定。

ADO历史

ADO（ActiveX Data Objects，ActiveX 数据对象）在1996年8月与OLE DB一起被发布，是在OLE DB上面创建的一个新的统一的数据访问的高层对象模型。ADO推出后顺利的取代了微软早期的数据访问对象层（包括RDO（Remote Data Objects）和DAO（Data Access Objects））。ADO将OLE DB的对象模型进一步简化，由数据库厂商开发满足OLE DB接口的数据提供者（data provider），而ADO本身则是与数据源无关（data source independent）的对象结构，这使得ADO通用性极好。

ODBC、OLEDB和ADO之间的关系

VB脚本下的数据库接口代码示例

OLEDB连接

标准安全模式(Standard Security)

dim conn, sCon
set conn=server.CreateObject("ADODB.Connection")
sCon="Provider=SQLOLEDB;Data Source=(local); Initial Catalog =dbname;User ID=sa;Password=123"
conn.open(sCon)

信任连接(Trusted connection)

dim conn, sCon
set conn=server.CreateObject("ADODB.Connection")
sCon="Provider=SQLOLEDB; Integrated Security=SSPI; Persist Security Info=False;Data Source=(local); Initial Catalog =dbname"
conn.open(sCon)

ODBC连接

通过系统数据源(System DSN)连接

先注册数据源：DSN。打开管理工具 -> 数据源(ODBC) -> 打开系统DSN选项卡 -> 单击添加按钮 -> 从列表中选择SQL Server，单击完成 -> 在名称中输入数据库名称，在你想连接的SQL Server服务器中输入（local）-> 按向导提示完成。

dim conn, sCon
set conn=server.CreateObject("ADODB.Connection")
sCon="DSN=注册名;Uid=sa;Pwd=123"
conn.open(sCon)

通过ODBC驱动程序连接

dim conn, sCon
set conn=server.CreateObject("ADODB.Connection")
sCon="Driver={SQL Server};Server=(local); Uid=sa;Pwd=123;Database=dbname"
conn.open(sCon)

执行SQL语句

Dim Sql, rs
Sql = "Select * From tablename"
Set rs = CreateObject("ADODB.Recordset")
rs.Open sSql,conn,2,3

rs.Open的参数如下：

• Source：有效的命令对象、SQL语句、表名、存储过程调用、URL 或包含持久存储的记录集的文件或流对象的名称。
• ActiveConnection：有效的连接对象变量，或包含ConnectionString参数的字符串。
• CursorType：可选。一个CursorTypeEnum值，确定提供程序在打开记录集时应使用的游标类型。默认值为adOpenForwardOnly。
• LockType：可选。LockTypeEnum值，用于确定提供程序在打开记录集 时应使用的(并发)类型。默认值为adLockReadOnly。

CursorTypeEnum

常量	值	说明
adOpenDynamic	2	使用动态游标。 其他 用户的添加 、更改和删除是可见的，并且允许所有类型的移动
adOpenForwardOnly	0	默认。 使用只进游标。 与静态游标相同，不同之处在于只能向前滚动记录。 当只需通过 记录集 进行一次传递时，这可以提高性能。
adOpenKeyset	1	使用键集游标。 与动态游标类似，但你看不到其他用户添加的记录，但其他用户删除的记录无法从 记录集中 访问。 其他用户的数据更改仍然可见。
adOpenStatic	3	使用静态游标，该游标是一组记录的静态副本，可用于查找数据或生成报表。 其他用户的添加、更改或删除不可见。
adOpenUnspecified	-1	不指定游标的类型。

 

LockTypeEnum

常量	值	说明
adLockBatchOptimistic	4	执行多行批处理更新时使用这种类型。
adLockOptimistic	3	乐观锁定，仅在调用Update方法时锁定。
adLockPessimistic	2	悲观锁定，当记录集打开的时候将其锁定。
adLockReadOnly	1	默认，用来打开只读记录。
adLockUnspecified	-1	不指定锁的类型。

 

游标操作

rs.MoveFirst        '把记录指针移动到第一条记录。
rs.MoveLast         '把记录指针移动到最后一条记录。
rs.MoveNext         '把记录指针移动到下一条记录。
rs.MovePrevious     '把记录指针移动到上一条记录。

 

读取数据　

读取当前游标指示的行。　

dim value
value = rs("Field1")

 

添加数据

rs.AddNew
rs("Field1")=Value1
rs("Field2")=Value2
rs("Field3")=Value3
rs.update

　　

修改数据

修改当前游标指示的行。

rs("Field1")=Value1
rs("Field2")=Value2
rs.update

　　

常用属性

rs.Fields.Count     '字段数量
rs.RecordCount      '数据集行数
rs.BOF              '如果当前的记录位置在第一条记录之前，则返回 true，否则返回 fasle。
rs.EOF              '如果当前记录的位置在最后的记录之后，则返回 true，否则返回 fasle。

　　

关闭连接

执行了select语句后Recordset会处在打开状态，其他执行其他SQL语句的Recordset未处在打开状态，不需要关闭。

在VBS中，离开对象作用域后会自动关闭。

rs.Close
set rs=Nothing
conn.Close
set conn=Nothing

　　

在SQL Server中通过链接服务器查询OLE DB数据源

创建链接服务器

配置链接服务器使SQL Server数据库引擎能够访问SQL Server实例之外的OLE DB数据源。

在SQL Server Management Studio中，打开对象资源管理器，展开“服务器对象”，右键单击“链接服务器”，然后单击“新建链接服务器”。

在“常规”页中，“链接服务器”一栏中为链接服务器命名，并填入其他信息。 

对链接数据库查询

在指定的链接服务器上执行传递查询。语法如下 ：

OPENQUERY ( linked_server ,'query' )  

参数：

• linked_server：表示链接服务器名称的标识符。
• ' query '：在链接服务器中执行的查询字符串。该字符串的最大长度为 8 KB。

OPENQUERY 可以在查询的 FROM 子句中引用，就好象它是一个表名

SELECT * FROM OPENQUERY (OracleSvr, 'SELECT name FROM joe.titles WHERE name = ''NewTitle'''); 

 

在SQL Server中直接查询OLE DB数据源

通过OPENDATASOURCE()函数直接查询OLE DB数据源，语法如下：

OPENDATASOURCE ( 'provider_name', 'init_string' )

示例如下：

SELECT GroupName, Name, DepartmentID  
FROM OPENDATASOURCE('MSOLEDBSQL', 'Server=Seattle1;Database=AdventureWorks2016;TrustServerCertificate=Yes;Trusted_Connection=Yes;').HumanResources.Department  
ORDER BY GroupName, Name;