Command对象

作者:网络 来源:佚名 更新时间:2008-02-05 19:22:30 点击:
注册会员,创建你的web开发资料库,


    command对象特定地为处理各种类型的命令而设计,特别是那些需要参数的命令。与connection对象相似,command对象可以运行返回记录集和不返回记录集两种类型的命令。实际上,如果命令不含有参数,那么它并不关心是使用connection对象,还是command对象,还是recordset对象。

9.2.1 返回记录集
       对于一个返回记录集的命令,可使用execute方法。然而,与connection对象不同,必须使用commandtext属性,而不能在execute方法中使用命令文本。
       set cmdauthors = server.createobject("adodb.command")

       cmdauthors.commandtext = "authors"

       set rsauthors = cmdauthors.execute
       这是告诉command对象去执行一个简单的、返回一个只读记录集的命令的最简单方法。
       execute方法也有一些可选参数,如表9-2所示:
表9-2  command对象的execute方法的参数及说明


       参数recordsaffected与options同前面解释的一样,另外也可以使用commandtype属性设置命令类型:
       set cmdauthors = server.createobject("adodb.command")

       cmdauthors.commandtext = "authors"
       cmdauthors.commandtype = adcmdtable
       如果不设置其他参数,也可以在execute行上设置,必须为它们使用逗号:
       set rsauthors = cmdauthors.execute(, , adcmdtable)
       在本章后面处理存储过程时,将会看到参数parameters的用途。
       改变光标类型
       值得注意是,使用execute方法返回的记录集具有缺省的光标类型。这意味着这是只能前移的、只读的记录集。虽然使用execute方法不能改变这种情况,但对这个问题有一个解决的方法。
       如果需要使用一个命令,并且要求不同的光标和锁定类型,那么应该使用recordset的open方法,此时command对象作为recordset的数据源。例如:
       cmdauthors.activeconnection = strconn
       cmdauthors.commandtext = "authors"
       cmdauthors.commandtype = adcmdtable

       rsauthors.open cmdauthors, , adopendynamic, adlockoptimistic
       注意,在open命令行中忽略了连接细节,因为连接设置在command对象中。连接细节在命令打开前已经设置在command对象的activeconnection属性中。

9.2.2 操作命令
       对于操作命令,比如那些无记录返回的更新命令,整个过程相似,只需移去设置记录集的代码:
       set cmdupdate = server.createobject("adodb.command")
       strsql = "update titles set price = price * 1.10" & "where type='business'"

       cmdupdate.activeconnection = strconn
       cmdupdate.commandtext = ssql
       cmdupdate.commandtype = adcmdtext

       cmdupdate.execute , , adexecutenorecords
       注意,我们在此设置了命令类型,然后在execute行中增加了额外的设置选项。这段代码运行update命令,并且保证不会创建新的记录集。
9.2.3 存储过程
       存储过程的使用是command对象得到应用的一个领域。存储过程(有时也称存储查询)是存储在数据库中预先定义的sql查询语句。
       为什么应该创建和使用存储过程而不是在代码中直接使用sql字符串呢?主要有以下几个理由:
       · 存储过程被数据库编译过。这样可以产生一个“执行计划”,因此数据库确切地知道它将做什么,从而加快了过程的执行速度。
       · 存储过程通常被数据库高速缓存,这样使它们运行得更快,因为此时不需要从磁盘中读取它们。并非所有的数据库都支持这种缓存机制,比如微软的access就不支持,而sql server却支持。
       · 通过指定数据库中的表只能被存储过程修改,可以确保数据更安全。这意味着具有潜在危险的sql操作不会执行。
       · 可以避免将asp代码和冗长的sql语句混在一起,从而使asp代码更易于维护。
       · 可以将所有sql代码集中存放于服务器。
       · 可以在存储过程中使用输出参数,允许返回记录集或其他的值。
       一般说来,存储过程几乎总是比相当的sql语句执行速度快。
       为了使用存储过程,只要将存储过程的名字作为命令文本,并设置相应的类型。例如,考虑前面更新书价的例子。如果在sql server上创建一个存储过程,可以编写代码:
       create procedure usp_updateprices
       as
              update titles
              set        price = price * 1.10
              where  type='business'
       对于微软的access数据库,可以使用一个简单的更新查询语句完成相同的任务,如图9-1所示:

       要在asp网页中运行该存储过程,只需要使用以下代码:
       set cmdupdate = server.createobject("adodb.command")

       cmdupdate.activeconnection = strconn
       cmdupdate.commandtext = "usp_updateprices"
       cmdupdate.commandtype = adcmdstoredproc

       cmdupdate.execute , , adexecutenorecords
       这只是运行存储过程。没有记录集返回,因为只是在更新数据。需要记住的是,除非确实需要,不要创建记录集。
       虽然这样做也可以,但并不是很灵活,因为仅仅处理一种类型的书。更好的做法是创建一个允许我们选择书类型的过程,这样就不必为每类书创建一个过程。同样也可去掉固定的10%更新,这样使得灵活性更好。那么,如何才能做到这一点呢,很简单,使用参数。
