1、一種簡單的powerbuilder10數(shù)據(jù)庫編程介紹1、需求分析與數(shù)據(jù)庫建立進(jìn)行需求分析（需求分析文檔），確定數(shù)據(jù)關(guān)系，建立各種數(shù)據(jù)表，建立數(shù)據(jù)庫(Access)，設(shè)置ODBC數(shù)據(jù)源（控制面板-管理工具-數(shù)據(jù)源ODBC-“用戶DSN”菜單下點(diǎn)“添加”選相應(yīng)的數(shù)據(jù)源驅(qū)動(dòng)程序，如果用Access2003建立的，則選第三項(xiàng)“Driver do Microsoft Access(*.mdb)，然后點(diǎn)擊“完成”-輸入數(shù)據(jù)源名，如mydata，然后點(diǎn)擊“選擇”按鈕選擇建好的數(shù)據(jù)庫，最后點(diǎn)“確定”，如下圖所示）2、界面設(shè)計(jì)（功能界面、重要數(shù)據(jù)表維護(hù)界面）界面設(shè)計(jì)主要分為幾個(gè)部分（1）按照管理系統(tǒng)所需的功能設(shè)

2、計(jì)界面。首先畫出所有可能的業(yè)務(wù)流程（數(shù)據(jù)的各種可能輸入、修改、刪除業(yè)務(wù)，數(shù)據(jù)的輸出、顯示業(yè)務(wù)）（2）按照需要維護(hù)的表設(shè)計(jì)界面（往往給最高權(quán)限管理員直接修改數(shù)據(jù)用）3、開始程序編寫(1) 新建一個(gè)workspace(new-workspace-workspace)(2) 在workspace下建立一個(gè)目標(biāo)(new-Target-application)，可取名frame(3) 在目標(biāo)下建一個(gè)主窗口可取名w_main，窗體名一般以w_開頭（new-PB Object-Window），將其Window Type設(shè)為“mdihelp!”(4) 為主窗口建一個(gè)主菜單可取名m_frame，菜單名一般以m_

3、開頭（new-PB Object-Menu）(5) 程序中設(shè)置ODB ODBC點(diǎn)擊按鈕，選中“ODB ODBC”項(xiàng)，然后單擊右邊“New”按鈕，彈出如下對話框，設(shè)置Profile Name和Data Source，如圖所示。(6) 建立配置文件配置文件取名“config.ini”，內(nèi)容如下：MyDBDBMS=ODBCAutoCommit=FalseDBParm=ConnectString=DSN=mydata;UID=;PWD=(7) 自動(dòng)連接數(shù)據(jù)庫與退出程序關(guān)閉數(shù)據(jù)庫的編寫首先建立Global Variables如下：string gs_userid,gs_username/登錄用戶標(biāo)識、用

4、戶姓名string gs_root_path,gs_ini_path/應(yīng)用路徑和主配置文件路徑其次申明Global External Functions如下：FUNCTION int GetComputerNameA(ref string computername,ref long size) LIBRARY KERNEL32.DLL alias for GetComputerNameA;AnsiFUNCTION long GetCurrentDirectoryA( long nBufferLength, REF string szBuffer ) LIBRARY KERNEL32.DLL a

5、lias for GetCurrentDirectoryA;AnsiFUNCTION long SetCurrentDirectoryA( string szPathName ) LIBRARY KERNEL32.DLL alias for SetCurrentDirectoryA;Ansi/end prototypes在程序的總?cè)肟冢ㄟM(jìn)入“”）Open事件中寫入以下代碼：/ Profile moneyandfriendsstring ls_1/設(shè)置應(yīng)用根目錄gs_root_path = space(255)GetCurrentDirectoryA( 255, gs_root_path )if

