如何在dw中动态显示sql查询结果？

[lihao24]

有一个datawindow：dw_1
一个编辑框:sle_1
如何实现在sle_1中输入sql语句按回车后，在dw_1中将查询结果显示出来？

[hzhxxx]

PowerBuilder These statements create a new DataWindow in the control dw_new from the DataWindow source code returned by the SyntaxFromSQL function. Errors from SyntaxFromSQL and Create are displayed in the MultiLineEdits mle_sfs and mle_create. After creating the DataWindow, you must call SetTransObject for the new DataWindow object before you can retrieve data:

string error_syntaxfromSQL, error_create

string new_sql, new_syntax

new_sql = 'SELECT emp_data.emp_id, ' &
+ 'emp_data.emp_name ' &
+ 'from emp_data ' &
+ 'WHERE emp_data.emp_salary>45000'

new_sql = Trim(sle_1.Text)

new_syntax = SQLCA.SyntaxFromSQL(new_sql, &
'Style(Type=Form)', error_syntaxfromSQL)

IF Len(error_syntaxfromSQL) > 0 THEN
// Display errors
mle_sfs.Text = error_syntaxfromSQL

ELSE
// Generate new DataWindow
dw_new.Create(new_syntax, error_create)
IF Len(error_create) > 0 THEN
mle_create.Text = error_create
END IF

END IF

dw_new.SetTransObject(SQLCA)

dw_new.Retrieve()

[lihao24]

up

[luotitan]

以前从网上down了一个，你看看吧。
//以下代码 save as w_sql.srw
//然后 import 引入你的 *.pbl
//===========================
$PBExportHeader$w_sql.srw
$PBExportComments$数据浏览统一格式
forward
global type w_sql from window
end type
type cb_undo from commandbutton within w_sql
end type
type mle_1 from multilineedit within w_sql
end type
type cb_execute from commandbutton within w_sql
end type
type cb_close from commandbutton within w_sql
end type
type dw_1 from datawindow within w_sql
end type
end forward

global type w_sql from window
integer width = 2309
integer height = 1396
boolean titlebar = true
string title = "数据查询分析器"
boolean controlmenu = true
boolean minbox = true
boolean maxbox = true
boolean resizable = true
long backcolor = 67108864
string icon = "Form!"
boolean clientedge = true
cb_undo cb_undo
mle_1 mle_1
cb_execute cb_execute
cb_close cb_close
dw_1 dw_1
end type
global w_sql w_sql

type variables
boolean ib_onoff = false //操作权限开关
end variables

forward prototypes
public subroutine wf_query (string s_sql, ref datawindow dw_x)
end prototypes

public subroutine wf_query (string s_sql, ref datawindow dw_x);string ls_presentation //dw 的格式
string ls_dwsyntax //dw 的语法
string ls_errors //错误信息
ls_presentation = "style(type=grid)"

ls_dwsyntax = SQLCA.SyntaxFromSQL(s_sql,ls_presentation,ls_errors);

if len(ls_errors) > 0 then
MessageBox('系统错误信息','datawindow 语法错误：' + ls_errors);
return;
end if

dw_x.create(ls_dwsyntax, ls_errors);

if len(ls_errors) > 0 then
MessageBox('系统错误信息','datawindow 语法错误：' + ls_errors);
return;
end if

dw_x.setTransObject(sqlca);
long ll_rows
ll_rows = dw_x.retrieve();
dw_x.Modify("DataWindow.ReadOnly=Yes")
messagebox('提示信息','满足条件的记录共 ' + string(ll_rows) + ' 行')
end subroutine
on w_sql.create
this.cb_undo=create cb_undo
this.mle_1=create mle_1
this.cb_execute=create cb_execute
this.cb_close=create cb_close
this.dw_1=create dw_1
this.Control[]={this.cb_undo,&
this.mle_1,&
this.cb_execute,&
this.cb_close,&
this.dw_1}
end on

on w_sql.destroy
destroy(this.cb_undo)
destroy(this.mle_1)
destroy(this.cb_execute)
destroy(this.cb_close)
destroy(this.dw_1)
end on

event resize;//窗体改变重新设置 dw_1 的大小
mle_1.width = this.WorkSpaceWidth() - gi_broderwidth - 440
dw_1.x = gi_broderwidth
dw_1.y = 410
dw_1.width = this.WorkSpaceWidth() - 2*gi_broderwidth
dw_1.Height = this.WorkSpaceHeight() - dw_1.y
dw_1.resize(this.WorkSpaceWidth() - 2*gi_broderwidth,this.WorkSpaceHeight() - dw_1.y - gi_broderwidth)
end event

