Sql Server每日一练-连接-Connections

连接-Connections

使用远程视图操作远程数据的第一步就是建立与远程数据源的通讯，这里有好几种方法可供选择，请注意所有这些方法都使用 ODBC 与远程数据连接。

这里有一个非常简单的远程视图，它的作用是：读取 Northwind 数据库中 Customers 表的记录到远程视图 Vcustomers 中。

CREATE SQL VIEW Vcustomers ;
     REMOTE CONNECTION Northwind ;
     AS SELECT * From Customers
*先别试用这条语句，因为连接还没有建立

在上述命令的第二行我们告诉Visual FoxPro使用连接 Northwind 与 SQL Server 通讯。在运行上面的语句时，Visual FoxPro将在两个地方寻找这个连接：

当前 数据库容器（DBC）中查看是否存在数据库“连接对象”——Northwind。笔者称之为基于 DSN 的连接对象。 如果没有发现，Visual FoxPro将在客户机的 OCBC Data Source Names(DSNs)中查看是否存在连接 Northwind。笔者称之为 DSN 连接。

　

建立 DSNs 连接

建立连接的最快、最方便的方法是建立DSNs，您可以在控制面板中打开 ODBC 控制器，如图1。

图 1. ODBC Data Source Administrator 面板

我们发现有三种DSN 连接:

用户型DSN、系统型DSN、文件型DSN。其中用户型只对建立它的用户有效，譬如你以 Administrator 的身份登录系统并建立了一个用户DSN，那么除非你以 Administrator 登录系统否则你不能使用这一DSN；系统型则对当前用机器所有用户生效，无论你以什么身份登录系统；文件型DSN实际上是一个以DSN为后缀名的文本文件。从 Visual FoxPro 的角度，笔者把这三种 DSN 分成两类：

用户型、系统型 DSN。它可以单独作为连接为远程视图使用；也可以作为“基于 DSN 连接对象”的基础，“基于 DSN 连接对象”依赖于用户型或是系统型 DSN。 文件型 DSN。它不可以单独作为连接为远程视图使用；它可以为设计“基于字符串的连接对象”提供资料，但“基于字符串的连接对象”不依赖于文件型 DSN。

我们先建立一个系统型 DSN。

在 ODBC Data Source Administrator 面板中选中 “System DSN”页，按“Add”键，再选择 SQL Server 驱动程序，按“完成”按钮。出现图2。

图 2. 设定 DSN 的名称及服务器

这里我们选择“（local)”，注意如果系统将尝试连接目标服务器如果无法连接将报错，如果顺利的话您将看到图3的画面，要求选择用户认证方式，这里我们选择 SQL Server 与 Windows 混合认证方式，并输入登录SQL Server的用户名：“sa”，口令为空。

图 3. 设定 DSN 的登录信息

如果顺利画面4将出现在屏幕上，这里有一个选项要特别的注意，就是选择目标数据库。这里我们当然是选“

Northwind”。

图 4. 设定 DSN 的登录数据库

废话就不多说了，如果一些顺利，这条叫“Northwind”的System DSN 就建成了。

在Visual FoxPro的命令窗口中建立如下语句：

CREATE DATABASE Northwind
*建立本地数据库容器，存储数据库对象。
CREATE SQL VIEW VCustomers ;
  REMOTE CONNECTION Northwind ;
  AS SELECT * FROM Customers
*建立远程视图，注意这个视图是数据库（DBC）的一个对象，所以必须先建立DBC
USE VCustomers
*打开远程视图
BROWSE
*浏览远程视图

建立数据库容器（DBC）中的“连接对象”

记得在前文我们讲过连接可以存在于两个地方，就是 DSNs 与 DBC，那么 DSN 与基于 DSN 的连接对象有什么区别？

基于 DSN 的连接对象可以为数个远程视图共享，而 DSN 连接不可以。我们知道，每一条与 SQL Server的连接都是要收费的，共享连接就可以省钱；我们还知道，SQL Server管理每条连接大约要花去 24K 的内存空间，有人讲：24K不多，但您别忘了SQL Server 是服务器，不只是您一个人使用它——东一条连接、西一条连接——系统效率一定会受影响。因此无论从开发成本还是系统工作效率的角度，共享连接都是开发人员必然的选择。 

