关于数据库字典的汉字注释维护
(深圳:独孤求败 2003-05-16)
(深圳:独孤求败 2003-05-16)
众所周知,datawindow 是PB的灵魂,但由于一般的数据库只支持英文字段名,所以字段的header和label也是英文的,给数据窗口对象设计带来极大的不方便,每次重画一次数据窗口对象都要重新输入一次汉字。虽然在数据库画板中可以一次性地把字段的header和label都设置成汉字,但总觉得其过于繁锁,也不方便维护,有没有什么更好的方法呢?
本人就是带着这一疑问进行了一番探索,终于如愿,现提出来与大家分享。
大家都知道,数据库表的属性,包括header和label的汉字注释均存放在数据库系统表中,但由于数据库系统表不容人置喙,“系统”两字咳住了不少人。其实只要知道其意义,不难对它操作自如。 本文别找曲径,实现步骤如下:
1、创建数据窗口d_table的SQL语法:
SELECT Table_name, Comments
FROM user_tab_comments
WHERE table_type ='TABLE' and table_name like :as_like
ORDER BY table_name asc
SELECT Table_name, Comments
FROM user_tab_comments
WHERE table_type ='TABLE' and table_name like :as_like
ORDER BY table_name asc
2、创建数据窗口d_col 的SQL语法:
SELECT a.cname, b.comments, b.table_name, b.column_name, a.coltype, a.width, a.scale,
a.precision, a.nulls, a.defaultval, a.colno
FROM col a, user_col_comments b
WHERE a.tname = b.table_name AND a.cname = b.column_name AND a.tname = :as_table
ORDER BY colon
SELECT a.cname, b.comments, b.table_name, b.column_name, a.coltype, a.width, a.scale,
a.precision, a.nulls, a.defaultval, a.colno
FROM col a, user_col_comments b
WHERE a.tname = b.table_name AND a.cname = b.column_name AND a.tname = :as_table
ORDER BY colon
3、创建窗口w_wh_sjzd,窗口上放置控件如下:
控件名 属性 值
Datawindow Control Name Dw_1
Dataobject D_table
Datawindow Control Name Dw_2
Dataobject D_col
Command Button Name Cb_save
Text 保存
Command Button Name Cb_close
Text 关闭
控件名 属性 值
Datawindow Control Name Dw_1
Dataobject D_table
Datawindow Control Name Dw_2
Dataobject D_col
Command Button Name Cb_save
Text 保存
Command Button Name Cb_close
Text 关闭
4、定义实例变量:
string is_ownr //数据库登录用户名称
窗口w_wh_sjzd的open事件
dw_1.settransobject(sqlca)
dw_2.settransobject(sqlca)
dw_1.retrieve()
string is_ownr //数据库登录用户名称
窗口w_wh_sjzd的open事件
dw_1.settransobject(sqlca)
dw_2.settransobject(sqlca)
dw_1.retrieve()
5、数据窗口控件dw_1的pfc_updaterow 用户自定义事件:
string ls_table,ls_comment,ls_sql
long ll_row,ll_count,i
string ls_newrow,ls_tt
this.accepttext()
ll_count = this.rowcount()
for i = 1 to ll_count
ls_tt = "evaluate('IsRowModified()',"+string(i)+")"
ls_newrow = this.Describe(ls_tt)
ls_table = this.object.table_name[i]
ls_comment = this.object.comments[i]
ls_table = this.object.table_name[i]
//判断是否修改过
if ls_newrow ='true' then
ls_sql = "comment on table "+ls_table+" is '"+ls_comment+"'"
if not isnull(ls_sql) then
EXECUTE IMMEDIATE:ls_sql;
end if
//设置表名汉字注释
if ls_comment<> '' then
insert into SYSTEM.pbcattbl(pbt_tnam,pbt_ownr,pbt_cmnt)
values(:ls_table,:is_ownr,:ls_comment);
if sqlca.sqlcode = -1 then
update SYSTEM.pbcattbl
set pbt_tnam =:ls_table,
pbt_ownr =:is_ownr,
pbt_cmnt =:ls_comment;
end if
end if
end if
next
commit;
string ls_table,ls_comment,ls_sql
long ll_row,ll_count,i
string ls_newrow,ls_tt
this.accepttext()
ll_count = this.rowcount()
for i = 1 to ll_count
ls_tt = "evaluate('IsRowModified()',"+string(i)+")"
ls_newrow = this.Describe(ls_tt)
ls_table = this.object.table_name[i]
ls_comment = this.object.comments[i]
ls_table = this.object.table_name[i]
//判断是否修改过
if ls_newrow ='true' then
ls_sql = "comment on table "+ls_table+" is '"+ls_comment+"'"
if not isnull(ls_sql) then
EXECUTE IMMEDIATE:ls_sql;
end if
//设置表名汉字注释
if ls_comment<> '' then
insert into SYSTEM.pbcattbl(pbt_tnam,pbt_ownr,pbt_cmnt)
values(:ls_table,:is_ownr,:ls_comment);
if sqlca.sqlcode = -1 then
update SYSTEM.pbcattbl
set pbt_tnam =:ls_table,
pbt_ownr =:is_ownr,
pbt_cmnt =:ls_comment;
end if
end if
end if
next
commit;
6、数据窗口控件dw_ 1的rowfocuschanged 事件:
string ls_xx
if currentrow>0 then
ls_xx = this.object.table_name[currentrow]
dw_2.retrieve(ls_xx)
end if
string ls_xx
if currentrow>0 then
ls_xx = this.object.table_name[currentrow]
dw_2.retrieve(ls_xx)
end if
7、数据窗口控件dw_ 2的pfc_updaterow 用户自定义事件:
string ls_table,ls_comment,ls_col,ls_sql
long ll_row,ll_count,i
string ls_newrow,ls_tt
this.accepttext()
ll_row = dw_1.getrow()
if ll_row<0 or isnull(ll_row) then return
ls_table = dw_1.object.table_name[ll_row]
ll_count = this.rowcount()
for i = 1 to ll_count
ls_tt = "evaluate('IsRowModified()',"+string(i)+")"
ls_newrow = this.Describe(ls_tt)
ls_col = this.object.col_cname[i]
ls_comment = this.object.user_col_comments_comments[i]
ls_col = this.object.col_cname[i]
//判断是否修改过
if ls_newrow ='true' then
ls_sql = "comment on column "+ls_table+"."+ls_col+" is '"+ls_comment+"'"
if not isnull(ls_sql) then
EXECUTE IMMEDIATE:ls_sql;
end if
if ls_comment<> '' then
//设置header、label和字段名的汉字注释
insert into "SYSTEM"."PBCATCOL"(pbc_tnam, pbc_ownr, pbc_cnam, pbc_labl, pbc_hdr) values(:ls_table,:is_ownr,:ls_col,:ls_comment,:ls_comment);
if sqlca.sqlcode <> 0 then
UPDATE "SYSTEM"."PBCATCOL"
SET "PBC_TNAM" = :ls_table,
"PBC_OWNR" = :is_ownr,
"PBC_CNAM" = :ls_col,
"PBC_LABL" = :ls_comment,
"PBC_HDR" = :ls_comment;
end if
end if
end if
next
commit;
8、按钮cb_save的点击事件:
SetPointer(HourGlass!)
dw_1.dynamic event pfc_updaterow()
dw_2.dynamic event pfc_updaterow()
SetPointer(Arrow!)
9、按钮cb_close的点击事件:
close(parent)
string ls_table,ls_comment,ls_col,ls_sql
long ll_row,ll_count,i
string ls_newrow,ls_tt
this.accepttext()
ll_row = dw_1.getrow()
if ll_row<0 or isnull(ll_row) then return
ls_table = dw_1.object.table_name[ll_row]
ll_count = this.rowcount()
for i = 1 to ll_count
ls_tt = "evaluate('IsRowModified()',"+string(i)+")"
ls_newrow = this.Describe(ls_tt)
ls_col = this.object.col_cname[i]
ls_comment = this.object.user_col_comments_comments[i]
ls_col = this.object.col_cname[i]
//判断是否修改过
if ls_newrow ='true' then
ls_sql = "comment on column "+ls_table+"."+ls_col+" is '"+ls_comment+"'"
if not isnull(ls_sql) then
EXECUTE IMMEDIATE:ls_sql;
end if
if ls_comment<> '' then
//设置header、label和字段名的汉字注释
insert into "SYSTEM"."PBCATCOL"(pbc_tnam, pbc_ownr, pbc_cnam, pbc_labl, pbc_hdr) values(:ls_table,:is_ownr,:ls_col,:ls_comment,:ls_comment);
if sqlca.sqlcode <> 0 then
UPDATE "SYSTEM"."PBCATCOL"
SET "PBC_TNAM" = :ls_table,
"PBC_OWNR" = :is_ownr,
"PBC_CNAM" = :ls_col,
"PBC_LABL" = :ls_comment,
"PBC_HDR" = :ls_comment;
end if
end if
end if
next
commit;
8、按钮cb_save的点击事件:
SetPointer(HourGlass!)
dw_1.dynamic event pfc_updaterow()
dw_2.dynamic event pfc_updaterow()
SetPointer(Arrow!)
9、按钮cb_close的点击事件:
close(parent)