VFP及SQL-SERVER搭配.doc

VFP和SQL SERVER搭配做C/S系统
出处:网络

1.CLIENT/SERVER 到底是什么?C/S属于2-TIER系统，适合于中小型应用系统。大系统一般都用3-TIER了。
打个比方单机数据库系统(比如VFP), 相当于前店后库.店里需要什么东西,得自己去库房找,库房管理也是有你自己进行. 而C/S系统下,店和库是相对独立的,有一个专门的库房管理(数据SERVER),店里需要什么,按照手续把单子给
库房管理人员,由他们去操作.

因此可以看出C/S的优点: 支持多用户; 更有效的数据管理,数据安全和可靠得多;远程使用数据.
如果你是单机使用当然没有太大必要使用C/S了.

如果用VFP本身做C/S系统, 效果不是很明显,因为VFP的数据管理功能不是很强,比如加锁解锁都需要程序来操作. 我主要说的是VFP/SQL SERVER系统.

2. SQL SERVER
SQL SERVER是微软发布的RDBMS(关系数据库管理系统), ORACLE, INFOMIX,POWERBUILD,也都是类似的系统. 他们就相当于仓库的管理系统,但功能不仅仅是数据管理. 微软的数据库产品从功能和规模由小到大排列依次是: ACCESS, VFP, SQL SERVER.

稍微大一点的系统,SQL SERVER是需要专人管理的,这就是DBA (DATABASE ADMINISTRATOR)的位置.现在北美人才市场上, DBA的工作比程序员的工作好找些. 工资比普通程序员略高, 工作稳定性也强一些.

SQL SERVER是一套大的软件系统,可以安装在专门的NT数据SERVER上,也有个人版可以安装在WIN95/98上, 主要是为了咱们这些程序员方便测试. 它的功能主要有这么几块: 数据库的管理和维护,用户/安全管理, 数据的发布/转换.

3. 用VFP/SQL SERVER做C/S系统
VFP和SQL SERVER的搭配应该是比较完美的, 但因为VB才是微软的主流产品(VB一直是微软的,FOXPRO是后来才买的,后娘生的), 所以微软从来没有大力推荐或者宣传过VFP, 而是把VFP独有的数据库技术融合到自己的其它产品里了: SQL SERVER, ODBC, OLE DB/ADO. 最近甚至把VFP从VISUAL STUDIO里独立出来了.
VFP和SQL SERVER的交流,可以通过3种方式进行:
A. 远程视图 B. ADO控件 C. SPT (SQL PASS THROUGH)
3种方式各有优缺点, 而我最偏好第3种. 远程视图和ADO都在VFP和SQL SERVER中间加了一个层次, 而这层次就相当于一个黑箱,你不清楚它们到底怎么操作数据的,只需按照它们的规则进行设置,使用
相应的命令就可以. 反正我作为程序员,喜欢清楚地知道我的程序每一步都在干什么,所以我喜欢用SPT, 也从不用向导来建立表单报表一类的.

SPT技术是通过VFP的函数SQLCONNECT()来和SQL SERVER建立连接, 然后用SQLEXEC()函数把要执行的SQL命令 送到SQL SERVER上去执行,
所有对数据的操作都是通过这些SQL命令来进行的. 每一个细节都由自己的代码来控制.

4. VFP数据的升迁
VFP的数据库,可以直接用VFP自带的升迁向导转到SQL SERVER上,但必须把所有的表都放到数据库里,自由表不能直接升迁, 而实际上升迁上去的又只是表,数据库本身不能转到SQL SERVER里. 感觉怪怪的. 也许是为了升迁索引或者关系吧.
要把VFP数据升迁到SQL SERVER上, 必须先在SQL SERVER里建立相应的数据库. SQL SERVER的管理是通过ENTERPRISE MANAGER (EM)来进行的. 通过它建立数据库, 每个数据库里可以包含 数据表,用户/用户群,视图,存储过程等等.
至于改用SQL SERVER后,原来的VFP程序是肯定需要修改的, 而且是比较大的改动.

