PB动态创建带参数据窗口

最新推荐文章于 2023-12-10 10:37:32 发布

JasonFriday

最新推荐文章于 2023-12-10 10:37:32 发布

阅读量2.7k

点赞数 3

文章标签： powerbuilder

本文链接：https://blog.csdn.net/JasonFriday/article/details/114327390

版权

文章目录

动态数据窗口
基于SQL数据源
基于存储过程数据源
项目源代码

动态数据窗口

PB在开发应用中，经常需要动态地创建数据窗口。常用的动态创建数据窗口的方法是调用事务对象的SyntaxFromSQL方法，传入SQL语句，返回数据窗语法，再通过数据窗语法动态创建数据窗口（数据存储）。例如：

string ls_sql, ls_presentation, ls_syntax, ls_err
datastore ds

ls_sql = "select empno, ename, job, mgr, hiredate, sal from scott.emp where deptno = '20'"
ls_presentation = "style(type=grid)"
ls_syntax = SQLCA.SyntaxFromSQL(ls_sql, ls_presentation, ls_err)
if len(ls_err) > 0 then return -1

ds = create datastore
ds.create(ls_syntax, ls_err)
if len(ls_err) > 0 then return -1

ds.settransobject(SQLCA)
ds.retrieve()
return 0

在这里插入图片描述
SQLCA.SyntaxFromSQL返回数据窗语法如下：

release 12.5;
datawindow(units=0 timer_interval=0 color=1073741824 processing=1 print.margin.bottom=96 print.margin.left=110 print.margin.right=110 print.margin.top=96 print.preview.buttons=no )
table(
	column=(type=decimal(0) update=yes key=yes updatewhereclause = yes name=empno  dbname="emp.empno")
	column=(type=char(10) update=yes updatewhereclause = yes name=ename  dbname="emp.ename")
	column=(type=char(9) update=yes updatewhereclause = yes name=job  dbname="emp.job")
	column=(type=decimal(0) update=yes updatewhereclause = yes name=mgr  dbname="emp.mgr")
	column=(type=datetime update=yes updatewhereclause = yes name=hiredate  dbname="emp.hiredate")
	column=(type=decimal(2) update=yes updatewhereclause = yes name=sal  dbname="emp.sal")
	retrieve="select empno, ename, job, mgr, hiredate, sal from scott.emp where deptno = '20'"
	update="scott.emp" updatewhere=1)
