Geek Answers Handbook

Call a parameterized Oracle query from ADODB in classic ASP

I am currently working on a classic ASP project working with an Oracle database. I am trying to find a way to safely call an Oracle PL / SQL script and pass parameters using ADO. Currently, the solution creates a SQL script manually with built-in variables such as:

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 still have (removed from various non-classic asp-based websites) 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 results in the error "The parameter object is incorrectly defined. Inconsistent or incomplete information was provided"

Oracle/PL/SQL ADO? , SQL , SQL.

+5
1

filter_value? String 10 ( ), .

( ) named OraOLEDB ( ADODB).

. Oracle® Provider OLE DB Developer Guide 11g Release 1 (11.1) " " (8iR3, 9i, 9iR2, 10g, 10gR2):

Oracle ANSI SQL . ODBC SQL (?).

OraOLEDB , PL/SQL . OraOLEDB SQL.

" : OraOLEDB ".

, OraOLEDB:

oFilteredList.NamedParameters = True

, , Oracle 10gR2.

, . , :

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

More articles:


All Articles