PB 中用带参数的 SQL 语句字符串检索数据并动态生成Datawindow

来源：互联网发布：单片机液晶屏显示编辑：程序博客网时间：2024/05/13 14:42

public function integer uf_cdw (string sql_s, any sql_arg[], ref string dw_cs);
DynamicStagingArea dsa_tmp
DynamicDescriptionArea dda_tmp
dsa_tmp = create DynamicStagingArea
dda_tmp = create DynamicDescriptionArea

string error_syntaxfromSQL,error_create
string new_syntax
int li_i
any anyvar[]
int err_ = 0
datastore ds_tmp
ds_tmp = create datastore

new_syntax = sqlca.syntaxfromsql(sql_s,'style(type=grid)',error_syntaxfromsql)

if len(error_syntaxfromsql) > 0 then
    dw_cs = '服务器生成临时表错误:~n'+error_syntaxfromsql
    err_ = -1

else
    long ll_r
    ds_tmp.create(new_syntax,error_create)
    setnull(new_syntax)

    DECLARE l_Cursor DYNAMIC CURSOR FOR dsa_tmp ;
    PREPARE dsa_tmp FROM :sql_s Using sqlca;
    DESCRIBE dsa_tmp INTO dda_tmp ;

    for li_i = 1 to upperbound(sql_arg)
        setdynamicparm(dda_tmp, li_i, sql_arg[li_i])
    next

    OPEN DYNAMIC l_Cursor USING DESCRIPTOR dda_tmp ;
    FETCH l_Cursor USING DESCRIPTOR dda_tmp ;
    ll_r ++
    DO WHILE TRUE
        CHOOSE CASE sqlca.SQLCode
            CASE -1
                dw_cs = '第 '+string(ll_r)+' 行次数据检索错误：~r~n'+sqlca.sqlerrtext
                err_ = -1
                exit
            CASE 100
                EXIT
            CASE 0
                ds_tmp.insertrow(ll_r)
                for li_i=1 to dda_tmp.numoutputs
                    choose case dda_tmp.outparmtype[li_i]
                        case TypeString!
                            anyvar[li_i]=GetDynamicString(dda_tmp,li_i)
                        case TypeUInt!,TypeDecimal!,TypeDouble!,TypeInteger!,TypeLong!,TypeReal!,TypeBoolean!,TypeULong!
                            anyvar[li_i]=getdynamicnumber(dda_tmp,li_i)
                        case TypeDate!
                            anyvar[li_i]=getdynamicdate(dda_tmp,li_i)
                        case TypeDateTime!
                            anyvar[li_i]=getdynamicdatetime(dda_tmp,li_i)
                        case TypeTime!
                            anyvar[li_i]=getdynamictime(dda_tmp,li_i)

                        case else // TypeUnknown!
                            dw_cs = '第 '+string(ll_r)+' 行次数据检索错误：~r~n未知的数据类型，请检查第 '+string(li_i)+' 列数据！'
                            err_ = -1
                            exit
                    end choose
                    if err_ <> -1 then
                        ds_tmp.setitem(ll_r,li_i,anyvar[li_i])
                    end if
                next
        END CHOOSE
        FETCH l_Cursor USING DESCRIPTOR dda_tmp ;
        ll_r ++
    LOOP
    CLOSE l_Cursor;
end if

if err_ = 0 then
    dw_cs = ds_tmp.object.datawindow.syntax + ds_tmp.object.datawindow.syntax.data

end if

destroy ds_tmp
destroy dsa_tmp
destroy dda_tmp

return err_

end function

该函数调用方式如下:

string sql_s
string dw_cs
any sql_arg[]

sql_s = 'select * from spt_datatype_info where fixlen = ? and type_name like ?'
sql_arg[1] = 2
sql_arg[2] = 'sm%'

if uf_cdw(sql_s,sql_arg[],ref dw_cs) = 0 then
    dw_1.create(dw_cs)
else
    messagebox('',dw_cs)

end if

SQL Server 2000 以及 MDB 数据库测试通过.
使用注意时注意 Transaction 的 DBMS 属性!
PB 90 中测试时使用 "MSS Microsoft SQL Server" 会报错,PB 115 中测试时使用"ADO.Net" 也会报错.
建议使用"OLE DB"或"ODBC"
其它的未测试.