header(height=72)
detail(height=84)
column(band=detail id=1 x="14" y="8" height="68" width="329" tabsequence=10 alignment="1" font.face="宋体              " font.height="-9" font.weight="400" font.charset="134" font.pitch="2" font.family="0" font.underline="0" font.italic="0" font.strikethrough="0" border="0" color="0" background.mode="1" background.color="536870912" background.brushmode="0" background.gradient.color="8421504" background.gradient.scale="100" background.gradient.spread="100" background.gradient.repetition.mode="0" background.gradient.repetition.count="0" background.gradient.repetition.length="100" tooltip.enabled="0" tooltip.icon="0" tooltip.backcolor="134217752" tooltip.textcolor="134217751" tooltip.delay.initial="0" tooltip.delay.visible="32000" tooltip.isbubble="0"  edit.autoselect=yes edit.autohscroll=yes edit.autovscroll=no edit.focusrectangle=no )
text(band=header text="Empno" x="14" y="8" height="56" width="329" font.face="宋体              " font.height="-9" font.weight="400" font.charset="134" font.pitch="2" font.family="0" font.underline="0" font.italic="0" font.strikethrough="0" border="0" color="0" background.mode="1" background.color="536870912" background.brushmode="0" background.gradient.color="8421504" background.gradient.scale="100" background.gradient.spread="100" background.gradient.repetition.mode="0" background.gradient.repetition.count="0" background.gradient.repetition.length="100" tooltip.enabled="0" tooltip.icon="0" tooltip.backcolor="134217752" tooltip.textcolor="134217751" tooltip.delay.initial="0" tooltip.delay.visible="32000"  tooltip.isbubble="0" alignment="2"  name=empno_t )
column(band=detail id=2 x="357" y="8" height="68" width="302" tabsequence=20 edit.limit=10 alignment="0" font.face="宋体              " font.height="-9" font.weight="400" font.charset="134" font.pitch="2" font.family="0" font.underline="0" font.italic="0" font.strikethrough="0" border="0" color="0" background.mode="1" background.color="536870912" background.brushmode="0" background.gradient.color="8421504" background.gradient.scale="100" background.gradient.spread="100" background.gradient.repetition.mode="0" background.gradient.repetition.count="0" background.gradient.repetition.length="100" tooltip.enabled="0" tooltip.icon="0" tooltip.backcolor="134217752" tooltip.textcolor="134217751" tooltip.delay.initial="0" tooltip.delay.visible="32000" tooltip.isbubble="0"  edit.autoselect=yes edit.autohscroll=yes edit.autovscroll=no edit.focusrectangle=no )
text(band=header text="Ename" x="357" y="8" height="56" width="302" font.face="宋体              " font.height="-9" font.weight="400" font.charset="134" font.pitch="2" font.family="0" font.underline="0" font.italic="0" font.strikethrough="0" border="0" color="0" background.mode="1" background.color="536870912" background.brushmode="0" background.gradient.color="8421504" background.gradient.scale="100" background.gradient.spread="100" background.gradient.repetition.mode="0" background.gradient.repetition.count="0" background.gradient.repetition.length="100" tooltip.enabled="0" tooltip.icon="0" tooltip.backcolor="134217752" tooltip.textcolor="134217751" tooltip.delay.initial="0" tooltip.delay.visible="32000"  tooltip.isbubble="0" alignment="2"  name=ename_t )
column(band=detail id=3 x="673" y="8" height="68" width="274" tabsequence=30 edit.limit=9 alignment="0" font.face="宋体              " font.height="-9" font.weight="400" font.charset="134" font.pitch="2" font.family="0" font.underline="0" font.italic="0" font.strikethrough="0" border="0" color="0" background.mode="1" background.color="536870912" background.brushmode="0" background.gradient.color="8421504" background.gradient.scale="100" background.gradient.spread="100" background.gradient.repetition.mode="0" background.gradient.repetition.count="0" background.gradient.repetition.length="100" tooltip.enabled="0" tooltip.icon="0" tooltip.backcolor="134217752" tooltip.textcolor="134217751" tooltip.delay.initial="0" tooltip.delay.visible="32000" tooltip.isbubble="0"  edit.autoselect=yes edit.autohscroll=yes edit.autovscroll=no edit.focusrectangle=no )
text(band=header text="Job" x="673" y="8" height="56" width="274" font.face="宋体              " font.height="-9" font.weight="400" font.charset="134" font.pitch="2" font.family="0" font.underline="0" font.italic="0" font.strikethrough="0" border="0" color="0" background.mode="1" background.color="536870912" background.brushmode="0" background.gradient.color="8421504" background.gradient.scale="100" background.gradient.spread="100" background.gradient.repetition.mode="0" background.gradient.repetition.count="0" background.gradient.repetition.length="100" tooltip.enabled="0" tooltip.icon="0" tooltip.backcolor="134217752" tooltip.textcolor="134217751" tooltip.delay.initial="0" tooltip.delay.visible="32000"  tooltip.isbubble="0" alignment="2"  name=job_t )
column(band=detail id=4 x="961" y="8" height="68" width="329" tabsequence=40 alignment="1" font.face="宋体              " font.height="-9" font.weight="400" font.charset="134" font.pitch="2" font.family="0" font.underline="0" font.italic="0" font.strikethrough="0" border="0" color="0" background.mode="1" background.color="536870912" background.brushmode="0" background.gradient.color="8421504" background.gradient.scale="100" background.gradient.spread="100" background.gradient.repetition.mode="0" background.gradient.repetition.count="0" background.gradient.repetition.length="100" tooltip.enabled="0" tooltip.icon="0" tooltip.backcolor="134217752" tooltip.textcolor="134217751" tooltip.delay.initial="0" tooltip.delay.visible="32000" tooltip.isbubble="0"  edit.autoselect=yes edit.autohscroll=yes edit.autovscroll=no edit.focusrectangle=no )
text(band=header text="Mgr" x="961" y="8" height="56" width="329" font.face="宋体              " font.height="-9" font.weight="400" font.charset="134" font.pitch="2" font.family="0" font.underline="0" font.italic="0" font.strikethrough="0" border="0" color="0" background.mode="1" background.color="536870912" background.brushmode="0" background.gradient.color="8421504" background.gradient.scale="100" background.gradient.spread="100" background.gradient.repetition.mode="0" background.gradient.repetition.count="0" background.gradient.repetition.length="100" tooltip.enabled="0" tooltip.icon="0" tooltip.backcolor="134217752" tooltip.textcolor="134217751" tooltip.delay.initial="0" tooltip.delay.visible="32000"  tooltip.isbubble="0" alignment="2"  name=mgr_t )
column(band=detail id=5 x="1304" y="8" height="68" width="603" tabsequence=50 alignment="0" font.face="宋体              " font.height="-9" font.weight="400" font.charset="134" font.pitch="2" font.family="0" font.underline="0" font.italic="0" font.strikethrough="0" border="0" color="0" background.mode="1" background.color="536870912" background.brushmode="0" background.gradient.color="8421504" background.gradient.scale="100" background.gradient.spread="100" background.gradient.repetition.mode="0" background.gradient.repetition.count="0" background.gradient.repetition.length="100" tooltip.enabled="0" tooltip.icon="0" tooltip.backcolor="134217752" tooltip.textcolor="134217751" tooltip.delay.initial="0" tooltip.delay.visible="32000" tooltip.isbubble="0"  edit.autoselect=yes edit.autohscroll=yes edit.autovscroll=no edit.focusrectangle=no )
text(band=header text="Hiredate" x="1304" y="8" height="56" width="603" font.face="宋体              " font.height="-9" font.weight="400" font.charset="134" font.pitch="2" font.family="0" font.underline="0" font.italic="0" font.strikethrough="0" border="0" color="0" background.mode="1" background.color="536870912" background.brushmode="0" background.gradient.color="8421504" background.gradient.scale="100" background.gradient.spread="100" background.gradient.repetition.mode="0" background.gradient.repetition.count="0" background.gradient.repetition.length="100" tooltip.enabled="0" tooltip.icon="0" tooltip.backcolor="134217752" tooltip.textcolor="134217751" tooltip.delay.initial="0" tooltip.delay.visible="32000"  tooltip.isbubble="0" alignment="2"  name=hiredate_t )
column(band=detail id=6 x="1921" y="8" height="68" width="329" tabsequence=60 alignment="1" font.face="宋体              " font.height="-9" font.weight="400" font.charset="134" font.pitch="2" font.family="0" font.underline="0" font.italic="0" font.strikethrough="0" border="0" color="0" background.mode="1" background.color="536870912" background.brushmode="0" background.gradient.color="8421504" background.gradient.scale="100" background.gradient.spread="100" background.gradient.repetition.mode="0" background.gradient.repetition.count="0" background.gradient.repetition.length="100" tooltip.enabled="0" tooltip.icon="0" tooltip.backcolor="134217752" tooltip.textcolor="134217751" tooltip.delay.initial="0" tooltip.delay.visible="32000" tooltip.isbubble="0"  edit.autoselect=yes edit.autohscroll=yes edit.autovscroll=no edit.focusrectangle=no )
text(band=header text="Sal" x="1921" y="8" height="56" width="329" font.face="宋体              " font.height="-9" font.weight="400" font.charset="134" font.pitch="2" font.family="0" font.underline="0" font.italic="0" font.strikethrough="0" border="0" color="0" background.mode="1" background.color="536870912" background.brushmode="0" background.gradient.color="8421504" background.gradient.scale="100" background.gradient.spread="100" background.gradient.repetition.mode="0" background.gradient.repetition.count="0" background.gradient.repetition.length="100" tooltip.enabled="0" tooltip.icon="0" tooltip.backcolor="134217752" tooltip.textcolor="134217751" tooltip.delay.initial="0" tooltip.delay.visible="32000"  tooltip.isbubble="0" alignment="2"  name=sal_t )
htmltable(border="0" cellpadding="1" cellspacing="1" generatecss="no" nowrap="no")