1.  参数
存储过程的参数(或变量)与一般的过程和函数的参数一样,可以传到函数内部,然后函数可以使用它的值。sql server(其他数据库也一样,包括access)中的存储过程都具有这样的功能。
为了使存储过程能处理多种类型的书,甚至允许用户指定价格的增加(或减少),需要增加一些参数:
create procedure usp_updateprices
       @type           char(12),
       @percent              money

as
       update titles
       set        price = price * (1 + @percent / 100)
       where  type = @type
现在,存储过程usp_updateprices带有两个参数:
· 一个是书的类型(@type)。
· 一个是书价变化的百分比(@percent)。
与vbscript的函数一样,这些参数都是变量。然而,与vbscript和其他脚本语言不同的是:在这些脚本语言中的变量都是variant类型,而sql变量具有确定的类型(char、money等等)。必须遵守sql变量的命名规范,即变量必须以符号@开始。
注意,我们让百分数作为一个整数(如10代表10%),而不是作为一个分数值传入此过程。这只是让存储过程变得更直观一些。
2.  parameters集合
那么,现在有了带参数的存储过程,但如何通过ado来调用它呢?我们已经见到了如何用command对象调用不带参数的存储过程,实际上,它们之间并没有什么不同。不同之处在于parameters集合的使用。
parameters集合包含存储过程中每个参数的parameter对象。然而,ado并不会自动地知道这些参数是什么,因此,必须用createparameter方法创建它们,采用下面的形式:
set parameter = command.createparameter (name, [type], [direction], [size], [value])
参数及说明如表9-3所示:
表9-3  createparameter方法的参数及说明


一旦创建了参数就可以将其追加到parameters集合中,例如:
set parvalue = cmdupdate.createparameter("@type", advarwchar, adparaminput, _
                                                               12, "business")
cmdupdate.parameters.append parvalue

set parvalue = cmdupdate.createparameter("@percent", adcurrency, _
                                                               adparaminput,  , 10)
cmdupdate.parameters.append parvalue
没有必要显式地创建一个对象去保存参数,缺省的variant类型已经可以工作得相当好。如果不想创建一个变量,也可以走捷径,例如下面的代码:
cmdupdate.parameters.append =  _
cmdupdate.createparameter("@percent", adcurrency, adparaminput,  , 10)
       这使用createparameter方法返回一个parameter对象,并用append方法接收它。这种方法比使用变量运行得快,却加长了代码行,可读性比较差。可以根据自己的爱好选择其中一种方法。
       参数加到parameters集合后,就保留在其中,因此,不一定在创建参数时就为每个参数赋值。可以在命令运行前的任何时候设置参数的值。例如:
cmdupdate.parameters.append =  _
cmdupdate.createparameter("@percent", adcurrency, adparaminput)

       cmdupdate.parameters("@percent") = 10
