使用带有Oracle ODBC连接的参数

I'm connecting succesfully to an Oracle 10g DB with an the Microsoft ODBC for Oracle driver.

我正在使用Microsoft ODBC for Oracle驱动程序成功连接到Oracle 10g数据库。

Regular queries without parameters work fine, but parameterized queries act as if the parameters aren't getting passed in.

没有参数的常规查询可以正常工作,但参数化查询就好像参数没有被传入一样。

ex.

--this works fine
Select * from tbl1 where column1 = 'test'

--this doesn't
select * from tbl1 where column1 = ?

--odbc string parameter 'test'

Here's what my connection string looks like:

这是我的连接字符串的样子:

"Driver={Microsoft ODBC for Oracle}; " & _
 "CONNECTSTRING=(DESCRIPTION=" & _
 "(ADDRESS=(PROTOCOL=TCP)" & _
 "(HOST=" & pstrServer & ")(PORT=" & pintPort.ToString & "))" & _
 "(CONNECT_DATA=(SERVICE_NAME=" & pstrPhysicalName & "))); " & _
 "uid=" & pstrUserName & ";pwd=" & pstrPassword & ";"

And I'm adding parameters to my ODBC command like this:

我正在为我的ODBC命令添加参数,如下所示:

arrOdbcParam(index) = New OdbcParameter("@paramName", paramValue)

...

cmd.Parameters.AddRange(arrOdbcParam)

Forgive the partialy copied, somewhat pseuedo code.

原谅那些部分复制的,有些过时的代码。

3 个解决方案

#1

Bit of necromancing here, but since I just struggled with a similar Problem, here is how it worked with the ODBC-driver for Centura SQLBase:

这里有一些坏消息,但由于我只是遇到了类似的问题,下面是它如何使用Centura SQLBase的ODBC驱动程序:

OdbcCommand com = con.CreateCommand();
com.CommandText = @"
  SELECT  thing
  FROM    table
  WHERE   searchInt = ? AND searchDat = ?";
com.Parameters.Add(new OdbcParameter("", OdbcType.Int)).Value = 12345;
com.Parameters.Add(new OdbcParameter("", OdbcType.DateTime)).Value = DateTime.Now;
OdbcDataReader reader = com.ExecuteReader();

This searches in "table" for records with the value 12345 in "searchInt" and todays date in "serachDat".
Things to note:

这将在“table”中搜索“searchInt”中值为12345且“serachDat”中的今天日期的记录。注意事项:

Parameters are marked as ? in the SQL command

参数标记为?在SQL命令中

Parameters need no name, but position (and the correct type) are important

参数不需要名称,但位置(和正确的类型)很重要

#2

ODBC parameters (marked by the symbol ?) are bound by position, so you have to make sure that you add the OdbcParameters in the correct order. Their name is then unimportant, but I would suggest paramName, without the @ which is a SQL Server (or, rather, Microsoft) specific name format.

ODBC参数(由符号?标记)是按位置绑定的,因此您必须确保以正确的顺序添加OdbcParameters。他们的名字然后不重要,但我建议使用paramName,而不是@是SQL Server(或者更确切地说是Microsoft)特定的名称格式。

You could also try to use the Oracle parameter format, which should be recognized by the Microsoft ODBC for Oracle driver and would allow you binding by name instead (not 100% sure about this, though) :

您还可以尝试使用Oracle参数格式,该格式应该由Microsoft ODBC for Oracle驱动程序识别,并允许您通过名称进行绑定(尽管不是100%确定):

Replace ? by :paramName in your query.

替换? by:您的查询中的paramName。

Name your parameter paramName.

将参数命名为paramName。

#3

Try using ":paramName" instead of "paramName".

尝试使用“:paramName”而不是“paramName”。

#1