在经典 ASP 中从 ADODB 调用参数化 Oracle 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1135117/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Call a parameterized Oracle query from ADODB in Classic ASP
提问by Buzzrick
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:
我目前正在处理一个与 Oracle 数据库对话的经典 ASP 项目。我正在尝试找到一种方法来安全地调用 Oracle PL/SQL 脚本并使用 ADO 传递参数。当前的解决方案使用嵌入式变量手动构建 SQL 脚本，如下所示：
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:
到目前为止，我拥有的代码（从各种非经典的基于 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
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.
从 ADO 使用命名参数调用 Oracle/PL/SQL 的正确方法是什么？我需要使用命名参数，因为实际的 SQL 代码稍微复杂一些，并且在整个 SQL 命令中多次使用不同的参数。
采纳答案by BQ.
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.
你是怎么filter_value定义的？如果它没有声明为字符串，或者如果您分配了一个长度超过 10 个字符的字符串（正如您在创建参数时所指出的那样），您就会遇到问题。
Additionally (and partly for my own reference), named parameters are not supported via OraOLEDB(i.e. ADODB).
此外（部分供我自己参考），OraOLEDB（即 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):
看到甲骨文了吗？OLE DB 开发人员指南 11g 第 1 版 (11.1) 的提供程序或遵循任何先前版本（8iR3、9i、9iR2、10g、10gR2）上的“命令参数”标题链接：
Command Parameters
When using Oracle ANSI SQL, parameters in the command text are preceded by a colon. In ODBC SQL, parameters are indicated by a question mark (?).
OraOLEDB supports input, output, and input and output parameters for PL/SQL stored procedures and stored functions. OraOLEDB supports input parameters for SQL statements.
"Note: OraOLEDB supports only positional binding."
命令参数
使用 Oracle ANSI SQL 时，命令文本中的参数前面有一个冒号。在 ODBC SQL 中，参数由问号 (?) 指示。
OraOLEDB 支持 PL/SQL 存储过程和存储函数的输入、输出以及输入和输出参数。OraOLEDB 支持 SQL 语句的输入参数。
"注意：OraOLEDB 只支持位置绑定。"
That said, this should have no bearing on your query when using OraOLEDB:
也就是说，在使用 OraOLEDB 时，这应该与您的查询无关：
oFilteredList.NamedParameters = True
I've had success running queries exactly as the rest of your example shows though on Oracle 10gR2.
尽管在 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:
你没有显示你的连接字符串，所以我必须假设它是有效的。行为可能因那里的选项而异，所以这是我成功使用的内容：
`"Provider=OraOLEDB.Oracle;Data Source=TNSNAMES_ENTRY;User ID=XXXX;Password=YYYY;DistribTx=0;"`