前一章提到了访问集合中的值有好几种方法,parameters集合并没有什么不同。上面的例子使用参数的名字在集合中检索参数,也可以使用索引号进行检索:
cmdupdate.parameters(0) = 10
以上代码对参数集合中第一个(parameters集合从0开始编号)参数进行了赋值。使用索引号比使用名字索引速度快,但很显然使用名字使代码更易读。
重点注意parameters集合中参数的顺序必须与存储过程中参数的顺序相一致。
运行带参数的命令
一旦加入参数,就可立即运行命令,同时这些参数的值传入存储过程。现在可用一个友好的网页去更新用户选择的类型的书价。例如,假设有一个名为updateprices.asp的网页,其运行时的界面如图9-2所示:

                   图9-2  updateprices.asp网页运行时的界面
通过数据库中获取书类型的列表,可以很轻松地动态创建该页面。首先要做的是包含文件connection.asp,该文件包含了连接字符串(保存在变量strconn中)以及对ado常数的引用,这在前面的章节已经讨论过。
<!-- #include file="../include/connection.asp" -->
接下来,可以创建一个窗体(在这儿不显示大量文本,仅仅用一个样本文件)。该窗体调用一个名为storeprocedure.asp的文件。
<form name="updateprices" method="post" action="storedprocedure.asp">
<table>
<tr>
  <td>book type:</td>
  <td><select name="lsttypes"></td>
现在开始编写asp脚本从title表中读取书的类型。使用一个sql字符串只返回唯一的书类型,然后将返回值放到html的option标记中:
<%
  dim rectypes
  dim sdq

  sdq = chr(34)          ' double quote character

  set rectypes = server.createobject("adodb.recordset")

  rectypes.open "usp_booktypes", strconn

  while not rectypes.eof
    response.write "<option value=" & sdq & rectypes("type") & sdq & _
                   ">" & rectypes("type")
    rectypes.movenext
  wend

  rectypes.close
  set rectypes = nothing
  
%>
显示书的类型后,接着可以构建窗体的其他部分,包括一个允许用户输入书价变化百分数的文本框。
</select>
  </td>
</tr>
<tr>
  <td>percent value</td>
  <td><input name="txtpercent" type="text"></td>
</tr>
</table>
<p>
<input type="submit" value="run query">
</form>
现在看一下run query按钮调用的asp文件storedprocedure.asp。首先,声明变量并从调用窗体取出书的类型和百分数。
dim cmdupdate
dim lrecs
dim stype
dim cpercent

' get the form values
stype = request.form("lsttypes")
cpercent = request.form("txtpercent")
现在可以向用户显示一些确认信息,告诉他们将发生什么。
' tell the user what's being done
response.write "updating all books"
if stype <> "all" then
  response.write " of type <b>" & stype & "</b>"
end if
response.write " by " & cpercent & "%<p>"
现在重新回到代码内部,在此创建command对象和参数。
set cmdupdate = server.createobject("adodb.command")

with cmdupdate
  .activeconnection = strconn
  .commandtext = "usp_updateprices"
  .commandtype = adcmdstoredproc
利用从前面网页的窗体中提取的数据值,使用快捷方法创建和增加参数。
' add the parameters
.parameters.append .createparameter ("@type", advarwchar, adparaminput, _
12, stype)
.parameters.append .createparameter ("@percent", adcurrency, _
adparaminput, , cpercent)
现在,运行存储过程。
' execute the command
  .execute lrecs, , adexecutenorecords
end with
为了确认,可以告诉用户已经更新多少条记录。
  ' and finally tell the user what's happened
  response.write "procedure complete. " & lrecs & " were updated."

  set cmdupdate = nothing
%>
这样就有了两个简单界面。前者创建了一个供选择的项目列表,后者使用其中某个项目值更新数据。这是许多需要显示和更新数据的asp页面的基础。
3.  传递数组参数
parameters参数集合一般来说比较好用,但有时稍有麻烦(尤其对于新手)。好在有一种快捷方法,使用execute方法的parameters参数。例如,调用存储过程usp_updateprices,但不使用parameters集合。
创建一个command对象,并同前面一样设置其属性。
' set cmdupdate = server.createobject("adodb.command")

' set the properties of the command
with cmdupdate
       .activeconnection = strconn
       .commandtext = "usp_updateprices"
       .commandtype = adcmdstroreproc
但这里正是差异所在。我们仅是通过execute方法传递参数给存储过程,而不是创建参数并添加到集合中。
       ' execute the command
       .execute lngrecs, array(strtype, curpercent), adexecutenorecords
