VFP 愛用者社區

Ruey · 註冊時間: 2003-03-12 文章: 1698 來自: tunglo 第 1 樓

VFP與SQL遠端異構資料庫

文/陳純

　　熟悉 VFP的朋友都知道，在 VFP 裏我們可以使用遠端視圖 (Remote View) 和 SPT(SQL Pass Through) 技術控制遠端異構資料庫。這些技術其實是 VFP 對 ODBC 的 API 的封裝，所以對於用戶來說訪問遠端資料庫就像操作傳統的DBF一樣簡單。關於這兩種技術的使用，完全可以洋洋灑灑地寫下一本書，鑒於本文主題及篇幅，這裏僅枚舉 SPT 技術訪問遠端資料的應用。

　　很多人認為有了遠端視圖這樣直觀、簡單的工具，為什麼還需要 SPT 呢？確實 SPT 較遠端視圖難以掌握，但細細體會你會發現：遠端視圖其實是對 SPT 的視覺化工具！SPT 較遠端視圖更具威力，遠端視圖提供的功能只是 SPT 的一個子集。其優勢和劣勢主要體現在以下幾個方面：

　　l、SPT 的優勢

　　1）一次得到多個Cursor；

　　2）執行除 Select 以外的其他 SQL 語句，如 Insert、Update、Delete等；

　　3）執行遠端資料庫的存儲過程；

　　4）執行遠端資料庫的特殊函數、命令等；

　　5）事務管理。

　　2、SPT 的劣勢

　　1）沒有圖形用戶介面；

　　2）必須人工維護連接；

　　3）得到的Cursor默認是“可讀寫”Cursor，要使它成為“可更新”Cursor必須經過設置才行。

　　下面就順著我們對 SPT 的認識，來流覽一下這個偉大的工具吧！（注意：本文所有常式均使用 SQL Server的NorthWind 資料庫演示）。

　　管理連接

　　l、建立連接

　　注意：本文所有示例的代碼若用到連接的，默認採用“建立連接”代碼中產生的連接控制碼 “CON”。

　　WAIT ' 連接到 SQL Server 上去 ' NOWAIT NOCLEAR WINDOW

　　SQLSETPROP(0,"DispLogin" ,3) &&&& 設置環境為“從不顯示 ODBC 登錄對話方塊”。

　　CON=SQLSTRINGCONNECT("driver=SQL Server;Server=BOE;Uid=sa;pwd=;database=northwind")

　　*假定 SQL Server 伺服器名為 BOE, 用戶 ID 是sa, 口令是空串

　　*如果你的 SQL Server 的伺服器名, 用戶 ID, 口令與上不同，請修改以上代碼中的相關部分以符合你系統中的設置

　　WAIT clear

　　IF con<=0

　　MESSAGEBOX(' 連接失敗 ',64,' 連接到 SQL Server 上去 ')

　　ELSE

　　MESSAGEBOX(' 連接成功 ',64,' 連接到 SQL Server 上去 ')

　　ENDIF

　　2、斷開連接

　　SQLDISCONNECT(CON)

　　一次得到多個Cursor

　　我們可以用一次 SPT 傳回多個Cursor，如下：

　　cSQL="SELECT * FROM EMPLOYEES"+CHR(10)+"SELECT * FROM CUSTOMERS"+CHR(10)+"SELECT * FROM PRODUCTS"

　　?SQLEXEC(con,cSQL,"TEMP")

　　SQLEXEC( ) 的返回值表示Cursor的數量，這裏返回 3 。這三個Cursor分別以 TEMP、TEMP1和TEMP2 命名。

　　執行其他 SQL 語句

　　下面我們嘗試執行SQL Server以外的SQL語句：

　　cSQL="IF EXISTS(SELECT * FROM CUSTOMERSswheres CUSTOMERID='TEST')"

　　cSQL=cSQL+" DELETE FROM CUSTOMERSswheres CUSTOMERID='TEST'" cSQL=cSQL+" ELSE INSERT CUSTOMERS(CUSTOMERID,COMPANYNAME) VALUES('TEST',' 這是一個測試！ ')"

　　IF SQLEXEC(CON,cSQL)<=0

　　MESSAGEBOX(' 執行失敗 ',64,' 發送語句到 SQL Server 上去 ')

　　ELSE

　　MESSAGEBOX(' 執行成功 ',64,' 發送語句到 SQL Server 上去 ')

　　ENDIF

　　行文至此，也許有朋友會問：如果 SQL 語句中 CUSTOMERID 是一個變數怎麼辦呢？其實我們可以通過兩個常用的解決方案來解決：

　　1、拼接字串

　　CUSTID='TEST'

　　cSQL="IF EXISTS(SELECT * FROM CUSTOMERSswheres CUSTOMERID='"+CUSTID+"')"

