VFP資料庫入門及後端資料庫(postgresql,mysql)

本人是由foxpro(dos版)轉為windows(vfp),還好沒有被淘汰,由於參考文件資料有限(書籍很少),雖然程式還是會寫,但是有些功能都 沒有用到,而實例也不多,後來碰到vfp當前端輸入介面,而sql為後端,直接使用連線指令,則會被sql指令煩死,這樣的情況已經3,4年,也就是說我 碰到了瓶頸,但在一次的測試,讓我覺得之前的工作都是白費了,非常簡單工作被我複雜化,因此將其寫下,雖然對別人可能是很簡單,但對我來講可是阻礙好幾年 的關卡
本文參考vfp8.0的help檔
在vfp的dbc是vfp最重要的 功能之一(這是必練的功,會此才能自由的運用資料庫)
首先在vfp的資料庫有一種dbc 的物件主要包含了
資料庫表格-一個本地dbf的檔案,這是內定支援的資料表格
本地資料集-local view(由本地的資料庫檔案或是遠端資料集所產生的假的資料庫)
遠端資料集-remote  view (由遠端資料庫所產生假的資料庫)

原本看到上述沒有覺得和其它的sql的說明文件有所不同,但不同就在這裏
遠端資料集-是可以透過odbc來存取遠端的資料庫,合併不同的表格成為單一表格,但重點是這個view資料庫是可以使用sql指令操作,新增、修改、刪 除,也就是它對你來說是一個本地的表格,你可以直接使用本地的命令來修改它,至於回寫到遠端的資料庫的動作除了主要設定資料庫緩衝模式3,5及兩個主要命 令tableupdate(),tablerevert(),你可以專注在本地表格的操作,而忘記遠端表格的存在,不用費心處理遠端表格的指令

而local view有什麼不同呢?其主要的不同是資料庫的來源是本地的dbf檔

#在sql伺服器上create view意味著你只能查詢(取回的資料是唯讀的),但在VFP的view是可以設為可寫的,而在更新完畢後,它會自動寫回sql伺服器
view設定可寫入及不可寫入
在更新條件內，將發送sql更新打勾，另外你可以看到有一個鉛筆的圖示代表該欄位是否可寫入，不打勾代表唯讀.
再來是考慮sql where子句,我們以實例來講
一般表格可以成二種
1.合併不同的欄位變成一key,可以成為不可重複性的記錄
2.可重複性的記錄,例如一個客戶可以買非常多不同的貨品,而同種商品可以如下
   原子筆-10支
   原子筆-10支
   橡皮擦-2個
考慮2.當客戶修改如下
   原子筆-12支
   原子筆-10支
   橡皮擦-2個
照順序,你判別是修改第一筆的原子筆,但是在資料庫並無法得知是那一個,但是既然如此定義表格那就是表示修改任何一個都可以,只要內容相同就好了,此時在 sql where子句及key部分要設好否則資料的更新,新增,刪除等會出錯,它不同於本地資料庫可以直接指定修改或刪除的記錄所在筆數,由於本條件無法合乎所 有的要求,有時要配合"更新使用",(先delete再insert),對多筆時最好使用這個功能
update 指令在客戶相同,貨物相同,數量不同
SQL WHERE 子句
根據sql的語法,sql where 是指令變更的條件(原文的help說明解譯中說偵測有沒有人變更過,不懂它在說什麼)
1.僅鍵值
2.鍵值及可更新的
3.鍵值及已變更的(預設)
4.鍵值及時間stamp
我的說明是:
UPDATE 表格名稱  設定變更的欄位  where條件
當條件設錯了則更新的動作也就錯了

其它事項
1.要建立remote view 要先開啟一個資料庫
  open database .....
2.再開啟或建立一個connection
  create connections
2.最後建立view
  create sql view