用该数据窗语法能够动态地创建数据窗窗口或数据存储。可以看到，示例代码中成功创建了数据存储并检索出了数据。

在一般情况下，对于不带参数的SQL语句，都可以通过这种方式来动态生成数据窗口。但是，对于带参数的SQL，PB不能支持动态生成数据窗口，直接调用事务对象的SyntaxFromSQL方法会返回错误。

要实现含参SQL动态创建数据窗口，一个想法是，先使用不含参的SQL动态创建数据窗口，然后调用数据窗的Modify方法修改数据窗参数，再调用数据窗的SetSQLSelect方法把SQL语法换成带参数的形式。但是经过实测，数据窗参数为只读属性，Modify方法并不能修改数据窗参数。

于是，只剩下一种办法：先使用不含参的SQL生成数据窗语法，然后修改数据窗语法，向其中添加检索参数信息，再修改检索SQL为含参的SQL。

下面将演示这种方法：

基于SQL数据源

新建窗口，添加数据窗dw_emp，添加按钮cb_create：
在这里插入图片描述
按钮cb_create的clicked事件添加代码：

datastore_generator dsgen
dsgen = create datastore_generator

dsgen.set_sql('select empno, ename, job, mgr, hiredate, sal from scott.emp where deptno = :deptno')
dsgen.set_argument( 1, 'deptno', dsgen.STRING_TYPE)
dsgen.create_datawindow( dw_emp)