为了说明什么是共享连接，为了证明 DSN 连接不能为试图共享，我们做以下试验：

CREATE SQL VIEW VOrders ;
  REMOTE CONNECTION Northwind SHARE;
  AS SELECT * FROM Orders
*新建一个远程视图，仍然使用名称为Northwind 的DSN连接
DBSETPROP('VCustomers', 'View', 'ShareConnection', .T.)
USE VCustomers IN 0
*打开刚才的那个视图
USE VOrders IN 0
*打开新建的远程视图
?CURSORGETPROP("ConnectHandle", "VCustomers")
*显示 1
?CURSORGETPROP("ConnectHandle", "VOrders")
*显示 2
*这两个远程视图（光标）的连接句柄不是同一个。如果一条连接被共享了，那么这两个光标的连接句柄应是同一个数字；这两个视图虽然使用同一个 DSN 但实际上它们没有共享连接。

为了使用共享连接着一特性，我们建立“基于 DSN 的 连接对象”：

CREATE CONNECTION Northwind DATASOURCE Northwind

以上这条连接是建立在上面我们建立的DSN连接之上的，连接成为数据库容器的一个对象了。但注意：这种连接对象并不能摆脱DSN的控制，它的连接信息仍然存储在DSN中。

为了在视图间共享连接，我们必须设定设图的共享连接的属性,凡是参与共享连接的远程视图都必须具备共享连接的属性，否则共享连接还是不能实现。设定共享连接的方法有两种：

建立视图设计使用关键字 SHARE。如： CREATE SQL VIEW VOrders ; REMOTE CONNECTION Northwind SHARE; AS SELECT * FROM Orders 设定已视图的ShareConnection的属性为.t.。如： DBSETPROP('VCustomers', 'View', 'ShareConnection', .T.)

为了证明连接共享的特性我们执行以下代码：

USE VCustomers IN 0
USE VOrders IN 0
?CURSORGETPROP("ConnectHandle", "VCustomers")
*显示：1
?CURSORGETPROP("ConnectHandle", "VOrders")
*显示：1

我们已经学习了怎样建立“基于 DSN 的连接对象”，并了解了连接共享。仔细思考，您也许会发现“基于 DSN 的连接对象”存在下列问题：

运行时（RUN TIME）的可控性差。比如我们希望应用程序在不同的条件下使用不同的用户登录数据库服务器、或是不同的数据库，是不是要建立 N 个 DSN 以及 N 个“基于 DSN 的连接”？太可怕了！ 由于“基于 DSN 的连接”对 DSN 的依赖性很强，例如：用户删除或是更改了 DSN 信息，那么我们应用程序就无法正常运行了！

为了解决以上问题，Visual FoxPro 提出了“连接字符串的概念”。笔者称之为：基于字符串的连接。一个连接字符串基本上包括五个主要内容：ODBC 驱动器、服务器、用户名、登录名、数据库。（针对 SQL Server 而言)

CREATE CONNECTION Northwind2 ;
CONNSTRING "DRIVER=SQL;UID=sa;DATABASE=Northwind;SERVER=(local);PWD="

通过工具建立连接

以上我们都是通过命令的方式建立连接，现在我们将使用Visual FoxPro提供的连接设计器建立连接。上文我们讲到：连接对象分为两类：“基于 DSN 的连接对象”和“基于字符串的连接对象”。图5：

在连接设计器中选择“数据源、用户标识、密码”就说明在建立“基于 DSN 的连接对象”，在“数据源”列表框中可选择当前可用的用户型、系统型 DSN。 在连接设计器中选择“连接串”就说明在建立“基于字符串的连接对象”，为了快速、正确的设计连接字符串，我们可以打开文件型 DSN 参考，注意：“基于字符串的连接对象”不依赖文件型 DSN。

图 5. 连接设计器

连接属性的设定

从连接设计器的画面上我们可以发现很多连接属性，他们代表什么意思呢？在讲述这些问题之前，我想告诉大家：所有这些属性的设定都可以通过DBSETPROP()函数，所有这些属性值的获取都可以通过DBGETPROP()函数得到。下面，我们简要介绍几个属性：