在資料庫中某一些欄位會參考別的表 格
單純的做法
在資料環境中加入多個表格,不必關連,
使用LOCATE 的命令
1.在欄位輸入後驗證(VALID)
2.在有變動時驗證(INTERACTIVE CHANGE EVENT)
原始表格的索引去那裏?
使用create sql view 或是sqlexec()的命令,查詢回來的資料庫並不包含索引,也就是說如果你有引用到索引要自己建立,也就是說其實是兩個不同資料庫(遠端及本地),因 此索引不可能相同的,抓回來也沒有問
在遠端資料庫有一個表格包含了三個 欄位
create table blentry (bl_no char(12) primary key,port char(3),port_name char(30))
我使用create view wizard ,建立key bl_no,每一個欄位都可以被更新,查看它的sql指令如下
SELECT *;
 FROM ;
     public.blentry Blentry

DBSetProp(ThisView,"View","SendUpdates",.T.)
DBSetProp(ThisView,"View","BatchUpdateCount",1)
DBSetProp(ThisView,"View","CompareMemo",.T.)
DBSetProp(ThisView,"View","FetchAsNeeded",.F.)
DBSetProp(ThisView,"View","FetchMemo",.T.)
DBSetProp(ThisView,"View","FetchSize",100)
DBSetProp(ThisView,"View","MaxRecords",-1)
DBSetProp(ThisView,"View","Prepared",.F.)
DBSetProp(ThisView,"View","ShareConnection",.F.)
DBSetProp(ThisView,"View","AllowSimultaneousFetch",.F.)
DBSetProp(ThisView,"View","UpdateType",1)
DBSetProp(ThisView,"View","UseMemoSize",255)
DBSetProp(ThisView,"View","Tables","public.blentry")
DBSetProp(ThisView,"View","WhereType",3)

DBSetProp(ThisView+".bl_no","Field","DataType","C(12)")
DBSetProp(ThisView+".bl_no","Field","UpdateName","public.blentry.bl_no")
DBSetProp(ThisView+".bl_no","Field","KeyField",.T.)
DBSetProp(ThisView+".bl_no","Field","Updatable",.T.)

DBSetProp(ThisView+".port","Field","DataType","C(3)")
DBSetProp(ThisView+".port","Field","UpdateName","public.blentry.port")
DBSetProp(ThisView+".port","Field","KeyField",.F.)
DBSetProp(ThisView+".port","Field","Updatable",.T.)

DBSetProp(ThisView+".port_name","Field","DataType","C(30)")
DBSetProp(ThisView+".port_name","Field","UpdateName","public.blentry.port_name")
DBSetProp(ThisView+".port_name","Field","KeyField",.F.)
DBSetProp(ThisView+".port_name","Field","Updatable",.T.)
多個表格的remote view
我想使用多個view
blentry (bl_no c(12),port c(3),port_name c(5))
port    (port c(3),port_name c(5))
其中blentry.port 是引用自port.port(也就是port.port必須存在,blentry.port才可以使用)
同理blentry.port_name引用port.port_name
如何設定資料庫呢?
請不要在建立remote view加入這兩個表格並加入關連,會產生下面問題:
如果是新增,它會同時新增兩個表格,和你的需求不一樣,你請會更新一個表格
那要如何做了
在form的資料環中加入view(blentry,port),如此你可以同時使用,其關連就不用設定了,自己下指令就可以了
combo(下拉示選單)
你常會碰到如上述的狀況,但引用的欄位想要直接顯示出來

在上圖port就是combo,我想要它顯示blentry.port
但可以選擇是由port.port的項目
設定三個參數
ControlSource =blentry.port
RowSource     =port.port
RowSourceType =6-fields
                   
ControlSource=資料來源

RowSource=本選項的來源
RowSourcetype=如下表,一般的書都有
RowSourceType Source of the List Items
0 None. Programmatically add items to the list.
1 Value
2 Alias
3 SQL Statement
4 Query (.qpr)
5 Array
6 Fields
7 Files
8 Structure
9 Popup. Included for backward compatibility.

本身有兩個函式Additem(),removeitem()
函式ADDITEM("項目名稱")
函式REMOVEITEM(項目的次數)
根據上述combo的設定
並在combo.interactiveChange,中加入下述程式碼,則另一個欄位就會根據你的選擇而變更了
Select port
Locate For  port.port==this.value
If Found()
    thisform.blentry.port_name1=port.port_name
    Thisform.refresh
    WAIT WINDOW "你有改變"
