[VFP] SPT Updatable

在 VFP 中對 SQL Server 進行資料存取，有三種模式分別為 Remote View、SQL Pass Through（SPT） 和 CursorAdapter（CA），我一直是以 CA 為主來對資料進行存取、SPT 為查詢資料主力。

無論是 SPT 或 CA 在前端產生的 Cursor 要對後端資料庫進行新增、更新和刪除動作（updatable），有四個屬性一定要設定，分別為 Table、KeyFieldList、UpdatableFieldList、UpdateNameList，在 CA 可以利用勾選方式輕鬆完成設定，但在 SPT 中就必須一個一個 Key，其中又以 UpdatableFieldList 和 UpdateNameList 兩個屬性最讓人討厭，因此才會想寫個 PRG 來設定屬性。

• 參數說明 ：
• tcConnection：傳入連線。
• tcSQL： 傳入產生此 CURSOR 的 T-SQL 語法。
• tcTableName：SQL Server 內的 TableName，因會利用 INFORMATION_SCHEMA 的 VIEW，所以使用者必須明確指定要抓取 SQL Server 內的哪一個 Table。
• tcAlias：當使用者用 SPT 抓取資料後，可能會另外給 CURSOR 別名，此時必須針對此 CURSOR 別名進行設定。
• tnBuffering：設定資料緩衝，預設為 5

LPARAMETERS tnConnection AS number ,tcSQL AS string ,tcTableName AS string,tcAlias AS string,tnBuffering AS number

LOCAL lcTable,lcKeyFieldList,lcUpdatableFieldList,lcUpdateNameList,lcContent,lcSearchKey,lcTableName,lcSQL,lcTable_Name,lcColumn_name AS string
LOCAL lnCount,lnResult AS number
LOCAL liKey,liSpace,liDot AS integer  

lcTable = ""
lcKeyFieldList = ""
lcUpdatableFieldList = ""
lcUpdateNameList = ""

IF VARTYPE(tcConnection) = "L" OR tnConnection < 0
   WAIT WINDOWS "連線有問題"
   RETURN
ENDIF

IF VARTYPE(tcTableName) = "L" OR EMPTY(tcTableName)
   WAIT WINDOWS "沒有指定資料表名稱"
   RETURN
ENDIF

-- 沒有指定別名，表示別名跟資料名稱一樣
IF VARTYPE(tcAlias) = "L"
   tcAlias = tcTableName
ENDIF
IF !USED(tcAlias)
   WAIT WINDOWS "此 Cursor 並不存在，無法設定自動更新"
   RETURN
ENDIF

-- Buffering 預設為 5
IF VARTYPE(tnBuffering) = "L"
   tnBuffering = 5
ENDIF