end with
这里使用了array函数,将单个变量转换为数组,以适于方法调用。这种方法当然也有缺点:
· 只能使用输入参数。因为不能指定参数的类型和传递方向,而缺省为输入参数。
· 如果要多次调用存储过程,这种方法速度就比较慢,因为ado将向数据存储询问参数的内容及数据类型。
集合方法和数组方法之间在速度上的差异非常之小,几乎可以忽略。所以,如果只有输入参数,可随便使用哪一种。实际上,人们更喜欢使用parameters集合的方法,尽管它稍为繁琐,但是使参数的属性更加明确。
4.  输出参数
我们已经知道如何获得受命令影响的记录数,如果需要更多信息,却又不想返回一个记录集,怎么办?也许想从存储过程中返回两个或三个值,但又不想费心创建一个记录集。在这时,可以定义一个输出参数,其值由存储过程提供。
例如,对于更新书价的程序,如果想在更新之后找出最高价格,可将存储过程改成:
create procedure usp_updatepricesmax
       @type           char(12),
       @percent              money,
       @max            money           output
as
begin
       update titles
       set       price = price * (1 + @percent / 100)
       where  type = @type

       select @max = max(price)
       from    titles
end
这只是在执行更新后运行了一个简单的select语句,并将值赋给输出参数。
现在可以改写stroreprocedure.asp的代码从而获取变量@max的值。
<%
  dim cmdupdate
  dim lngrecs
  dim strtype
  dim curpercent
  dim curmax

  ' get the form values
  strtype = request.form("lsttypes")  
  curpercent = request.form("txtpercent")

  ' tell the user what's being done
  response.write "updating all books" & " of type <b>" & strtype & "</b>" & _
" by " & curpercent & "%<p>"

  set cmdupdate = server.createobject("adodb.command")

  ' set the properties of the command
  with cmdupdate
    .activeconnection = strconn
    .commandtext = "usp_updatepricesmax"
    .commandtype = adcmdstoredproc
我们只是在集合中加入了另一个参数,但这次指定为输出参数。注意它并没有赋值,因为其值将由存储过程提供,记住这是一个输出参数。
    ' add the parameters
    .parameters.append .createparameter("@type", advarwchar, adparaminput, _
12, strtype)
    .parameters.append .createparameter("@percent", adcurrency, _
adparaminput, , curpercent)
              .parameters.append.createparameter("@max", adcurrency, adparamoutput)

   ' execute the command
    .execute lngrecs, , adexecutenorecords
一旦执行这个过程,就可从集合中取得该值。
       ' extract the output parameter, which the stored
       ' procedure has supplied to the parameters collection
       curmax = .parameters("@max")
  end with


  ' and finally tell the user what's happened
  response.write "procedure complete. " & lngrecs & _
" records were updated.<p>"
         response.write "the highest price book is now " & _
                                   formatcurrency(curmax)

  set cmdupdate = nothing
%>
如果有不止一个输出参数,可用相同的方法访问。可以使用参数名或索引号取出集合中的值。
5.  返回值
对函数返回值的处理不同于存储过程返回值的处理,这常常导致混淆。在函数中,经常是返回一个布尔值来表明函数运行的成功与否。
if somefunctionname() = true then
       ' function succeeded
但在调用一个存储过程时,却不能使用同样的方法,因为存储是用execute方法运行的,同时返回一个记录集。
set rsauthors = cmdauthors.execute
如果得不到一个返回值,如何确定是否已正确执行存储过程?当发生错误时,会报告错误,这样就可使用前一章提供的错误处理代码来处理错误。但对于一些非致命的逻辑错误怎么办?
例如,考虑向employee表添加一个新职员的情形。你可能不想防止两个职员同名的情况,但想注明这个情况。那么,可以使用一个返回值以表明是否已有同名的职员存在。存储过程如下:
create procedure usp_addemployee
       @emp_id             char(9),
       @fname               varchar(20),
       @minit                  char(1),
       @lname               varchar(30),
       @job_id                     smallint,
       @job_lvl              tinyint,
       @pub_id              char(4),
       @hire_date           datetime