event open;mle_1.textcase = Upper!
dw_1.SetTransObject(SQLCA)




end event

type cb_undo from commandbutton within w_sql
integer x = 37
integer y = 152
integer width = 375
integer height = 104
integer taborder = 30
integer textsize = -10
integer weight = 400
fontcharset fontcharset = gb2312charset!
fontpitch fontpitch = variable!
string facename = "宋体"
string text = "[&U] 撤 消"
end type

event clicked;mle_1.undo()
end event

type mle_1 from multilineedit within w_sql
integer x = 439
integer y = 32
integer width = 1755
integer height = 352
integer taborder = 10
integer textsize = -10
integer weight = 400
fontcharset fontcharset = gb2312charset!
fontpitch fontpitch = variable!
string facename = "宋体"
long textcolor = 33554432
string text = "输入验证信息，然后选中执行，即可进行查询操作。"
boolean vscrollbar = true
boolean autohscroll = true
boolean autovscroll = true
integer tabstop[] = {3}
borderstyle borderstyle = stylelowered!
end type

type cb_execute from commandbutton within w_sql
integer x = 37
integer y = 272
integer width = 375
integer height = 104
integer taborder = 40
integer textsize = -10
integer weight = 400
fontcharset fontcharset = gb2312charset!
fontpitch fontpitch = variable!
string facename = "宋体"
string text = "[&E] 执 行"
end type

event clicked;string ls_exe //要执行的 sql 语句
ls_exe = trim(mle_1.selectedtext())

if len(ls_exe) < 7 then
MessageBox('系统提示','选中要执行的完整语句，然后再点执行！')
return
end if

//1操作权限验证
if ib_onoff = false then
if ls_exe = 'I AM SUSUE' then
ib_onoff = true
Messagebox('系统提示','你的操作权限已经通过系统验证，可以进行SELECT操作！~r~n~r~n如要执行DELETE,UPDATE,INSERT操作要特别谨慎！！~r~n~r~选中要执行的语句点【[E] 执行】即可。')
else
Messagebox('系统提示','当前的操作存在不安全因素，~r~n~r~n你的操作权限没有通过系统验证前，无法进行任何操作！')
end if
return
end if
//1
string ls_6 //sql 语句的前 6 个字母
ls_6 = left(ls_exe,6)

//执行查询操作
choose case ls_6
case 'SELECT'
//在 dw_1 显示查询结果
wf_query(ls_exe,dw_1)
case 'INSERT','DELETE','UPDATE'
//用动态 sql 执行操作
EXECUTE IMMEDIATE :ls_exe;
if sqlca.sqlcode = 0 and sqlca.sqlnrows > 0 then
messagebox('系统信息','本次操作影响的行数为：' + string(SQLCA.SQLNRows) + '行')
commit;
else
messagebox('错误信息','语法错误或数据错误,~n~r错误信息：' + SQLCA.SQLErrText)
rollback;
end if
case else
//操作提示
MessageBox('系统提示','非法 SQL 语句或被禁止的操作！')
end choose
mle_1.SetFocus()
end event
type cb_close from commandbutton within w_sql
integer x = 37
integer y = 32
integer width = 370
integer height = 104
integer taborder = 20
integer textsize = -10
integer weight = 400
fontcharset fontcharset = gb2312charset!
fontpitch fontpitch = variable!
string facename = "宋体"
string text = "[&C] 关 闭"
end type

event clicked;close(parent)
end event

type dw_1 from datawindow within w_sql
integer x = 27
integer y = 408
integer width = 2158
integer height = 768
integer taborder = 50
string title = "none"
boolean hscrollbar = true
boolean vscrollbar = true
boolean hsplitscroll = true
boolean livescroll = true
borderstyle borderstyle = stylelowered!
end type

event rowfocuschanged;This.SelectRow(0,False)
This.SelectRow(this.GetRow(),True)
end event



end event

[lihao24]

谢谢5楼的，明天我试试看，如果行的话就结贴了，谢谢各位了！！

[ouyangibing]

上面有实现代码了，我说说思路：用输入sql语句动态创建一数窗（可以是漂浮的），再检索就可以了……
说起来就这么简单，实现还是有点麻烦的，试试吧……

[青锋-SS]

up.

[lihao24]

谢谢一楼的，问题已解决，但是如果多次执行sql语句的话，dw_new会不会重复创建？dw_new是数据窗口对象还是数据窗口控件？这个问题解决后马上结贴，谢谢

[lzj033]

太复杂了，看的眼都花了,在查询按钮里写
string ls_sql = ""
ls_sql = sle_1.text
dw_1.settransobject(sqlca)
dw_1.object.datawindow.table.select = ls_sql
dw_1.retrieve()