dw_emp.settransobject(SQLCA)
dw_emp.retrieve('20')

return 0

运行效果如下图：
在这里插入图片描述
可以看到，这里将带参数的SQL语句动态地生成了数据窗口，并成功检索出数据。

如果SQL有多个参数，只需要多次调用set_argument方法，将所有参数设置完整即可，下面演示多个参数的形式：

窗口添加按钮cb_create2：
在这里插入图片描述
按钮cb_create2的clicked事件添加代码：

datastore_generator dsgen
dsgen = create datastore_generator

dsgen.set_sql('select empno, ename, job, mgr, hiredate, sal from scott.emp where deptno = :deptno and sal > :sal')
dsgen.set_argument( 1, 'deptno', dsgen.STRING_TYPE)
dsgen.set_argument( 2, 'sal', dsgen.NUMBER_TYPE)
dsgen.create_datawindow( dw_emp)

dw_emp.settransobject(SQLCA)
dw_emp.retrieve('20', 1500)

return 0

运行效果如下：
在这里插入图片描述

基于存储过程数据源

数据窗口的数据源不仅可以是SQL语句，还可以是返回结果集的存储过程。下面演示带参数的存储过程动态生成数据窗口。

新增存储过程pro_get_emp_list：

create or replace procedure pro_get_emp_list(p_job in varchar2,
                                             p_sal in number,
                                             emp_list out sys_refcursor) is
begin
  open emp_list for
    select empno, ename, job, mgr, hiredate, sal 
      from scott.emp 
     where job = p_job 
       and sal > p_sal;
end pro_get_emp_list;

窗口添加按钮cb_create3：
在这里插入图片描述
按钮cb_create3的clicked事件编辑代码：
需要特别注意，这里调用的是set_procedure方法，上文中SQL数据源使用的是set_sql方法。

datastore_generator dsgen
dsgen = create datastore_generator

dsgen.set_procedure('execute pro_get_emp_list; p_job=:p_job ,p_sal=:p_sal')
dsgen.set_argument( 1, 'p_job', dsgen.STRING_TYPE)
dsgen.set_argument( 2, 'p_sal', dsgen.NUMBER_TYPE)
dsgen.create_datawindow( dw_emp)

dw_emp.settransobject(SQLCA)
dw_emp.retrieve('CLERK', 1000)

return 0

运行效果如下：
在这里插入图片描述

项目源代码

使用过程中出现问题请反馈作者：
QQ：768310524
TEL、WX：18649713925

forward
global type datastore_generator from nonvisualobject
end type
type st_data_arg from structure within datastore_generator
end type
end forward

type st_data_arg from structure
	string		arg_name
	string		arg_type
end type

global type datastore_generator from nonvisualobject
end type
global datastore_generator datastore_generator

type variables
public:
	CONSTANT STRING STRING_TYPE = 'string'
	CONSTANT STRING NUMBER_TYPE = 'number'
	CONSTANT STRING DATE_TYPE = 'date'
	CONSTANT STRING TIME_TYPE = 'time'
	CONSTANT STRING DATETIME_TYPE = 'datetime'
	CONSTANT STRING DECIMAL_TYPE = 'decimal'
	CONSTANT STRING STRINGARRAY_TYPE = 'stringlist'
	CONSTANT STRING NUMBERARRAY_TYPE = 'numberlist'
	CONSTANT STRING DATEARRAY_TYPE = 'datelist'
	CONSTANT STRING TIMEARRAY_TYPE = 'timelist'
	CONSTANT STRING DATETIMEARRAY_TYPE = 'datetimelist'
	CONSTANT STRING DECIMALARRAY_TYPE = 'decimallist'

private:
	string sql_syntax
	boolean proc_flag
	st_data_arg args[]
	
	string presentation = 'style(type=grid)'
	transaction trs
end variables

forward prototypes
public function integer set_argument (integer position, string arg_name, string arg_type)
public function integer create_datastore (ref datastore ds)
public subroutine reset ()
public subroutine set_sql (string sql)
public subroutine set_procedure (string proc)
public function integer create_datawindow (datawindow dw)
public subroutine set_presentation (string p)
public subroutine set_transobject (transaction t)
public function integer create_syntax (ref string syntax)
end prototypes

public function integer set_argument (integer position, string arg_name, string arg_type);
st_data_arg arg

