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會對你的命令進行分析,然後告訴你建立什麼樣的索引對當前命令最有利.
可以把你程序裡常用的命令放到這裡進行分析,找出最恰當的索引。