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 SERVERSQL 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, STRINGlnWindowHandle=0lcODBCDriver=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 &不存在*添加新的ODBClreturn=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)ENDIFENDIF2。删除ODBC数据源*用完后，可以在表单DESTROY事件里删除ODBC。 如果不想重复设置参数，可以把这些参数加到表单作为属性。DECLARE INTEGER SQLConfigDataSource IN odbccp32 INTEGER, INTEGER, STRING, STRINGlnWindowHandle=0lcODBCDriver=SQL ServerlcODBCName=SharedDatalcODBCDesc=Shared Data SourcelcODBCServer=DEVSQLlcODBCDatabase=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=0MessageBox(删除ODBC源失败,16,BUFFER)ENDIFENDIF*清除DLLCLEAR DLLS3. 从VFP连接到SQL SERVERlnHandle=SQLConnect(SharedData,用户名”，“密码”)If lnHandle0 &连接成功*从库里获得数据(比如从EMP表里得到部门号为01的职工)lnReturn=SQLExec(lnHandle,Select * from Emp Where cDept=01,CursorEmp)If lnReturn0 &运行成功BrowseElse &失败&出错处理EndIfElse &连接失败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=ZhanglcFName=SanlnReturn=SQLEXEC(连接句柄，“Update Emp Set cLName=?lcName, cFName=?lcFName Where cEmpNo=733000)如果返回值lnReturn0，就更新成功了插入记录，或者逐条修改记录时，只能每次操作一条记录。比如要把临时表TmpEmp里的记录加到SQL SERVER的EMP表里，就得用循环Select TmpEmpScanlnReturn=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 0Use DBF(TmpEmp) Again Alias EmpUse 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 lnHandle0& 设置成手工事务处理模式，由代码来控制= 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. 用销售地点, 这应该是最恰当的物理索引人选了. 因为我们会经常根据地点来选择记录,