PB webservice端的多SQL语句执行 服务器端根据送入的SQL语句(可同时多条)动态执行

服务器端根据送入的SQL语句(可同时多条)动态执行,如果为SELECT　语句，结果值以文本形式返回，并且在另外一数组里面返回结果值数据类型，如其中任何一条语句有错误，则自动回滚．

SELECT 语句只能单独送入一条．

 

uf_execute函数(如果调用查询,则只送入一条SQL命令)

 

Long ll_Num,ll_Row,li_i
Any la_res
ll_Num = UpperBound(as_sql)
if ll_num <= 0 Then
 gf_getsqlcaerror(sqlca,'送入SQL语句错误!!!',as_error)
 return -1
end if
gf_begin_transaction(sqlca)
if sqlca.sqlcode<> 0 then
 gf_getsqlcaerror(sqlca,'数据库连接错误!' + sqlca.dbparm ,as_error)
 gf_rollback_transaction(sqlca)
 Return -1
end if
//调用执行函数,回滚在函数里面执行.
if this.uf_execute_sql(as_sql,as_result,as_type,as_error) = -1 then
 return -1
end if

gf_commit_transaction(sqlca)
return 0

 

uf_execute_sql 函数

Long ll_Num,ll_Row,li_i
Any la_res
ll_Num = UpperBound(as_sql)
String ls_Prev
For ll_Row = 1 To ll_Num
 ls_Prev = Upper(Left(as_sql[1],4))
 Choose Case ls_Prev
  Case 'SELE' //有返回值语句,一般情况下送入语句一条
   declare dc_value dynamic cursor for sqlsa ;
   prepare sqlsa from :as_sql[ll_Row];
   describe sqlsa into sqlda;
   open dynamic dc_value using descriptor sqlda;
   if sqlca.sqlcode<>0 then
    gf_getsqlcaerror(sqlca,'打开动态游标错误!',as_error)
    gf_rollback_transaction(sqlca)
    return -1
   end if
   fetch dc_value using descriptor sqlda;
    if  sqlca.sqlcode=0 then //只取第一条数据,如果查询有多条数据,则必须用数据窗口或者数据集方式获取
     for li_i=1 to sqlda.NumOutPuts
      choose case sqlda.OutParmType[li_i]
       case typestring!
        la_res=sqlda.getdynamicstring(li_i)
        as_type[li_i] = 'string'
       case typedate!
        la_res=sqlda.getdynamicdate(li_i)
        as_type[li_i] = 'date'
       case typedatetime!
        la_res=sqlda.getdynamicdatetime(li_i)
        as_type[li_i] = 'datetime'
       case typetime!
        la_res=sqlda.getdynamictime(li_i)
        as_type[li_i] = 'time'
       case else
        la_res=sqlda.getdynamicnumber(li_i)
        as_type[li_i] = 'number'
      end choose
      as_result[li_i] = string(la_res)
     next
    end if
   close dc_value;
  Case Else //非选择返回语句
   EXECUTE IMMEDIATE :as_sql[ll_Row] using sqlca;
   IF sqlca.sqlcode <> 0 THEN
    gf_getsqlcaerror(sqlca,'执行到语句:' + as_sql[ll_Row] + "出错!",as_error)
    gf_rollback_transaction(sqlca)
    return -1
   Else
    as_result[1] = "命令执行成功！一共执行了：" + String(ll_Num) + "条命令"
    as_type[1] = 'string'
   END IF
 end choose
Next

return 0