在 C# 中使用 LIKE 绑定 Oracle 变量
作为停止使用动态 SQL 生成并鼓励使用绑定变量的努力的一部分,我遇到了一些问题。
我正在使用 Oracle Data Providers for .NET 从 ASP.NET 页面查询 Oracle 9i 数据库
该查询是
sql = "SELECT somedata FROM sometable WHERE machine = :machineName ";
我按如下方式定义 Oracle 参数
OracleParameter parameter = new OracleParameter();
parameter.ParameterName = "machineName";
parameter.OracleDbType = OracleDbType.Varchar2;
parameter.Value = machine; //machine is a variable of type string
parameterList.Add(parameter);
这对于“=”运算符来说效果很好。但我似乎无法让它与“LIKE”一起工作。我不知道如何格式化查询以使其接受“%”通配符的使用。
我已经尝试过:
sql = "SELECT somedata FROM sometable WHERE machine LIKE :machineName% ";
sql = "SELECT somedata FROM sometable WHERE machine LIKE ':machineName%' ";
sql = "SELECT somedata FROM sometable WHERE machine LIKE :machineName||% ";
并且还:
parameter.Value = machine+'%';
但我得到的只是 ORA-00911 (非法字符)和 ORA-01036 (非法名称/值)异常。
我做错了什么?
As part of an effort to stop using dynamic SQL generation and encourage use of bind variables, I am running into some problems.
I am querying an Oracle 9i database from an ASP.NET page using Oracle Data Providers for .NET
The query is
sql = "SELECT somedata FROM sometable WHERE machine = :machineName ";
I define the Oracle Parameter as follows
OracleParameter parameter = new OracleParameter();
parameter.ParameterName = "machineName";
parameter.OracleDbType = OracleDbType.Varchar2;
parameter.Value = machine; //machine is a variable of type string
parameterList.Add(parameter);
This works fine for "=" operator. But I just can't seem to get it to work with "LIKE". I don't know how to format the query so that it accepts usage of the "%" wildcard.
I have tried:
sql = "SELECT somedata FROM sometable WHERE machine LIKE :machineName% ";
sql = "SELECT somedata FROM sometable WHERE machine LIKE ':machineName%' ";
sql = "SELECT somedata FROM sometable WHERE machine LIKE :machineName||% ";
and also:
parameter.Value = machine+'%';
but all I get are ORA-00911 (illegal character) and ORA-01036 (illegal name/value) exceptions.
What am I doing wrong?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
尝试:
由于 BIND 变量,因此不需要用单引号引起来。但 % 不是,所以我希望它需要被封装。
Try:
Because of the BIND variable, there wouldn't need to be single quotes around it. But the % is not, so I would expect it needing to be encapsulated.
这是一个完整的查询示例:
Here is a full query example: