在Oracle ODBC连接中使用参数 [英] Using Parameters with an Oracle ODBC Connection

问题描述

我已使用Microsoft ODBC for Oracle驱动程序成功连接到Oracle 10g DB.

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

没有参数的常规查询可以很好地工作，但是参数化查询的作用就像没有传递参数一样.

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

例如.

--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 & ";"

我正在像这样向ODBC命令中添加参数:

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

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

...

cmd.Parameters.AddRange(arrOdbcParam)

原谅部分复制的伪代码.

Forgive the partialy copied, somewhat pseuedo code.

推荐答案

有点麻烦，但是由于我刚刚遇到了类似的问题，因此这是它与Centura SQLBase的ODBC驱动程序一起工作的方式:

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:

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();

此操作在表"中搜索"searchInt"中值为12345，而今天的日期在"serachDat"中的记录.
注意事项:

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

• 参数在 SQL命令
• 参数不需要名称， 但是位置(和正确的类型)是 重要

• Parameters are marked as ? in the SQL command
• Parameters need no name, but position (and the correct type) are important

这篇关于在Oracle ODBC连接中使用参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！