在Classic ASP中从ADODB调用参数化的Oracle查询 [英] Call a parameterized Oracle query from ADODB in Classic ASP

问题描述

我目前正在研究一个与Oracle数据库通信的经典ASP项目.我正在尝试找到一种安全地调用Oracle PL/SQL脚本并使用ADO传递参数的方法.当前的解决方案使用以下嵌入式变量手动构建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.

到目前为止，我的代码(从各种非经典的基于ASP的网站中提取)看起来像这样:

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"

使用ADO中的命名参数调用Oracle/PL/SQL的正确方法是什么?我需要使用命名参数，因为实际的SQL代码稍微复杂一些，并且在整个SQL命令中多次使用不同的参数.

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.

推荐答案

如何定义filter_value?如果未将其声明为字符串，或者您分配的字符串长度超过10个字符(如在创建参数时所指出的那样)，则会遇到问题.

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.

此外(部分仅供我参考)，OraOLEDB (即ADODB)不支持 命名参数.

Additionally (and partly for my own reference), named parameters are not supported via OraOLEDB (i.e. ADODB).

请参见用于OLE DB的Oracle®Provider开发人员指南11g第1版(11.1)或在

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 ANSI SQL时的参数 在命令文本中，前面有一个 冒号.在ODBC SQL中，参数为 用问号(?)表示.

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支持输入，输出和 PL/SQL的输入和输出参数 存储过程和存储 功能. OraOLEDB支持输入 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仅支持 位置绑定."

"Note: OraOLEDB supports only positional binding."

也就是说，在使用OraOLEDB时，这与您的查询无关:

That said, this should have no bearing on your query when using OraOLEDB:

oFilteredList.NamedParameters = True

我在Oracle 10gR2上完全成功完成了查询，正如您的示例其余部分所示.

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:

`"Provider=OraOLEDB.Oracle;Data Source=TNSNAMES_ENTRY;User ID=XXXX;Password=YYYY;DistribTx=0;"`

这篇关于在Classic ASP中从ADODB调用参数化的Oracle查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！