SQL参数化查询讲座 (八)

如果程序中使用数据库存储过程，则更应该使用参数化查询。很多数据库在使用存储过程时，其OLEDB驱动程序能自动解析所需的参数，根本不需要明确调用CreateParameter方法，直接给对应的参数赋值即可执行。下面的例子中，程序访问到本机MS SQL Server服务器的pubs数据库，执行数据库中的ByRoyalty存储过程。该存储过程返回给定忠诚度的顾客信息，忠诚度为该存储过程唯一的输入参数（这个例子来自MSDN并适当修改）。

 

Dim cnn As Connection  ' 连接到SQL Server的pubs数据库

Dim cmdByRoyalty As Command  '  ByRoyalty存储过程

Dim rsByRoyalty As Recordset  '  接收ByRoyalty存储过程的返回结果

Dim intRoyalty As Integer  '  ByRoyalty存储过程所需的忠诚度参数

 

Set cnn = New Connection

' 实际环境中不应使用空密码

cnn.Open "Provider=SQLOLEDB;Data Source=(local);Initial Catalog=pubs;User Id=sa;Password= "

 

' 打开命令对象

Set cmdByRoyalty = New Command

cmdByRoyalty.ActiveConnection = cnn

cmdByRoyalty.CommandType = adCmdStoredProc

cmdByRoyalty.CommandText = "ByRoyalty"

cmdByRoyalty.Prepared = True

 

' 指定参数并执行

intRoyalty = Trim(InputBox("请输入客户忠诚度:"))

cmdByRoyalty.Parameters("Royalty").Value = intRoyalty

Set rsByRoyalty = cmdByRoyalty.Execute

' 以下是处理查询结果的代码

 

以上介绍的是原生的ADO参数化查询方式。Visual Basic、Delphi等可视化的快速开发环境在封装ADO的同时，也保留甚至改进了参数化查询。SQL语句和参数既可以在可视化界面设定，也可在运行中动态赋值。

ADO中的Command对象在VCL中对应TADOCommand控件，其SQL语句中的问号被以冒号开头的命名参数代替。如SQL语句“SELECT column1, column2, … FROM table1 WHERE param_column1 = ? AND param_column2 = ? ”在VCL中表示为“SELECT column1, column2, … FROM table1 WHERE param_column1 = :Param_column1 AND param_column2 = :Param_column2 ”。由此，VCL可从中解析出参数名称。VCL也试图从SQL语句中自动解析出参数的数据类型及其他属性（解析时需要连接数据库），但不一定总是正确的。这时可以手工修正。顺便说一句，除了ADO控件外，VCL中其他数据访问模型通常也支持参数化查询。

Visual Basic的数据环境设计器对参数化查询提供了强大的支持。读者如果不熟悉数据环境设计器，可参考Visual Basic的联机手册。在数据环境设计器中，命令对象被映射为数据环境设计器实例的子过程，命令的参数被映射为子过程的参数，命令的返回结果集被映射为数据环境设计器实例的属性，如果有返回结果的话。以本节开头的例子，假定您在数据环境设计器中创建了cmd1和cmd2两个命令，其SQL语句分别为 "INSERT INTO table1 VALUES(?,?)" 和 "SELECT column2 FROM table1 WHERE column1=?"。创建命令并设置好参数后，按F2进入对象浏览器中，则可看到在本工程创建的数据环境设计器对象实例下面，有cmd1和cmd2两个子过程，cmd1有两个参数，cmd2有一个参数，这与SQL语句刚好对应。另外，数据环境设计器实例还有一个Recordset类型的rsCmd2属性，每次运行cmd2子过程后，查询结果将保存到rsCmd2中。