谈谈VFP和SQL SERVER搭配做C/S系统 (二) --动态设置ODBC连接
1。设置ODBC数据源
VFP和SQL SERVER的连接是通过ODBC或者OLE DB(ADO)来进行的。可以打开控制面板的ODBC数据源进行设置。但很多情况下，我们希望能在程序里动态设置数据源，一来可以不用去为每个用户的机器手动设置， 二来为了数据的安全性，使用完后，希望把ODBC数据源删掉。
下面的例子是用API函数来设置和删除ODBC数据源。
函数名字是 SQLConfigDataSource，其中第二项参数是数字(1-增加 2-修改 3-删除)
DECLARE INTEGER SQLConfigDataSource IN odbccp32 INTEGER, INTEGER, STRING, STRING
lnWindowHandle=0
lcODBCDriver=′SQL Server′ &&DRIVER类型
lcODBCName=′SharedData′ &&数据源名字
lcODBCDesc=′Shared Data Source′ &&数据源描述
lcODBCServer=′DEVSQL′ &&SQL SERVER名字
lcODBCDatabase=′Shared′ &&要连接的数据库名字
**先试图修改已有的ODBC，如果不存在，返回0。
lreturn=SQLConfigDataSource(lnWindowHandle, 2, lcODBCDriver, ;
′DSN=′ + lcODBCName + CHR(0) ;

+ ′Description=′ + lcODBCDesc + CHR(0) ;
+ ′Server=′ + lcODBCServer + CHR(0) ;
+ ′Database=′ + lcODBCDatabase + CHR(0))
IF lreturn=0 &&不存在

**添加新的ODBC
lreturn=SQLConfigDataSource(lnWindowHandle, 1, lcODBCDriver, ;
′DSN=′ + lcODBCName + CHR(0) ;
+ ′Description=′ + lcODBCDesc + CHR(0) ;
+ ′Server=′ + lcODBCServer + CHR(0) ;
+ ′Database=′ + lcODBCDatabase + CHR(0))
IF lreturn=0 &&失败
MessageBox(′添加ODBC数据源失败′,16,′BUFFER′)
ENDIF
ENDIF

2。删除ODBC数据源
**用完后，可以在表单DESTROY事件里删除ODBC。 如果不想重复设置参数，可以把这些参数加到表单作为属性。
DECLARE INTEGER SQLConfigDataSource IN odbccp32 INTEGER, INTEGER, STRING, STRING

lnWindowHandle=0
lcODBCDriver=′SQL Server′
lcODBCName=′SharedData′
lcODBCDesc=′Shared Data Source′
lcODBCServer=′DEVSQL′
lcODBCDatabase=′Shared′

**先修改，或者其是否存在
lreturn=SQLConfigDataSource(lnWindowHandle, ;
2, lcODBCDriver, ;
′DSN=′ + lcODBCName + CHR(0) ;
+ ′Description=′ + lcODBCDesc + CHR(0) ;
+ ′Server=′ + lcODBCServer + CHR(0) ;
+ ′Database=′ + lcODBCDatabase + CHR(0))
IF lreturn=1 &&ODBC存在，删除它
lreturn=SQLConfigDataSource(lnWindowHandle, ;
3, lcODBCDriver, ;
′DSN=′ + lcODBCName + CHR(0) ;
+ ′Description=′ + lcODBCDesc + CHR(0) ;
+ ′Server=′ + lcODBCServer + CHR(0)
+ ′Database=′ + lcODBCDatabase + CHR(0))
IF lreturn=0
MessageBox(′删除ODBC源失败,16,′BUFFER′)
ENDIF
ENDIF

**清除DLL
CLEAR DLLS

3. 从VFP连接到SQL SERVER
lnHandle=SQLConnect("SharedData","用户名”，“密码”)
If lnHandle>0 &&连接成功
**从库里获得数据(比如从EMP表里得到部门号为‘01’的职工)
lnReturn=SQLExec(lnHandle,"Select * from Emp Where cDept=′01′","CursorEmp")
If lnReturn>0 &&运行成功
Browse
Else &&失败
&&出错处理
EndIf
Else &&连接失败
MessageBox("连接SQL SERVER失败”，16，“BUFFER”)
EndIf

**用完连接后，最好马上关闭，连接是很宝贵的资源，微软是按连接数收费的，而且每个连接会增加SQL SERVER的管理负担
=SQLDisconnect(lnHandle)

(续) (这些贴子必须连着看)

用SPT技术更新数据时，必须通过SQL命令进行，SQL命令必须符合ANSI或者T-SQL(微软的TRANSACTION SQL)，因为命令是通过VFP的SQLEXEC()函数送到SQL SERVER去执行的，语法必须遵守SQL SERVER的规则，而不是VFP的规则。不熟悉的人，最容易犯的错误就是把VFP的函数传送到SQL SERVER上执行，结果总出错。两者大部分函数都是不一样的。
比如ALLTRIM()是VFP的， SQL SERVER里是LTRIM()和RTRIM()
VFP日期以{}分界，但SQL SERVER里不认，必须用单引号.
如果SQL命令里用到VFP程序里的变量，变量前必须加问号“？”
比如，更新一个表的字段

lcLName="Zhang"
lcFName="San"
lnReturn=SQLEXEC(连接句柄，“Update Emp Set cLName=?lcName, cFName=?lcFName Where cEmpNo=′733000′")
如果返回值lnReturn>0，就更新成功了
插入记录，或者逐条修改记录时，只能每次操作一条记录。
比如要把临时表TmpEmp里的记录加到SQL SERVER的EMP表里，就得用循环
Select TmpEmp
Scan
lnReturn=SQLEXEC(连接句柄，"Insert Into Emp (cEmpNo,cLName,cFName) Values (?TmpEmp.cEmpNo,?cTmpEmp.cLName,?cTmpEmp.cFName)"
EndScan
*** 要得到SQL SERVER上某个表的结构，有两种办法，一是运行SQL SERVER自带的系统存储过程。一是运行一个SELECT命令。
比如要从SQL SERVER得到EMP表的结构
lnReturn=SQLEXEC(连接句柄,"Select Top 0 * From Emp","TmpEmp")
返回的结果都是临时表，是只读的，要想变成可读写的，需要做点小变化：
Select 0
Use DBF("TmpEmp") Again Alias Emp
Use In TmpEmp
现在得到的EMP临时表就是可读写的了。
*注 "Select Top 0 From Emp" 命令在VFP里是错误命令，但SQL SERVER里可以执行.

SPT和使用视图相比，优点是每一步你都清楚自己在做什么，知道为什么命令会出错。缺点是你需要多写命令，多了解SQL SERVER的语法。

谈谈VFP和SQL SERVER搭配做C/S系统 (五) --从VFP控制SQL SERVER事务处理和加锁

**建立与SQL SERVER的连接
lnHandle=SQLConnect("ODBC数据源","用户名"，"密码")

If lnHandle>0

&& 设置成手工事务处理模式，由代码来控制
= SQLSETPROP(lnSQL_Hand, ′Transactions′, 2)

**下面的命令从EMPPAY里选取记录，并给该表加上独占锁(TABLELOCKX)，一直到该事务结束(HOLDLOCK)

ln1=SQLEXEC(lnHandle, "SELECT * FROM Emppay WITH (TABLOCKX, HOLDLOCK)","Emppay")

**执行其它命令，比如更新数据
ln2=SQLEXEC(lnHandle,"Update PayTotal Set ......")
...

...

**如果所有命令都正确执行了，则
=SQLCOMMIT(lnHandle)

***如果要放弃整个事务处理，用
=SQLROLLBACK(lnHandle)

***关闭连接，事务自动结束，锁也解开
=SQLDISCONNECT(lnHandle)

Else
****连接失败
EndIf

*** SQLSETPROP()函数
这个VFP函数是用来设置当前连接的属性的。 比如上边的手工事务处理。
还有一个比较常用的属性是DISPLOGIN,该属性控制是否显示SQL SERVER的登录表单

第3个参数： 1 - 显示登录表单，如果登录信息(用户名，密码)不完全 2 - 总显示登录表单 3 - 不显示登录表单

例如：
=SQLSETPROP(lnHandle, "DISPLOGIN",1)

当使用SPT技术时，为节约连接数减少服务器负担，需要经常连接和断开SQL SERVER，有时候你并不希望每次连接都让用户登录，当用户首次登录后，可以把名字和密码存起来，以后的连接可以自动登录了。

谈谈VFP和SQL SERVER搭配做C/S系统 (六) -- SQL SERVER的索引

作为VFP程序员，我们深知索引对表的重要性，好的索引可以大大缩短程序读取数据的时间。
索引对于SQL SERVER数据表来说，同样是很重要的。你可以做个简单的测试。

1. 索引测试

从SQL SERVER程序组里或者ENTERPRISE MANAGER的TOOLS菜单上打开QUERY ANALYZER工具.
连接到你的SERVER后, 选择QUERY菜单上的CURRENT CONNECTION OPTIONS(当前连接选项),
把SHOW STATS TIME 和SHOW STATS I/O两个选项打勾,然后点OK. 这两个选项将会告诉你命令的运行时间和读写次数.
如果你的SQL命令里用到多个表,还可以选上QUERY菜单上的SHOW EXECUTION PLAN, 它会告诉你的命令在各各表上所花费的时间.

选一个没有索引的大小适当的表,然后执行一条SELECT命令,你会看到运行时间.
比如我用了表CHQ,有将近40万条记录, 执行下面的命令,返回5000多个记录

select * from Chq where date >=′02/01/2001′ and date<=′02/28/2001′

显示的时间和读写情况:
Table ′chq′. Scan count 1, logical reads 13145, physical reads 0, read-ahead reads 13152.
SQL Server Execution Times: CPU time = 5781 ms, elapsed time = 23526 ms.

然后我在CHQ表上对DATE字段建立索引,再运行相同的SELECT, 得到如下结果:
Table ′chq′. Scan count 1, logical reads 3965, physical reads 14, read-ahead reads 0.
SQL Server Execution Times: CPU time = 704 ms, elapsed time = 6432 ms.

比较两个结果可以看出, 建立索引后,逻辑读写和预读写次数大大减少, 占用CPU时间从5.781秒减少到了0.704秒 总运行时间从23.526秒减少到6.432秒
如果运行更复杂的命令,效果会更加明显.

2. 了解SQL SERVER的索引

SQL SERVER的数据文件和索引文件都是以页为单位存放的,每页是8K. 相当于把磁盘划分成8K大小的块,以块为单位存放数据.
了解这一点是非常重要的, 它能帮助你理解下面的内容.

SQL SERVER的索引有2大类,
一类是CLUSTERED(物理索引),每个表只能有一个, 记录在磁盘上存放时完全按照物理索引的顺序.
另一类是NONCLUSTERED(逻辑索引), 记录顺序存放在索引表里.

不管是哪类索引,只要能到达目的,索引表达式包含的字段)越简单越有效.

什么字段作为物理索引最合适?

有两个原则: 一是在根据范围来选择记录时,哪个字段最有效, 二是在往磁盘上写记录是不会引起热点(HOT SPOT).
热点是指,大量读写发生在磁盘的同一区域,引起I/O瓶颈效应,降低运行速度.

我们用例子来看看这些原则怎么用.
假如有个单据表, 包含这些字段: 单据号,单据日期,金额,销售地点,已经其它数据.
哪个适合作为物理索引呢? 一般的想法是用没有重复值的字段(相当于VFP里的主键).

a. 用单据号, 其实这不是恰当的选择. 因为很少有根据单据的范围来选择记录的, 比如你很少用命令来查询单据号大于或者小于某个值的记录. 而且在输入时,也比较容易产生热点, 因为插入的记录一般都加在表尾,当有大量用户同时输入时,会产生热点. 单据号不符合上面的2条原则.

b. 用单据日期, 似乎是个比较适当的选择. 因为经常需要用日期范围来选择记录, 但同样会因为大量用户同时输入最新单据而在磁盘上引起热点.

c. 用销售地点, 这应该是最恰当的物理索引人选了. 因为我们会经常根据地点来选择记录, 而在同一时刻插入的记录也不太可能来自同一个销售地点,避免了热点.

当然,不同的环境下,你的选择可能不同,比如你没有成千上万的用户,就不太需要考虑热点问题. 但要记住, 有唯一值的字段并不是物理索引的最佳选择.

3. 选择适当的FillFactor

在SQL-CREATE INDEX命令里,有个FILLFACTOR选项, 这个选项对索引的效率是有很大作用的.
FILLFACTOR是指建立索引时, 每一页存放数据的填满程度, 比如100%,表示把索引文件每一页都填满,隐含值是0 (100%).

如果你的表经常需要插入记录, 选择适当的FILLFACTOR就很重要了. 如果总是用100%, 当你插入一个新记录时, 由于每页都是满的, 就需要进行分页操作(把当前页分成2页),频繁的分页操作会占用服务器的资源和时间,也降低索引文件的效率. 而如果你的FILLFACTOR不是100%,比如设为80%, 在插入新记录时,由于当前页还有空间, 可以直接加入不需要分页. 当然, FILLFACTOR太低也会降低效率,因为那样的话,页数就多了,搜索数据用的时间长,占用空间也多.

如果是只读表,或者插入记录很少的表,用100% FILLFACTOR最好.

对于经常插入新记录的表,需要定期重建索引, 因为索引文件随着页数的增加, 效率会越来越低. 因为索引都是树状结构, 当下面的页加得没有规律时, 连接就会变乱, 重建索引可以重新整理树状结构.

4. 索引分析工具

在QUERY ANALYZER的QUERY菜单里,还有个选项PERFORM INDEX ANALYSIS.
在输入你的命令后,选择这一项, SQL SERVER会对你的命令进行分析,然后告诉你建立什么样的索引对当前命令最有利.
可以把你程序里常用的命令放到这里进行分析,找出最恰当的索引.

本文来自编程入门网：