　　cSQL=cSQL+" DELETE FROM CUSTOMERS swheresCUSTOMERID='"+CUSTID+"'"

　　cSQL=cSQL+" ELSE INSERT CUSTOMERS(CUSTOMERID,COMPANYNAME) VALUES('"+CUSTID+"',' 這是一個測試！ ')"

　　?SQLEXEC(CON,cSQL)

　　2、SPT 標準變數傳遞法

　　CUSTID='TEST'

　　cSQL="IF EXISTS(SELECT * FROM CUSTOMERSswheresCUSTOMERID=?CUSTID)"

　　cSQL=cSQL+" DELETE FROM CUSTOMERSswheresCUSTOMERID=?CUSTID"

　　cSQL=cSQL+" ELSE INSERT CUSTOMERS(CUSTOMERID,COMPANYNAME) VALUES(?CUSTID,' 這是一個測試！ ')"

　　?SQLEXEC(CON,cSQL)

　　特殊函數和命令

　　如果在 SQL Server 中你有足夠的許可權，通過 SPT 使用遠端資料庫的特殊函數和命令，你可以完全控制 SQL Server ，這裏我們就演示“怎樣取得資料庫伺服器的時間”：

　　?SQLEXEC(con,"select getdate() as serverdatetime","temp1")

　　?temp1.serverdatetime

　　USE IN ("temp1")

　　事務管理

　　在一些複雜的應用中，往往會有一項操作影響幾個表的情況。就用戶端來說，發送到遠端資料庫的資料變動可能來源很多：表緩衝的多行記錄的變動，行緩衝的單行記錄變化，以及前文我們演示的直接用 SQL 語句傳遞的資料維護，林林總總……怎樣把這些更新行為控制在一個事務中呢！要麼一起成功，要麼一起回滾。

　　cSQL="DELETE FROM CUSTOMERSswheresCUSTOMERID='BLAUS'"+CHR(10)

　　cSQL=cSQL+"INSERT CUSTOMERS(CUSTOMERID,COMPANYNAME) VALUES('TEST1',' 這是一個測試！ ')"

　　SQLSETPROP(CON,"Transactions" ,2)&&&& 開始一個事務

　　IRETURN=SQLEXEC(CON,cSQL)

　　IF IRETURN=1

　　SQLCOMMIT(CON)&&&& 事務交付

　　ELSE

　　SQLROLLBACK(CON)&&&& 事務回滾

　　ENDIF

　　SQLSETPROP(CON,"Transactions" ,1)&&&& 重新回到自動事務處理狀態

　　&&&&就本例而言，“DELETE FROM CUSTOMERSswheres CUSTOMERID='BLAUS'”總是不能執行的，SQL Server會返回出錯揭示：

　　&&&&DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_Orders_Customers'.

　　&&&&The conflict occurred in database 'Northwind', table 'Orders', column 'CustomerID'.

　　&&&&所以這筆事務總是被回滾的！！

　　從常式中可以看到，我們開啟的事務其實是針對“連接”的，也就是說通過該“連接”的所有資料更新都包含於事務中，直到事務被回滾或交付。

　　SQLSETPROP(CON,"Transactions" ,2 )，其實是開啟了人工事務處理，也就是說必須由用戶明確地給出交付或者回滾指令，事務才會結束。所以筆者以為：完成一筆事務以後，應執行 SQLSETPROP(CON,"Transactions" ,1 ) 將“連接”的事務模式設為默認的“自動”，這樣可以防止用戶陷入未知的事務中去。

　　SPT的基本操作還不止這些，以後我們還會為讀者朋友介紹其他一些基本操作。如果朋友們能掌握這些基本操作，就能編寫不錯的 C/S 程式了。雖然本文是用 SQL Server 作為遠端資料庫，但是如果你使用 DB2和Oracle等，在 VFP 中也可以進行同樣的處理。

　　本文開始已提到 VFP 在這方面的內容很廣泛，寥寥千言當然不能盡言，有興趣的朋友可以去訪問www.boeworks.com，以便查閱更多的內容。OK，希望有機會與大家一起討論這方面的問題。

syntech · 發表於: 星期一一月 26, 2004 11:17 am 文章主題:

Ruey · 發表於: 星期二一月 27, 2004 1:58 am 文章主題:

syntech · 發表於: 星期二一月 27, 2004 10:15 am 文章主題:

依上例,傳回來的Cursor 的確會叫 TEMP,TEMP1,TEMP2,
可是比較具可讀性的,卻應該叫 EMPLOYEES, CUSTOMERS,PRODUCTS,
如果別名只能叫TEMP,TMEP1,TEMP2,那與工作區編號有何不同?

取得多個cursor,實務上的需求在於若訂單檔為master及detail分開兩個檔案時,
以此法可一次取得master cursor及detail cursor,
若搭配 SQLPREPARE(),則效果更好,

