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命令中多次使用不同的参数。
1 个解决方案
#1
How do you have filter_value defined? 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?如果它没有被声明为String或者你已经分配了一个超过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):
请参阅Oracle®Providerfor 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 (?).
使用Oracle ANSI SQL时,命令文本中的参数前面有冒号。在ODBC SQL中,参数由问号(?)表示。
OraOLEDB supports input, output, and input and output parameters for PL/SQL stored procedures and stored functions. OraOLEDB supports input parameters for SQL statements.
OraOLEDB支持PL / SQL存储过程和存储函数的输入,输出和输入和输出参数。 OraOLEDB支持SQL语句的输入参数。
"Note: OraOLEDB supports only positional binding."
“注意: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;"`
#1
How do you have filter_value defined? 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?如果它没有被声明为String或者你已经分配了一个超过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):
请参阅Oracle®Providerfor 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 (?).
使用Oracle ANSI SQL时,命令文本中的参数前面有冒号。在ODBC SQL中,参数由问号(?)表示。
OraOLEDB supports input, output, and input and output parameters for PL/SQL stored procedures and stored functions. OraOLEDB supports input parameters for SQL statements.
OraOLEDB支持PL / SQL存储过程和存储函数的输入,输出和输入和输出参数。 OraOLEDB支持SQL语句的输入参数。
"Note: OraOLEDB supports only positional binding."
“注意: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;"`