异步执行。默认值为 .F.。同步执行是指：通过连接传送到后端的命令会被一句一句地执行，前一句没有执行完毕后一句就不会被执行；而同步执行是指：当通过连接发出命令后，不管后端是否完成并返回结果，客户端程序可以继续往下走。 超时间隔-空闲（分）。默认值是 0。设定一个非 0 值时，一个连接如果在这段时间内没被使用，Visual FoxPro 将使它休眠，再次使用该连接时 Visual FoxPro会激活它。注意使用该属性，因为它常常使您的应用程序产生不明不白的错误，如"Connectivity error: unable to retrieve specific error information. Driver is probably out of resources."实际上现在 ODBC 提供了 POOL 的功能，这些问题可由 ODBC 自行解决，我们不用操心。 超时间隔-连接（秒）。默认值是 0。设定一个非 0 值时，指在这个时间段内Visual FoxPro将试图连接到服务器，如果不成功，Visual FoxPro将产生一个错误。 显示 ODBC 登录提示。在应用程序中，我们总不希望有系统弹出登录窗口，这样于形象不利、于程序的安全性不利，特别是设计 COM 对象时，由于无人看护，更不希望登录窗口的弹出，Visual FoxPro 为我们提供了三种选择，笔者经常使用“从不显示”。 显示错误信息。如果设定为 .t.，任何 ODBC 错误将以 Messagebox 窗口显示出来；反之，ODBC 错误有用户自行处理。显然我们基本上使用默认设置 .f.。

提示：在Visual FoxPro中很多关于数据处理方面的设定，（如：set delete）都是限于数据工作期的，而连接被激活后可以在多个数据工作期内公用。 

远程视图

弱水三千，取一瓢饮——条件视图

在前文我们已经建立了两个远程视图，您也许发现这种整个把远程表读取过来的做法执行效果很好，其实不然。我有两大理由：

设想如果后端表很大（Customers 只有 91 条记录），有几万、几十万条记录，结果会这样？ 设想如果网络用户很多，网路很繁忙，结果会怎样？

Client/Server设计的一个重要的课题就是使网络流量最小化，所以在设计远程视图时就要考虑这一解决问题。我们的想法是使远程视图仅下载有必要的信息，这就叫：弱水三千，取一瓢饮。例如：

CREATE SQL VIEW VCustomers 
  REMOTE CONNECTION Northwind SHARE ;
  AS SELECT *  FROM Customers 
  WHERE customerid LIKE 'ALFKI'

我们在 WHERE子句中加入条件“customerid LIKE 'ALFKI'”，现在我们发现只有一条记录从远端被下载。您也与已经发现这中间的不足：这个远程视图太死板了——只能为 Customerid 类似于'ALFKI'的记录提供服务，是不是可以把 'ALFKI'作为一个参数，供使这个远程视图更灵活，服务面更广。

可以，Visual FoxPro支持这样的远程视图：

CREATE SQL VIEW VCustomers ;
  REMOTE CONNECTION Northwind SHARE ;
  AS SELECT * FROM Customers ;
  WHERE customerid LIKE ?cCustomerID 

如果您打开远程视图或对已打开的远程视图执行REQUERY()、REFRESH()函数，Visual FoxPro会查找是由存在名为cCustomerID的变量。如果存在，Visual FoxPro 自动把变量值填入远程视图的SQL语句中；如果变量不存在，Visual FoxPro将弹出如下窗口询问变量值。

在实际开发应用程序中，我们会经常实现设定远程视图参数，在需要其它信息时，改变参数值，再用REQUERY()函数刷新客户端的数据;远程视图参数不仅可以填入普通数据，还可以使用统配符，如下：

cCustomerID='ALFKI'
USE Vcustomers
BROWSE
cCustomerID='%B%'
REQUERY('Vcustomers')
BROWSE

这里有以下问题点大家必须注意：