Endif
再來就是多對多的表格
一個form有多個資料庫及一個表格
根據form精靈,產生一對多表單,檢視其資料環境,也是沒有直接設定關連,因此其可能是使用set filter等指令
由於一對多表單,有時from wizard 在新增資料記錄時會不知新增那一個,因此大部份是不能使用,要自訂
參考winziard
grid
RowSource     =blfreight
RowSourceType =1-Alias
column1
controlsource = blfreight.bl_no
column2
controlsource= blfreight.total
上述是我們在grid的設定原則
再來就是自定新增刪除更新儲存等按鈕
資料表格緩衝處理模式
0-不設定緩衝處理
1-使用預設
2-保守模式(記錄),編輯時鎖住,如果記錄指標沒有移動,可以使用TABLEREVERT()復原,TABLEUPDATE()更新
3-開放模式(記錄),編輯時不鎖住,如果記錄指標沒有移動,其它同上
4-保守模式(表格),編輯時所有的記錄均會鎖任,使用TABLEUPDATE()來更新,TABLEREVERT()復原
5-開放模式(表格),編輯時所有的記錄均不會被鎖住,其它同上

也就是2,3在記錄指標移動時會寫回資料表格,一般我是使用5
在使用FORM WIZARD時,記得有使用SELECT,之後必須切回原使用表格,否則,其產生的按鈕,會新增到錯誤的表格
實作一個小型資料庫
(server:postgresql for linux) 公司(表格的新增操作等..使用psql,phppgadmin,webmin,pgadmin3)
(server:mysql for windows )   家裏(表格的新增操作等..使用mysqlcc)

四個主要表格,三個小型表格(代碼表)
我以船務方面的資料庫來實作(我簡化這些表格,以便能清楚的說明)
v_schedule
vvd      c(12) 船名航次
port     c(3) 港口碼
on_board date 裝船日
blentry
vvd       c(12) 船名航次
bl_no     c(12) 提單號碼
port      c(3) 港口碼
port_name c(30) 港名
mark  text   麥頭
desp  text   品名
 freight
vvd   c(12) 船名航次
bl_no c(12) 提單號碼
p     float   先付
c     flaot   預收
container
vvd    c(12)   船名航次
bl_no  c(12)   提單號碼
type   c(4)    櫃型
qty    integer 櫃數

v_code
code   c(3)  船舶代碼
name   c(30) 船名
 p_code
code   c(3)  港口碼
name   c(30) 港名
 c_code
code  c(4)  貨櫃碼
name  c(30) 說明
建立表格
create table v_code (code char(3) primary key,name char(30))
create table p_code (code char(3) primary key,name char(30))
create table c_code (code char(4) primary key,name char(30))
create table v_schedule (vvd char(12) primary key,port char(3),on_board date)
create table blentry (vvd char(12),bl_no char(12),port char(3),port_name char(30),mark text,
                      desp text)
create table freight (vvd char(12),bl_no char(12),p float,c float)
create table container (vvd char(12),bl_no char(12),type char(4),qty integer )
注意:在sever建索引鍵是用來加快server端的尋找速度,不是用在 client端,如果client要用則要自己用


v_schedule(船期)
blentry(提單內容)
freight(提單費用)
conainer(提單櫃子)


blentry->v_schedule是一對多
freight->blentry是一對多
conainer->blentry也是一對多
代碼檔的設定非常簡單
再來就是v_schedule

我們要產生如上圖
當輸入vvd1則右邊label物件v_name會帶出v_code.name
當輸入port則右邊label物件port_name會帶出p_code.name
1.refresh:針對一進入這個畫面所時label的顯示
2.valid:針對在互動時label的顯示
至於combo(port)請參考上面
自定表格,我們使用wizard會 產生上述的按鈕
多個單一表格的from問題題
這裏要講一個技巧,因為我們常會只有使用一個from來編輯一個表格,如一般代碼表格,經測試的結果,我確信下面是最好的處理方法
 