6、 right(gs_root_path,1) = thengs_root_path = left(gs_root_path,len(gs_root_path) - 1)end if/設(shè)置配置文件路徑gs_ini_path = gs_root_path + config.iniIF not FileExists ( gs_ini_path ) THENMessageBox( 找不到配置文件 +gs_ini_path,系統(tǒng)配置錯(cuò)誤,stopsign!,ok! )RETURNEND IF/SetProfileString(gs_ini_path,DBMS,ls_1=ProfileString(gs_

7、ini_path,MyDB,DBMS,ODBC)SQLCA.DBMS =ls_1SQLCA.AutoCommit = Falsels_1=ProfileString(gs_ini_path,MyDB,DBParm,error)SQLCA.DBParm = ls_1connect using sqlca;open(w_main)/打開主界面在Close事件中加入如下代碼：disCONNECT USING sqlca;(8) 建立新的具體功能窗體（如w_zichuangti）8.1 設(shè)置窗體BackColor為“Cream”，輸入窗體Title；要在1024*768分辨率下基本滿屏，窗體大小可設(shè)置

8、為“4645*2748” 8.2在新窗體上一般用GroupBox來劃分功能區(qū)域，設(shè)置該控件的字體為“宋體”，大小為“10”，背景顏色為“Cream” 8.3用靜態(tài)文本做功能說明或指示，一般加黑，宋體9號字 示例程序界面如下：(9) 響應(yīng)菜單click事件彈出子窗口（相同子窗口只彈出一次待研究）在菜單下編寫代碼打開窗體（雙擊菜單即可在Click事件下編寫），簡單代碼如下：/選擇菜單時(shí)調(diào)出子窗體window lwopensheet(lw,w_ zichuangti,parentwindow,0,Cascaded! )lw.WindowState = Maximized!(10) 相同的子窗口只讓彈

9、出一次（代研究）(10) 建立數(shù)據(jù)窗口(11) 添加新的pbl文件 一般要用不同的pbl文件分類存儲不同窗體、數(shù)據(jù)窗口和菜單等資源；建立新的pbl文件的方法如下：點(diǎn)擊工具欄上“Library”按鈕，然后在彈出子窗口中進(jìn)入程序所在文件夾，在工具欄左下有“”創(chuàng)建pbl的按鈕。創(chuàng)建好pbl庫文件后在目標(biāo)（如果按前面建立名稱為frame的目標(biāo)，則在“”）上單擊右鍵，選擇屬性在彈出窗口中可添加Library List，如下圖，也可在這里建立新的pbl庫文件(12) 如何編譯出可運(yùn)行程序 首先新建一個(gè)Project(File-new-Project-Application)，如取名p_main 然后打開新

10、建的Project，設(shè)置可執(zhí)行文件生成目錄。(13) DataWindow窗口連通數(shù)據(jù)庫dw_1.settransobject(sqlca)(14) 格式化日期(2007-10-05變?yōu)?f_format_date函數(shù)，輸入string變量ps_date，返回stringif isnull(ps_date) then return if len(ps_date) 0 thenif MessageBox(提示,數(shù)據(jù)沒有保存，是否不保存退出？,Question!,YesNo!) =2 thenreturn 1end ifend if4、其他編程4.1 將數(shù)據(jù)庫中數(shù)據(jù)顯示到dropdownlistb

11、ox的下拉菜單里建立“f_ddlb_populate”函數(shù)，函數(shù)輸入兩個(gè)變量：dropdownlistbox：ddlb_this 要顯示到的空間名string：as_sql 查詢數(shù)據(jù)庫的sql語句函數(shù)代碼如下：ddlb_this.reset()ddlb_this.additem()string ls_disp, ls_dataDECLARE my_cursor DYNAMIC CURSOR FOR SQLSA ;PREPARE SQLSA FROM :as_sql ;OPEN DYNAMIC my_cursor ;FETCH my_cursor INTO :ls_disp, :ls_data;

12、if isnull(ls_disp) then ls_disp = if isnull(ls_data) then ls_data = do while sqlca.sqlcode = 0 ddlb_this.additem(trim(left(ls_disp+space(200),200) + ls_data)FETCH my_cursor INTO :ls_disp, :ls_data;loopCLOSE my_cursor ;as_sql語句里面需要有兩個(gè)輸出如果ddlb的數(shù)據(jù)項(xiàng)比較多，一般選中VScrollBar如下調(diào)用這個(gè)函數(shù)：string ls_sqlls_sql=select z

13、cr_bh, from zcr order by zcr_bh;/舉例f_ddlb_populate(ddlb_bh,ls_sql) /將ddlb_bh列表框里寫入列表項(xiàng)4.2 數(shù)據(jù)窗口常用幾個(gè)函數(shù)dw_deal.settransobject(sqlca)/連接數(shù)據(jù)庫dw_deal.insertrow(number row)：在指定行之前插入一行，如果要在最后一行新增一行，取row=0dw_deal.deleterow(number row)：刪除指定行，row=0時(shí)刪除當(dāng)前行dw_deal.rowcount()：返回?cái)?shù)據(jù)窗口中總行數(shù)dw_deal.setitem(li_n,lxr_zpdz,

14、pic)/設(shè)置某行某列的數(shù)據(jù)integer dwcontrol.AcceptText ( )：Applies the contents of the DataWindow controls edit control to the current item in the DataWindow eger dwcontrol.Filter ( )：根據(jù)給定過濾條件來列出滿足條件的記錄，過濾條件由SetFilter函數(shù)指定integer dwcontrol.SetFilter ( string format )：設(shè)定過濾條件integer dwcontrol.GetColumn (

15、 )：Returns the number of the current column in the DataWindow controlstring dwcontrol.GetColumnName ( )：Returns the name of the current column in the DataWindow control.date dwcontrol.GetItemDate ( long row, string column , DWBuffer dwbuffer , boolean originalvalue )date dwcontrol.GetItemDate ( long

16、 row, integer column , DWBuffer dwbuffer, boolean originalvalue )GetItemStringGetItemString（）：long dwcontrol.GetNextModified (long row, DWBuffer dwbuffer )：SetRow(long row): 設(shè)定指定行為當(dāng)前行GetRow()：獲得數(shù)據(jù)窗口中當(dāng)前行GetSelectedRow ( long row )：獲得指定行row后面第一個(gè)被選中的行，如沒有則返回零integer dwcontrol.SelectRow ( long row, bool

17、ean select )：選中指定行IsSelected ( long row )：判斷某行是否被選中ScrollToRow ( number row )：窗口數(shù)據(jù)滾動(dòng)在指定行，如果row=0則規(guī)東到第一行integer dwcontrol.RowsMove ( long startrow, long endrow, DWBuffer , datawindow targetdw, long beforerow, DWBuffer targetbuffer ) 將一個(gè)數(shù)據(jù)窗口的幾行數(shù)據(jù)移到另一個(gè)數(shù)據(jù)窗口。movebuffer和DWBuffer的范圍為Primary!、Delete!和Filter