远程视图参数只能在WHERE字句中设定，其他的连接子句、分组字句、排序子句都不支持这一特性。比如不可以出现 Order by ?cOrder。（有传言说 Visual FoxPro 7 支持参数加入排序子句，但笔者还未及尝试）。 一个远程视图可以设定多个参数。实例见下面的代码。 远程视图的SELECT-SQL语句必须是以后端数据库的语法为标准。例如，SQL Server以单引号确认字符串，而Visual FoxPro可使用双引号、方括号、单引号确认字符串，那么如下语句是错误： CREATE SQL VIEW VCustomers REMOTE CONNECTION Northwind SHARE ;   AS SELECT * FROM Customers WHERE customerid LIKE "ALFKI" 又如，Visual FoxPro中删除字符型字段的空格可使用ALLTR()函数，但远程视图的SELECT-SQL中不能用任何Visual FoxPro的函数，所以要实现上述功能，应如下使用SQL Server的函数： CREATE SQL VIEW VCustomers REMOTE CONNECTION Northwind SHARE ;   AS SELECT customerid,LTRIM(RTRIM(CompanyName)) FROM Customers WHERE customerid LIKE 'ALFKI' 第三点中我们强调了远程视图构建时必须使用后端数据库认识的Select-SQL。当远程视图被打开，成为Visual FoxPro的光标时，就可以对它使用Visual FoxPro的命令与函数。

CREATE SQL VIEW VEmployees;
  REMOTE CONNECTION Northwind SHARE
 AS SELECT EmployeeID,Birthdate FROM Employees
 WHERE BirthDate >?dBirthdate1 AND BirthDate <?dBirthdate2
dBirthdate1='19540101'
dBirthdate2='19601231'
USE VEmployees
BROWSE
*本例对日期型字段Birthdate使用了两个参数，并请注意日期型参数的传递。
CREATE SQL VIEW VEmployees1;
  REMOTE CONNECTION Northwind SHARE;
  AS SELECT EmployeeID, BirthDate, Country FROM Employees;
  WHERE birthDate > ?dBirthdate AND Country = ?cCountry
dBirthdate='19601231'
cCountry='uk'
USE VEmployees1
BROWSE
*本例对两个字段设定了参数

只下载有用的字段

上文中我们使用 SELECT * 从远端一古脑儿把所有列都下载到客户机，这样做不好。原因如下：

对远程数据操作时并不是所有的列（字段）都会被我们用到，特别是备注字段、大二进制字段。 在视图阶段就可以通过计算有关列得到更有用的信息，见下面的代码。 明确指定列信息有助于Visual FoxPro对远程数据表的版本控制。如果使用 SELECT *，那么只有在远程数据表增加字段时Visual FoxPro才知道，其他如减少、变更列的结构Visual FoxPro都不会知道。

例如：远程表中有FirstName,LastName列，我们在制作报表时只需要全名就可以了，那么我们解可以将它们相加成为一个新的列。

CREATE SQL VIEW VEmployees1 ;
  REMOTE CONNECTION Northwind SHARE ;
  AS SELECT EmployeeID, FirstName +' '+ LastName AS Name, Title ;
  FROM Employees

多表连接形成的远程视图

远程视图不仅支持远程一个表的操作，它还支持多表连接，如下：

CREATE SQL VIEW VEmployeeTerritories ;
REMOTE CONNECTION Northwind SHARE ;
AS SELECT Territories.TerritoryID, Territories.TerritoryDescription, Employees.EmployeeID,;
Employees.LastName, Employees.FirstName;
FROM Territories INNER JOIN;
EmployeeTerritories ON ;
Territories.TerritoryID = EmployeeTerritories.TerritoryID INNER JOIN;
Employees ON EmployeeTerritories.EmployeeID = Employees.EmployeeID

远程视图还支持自连接，如下：

CREATE SQL VIEW VEmployeeReportTO ;
REMOTE CONNECTION Northwind SHARE ;
AS SELECT Employees.EmployeeID as 领导工号,;
Employees.FirstName+' '+ Employees.LastName as 领导,;
Employees_a.FirstName +' '+ Employees_a.LastName as 下属,;
Employees_a.ReportsTo as 上级领导工号;
FROM Employees Employees INNER JOIN;
Employees Employees_a ON ;
Employees.EmployeeID = Employees_a.ReportsTo;
ORDER BY Employees_a.ReportsTo

从上面的语句大家可以发现：Visual FoxPro中的 SQL 与SQL Server 中的 SQL 十分相似，这方便了学习，但千万别陷入了“温柔陷阱”！