as
begin
       declare @exists       int                 -- return value

       -- see if an employee with the same name exists
       if exists(select *
                      from          employee
                      where fname = @fname
                      and            minit = @minit
                      and            lname = @lname)
          select @exists = 1
       else
          select @exists = 0

       insert into employee (emp_id, fname, minit, lname,
                                   job_id, job_lvl, pub_id, hire_date)
       values (@emp_id, @fname, @minit, @lname, @job_id,
                      @job_lvl, @pub_id, @hire_date)
       return @exists
end
该过程首先检查是否有同名的职员存在,并据此设定相应的变量exists,若存在同名,就设为1,否则为0。然后将该职员加到表中,同时把exists的值作为返回值返回。
注意尽管返回了一个值,但并未将其声明为存储过程的参数。
调用该过程的asp代码如下:
<!-- #include file="../include/connection.asp" -->
<%
  dim cmdemployee
  dim lngrecs
  dim lngadded

  set cmdemployee = server.createobject("adodb.command")

  ' set the properties of the command
  with cmdemployee
    .activeconnection = strconn
    .commandtext = "usp_addemployee"
    .commandtype = adcmdstoredproc

    ' create the parameters
       ' notice that the return value is the first parameter
    .parameters.append .createparameter ("return_value", adinteger, _
adparamreturnvalue)
    .parameters.append .createparameter ("@emp_id", adchar, adparaminput, 9)
    .parameters.append .createparameter ("@fname", advarwchar, adparaminput, 20)
    .parameters.append .createparameter ("@minit", adchar, adparaminput, 1)
    .parameters.append .createparameter ("@lname", advarwchar, adparaminput, 30)
    .parameters.append .createparameter ("@job_id", adsmallint, adparaminput)
    .parameters.append .createparameter ("@job_lvl", adunsignedtinyint, adparaminput)
    .parameters.append .createparameter ("@pub_id", adchar, adparaminput, 4)
    .parameters.append .createparameter ("@hire_date", addbtimestamp, _
adparaminput, 8)

    ' set the parameter values
    .parameters("@emp_id") = request.form("txtempid")
    .parameters("@fname") = request.form("txtfirstname")
    .parameters("@minit") = request.form("txtinitial")
    .parameters("@lname") = request.form("txtlastname")
    .parameters("@job_id") = request.form("lstjobs")
    .parameters("@job_lvl") = request.form("txtjoblevel")
    .parameters("@pub_id") = request.form("lstpublisher")
    .parameters("@hire_date") = request.form("txthiredate")

       ' run the stored procedure
    .execute lngrecs, , adexecutenorecords

       ' extract the return value
    lngadded = .parameters("return_value")
  end with

  response.write "new employee added.<p>"
  if lngadded = 1 then
    response.write "an employee with the same name already exists."
  end if

  set cmdemployee = nothing
%>
需要重点注意,返回值应当作为集合中第一个参数被创建。即使返回值并不作为一个参数出现在存储过程中,总是parameters集合中的第一个parameters。
因此,特别强调一点:
存储过程的返回值必须声明为parameters集合中第一个参数,同时参数的direction值必须为adparamreturnvalue。
使用返回值
现在定义一个初始窗体,如图9-3所示:

按下add employee按钮会产生如图9-4所示的显示:

再添加同样的细节(id号不同)会得到如图9-5所示的界面:

6.  更新参数
无需输入所有的参数细节,只需调用refresh方法,就能让ado完成更新。例如,假设已经创建了一个带有与前面例子相同的参数的过程usp_addemployee,并且没有改变运行的页面。
with cmdemployee
       .activeconnection = strconn
       .commandtext = "usp_addemployee"
       .commandtype = adcmdstoredproc