共有四個主要元件
1個pageframe(這裏共有9頁,當然要根據你的基本代碼檔有幾個來改變)
1個grid     (不指定任何recordsource,用途顯示多筆資料,唯讀)
2個button group (重點在編輯button,前三個會按下去會帶去真正的編輯視窗,類似下面
 

我已經將程式簡化,因此所用到的事件很少
重要的事件
form1.show()  -設定grid1的初始的顯示
form1.pageframe1.click()  -切換不同的資料庫,並變更grid1內的資料來源及欄位的名稱
form1.buttongroup1.click() -編輯,帶出編輯視窗
form1.buttongroup2.click() -切換記錄

form1.show()內容(不一定要使用,當第一個表格的欄位數大過其它表格可 以在grid1的參數設定)
select 表格名稱
#自動設定欄寬,空的表格請不要設
#thisform.grid1.autofit()
#因為欄位數在切換時如果有大有小,可能會無法顯示欄位內容,因此我必須使用各表格內的最大欄位數,例如:11
thisform.grid1.columncount=11
#清空欄位抬頭
for i=1 to 11
    with thisform.grid1
         .columns(i).header1.caption=""
    endwith
endfor

with thisform.grid1
     #設定
     #設定欄位的抬頭
     .column1.header1.caption="抬頭"
     .......

endwith
thisform.grid1.refresh

Thisform.pageframe1.click()
#這裏和上述很像,只是多了9個case,當然可以根據你的表格來定
do case
case this.activepage=1
     select 表格1
     thisform.grid1.recordsource=表格1
     for i=1 to 11
         with thisform.grid1
              .columns(i).header1.caption=""
         endwith
     endfor
      *設定欄位抬頭
      with thisform.grid1
           .column1.header1.caption="抬頭"
           ......
      endwith
case this.activepage=2
     select 表格2
.....
endcase
thisform.grid1.refresh

form1.buttongroup1.click()
*進入編輯模式
*會將你按新增(1),修改(2),刪除(3)傳給該表格
ACTIVEPAGE=THISFORM.pageframe1.ACTIVEPAGE
*新增or修改or刪除or查詢
IF THIS.VALUE==4
   THISFORM.RELEASE
ELSE
    DO CASE
    CASE ACTIVEPAGE=1
        DO FORM "本表格的form" WITH THIS.VALUE
    CASE ACTIVEPAGE=2
    ........
    CASE ACTIVEPAGE=9
        DO FORM "本表格的form" WITH THIS.VALUE
    ENDCASE
ENDIF
GO TOP
THISFORM.grid1.REFRESH
現在我們查看子from的事件,假設子from名稱subform
由於通常第一個欄位是主鍵因此考慮如下
1.新增:全部都可編輯
2.修改:第一個欄位不可編輯,其它均可編輯
3.刪除:全部不可編輯
form1.init()
PARAMETER STATUS
public t_status
t_status=status
if status=1
   append blank
endif


form1.activate()
*設定是否可以欄位是否可以修改
do case
   case t_status=1
   *新增
   with this
   .名稱.ENABLED=.F.      
   .名稱.ENABLED=.T.
   ...
   endwith
   case t_status=2
   *修改
   .....
   case t_status=3
   *刪除
endcase
form1.buttongroup1.click()
do case
   case this.value=1
   *確定
    if t_status=3
       *刪除
       delete
    endif
     =tableupdate()
   case this.value=2
   *離開
     =tablerevert()
endcase
thisform.release
*以上你可以套用在不同的代碼定義表格,非常實用
碰到的問題
1.記錄移動按鈕,無法在grid顯示所在的記錄
  在初始grid時要使用selct 表格
2.記錄移動按鈕,如果執行過delete的動作,無法正常動作,
  執行重新查詢=requery()
3.修改表格後執行tableupdate()後requery(),會有錯誤訊息,告訴你資料已經變更,但未commit
  =tableupdte(),只更新目前所在的單筆記錄,=tableupdate(.t.)才會更新全部變更的記錄
4.多個form之間的切換(form1->form2)原來記錄所在不見了....
  將form2的資料環境的參數,auto close table ,auto open table設為.F.