在powerbuilder中使用动态sql

     
  powerbuilder是客户/服务器体系结构下的客户端的开发工具，用于开发客户应用程序。powerbuilder支持所有的sql语句，特别是支持动态sql语句，这为开发中处理复杂的任务提供了方便。  
   
  一、动态sql的基础知识    
   
  我们经常使用的sql语句大多都是静态的，也就是说，sql语句的结构是固定的，当编译script时，整个sql语句都是已知的，它们不能在运行时动态的改变。但是，在很多情况下，sql语句或sql所带的参数在编译时并不知道，应用必须在运行时才能生成sql语句。这种在运行时才能生成的sql语句叫动态sql语句。动态sql允许应用程序向数据库发送任何查询。此外，某些类型的操作，尤其是数据定义语言的语句如create和   drop等，对嵌入式sql根本就不可能。动态sql使得可以象发送查询一样向数据库发送ddl和数据存取语言的语句。  
   
  为了支持动态sql，powerbuilder提供了两种数据类型：dynamicstagingarea   和dynamicdescriptionarea。powerbuilder使用dynamicstagingarea类型的变量为后续语句保存信息，用作语句的运行和事物对象之间的连接。dynamicstagingarea类型的变量是powerbuilder内部使用的，用户无法访问dynamicstagingarea中的信息。powerbuilder提供了一个全局的dynamicstagingarea变量，变量名为sqlsa，当需要一个dynamicstagingarea变量时可以使用它。用户也可以根据需要定义并创建另外的dynamicstagingarea类型的变量。下面的例子定义并创建了一个dynamicstagingarea类型的变量，变量名是dsa_stage1。  
   
            dynamicstagingarea   dsa_stage1  
   
            dsa_stage1=create   dynamicstagingarea  
   
  powerbuilder使用dynamicdescriptionarea类型的变量保存动态sql语句中的输入和输出参数的信息。powerbuilder也提供了一个全局的dynamicdescriptionarea变量，变量名为sqlda，当需要一个dynamicdescriptionarea变量时可以使用它。用户也可以根据需要定义并创建另外的dynamicdescriptionarea类型的变量。下面的例子定义并创建了一个dynamicdescriptionarea类型的变量，变量名是dda_desc1。  
   
            dynamicdescriptionarea   dda_desc1  
   
            dda_desc1=create   dynamicdescriptionarea  
   
  为适应所有的sql语句，powerbuilder根据语句的类型和未知数的个数把动态sql分为四种不同的格式。下面将详细介绍动态sql的这四种格式。  
   
  二、动态sql格式一    
   
          使用这种格式执行的sql语句不会产生结果集，并且不需要输入参数。可以使用这种格式执行所有的数据定义语言。语法如下：  
   
          execute   immediate   sqlstatement     {   using   transactionobject   }  
   
          其中，sqlstatement是包括合法sql语句的字符串，该字符串必须放在一行上，并且不能包含表达式。它可以是字符串常量或前面带有冒号的powerbuilder字符串变量。transactionobject是指向数据库的事物对象名。此方法成立的前提是：1、用于除select之外的sql语句。2、在sql语句中不能包含变量。  
   
  例如，下面语句创建一个名为   auths的表，它用串ls_sql存放sql语句。  
   
  string   ls_sql  
   
  //若与sql   server、sybase   11、odbc数据库相连，在运行create前，  
   
  //必须置系统对象属性autocommit为true  
   
  sqlca.autocommit=true  
   
  ls_sql="create   table   auths(auths_id   char(6)   not   null,"+&  
   
              "auths_name   char(10)   not   null,auths_phone   char(12),"+&  
   
              "auths_address   char(50),primary   key(auths_id))"  
   
  execute   immediate     :ls_sql     using   sqlca;  
   
  sqlca.autocommit=false  
   
          注意：在给出的sql语句串的结束位置不能加“；”号。  
   
  使用格式一的动态sql语句适合于在程序运行期间，创建一些简单的临时的数据库对象来保存数据等，做一些程序运行的辅助性工作。  
   
  三、动态sql格式二    
   
  使用这种格式执行的sql语句不产生结果集，但是需要输入参数。使用这种格式可以执行所有形式的数据定义语言。语法如下：  
   
          prepare   dynamicstagingarea   from   sqlstatement   {   using   transactionobject   }  
   
          execute     dynamicstagingarea     using   {parameterlist   }  
   
          其中，dynamicstagingarea是dynamicstagingarea变量名。sqlstatement和transactionobject的意义同格式一。parameterlist是逗号分隔的一组powerscript变量表，在它们的前面要有冒号。此方法成立的前提是：1、用于除select之外的sql语句。2、在sql语句中可用变量，但必须预先说明。  
   
  例如，下面语句在sqlsa中准备了一个带有一个参数的delete语句，然后把变量author_code的值带入并运行它：  
   
  string     sl_sql,author_code  
   
  sl.sql="delete   from   auths   where   auths_id=?"  
   
  prepare   sqlsa   from   :ls_sql   using   sqlca;  
   
  author_code=a00001  
   
          execute   sqlsa   using   :author_code;  
   
          格式二可用于执行除select语句之外的其它任意sql语句，由于可以输入参数，因此，它可以灵活地控制sql语句的执行条件。  
   
  四、动态sql格式三    
   
  使用这种格式执行的sql语句产生结果集，也需要输入参数，但结果集中列的数目必须固定。使用这种格式的语法如下：  
   
  declare   cursor   |   procedure  
   
                      dynamic   cursor   |   procedure   for   dynamicstagingarea;  
   
  prepare     dynamicstagingarea     from   sqlstatement  
   
                      {   using   transactionobject   };  
   
  open     dynamic     cursor   {   using   parameterlist   };  
   
  execute     dynamic     procedure     {   using   parameterlist   };  
   
  fetch     cursor   |   procedure     into   hostvariblelist   ;  
   
  close   cursor   |   procedure   ;  
   
          其中，cursor或procedure   是要使用的游标或过程名；dynamicstagingarea、sqlstatement、transactionobject和parameterlist的意义同格式二。hostvariblelist是存放查询结果的powerbuilder变量。此方法成立的前提是：1、用于动态的select语句，但select语句中列的数目必须固定，where、order   by等子句可任意合成。2、在sql语句中可用变量，但必须预先说明。  
   
  例如，下面语句在ls_sql中准备一个查询auths表的select语句，把查询结果返回给powerbuilder变量：  
   
  string   name,ls_sql,code      
   
  code="a%"                              
   
  ls_sql="select   auths_name   from   auths   where   auths_id   like   ?"  
   
  declare   c1   dynamic   cursor   for   sqlsa;  
   
  prepare   sqlsa   from   :ls_sql;  
   
  open   dynamic   c1   using   :code;  
   
  lable:  
   
  fetch   c1   into   :name;  
   
  if   sqlca.sqlcode=0   then       //如果成功取出记录  
   
        //进行相应处理  
   
        goto   lable                               //取下一条  
   
  end   if  
   
  close   c1;                                   //关闭游标  
   
          格式三可用于执行where、order   by   等子句任意组合的select语句。  
   
 

发表者：ice2water
五、动态sql   格式四    
   
  这是一种全动态的sql   语句。全动态的sql   语句是指这样的语句：在写程序时并不知道select语句的选择项是什么或有多少个，也不知道sql语句中包含了多少个变量，它们是变化的。全动态sql支持临时生成的sql。全动态sql实现起来很复杂，也很有用，下面我们将详细讲述。语法如下：  
   
  declare   cursor   |   procedure  
   
                        dynamic   cursor   |   procedure   for   dynamicstagingarea;  
   
  prepare     dynamicstagingarea     from   sqlstatement  
   
                        {   using   transactionobject   };  
   
  describe   dynamicstagingarea   into   dynamicdescriptionarea;  
   
  open   dynamic   cursor   |   procedure  
   
                        using   description   dynamicdescriptionarea;  
   
  execute     dynamic     cursor   |   procedure  
   
                        using   description   dynamicdescriptionarea;  
   
  fetch     cursor   |   procedure     using   description   dynamicdescriptionarea;  
   
  close     cursor   |   procedure   ;  
   
  其中，cursor或procedure   是要使用的游标或过程名；dynamicstagingarea是dynamicstagingarea变量名；sqlstatement   是包含合法sql语句的字符串，它可以是字符串常量或前面带有冒号的powerbuilder字符串变量，它必须包含在一行且不能包括表达式；transactionobject是指向数据库的事物对象名。dynamicdescriptionarea   是dynamicdescriptionarea   变量名。  
   
            可以用以下方法设置在prepare语句中出现的每个输入参数的类型和值。powerbuilder在   describe运行时可取得sqlda的属性numinputs和inparmtype，可用这些值和函数setdynamicparm给输入参数的类型和值赋值。输入参数是任选的，若要使用它们，应在运行open或execute语句之前给它们赋值。  
   
            可以用以下方法取得prepare语句中的每个输出参数的类型和值。如果使用的数据库支持输出参数描述，powerbuilder应用可在describe语句执行时取得sqlda的numoutputs值；若数据库不支持输出参数描述，powerbuilder应用可在fetch语句执行时取得sqlda的numoutputs值。可用输出参数的号码从输出参数类型数组中获得特定参数的类型。得到类型后，在fetch语句后可调用恰当的函数取出输出值。  
   
          下面是一个使用动态sql格式四的例子，此例有一个字符串输入参数，但输出参数的类型和个数不定。  
   
          string   ls_sql,ls_val  
   
          integer   i,li_boolean  
   
          ls_sql="select   *   from   auths   where   auths_id=?"  
   
          declare   c1   dynamic   cursor   for   sqlsa；    
   
          prepare   sqlsa   from   :ls_sql;  
   
          describe   sqlsa   into   sqlda;  
   
          //若describe成功，则输入描述符数组将包含一个输入描述符，    
   
          //在打开游标前必须先给输入描述符赋值。    
   
          setdynamicparm(sqlda,1,a00001)           //这里的“1”表示第一个参数    
   
          open   dynamic   c1   using   descriptor   sqlda;    
   
          lable:    
   
          fetch   c1   using   descriptor   sqlda;    
   
          if   sqlca.sqlcode=0   then   //若fetch成功    
   
            for   i=1   to   sqlda.numoutputs      
   
                  //sqlda.numoutputs含有输出参数的个数    
   
                  choose   case   sqlda.outparmtype[i]    
   
                  case   typestring!    
   
                            ls_val=getdynamicstring(sqlda,i)    
   
                  case   typedate!    
   
                            ls_val=string(getdynamicdate(sqlda,i),dd   mm   yyyy)    
   
                  case   typetime!    
   
                            ls_val=string(getdynamictime(sqlda,i),hh:mm:ss)    
   
                  case   typedatatime!    
   
                            ls_val=string(getdynamicdatetime(sqlda,i),&    
   
                                                          dd   mm   yyyy   hh:mm:ss)    
   
                  case   typeinteger!,typelong!,typedecimal!,typedouble!,typereal!    
   
                            ls_val=string(getdynamicnumber(sqlda,i))    
   
                  case   typeboolean!    
   
                            li_boolean=getdynamicnumber(sqlda,i)    
   
                            if   li_boolean=1   then    
   
                                  ls_val="true"    
   
                            else    
   
                                  ls_val="false"    
   
                            end   if  
   
                  end   choose  
   
                  ……   //进行相应处理  
   
            next  
   
            goto   lable     //取下一条记录  
   
            enf   if  
   
            close   c1；  
   
            格式四适合于所有子句都不确定的select语句，这是最有用的一种动态sql。    
   
  六、使用动态sql的一个实例  
   
     
         
   
          下面我们介绍一个用于单表单查询条件生成的通用窗口的实现，在该窗口的实现中使用了动态sql格式四。    
   
         
   
          该窗口是一个响应式窗口，在调用该窗口时传递所要查询的表名给此窗口，在字段名下拉列表框中显示出此表中的所有字段，选定字段名后，在数值下拉列表框中显示出选定字段的内容，按“确定”按钮，生成查询条件并返回。下面是源代码。  
   
  1、定义窗口的实例变量：  
   
  string     tablename       //用于存放表名  
   
  string     s_field             //用于存放选择的字段名  
   
  string     typename         //用于存放所选字段的数据类型  
   
          2、窗口open事件中的脚本:  
   
  tablename=message.stringparm  
   
  //定义cursor将表名为tablename的所有列从系统表“pbcatcol”中取出，  
   
  //并显示在下拉列表框中  
   
  int   i  
   
  string   str  
   
  declare   c1   cursor   for   select   pbc_hdr   from   pbcatcol  
   
                  where   pbc_tnam=:tablename;  
   
  open   c1;  
   
  fetch   c1   into   :str;  
   
  do   while   sqlca.sqlcode=0  
   
                ddlb_1.additem(str)  
   
                i=i+1  
   
                fetch   c1   into   :str;  
   
  loop  
   
  close   c1;  
   
          3、字段名下拉列表框selectchanged事件中的脚本：  
   
  string   s_ddlbtext,sql  
   
  s_ddlbtext=string(ddlb_1.text)  
   
  select   pbc_cnam     into   :s_field   from   pbcatcol      
   
        where   pbc_tnam=:tablename   and   pbc_hdr=:s_ddlbtext;  
   
  ddlb_2.reset()  
   
  //定义cursor将字段名为s_field的字段值从表“tablename”中取出，  
   
  //并显示在下拉列表框中  
   
  string   str  
   
  sql="select   distinct   "+s_field+"   from   "+tablename  
   
  prepare   sqlsa   from   :sql;  
   
  describe   sqlsa   into   sqlda;  
   
  declare   c1   dynamic   cursor   for   sqlsa;  
   
  open   dynamic   c1   using   descriptor   sqlda;  
   
  if   sqlca.sqlcode<0   then  
   
                messagebox("提示信息","动态游标无法执行!"+sqlca.sqlerrtext)  
   
                return   sqlca.sqlcode  
   
  end   if  
   
  lab1:  
   
    fetch   c1   using   descriptor   sqlda;  
   
    if   sqlca.sqlcode=0   then  
   
                              choose   case   sqlda.outparmtype[1]  
   
                                            case   typestring!  
   
                                                          typename="string"  
   
                                                          str=getdynamicstring(sqlda,1)  
   
                                            case   typedate!  
   
                                                          typename="date"  
   
                                                          str=string(getdynamicdate(sqlda,1),"yyyy-mm-dd")  
   
                                            case   else  
   
                                                          typename="number"  
   
                                                          str=string(getdynamicnumber(sqlda,1))  
   
                              end   choose  
   
                              ddlb_2.additem(str)  
   
        goto   lab1  
   
  end   if  
   
  close   c1;  
   
          4、命令按钮“确定”的clicked事件的脚本：  
   
  string   wheresql  
   
  if   string(ddlb_1.text)<>""   and   string(ddlb_2.text)<>""   &  
   
        and   string(ddlb_3.text)<>""   then  
   
                choose   case   typename  
   
                case   "string"  
   
                              wheresql=s_field+"   "+ddlb_3.text+"   "+string(ddlb_2.text)+""  
   
                case   "date"  
   
                              wheresql=s_field+"   "+ddlb_3.text+"   date("+string(ddlb_2.text)+")"  
   
        case   "number"  
   
                              wheresql=s_field+"   "+ddlb_3.text+"   "+ddlb_2.text  
   
        end   choose  
   
                closewithreturn(parent,wheresql)  
   
  else  
   
                messagebox("提示信息","请正确输入字段名、运算符和数值！")  
   
                return   1  
   
  end   if