cORDER_NO = '0001'
cSQL="SELECT * FROM ORDERMASTER WHERE ORDER_NO =?cORDER_NO"+CHR(10)+;
　　　　"SELECT * FROM ORDERDETAIL WHERE ORDER_NO =?cORDER_NO"
=SQLPREPARE(con,cSQL,"ORDER")
=SQLEXEC(con)
以後只要下 (當然有些隱藏條件)
cORDER_NO = '0002'
=SQLEXEC(con)
就能取得訂單號碼為 '0002' 的 Cursor,無須動不動就重下sqlcommand

但是為了取得三種具可讀性的Cursor,
必須要這樣做:
cSQL="SELECT * FROM EMPLOYEES"
?SQLEXEC(con,cSQL,"EMPLOYEES")

cSQL="SELECT * FROM CUSTOMERS"
?SQLEXEC(con,cSQL,"CUSTOMERS")

cSQL="SELECT * FROM PRODUCTS"
?SQLEXEC(con,cSQL,"TEMP")

也就是您所說的 "單獨下達" ,可是工作量就加倍了,

cursor取回來之後似乎就無法再換一次別名,至少我試過作不出來,可否賜教,謝謝

Ruey · 發表於: 星期五一月 30, 2004 10:50 am 文章主題:

工作量確實會加倍

但可從資料庫最佳化.或查詢最佳化,操作流程等去改善

可以轉各彎改名稱
cSQL="SELECT * FROM PRODUCTS"
?SQLEXEC(con,cSQL,"TEMP")

select * from temp into cursor cur_product

syntech · 發表於: 星期一二月 02, 2004 11:15 am 文章主題:

所以說:"不能自由指定各 cursor 的別名是最大敗筆"

"select * from temp into cursor cur_product"
這樣子有問題,
在SPT下無法作成"可更新"Cursor吧!?

Ruey · 發表於: 星期一二月 02, 2004 11:39 am 文章主題:

1.這方面資料已經有文章.多用搜尋器吧!!

如何讓Cursor可更新?
http://vfp.sunyear.com.tw/viewtopic.php?t=128&highlight=%A5i%A7%F3%B7s

syntech · 發表於: 星期一二月 02, 2004 2:22 pm 文章主題:

我想我沒有解釋清楚,
用SPT傳回的CURSOR,
若用 select ... into cursor 所產生的CURSOR來更換別名,
這個新的CURSOR應該是不能作成"可更新CURSOR",
在利用5個步驟設定成"可更新CURSOR"時,
會出現 ERR_CODE 1468 "Property is invalid for table cursors "

盡信書,不如無書,
有些時候還是要行萬里路的.

我不否認VFP的CURSOR操作很好用,
但是VFP CURSOR的操作限制應該要徹底弄清楚,
才不會真的拿SPT賺錢時才知道有些事情不是想像中那樣.

"不能自由指定各 cursor 的別名是最大敗筆"

syntech · 發表於: 星期一二月 02, 2004 3:26 pm 文章主題:

好像解釋不太清楚,以此例來說:

cSQL="SELECT * FROM PRODUCTS"
?SQLEXEC(con,cSQL,"TEMP")

select * from temp into cursor cur_product

直接這樣拿去設定"可更新CURSOR",
CURSORSETPROP("Tables",".....","cur_product")
CURSORSETPROP("KeyFieldList",".....","cur_product")
CURSORSETPROP("UpdatableFieldList" ,"....","cur_product")
CURSORSETPROP("UpdateNameList","....","cur_product")
CURSORSETPROP("SendUpdates" ,.t.,"cur_product")

就會出現 1468,因為 cur_product 被認為是 table cursor,
不是所謂 view cursor

若改用 USE DBF(TEMP1) AGAIN ALIAS cur_product,
DBF(TEMP1) 傳回值可不是後端的某個table name,
而是本地hdd上的某個暫存檔的檔名,
不管你是不是先把 TEMP 設定為"可更新cursor",
(註.對應的後端table name 是用 cursorsetprop()設定的,與本地暫存檔無關,
用 DBF() 是找出這ALIAS 對應成本地的TABLE NAME,以此例為本地hdd上的某個暫存檔的檔名)
當用 USE DBF(TEMP1) AGAIN ALIAS cur_product 時,
就會出現 ERR_CODE 1 File ........XXXXX.TMP does not exist

Ruey · 發表於: 星期三二月 18, 2004 11:03 pm 文章主題:

目前是沒辦法.做到你說的方式.

Embarassed

syntech · 發表於: 星期四二月 19, 2004 11:03 am 文章主題:

唉.....

M$就是這樣,每每都要留下"進步的空間",
雖然作成procedure是沒差,
但是就是不爽. Evil or Very Mad