if position < 1 then return -1
if not (arg_type=STRING_TYPE or &
        arg_type=NUMBER_TYPE or &
		  arg_type=DATE_TYPE or &
		  arg_type=TIME_TYPE or &
		  arg_type=DATETIME_TYPE or &
		  arg_type=DECIMAL_TYPE or &
		  arg_type=STRINGARRAY_TYPE or &
        arg_type=NUMBERARRAY_TYPE or &
		  arg_type=DATEARRAY_TYPE or &
		  arg_type=TIMEARRAY_TYPE or &
		  arg_type=DATETIMEARRAY_TYPE or &
		  arg_type=DECIMALARRAY_TYPE) then
	return -1
end if

arg.arg_name = arg_name
arg.arg_type = arg_type
args[position] = arg

return 0
end function

public function integer create_datastore (ref datastore ds);string dwsyntax
if create_syntax(dwsyntax) < 0 then return -1

if not isvalid(ds) then
	ds = create datastore
end if

return ds.create(dwsyntax)
end function

public subroutine reset ();st_data_arg empty_args[]

args = empty_args
sql_syntax = ''
presentation = 'style(type=grid)'
trs = sqlca
end subroutine

public subroutine set_sql (string sql);
sql_syntax = sql
proc_flag = false

end subroutine

public subroutine set_procedure (string proc);
sql_syntax = proc
proc_flag = true

end subroutine

public function integer create_datawindow (datawindow dw);
if not isvalid(dw) then return -1

string dwsyntax
if create_syntax(dwsyntax) < 0 then return -1

return dw.create(dwsyntax)
end function

public subroutine set_presentation (string p);presentation = p
end subroutine

public subroutine set_transobject (transaction t);trs = t
end subroutine

public function integer create_syntax (ref string syntax);int i
long p, p_start, p_end
string sql
char csql[]
string param
sql = sql_syntax
csql = sql
for i = 1 to upperbound(args)
	p_start = 1
	do while true
		p = pos(sql, ':'+args[i].arg_name, p_start)
		if p <= 0 then exit
		p_end = p + len(args[i].arg_name)
		if p_end < len(sql) then
			if not (csql[p_end + 1] = ' ' or &
						csql[p_end + 1] = '~r' or &
						csql[p_end + 1] = '~n' or &
						csql[p_end + 1] = '~t' or &
						csql[p_end + 1] = ')') then
				p_start = p_end + 1
				continue
			end if
		end if
		
		choose case args[i].arg_type
			case STRING_TYPE, STRINGARRAY_TYPE
				param = "'"+space(255)+"'"
			case NUMBER_TYPE, NUMBERARRAY_TYPE
				param = '0'
			case DECIMAL_TYPE, DECIMALARRAY_TYPE
				param = '0.0'
			case else
				param = ''
		end choose
		sql = replace(sql, p, len(args[i].arg_name) + 1, param)
		csql = sql
		p_start = p
	loop
next

string dwsyntax, err
dwsyntax = trs.syntaxfromsql( sql, presentation, err)
if len(err) > 0 then return -1

if upperbound(args) <= 0 then
	syntax = dwsyntax
	return 0
end if

string s
char csyntax[]
long j, k
csyntax = dwsyntax
if proc_flag then
	s = 'procedure="'
else
	s = 'retrieve="'
end if
p = pos(dwsyntax, s)
j = p + len(s)
do while j < upperbound(csyntax)
	if csyntax[j] = '"' then
		p_end = j
		exit
	end if
	if csyntax[j] = '~~' then
		j += 2
		continue
	end if
	j ++
loop

string arguments
for i = 1 to upperbound(args)
	arguments += '("' + args[i].arg_name + '", ' + args[i].arg_type + ')'
	if i < upperbound(args) then
		arguments += ','
	end if
next
arguments = ' arguments=(' + arguments + ')'
dwsyntax = replace(dwsyntax, p_end + 1, 0, arguments)

char cdwsql[]
csql = sql_syntax
for j = 1 to upperbound(csql)
	if csql[j] = '~~' or csql[j] = '"' then
		k++
		cdwsql[k] = '~~'
	end if
	k++
	cdwsql[k] = csql[j]
next
dwsyntax = replace(dwsyntax, p + len(s), p_end - p - len(s), cdwsql)
syntax = dwsyntax

return 0
end function

on datastore_generator.create
call super::create
TriggerEvent( this, "constructor" )
end on

on datastore_generator.destroy
TriggerEvent( this, "destructor" )
call super::destroy
end on

event constructor;trs = sqlca
end event