CREATE CURSOR curTableName (TableName C(30)) -- 用來紀錄抓取的 TableName 避免重覆
FOR lnCount = 1 TO 2

   lcContent = tcSQL -- tcSQL 要跑兩次，所以把它另外存進一個變數

   IF lnCount = 1
      -- 找出 Primary Key
      TEXT TO lcSQL TEXTMERGE NOSHOW
        SELECT T1.TABLE_NAME,T1.CONSTRAINT_Name,T2.COLUMN_NAME
        FROM
            INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS T1
            JOIN
            INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS T2 ON T1.TABLE_NAME = T2.TABLE_NAME AND T1.CONSTRAINT_NAME = T2.CONSTRAINT_NAME
        WHERE T1.CONSTRAINT_TYPE = 'PRIMARY KEY' AND T1.Table_Name = ?tcTableName
      ENDTEXT

      lcSearchKey = "FROM"
   ELSE
      -- 找出全部 Columns
      TEXT TO lcSQL TEXTMERGE NOSHOW
        SELECT TABLE_NAME,COLUMN_NAME
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_NAME = ?tcTableName
      ENDTEXT

      lcSearchKey = "JOIN"
   ENDIF
   lnResult = SQLEXEC(tnConnection,lcSQL,"curData")
   IF lnResult < 0
      AERROR(laError)
      MESSAGEBOX(laError(2))
      RETURN
   ELSE
      IF RECCOUNT("curData") = 0
         WAIT WINDOWS "SQL Server內找不到此 Table "
         RETURN
      ENDIF
   ENDIF

   -- 組合 Table property
   DO WHILE ATC(lcSearchKey,lcContent) <> 0

      liKey = ATC(lcSearchKey,lcContent) -- lcSearchKey 有兩個 FROM 和 JOIN
      lcContent = ALLTRIM(SUBSTR(lcContent,liKey + 4,LEN(lcContent))) -- 縮短字串，並把關鍵字前的空格通通刪除
      liSpace = ATC(SPACE(1),lcContent) -- TableName 後面一定會有空格
      lcTableName = LOWER(ALLTRIM(SUBSTR(lcContent,1,liSpace - 1))) -- 抓取 TableName

      liDot = RAT(".",lcTableName) -- 避免使用者輸入完整名稱 EX：DBName.Owner.TableName OR Owner.TableName
      IF liDot <> 0
         lcTableName = SUBSTR(lcTableName,liDot + 1, LEN(lcTableName))
      ENDIF

      SELECT TableName FROM curTableName WHERE ALLTRIM(TableName) == lcTableName INTO CURSOR curTemp
      IF RECCOUNT("curTemp") = 0
         lcTable = IIF(EMPTY(lcTable),lcTableName,lcTable + "," + lcTableName)
         INSERT INTO curTableName (TableName) VALUES (lcTableName)
      ENDIF
      USE IN SELECT("curTemp")

      lcContent = ALLTRIM(SUBSTR(lcContent,liSpace,LEN(lcContent)))
   ENDDO

   -- 組合 KeyFieldList、UpdatableFieldList 和 UpdateNameList
   SELECT curData
   SCAN

      IF lnCount = 1
         lcColumn_name = LOWER(ALLTRIM(curData.Column_name))

         lcKeyFieldList = IIF(EMPTY(ALLTRIM(lcKeyFieldList)),lcColumn_name,ALLTRIM(lcKeyFieldList) + "," + lcColumn_name)
      ELSE
         lcTable_Name = LOWER(ALLTRIM(curData.Table_Name))
         lcColumn_name = LOWER(ALLTRIM(curData.Column_name))

         lcUpdatableFieldList = IIF(EMPTY(ALLTRIM(lcUpdatableFieldList)),lcColumn_name,ALLTRIM(lcUpdatableFieldList) + "," + lcColumn_name)
         lcUpdateNameList = IIF(EMPTY(ALLTRIM(lcUpdateNameList)),lcColumn_name + " " + lcTable_Name + "." + lcColumn_name,ALLTRIM(lcUpdateNameList) + "," + lcColumn_name + " " + lcTable_Name + "." + lcColumn_name)
      ENDIF

      SELECT curData
   ENDSCAN

ENDFOR

CURSORSETPROP("Tables",lcTable,tcAlias)
CURSORSETPROP("KeyFieldList",lcKeyFieldList,tcAlias)
CURSORSETPROP("UpdatableFieldList",lcUpdatableFieldList,tcAlias)
CURSORSETPROP("UpdateNameList",lcUpdateNameList,tcAlias)
CURSORSETPROP("SendUpdates",.T.,tcAlias)
CURSORSETPROP("Buffering", tnBuffering, tcAlias)

USE IN SELECT("curData")
USE IN SELECT("curTableName")

• 使用簡易說明

lnHandle = SQLSTRINGCONNECT(...........)
TEXT TO lcSQL TEXTMERGE NOSHOW PRETEXT 2
   SELECT A.*,B.ColName
   FROM TableA AS A JOIN TableB AS B ON A.Key = B.Key
ENDTEXT
lnResult = SQLEXEC(lnHandle,lcSQL,"cTemp")
IF lnResult < 0
   AERROR(laError)
   MESSAGEBOX(laError(2))
   RETURN
ENFIF

DO SPTUpdate WITH lnHandle,lcSQL,"TableA","cTemp",5

這個 PRG 只經過簡單測試可以 Updatable，沒有使用在實際環境中過。

參考資料：
• MakeUpdatable in leafe
• 簡單的SPT物件示範