在 Oracle ODBC 连接中使用参数
我使用 Microsoft ODBC for Oracle 驱动程序成功连接到 Oracle 10g DB。
不带参数的常规查询工作正常,但参数化查询的行为就好像参数没有传入一样
。
--this works fine
Select * from tbl1 where column1 = 'test'
--this doesn't
select * from tbl1 where column1 = ?
--odbc string parameter 'test'
我的连接字符串如下所示:
"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 命令添加参数,如下所示:
arrOdbcParam(index) = New OdbcParameter("@paramName", paramValue)
...
cmd.Parameters.AddRange(arrOdbcParam)
请原谅部分复制的、有些伪代码。
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.
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:
arrOdbcParam(index) = New OdbcParameter("@paramName", paramValue)
...
cmd.Parameters.AddRange(arrOdbcParam)
Forgive the partialy copied, somewhat pseuedo code.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
这里有点死灵术,但由于我刚刚遇到了类似的问题,因此它与 Centura SQLBase 的 ODBC 驱动程序一起工作的方式如下:
这在“表”中搜索“searchInt”中值为 12345 且今天日期为“搜索数据”。
注意事项:
?
SQL命令
但位置(和正确的类型)是
重要的
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:
This searches in "table" for records with the value 12345 in "searchInt" and todays date in "serachDat".
Things to note:
?
in theSQL command
but position (and the correct type) are
important
ODBC 参数(由符号
?
标记)按位置绑定,因此您必须确保以正确的顺序添加OdbcParameter
。 他们的名字并不重要,但我建议使用paramName
,而不使用@
,这是 SQL Server(或者更确切地说,Microsoft)特定的名称格式。您还可以尝试使用 Oracle 参数格式,该格式应能被 Microsoft ODBC for Oracle 驱动程序识别,并允许您通过名称进行绑定(不过对此不能 100% 确定):
?
通过查询中的:paramName
。paramName
。ODBC parameters (marked by the symbol
?
) are bound by position, so you have to make sure that you add theOdbcParameter
s in the correct order. Their name is then unimportant, but I would suggestparamName
, without the@
which is a SQL Server (or, rather, Microsoft) specific name format.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) :
?
by:paramName
in your query.paramName
.尝试使用“:paramName”而不是“paramName”。
Try using ":paramName" instead of "paramName".