在经典 ASP 中从 ADODB 调用参数化 Oracle 查询
我目前正在开发一个与 Oracle 数据库交互的经典 ASP 项目。 我正在尝试找到一种安全调用 Oracle PL/SQL 脚本并使用 ADO 传递参数的方法。 当前的解决方案使用嵌入变量手动构建 SQL 脚本，如下所示：
strSQL = "SELECT field1, etc FROM my_table WHERE (field = '" & filter_value & "')"
当然，这很丑陋且不安全，并且容易被滥用。
到目前为止，我的代码（从各种非经典的基于 ASP 的网站盗取的）如下所示：
dim strSQL, oConn, oCommand, oParam
set oConn = server.createobject("ADODB.Connection")
oConn.Open myConnString
strSQL = "SELECT field1, etc FROM my_table WHERE (field = :filter_field)"
dim oFilteredList
set oFilteredList = Server.CreateObject("ADODB.Command")
oFilteredList.ActiveConnection = oConn
oFilteredList.CommandText = strSQL
oFilteredList.CommandType = adCmdText
oFilteredList.NamedParameters = True
set oParam = oFilteredList.CreateParameter("filter_field", adVarChar, adParamInput, 10, filter_value)
oFilteredList.Parameters.Append oParam
set rsResults = oFilteredList.Execute
这会导致错误“参数对象定义不正确。 提供的信息不一致或不完整”
从 ADO 中使用命名参数调用 Oracle/PL/SQL 的正确方法是什么？ 我需要使用命名参数，因为实际的 SQL 代码有些复杂，并且在整个 SQL 命令中多次使用不同的参数。
I’m currently working on a classic ASP project talking to an Oracle database. I’m trying to find a way to safely call an Oracle PL/SQL script and passing parameters with ADO. The currently solution builds the SQL script by hand with embedded variables like this:
strSQL = "SELECT field1, etc FROM my_table WHERE (field = '" & filter_value & "')"
This, of course, is ugly and insecure, and open to abuse.
The code that I have so far (purloined from various non classic asp based web sites) looks like this:
dim strSQL, oConn, oCommand, oParam
set oConn = server.createobject("ADODB.Connection")
oConn.Open myConnString
strSQL = "SELECT field1, etc FROM my_table WHERE (field = :filter_field)"
dim oFilteredList
set oFilteredList = Server.CreateObject("ADODB.Command")
oFilteredList.ActiveConnection = oConn
oFilteredList.CommandText = strSQL
oFilteredList.CommandType = adCmdText
oFilteredList.NamedParameters = True
set oParam = oFilteredList.CreateParameter("filter_field", adVarChar, adParamInput, 10, filter_value)
oFilteredList.Parameters.Append oParam
set rsResults = oFilteredList.Execute
This causes the error “Parameter object is improperly defined. Inconsistent or incomplete information was provided”
What is the correct method of calling Oracle / PL/SQL with named parameters from ADO? I need to use named parameters because the actual SQL code is somewhat more complex, and different parameters are used multiple times throughout the SQL command.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
如何定义
filter_value？ 如果它没有声明为字符串，或者您分配的字符串长度超过 10 个字符（正如您在创建参数时所指示的那样），则会遇到问题。另外（部分供我自己参考），OraOLEDB（即 ADODB）不支持命名参数。
请参阅 Oracle® Provider for OLE DB 开发人员指南 11g版本 1 (11.1) 或点击任意 以前的版本（8iR3、9i、9iR2、10g、10gR2）：
也就是说，在使用 OraOLEDB 时，这应该对您的查询没有影响：
我已经成功地运行了查询，正如您的示例的其余部分在 Oracle 10gR2 上显示的那样。
您没有显示连接字符串，所以我必须假设它是有效的。行为可能会根据那里的选项而有所不同，所以这是我成功使用的：
How do you have
filter_valuedefined? If it's not declared as a String or if you've assigned a string longer than 10 characters (as you've indicated when creating the parameter), you'll have issues with that.Additionally (and partly for my own reference), named parameters are not supported via OraOLEDB (i.e. ADODB).
See Oracle® Provider for OLE DB Developer's Guide 11g Release 1 (11.1) or follow the "Command Parameters" heading link on any of the previous versions (8iR3, 9i, 9iR2, 10g, 10gR2):
That said, this should have no bearing on your query when using OraOLEDB:
I've had success running queries exactly as the rest of your example shows though on Oracle 10gR2.
You don't show your connection string, so I must assume it to be valid. Behavior can differ depending on options there, so here's what I successfully use: