PB中如何实现数据模糊查询 -电脑资料
本文主要介绍如何在pb中根据数据窗口中的字段对数据窗口进行模糊查询，
PB中如何实现数据模糊查询
。本系统的代码示例采用Power Builder6.5进行演示。代码及设计：
1. 新建一个窗口，命名为w_query。在窗口中放入一个数据窗口控件，命名为dw_master。在dw_master中放入一个数据窗口；放置一个按钮，命名为cb_query。见下图：
2. 新建一个结构(structure)命名为str_result_column，其参数如下：
3. 在dw_master增加两个用户事件，命名为ue_action_query、ue_action_refresh。
4. 在cb_query中增加如下代码：
5. 在窗口的Open事件中增加如下代码：
dw_master.setTransObject(sqlca)
6. 在dw_master的ue_action_query中增加如下代码：
str_result_column lstr_17. 在dw_master的ue_action_refresh事件中增加如下代码：
//结构str_result_column的成员adw_result指查询结果所产生作用的dw
// 成员adw_column指在本窗口查询条件所要显示的dw
lstr_1.adw_result = this
lstr_1.adw_column = this
OpenWithParm(wroot_dw_query, lstr_1)
dw_master.retrieve()
8. 新建一个窗口，命名为wroot_dw_query，该窗口用以进行模糊查询。其设计完成后界面如下所示：
其中"执行"按钮名为"cb_exec"、"返回"按钮名为"cb_exit"、"返回"按钮下的数据窗口名为dw_column，其dataObject为d_column_set、中间的数据窗口名为dw_where，其dataObject为d_where。
9. d_column_set的设计完成界面如下所示：
10. 其sql为：
11. dw_where的设计完成界面如下：
12. 其sql为：
13. 属性和代码如下：
1. wroot_dw_query属性：
X = 9 Y = 1132 Width = 2912 Height = 712
Visible = true Enabled = true TitleBar = true Title = "定位查询"
ControlMenu = true Border = true WindowType = response! WindowState = normal!
BackColor = 79741120
2. 窗口实例变量：
Boolean ib_changed
Long MaxEditRow = 1
String sWhere, oldsql, orisql, is_title, is_section = 'WhereClause'
DataWindow dw_result,dw_detail
pfc_n_cst_string inv_string
string sSyntax
注释：a.pfc_n_cst_string 为PFC用户对象。
3. 窗口事件代码：
3.1 close 事件：
功能：将用户本次所输入的查询条件记录到文件sIniFile的WhereClause1...n小节中去以备下次启动时置初始查询条件。
3.2 open 事件：
功能：设置初始值，具体请看代码中的注释。
integer i, row, li_where_row = 10
string tmp
str_result_column lstr_1
//结构str_result_column的成员adw_result指查询结果所产生作用的dw
// 成员adw_column指在本窗口查询条件所要显示的dw
lstr_1 = Message.PowerObjectParm
dw_result = lstr_1.adw_result
dw_detail = lstr_1.adw_column
if isnull(dw_result) or not isvalid(dw_result) then return
if isnull(lstr_1.adw_column) or not isvalid(lstr_1.adw_column) then return
window act_w
act_w=MainWindow.getactivesheet()
x = act_w.x + 8
y = act_w.y + act_w.height - height+258
width = act_w.width
cb_exec.x=width - cb_exec.width -80
cb_exit.x=width - cb_exec.width -80
dw_where.x=10
dw_where.Width = width - 2 * dw_where.X - cb_exec.width -100
//-----s
dw_column.visible = False
wf_setcolumn(lstr_1.adw_column, dw_column)
//orisql = dw_result.Object.DataWindow.Table.Select //原始语法,close中用.
orisql = dw_result.GetSqlSelect() //上句对CrossTab无效
oldsql = lower(orisql)
For i = 1 to li_where_row
dw_where.InsertRow(0)
Next
dw_where.setrowfocusindicator(Hand!)
dw_where.ScrollToRow(0)
dw_where.SetColumn("column1")
cb_exec.SetFocus()
datawindowchild dwc
dw_where.GetChild("column1",dwc)
dwc.SetTransObject(sqlca)
dwc.Reset()
For i = 1 to dw_column.RowCount()
tmp = dw_column.GetItemString(i,1)
row = dwc.InsertRow(0)
dwc.SetItem(row,1,tmp)
Next
//将用户上次所输入的查询条件从文件sIniFile的WhereClause1...n小节中取出来,
//本次启动时置为初始查询条件.
window w_parent
if dw_result.GetParent().typeof() = window! then
w_parent = dw_result.GetParent()
is_title = w_parent.title
else
is_title = dw_result.DataObject
end if
ib_changed = True
row = 0
tmp = ''
is_title = gnv_app.is_regkey + '\' + is_title + '\' + scname
For i = 1 to li_where_row
RegistryGet(is_title, is_section + String(i), RegString!, tmp)
//tmp = ProfileString(sinifile,is_title,"WhereClause" + string(i),"")
if tmp <> "" and (Not IsNull(tmp)) then
//SetProfileString(sinifile,is_title,"WhereClause" + string(i),"")
RegistrySet(is_title, is_section + String(i), RegString!, '')
row ++
dw_where.object.data[row,1] = inv_string.of_gettoken(tmp, ",")
dw_where.object.data[row,2] = inv_string.of_gettoken(tmp, ",")
dw_where.object.data[row,3] = inv_string.of_gettoken(tmp, ",")
dw_where.object.data[row,4] = inv_string.of_gettoken(tmp, ",")
dw_where.object.data[row,5] = inv_string.of_gettoken(tmp, ",")
dw_where.object.data[row,6] = inv_string.of_gettoken(tmp, ",")
end if
Next
MaxEditRow = row // MaxEditRow 为dw_where中当前已编辑过的最大行的行号.实例变量.
If MaxEditRow = 0 Then MaxEditRow = 1
4. 函数：
4.1 public function string wf_getywname (string hzname)函数
功能：返回"表名.列名"，如"department.d_id"。
4.2 public function string wf_getywtype (string hzname)函数
功能：返回列类型。
注释：
（1） f_getoken()函数代码如下：
4.3 public function string wf_dateconvert (string svalue)函数
功能：见程序中注释。
string syear,smonth,sday
date idate
idate = date(svalue)
syear = string(year(idate))
smonth = string(month(idate))
sday = string(day(idate))
svalue = syear + "-" + smonth + "-" + sday
return svalue
end function
public function string wf_datetime (string inputvalue)
inputvalue = trim(inputvalue)
integer position
string bef,aft
/* bef 为日期，aft为时间*/
position = pos(inputvalue," ")
if position = 0 then inputvalue += " 00:00:00"
position = pos(inputvalue," ")
if position = 0 then
return "error"
else
bef = left(inputvalue , position - 1)
aft = right(inputvalue,len(inputvalue) - position)
if (not isdate(bef)) or (not istime(aft)) then
return "error"
end if
end if
//bef = wf_dateconvert(bef)
//return bef + " " + aft
string syear,smonth,sday
date idate
idate = date(bef)
syear = string(year(idate))
smonth = right('00'+string(month(idate)),2)
sday = right('00'+string(day(idate)),2)
return syear+smonth+sday
end function
public subroutine wf_setcolumn (datawindow dw_1, datawindow dw_2)
pfc_n_cst_string lnv_string
String scol, stable_col
String shz, syw, stype, stable
Integer i, row
If Not IsValid(dw_1) Then Return
If Not IsValid(dw_2) Then Return
dw_2.ReSet()
For i =1 To long(dw_1.Object.DataWindow.Column.Count)
scol = dw_1.Describe("#" + String(i) + ".Name") //列名(可变)
stable_col = dw_1.Describe(scol + ".dbName") //所在表.列名(OK)
stable = lnv_string.of_gettoken(stable_col,".") //所在表
syw = stable_col //列名(不变.OK) 本文来自http://bianceng.cn（编程入门）
shz = trim(dw_1.Describe(scol + "_t.Text")) //中文名
stype = dw_1.Describe(scol + ".ColType") //列类型
if dw_1.Describe(scol + ".Type") = "column" &
and shz <> "!" and shz <> "?" then
shz = lnv_string.of_globalreplace(shz,"'","") //去掉单引号
shz = lnv_string.of_globalreplace(shz,'"','') //去掉双引号
shz = lnv_string.of_globalreplace(shz,"~r~n","_") //去掉换行符
shz = lnv_string.of_globalreplace(shz," ","_") //去掉空格
shz = lnv_string.of_globalreplace(shz,":","") //去掉冒号
shz = lnv_string.of_globalreplace(shz,"：","") //去掉冒号
row = dw_2.InsertRow(0)
dw_2.object.data[row,1] = shz
dw_2.object.data[row,2] = syw
dw_2.object.data[row,3] = stype
dw_2.object.data[row,4] = stable
end if
Next
4.4 public function long wf_min (long a, long b, long c)函数
功能：给定三个数a,b,c, 如果a,b,c均为0, 则返回0;否则,返回a,b,c中不为0的数中的最小值.例1: a = 0, b = 0, c = 0 ,则返回0。例2: a = 0, b = 2, c = 6 ,则返回2。例3: a = 9, b = 0, c = 0 ,则返回9。
5. StaticText: st_1属性：
X = 46 Y = 32 Width = 466 Height = 64
TabOrder = 0 Visible = true Text = "请输入查询条件: " TextColor = 0
BackColor = 80269524 Alignment = left! FillPattern = solid!
6. CommandButton: cb_exit属性：
X = 2578 Y = 376 Width = 256 Height = 108
TabOrder = 30 Visible = true Enabled = true Text = "&F.返回"
Cancel = true
6.1 CommandButton: cb_exit的 clicked 事件：
功能：直接退出。
Close(parent)
7. CommandButton: cb_exec属性：
X = 2578 Y = 204 Width = 256 Height = 108
TabOrder = 20 Visible = true Enabled = true Text = "&Z.执行"
Default = true
7.1 CommandButton: cb_exec的 clicked 事件：
功能：设置组和sql语法。
注释：
（1）.n_cst_sql、n_cst_sqlattrib为PFC中用户对象，这里不再详述。
8. DataWindow: dw_column属性：
X = 2578 Y = 536 Width = 69 Height = 60
TabOrder = 10 Visible = true Enabled = true DataObject = "d_column_set"
Border = true LiveScroll = true BorderStyle. = stylebox
9. DataWindow: dw_where属性：
X = 46 Y = 120 Width = 2459 Height = 448
TabOrder = 40 Visible = true Enabled = true DataObject = "d_where"
VScrollBar = true Border = true BorderStyle. = stylelowered!
9.1 DataWindow: dw_where的 editchanged 事件：
功能：设置"执行"按钮是否有效，
电脑资料
《PB中如何实现数据模糊查询》()。9.2 DataWindow: dw_where的itemchanged 事件：
功能：见代码中的注释。
string colname,colvalue, logvalue
long currow
ib_changed = true
cb_exec.Enabled = True
currow = GetRow()
If MaxEditRow < Currow Then MaxEditRow = currow
// MaxEditRow 为当前已编辑过的最大行的行号。实例变量。
colname = GetColumnName()
colvalue = GetItemString(currow,colname)
Choose Case dwo.name
Case 'dispvalue'
Object.value[row] = data
Case Else
End Choose
//设置当前行的operator的初始值为"="
//设置上一行的logical的初始值为"and"
if colname = "column1" then
if colvalue = "" or isnull(colvalue) then
SetItem(currow,"operator","=")
if currow >= 2 then
colvalue = GetItemString(currow - 1,colname)
logvalue = GetItemString(currow - 1,"logical")
if colvalue <> "" and (logvalue = "" or isnull(logvalue)) then
SetItem(currow - 1,"logical","and")
end if
end if
end if
end if
//检查并设置左括号。
long ll, i
colvalue = GetText()
if colname = "precol" then
if colvalue <> "" and not isnull(colvalue) then
ll = len(colvalue)
colvalue = ""
For i = 1 to ll
colvalue += "("
Next
SetItem(currow,"precol",colvalue)
this.Settext(colvalue)
Return 2
end if
end if
9.3 DataWindow: dw_where的losefocus 事件：
AcceptText()
9.4 DataWindow: dw_where的rbuttondown 事件：
功能：设置弹出式菜单。
注释：
（1） m_cpq_rbutton_paste菜单的属性和代码如下：
1.MenuItem = m_1 "a1"
Visible = true Enabled = true
2.MenuItems for m_1
MenuItem = m_value "&V.取现有值"
Visible = true Enabled = true
3.Script. for: nt clicked event
long ll_pos
String sSyntax,ls_parm,ls_data,ls_disp
datawindow dwp
dwp = Message.PowerObjectParm
sSyntax = Message.StringParm
if sSyntax = "" or isNull(sSyntax) then
beep(3)
return
end if
OpenWithParm(w_paste,sSyntax) //w_paste为响应式窗口
ls_parm = Message.StringParm
if ls_parm <> "cancel" then
ll_pos = Pos ( ls_parm,'/')
If ll_pos = 0 Then
ls_data = ls_parm
ls_disp = ls_parm
Else
ls_data = Left ( ls_parm , ll_pos - 1 )
ls_disp = Mid ( ls_parm , ll_pos + 1 )
End If
dwp.SetItem(dwp.GetRow(),"value",ls_data)
dwp.SetItem(dwp.GetRow(),"dispvalue",ls_disp)
dwp.AcceptText()
end if
5. MenuItem = m_clear "&D.清除本列"
Visible = true Enabled = true
6. Script. for: clicked event
datawindow dwp
dwp = Message.PowerObjectParm
dwp.DeleteRow(0)
dwp.InsertRow(0)
End of Script
MenuItem = m_return "&N.返回"
Visible = true Enabled = true
9.5 DataWindow: dw_where的 ue_where 事件
功能：形成WHERE子句,并更新语法框。
string 
//sWhere 现为实例变量,在wroot_query中为局部变量.
string sOper, sLog, sLeft_kh,sRight_kh, tmpsValue
long left_kh,right_kh //左、右括号数
integer i, rownum, delnum //, typenum
dwItemStatus l_status
if ib_changed = true then
ib_changed = false
else
return 0
end if
rownum = dw_where.RowCount()
//去掉dw_where中MaxEditRow行以前所有中间为空或
//者输入不完整的行, 并更新MaxEditRow.
i = 1
delnum = 0
DO WHILE i <= MaxEditRow
l_status = dw_where.GetItemStatus(i,0, Primary!)
if l_status <> New! then
hzcol = GetItemString(i,"column1")
sValue = GetItemString(i,"value")
if (hzcol = "" or isnull(hzcol)) or (sValue = "" or isnull(sValue)) then
dw_where.DeleteRow(i)
delnum += 1
MaxEditRow += -1
Continue
end if
else
dw_where.DeleteRow(i)
delnum += 1
MaxEditRow += -1
Continue
end if
i += 1
LOOP
For i = 1 to DelNum
dw_where.InsertRow(0)
Next
//检查左右括号是否匹配, 即其数量是否一样多.
For i = 1 to MaxEditRow
l_status = dw_where.GetItemStatus(i,0, Primary!)
if l_status <> New! then
left_kh += inv_string.of_countoccurrences(GetItemString(i,"precol"),"(")
right_kh += inv_string.of_countoccurrences(GetItemString(i,"value"),")")
end if
Next
i = left_kh - right_kh
if i <> 0 then
if i > 0 then
sValue = "查询条件中左括号比右括号多了" + String(i) + "个"
else
sValue = "查询条件中左括号比右括号少了" + String(-i) + "个"
end if
if MessageBox("综合查询输入错误",sValue + ",请改正；" + &
"~r~n~r~n否则，所有查询条件将被忽略。",None!,OKCancel!,2)=1 then
return 1
else
dw_where.setfocus()
return 0
end if
end if
//形成WHERE子句,并更新语法框。
sWhere = ""
For i = 1 to MaxEditRow
hzcol = GetItemString(i,"column1")
sOper = space(1) + GetItemString(i,"operator") + space(1)
// 去掉空格键
sValue = Trim(GetItemString(i,"value"))
sLeft_kh = GetItemString(i,"precol") //保存左括号
if isNull(sLeft_kh) then sLeft_kh = ""
if Pos(sValue,")",1) > 0 then //保存右括号
sRight_kh = Right(sValue,(Len(sValue) - Pos(sValue,")",1) + 1))
else
sRight_kh = ""
end if
sValue = inv_string.of_globalreplace(sValue,"'","") //去掉sValue中的单引号.
sValue = inv_string.of_globalreplace(sValue,'"','') //去掉sValue中的双引号.
sValue = inv_string.of_globalreplace(sValue,")","") //去掉sValue中的右括号.
sLog = GetItemString(i,"logical")
if sLog = "" or isNull(sLog) then
sLog = "and"
dw_where.SetItem(i,"logical","and")
end if
ywcol = wf_getYwName(hzcol) //表名.列名
sType = lower(wf_getYwType(hzcol))
CHOOSE CASE sType
CASE "char","character","string","nchar","nvarchar","varchar","time"
if trim(sOper) = "like" or trim(sOper) = "not like" then
sWhere += " (" + sLeft_kh + ywcol + sOper + "'%" + sValue + "%') " + sRight_kh + sLog
else
sWhere += " (" + sLeft_kh + ywcol + sOper + "'" + sValue + "') " + sRight_kh + sLog
end if
CASE "numeric","decimal","decimaln","dec","double","integer","int","smallint",&
"number","long","real","uint","ulong","unsignedint","unsignedinteger","unsignedlong"
if trim(sOper) = "like" or trim(sOper) = "not like" then
if MessageBox("提示信息",hzcol + "不是字符型,不能使用<含有>或<不含有>操作符," + &
"~r~n~r~n请改正; 否则，所有查询条件将被忽略。",None!,OKCancel!,2)=1 then
return 1
else
dw_where.setfocus()
return 0
end if
end if
if isNumber(sValue) then
sWhere += " (" + sLeft_kh + ywcol + sOper + sValue + ") " + sRight_kh + sLog
else
if MessageBox("综合查询输入错误",hzcol + "的值应为数字型,请改正；" + &
"~r~n~r~n否则，所有查询条件将被忽略。",None!,OKCancel!,2)=1 then
Return 1
else
dw_where.setfocus()
return 0
end if
end if
CASE "date","datetime","datetimn","smalldatetime","timestamp"
if trim(sOper) = "like" or trim(sOper) = "not like" then
if MessageBox("提示信息",hzcol + "不是字符型,不能使用<含有>或<不含有>操作符," + &
"~r~n~r~n请改正; 否则，所有查询条件将被忽略。",None!,OKCancel!,2)=1 then
return 1
else
dw_where.setfocus()
return 0
end if
end if
if sType = "date" then
if not isdate(sValue) then
if MessageBox("综合查询输入错误",hzcol + "的值应为日期型,请改正;" + &
"~r~n~r~n否则,所有查询条件将被忽略.",None!,OKCancel!,2)=1 then
Return 1
else
dw_where.setfocus()
return 0
end if
end if
sValue = wf_dateconvert(sValue)
sWhere += " (" + sLeft_kh + ywcol + sOper + "'" + sValue + "') " + sRight_kh + sLog
else
//datetime型的字段在sybase中转换为字符串的类型
sValue = wf_datetime(sValue)
if sValue = "error" then
if MessageBox("综合查询输入错误",hzcol + "的值应为日期时间型,请改正；" + &
"~r~n~r~n否则，所有查询条件将被忽略。",None!,OKCancel!,2)=1 then
Return 1
else
dw_where.setfocus()
return 0
end if
end if
sWhere += " (" + sLeft_kh + "convert(varchar(8),"+ywcol+",112)" + sOper + " '"+sValue+"') " + sRight_kh +
sLog
end if
CASE ELSE
beep(1)
MessageBox("综合查询",sType + "这个数据类型未在本模块中定义。")
Return 0
END CHOOSE
Next
//去掉最后一个逻辑符。
Long pok, pp
pp = 0
DO WHILE True
pok = pp
pp = Pos(sWhere, ")", pp + 1)
if pp = 0 then Exit
LOOP
if pok > 0 then sWhere = Trim(Left(sWhere, pok))
return 1
//最后一个逻辑符去掉结束.
//至此, 用户的WHERE语句部分已经形成完毕于sWhere中.
9.6 DataWindow: dw_where的 ue_mousemove 事件
功能：控制鼠标的移动。
string s_object
s_object = This.GetObjectAtPointer()
if left(s_object,5) = "value" then
MainWindow.SetMicroHelp("此时按鼠标右键可粘贴现有值")
else
MainWindow.SetMicroHelp("准备好")
end if
9.7 DataWindow: dw_where的ue_retrieve事件
功能：对数据窗口进行查询，详细请见代码。
// 如果数据窗口dw_result上并无ue_action_refresh事件,
// 则用户必须重载本事件,编写自己的脚本.
// 如:
// dw_result.retrieve()
//
// 或者如果数据窗口dw_result有retrieve参数,
// 则用户也必须重载本事件,编写自己的脚本.
// 如:
// string ls_id
// ls_id = ...
// dw_result.retrieve(ls_id)
dw_result.triggerevent("ue_action_refresh")