然后调用refresh方法。
.parameters.refresh
这告诉ado向数据存储请求每个参数的细节,并创建parameters集合。然后可以为其赋值。
.parameters("@emp_id") = request.form("txtempid")
.parameters("@fname") = request.form("txtfirstname")
.parameters("@minit") = request.form("txtinitial")
.parameters("@lname") = request.form("txtlastname")
.parameters("@job_id") = request.form("lstjobs")
.parameters("@job_lvl") = request.form("txtjoblevel")
.parameters("@pub_id") = request.form("lstpublisher")
.parameters("@hire_date") = request.form("txthiredate")
注意并不需要创建任何参数,包括返回值。
这似乎真是一条捷径,但应意识到这种方法也造成了性能上的损失,因为ado必须向提供者查询以获得存储过程的参数细节。尽管如此,这种方法还是很有用的,尤其是在从参数中取出正确的值有困难的时候。
实际上,可以编写一个小实用程序作为开发工具使用,用来完成更新并建立append语句,可以将其粘贴到自己的代码中。它看上去应该与图9-6所示的generateparameters.asp asp页面类似。

其代码相当简单。首先是包含连接符串和另一个adox常数文件。
<!-- #include file="../include/connection.asp" -->
<!-- #include file="../include/adox.asp" -->
接下来创建一个窗体,指定目标为printparameters.asp asp页面。
<form name="procedures" method="post" action="printparameters.asp">
connection string:<br>
<textarea name="txtconnection" cols="80" rows="5">
<% = strconn %>
</textarea>
<p>
stored procedure:<br>
<select name="lstprocedures">
然后,使用adox从sql server中得到存储过程的列表,同时创建一个含有这些存储过程名字的列表框。
<%
  dim catpubs
  dim procprocedure

  ' predefine the quote character
  strquote = chr(34)
  set catpubs = server.createobject("adox.catalog")

  catpubs.activeconnection = strconn

  for each procprocedure in catpubs.procedures
   response.write "<option value=" & _
strquote & procprocedure.name & _
strquote & ">" & procprocedure.name
  next

  set procprocedure = nothing
  set catpubs = nothing
%>
</select>
<p>
<input type="submit" value="print paramaters">
</form>
这是一个简单的窗体,包括一个用于显示连接字符串的textarea控件和用于显示存储过程名称的select控件。以前没有见过的是adox,adox是数据定义与安全的ado扩展,可以用来访问数据存储的目录(或是元数据)。
本书不打算介绍adox的内容,但其十分简单。进一步的细节可参见《ado programmer's reference》,wrox出版社出版,2.1版或2.5版都行。
上面的例子使用了procedures集合,这个集合包含数据存储中的所有存储过程的列表。按下printparameters按钮时,将得到图9-7所示的显示:

可以简单地从这里拷贝参数行到代码中。在前面使用了一个以前从未见过的包含文件。该文件包含了几个将ado常数(例如数据类型、参数方向等)转换为字符串值的函数:
<!-- #include file="../include/descriptions.asp" -->
接下来,定义一些变量,提取用户请求并创建command对象。
<%
  dim cmdproc
  dim parp
  dim strconnection
  dim strprocedure
  dim strquote

  ' get the connection and procedure name from the user
  strquote = chr(34)
  strconnection = request.form("txtconnection")
  strprocedure = request.form("lstprocedures")

  'update the user
  response.write "connecting to <b>" & strconnection & "</b><br>"
  response.write "documenting parameters for <b>" & _
strprocedure & "</b><p><p>"

  set cmdproc = server.createobject("adodb.command")

  ' set the properties of the command, using the name
  ' of the procedure that the user selected
  with cmdproc
    .activeconnection = strconnection
    .commandtype = adcmdstoredproc
    .commandtext = strprocedure
然后使用refresh方法自动填写parameters集合。
.parameters.refresh
现在可以遍历整个集合,写出包含创建参数所需的细节内容的字符串。
for each parp in .parameters
    response.write ".parameters.append & _
"("strquote & parp.name & _
strquote & ", " & _
datatypedesc(parp.type) & ", " & _
paramdirectiondesc(parp.direction) & _
", " & _
parp.size & ")<br>"
next
end with

set cmdproc = nothing
%>
在descriptions.asp包含文件中可以找到函数datatypedesc和paramdirectiondesc。
       descriptions.asp包含文件以及其他的例子文件可以在web站点http://www.wrox.com中找到。
这是一个非常简单的技术,它较好地使用了refresh方法。