18、!string dwcontrol.Describe ( string propertylist )：返回?cái)?shù)據(jù)窗口中控件屬性string dwcontrol.Modify ( string modstring ) ：修改數(shù)據(jù)窗口中控件屬性integer dwcontrol.SetItemStatus(long row, integer column, dwbuffer dwbuffer, dwitemstatus status)：改變數(shù)據(jù)狀態(tài)，dwitemstatus有以下四種狀態(tài)NotModified!，DataModified!，New!，NewModified!4.3 數(shù)據(jù)窗口常用幾個(gè)事

19、件RetrieveRow event (DataWindows)：在一行被檢索后響應(yīng)；返回0則繼續(xù)，返回1則停止檢索4.4 常用SQL語句SELECT *FROM financesWHERE description LIKE gs_ ESCAPE S這里ESCAPE S表示不將s后面的通配符_作為通配符，而只是作為一般的字符4.5 給數(shù)據(jù)窗口增加條件The following scripts dynamically add a WHERE clause to a DataWindow object that was created with a SELECT statement that di

20、d not include a WHERE clause. (Since this example appends a WHERE clause to the original SELECT statement, additional code would be needed to remove a where clause from the original SELECT statement if it had one.) This technique is useful when the arguments in the WHERE clause might change at execu

21、tion time. The original SELECT statement might be:SELECT employee.emp_id, employee.l_name FROM employeePresumably, the application builds a WHERE clause based on the users choices. The WHERE clause might be:WHERE emp_id 40000The script for the windows Open event stores the original SELECT statement

22、in original_select, an instance variable:dw_emp.SetTransObject(SQLCA)original_select = & dw_emp.Describe(DataWindow.Table.Select)The script for a CommandButtons Clicked event attaches a WHERE clause stored in the instance variable where_clause to original_select and assigns it to the DataWindows Tab

23、le.Select property:string rc, mod_stringmod_string = DataWindow.Table.Select= & + original_select + where_clause + rc = dw_emp.Modify(mod_string)IF rc = THEN dw_emp.Retrieve( )ELSE MessageBox(Status, Modify Failed + rc)END IF4.6 常用PowerScript語句1） CHOOSE CASE語句CHOOSE CASE WeightCASE IS 0 thenls_front

24、 = left(ps_sql, pos(ps_sql, where ) - 1)if pos(ps_sql, group by ) 0 thenls_end = right(ps_sql, len(ps_sql) - pos(ps_sql, group by ) + 1)ls_where = mid(ps_sql, pos(ps_sql, where )+6, len(ps_sql)-len(ls_front)-len(ls_end)-6)elseif pos(ps_sql, order by ) 0 thenls_end = right(ps_sql, len(ps_sql) - pos(ps_sql, order by ) + 1)ls_where = mid(ps_sql, pos(ps_sql, where )+6, len(ps_sql)-len(ls_front)-len(ls_end)-6)elsels_where = mid(ps_sql, pos(ps_sql, where )+6, len(ps_sql)-len(ls_front)-6)end ifls_return = ls_front + where ( +