Page 1 of 1
Case Insensitive Queries are not working
Posted: Mon 07 Apr 2014 12:21
by shyam.pundkar
Dear Support,
We have case insensitive oracle instance (NLS_COMP:LINGUISTIC,NLS_SORT:BINARY_CI). when we try to execute queries on it using devart oracle provider, we are unable to get result from case insensitive queries.
For example:
select * from "Test" where "Field1" like 'A%'
this query returns result for field1 values starting with 'a' as well as 'A' in SQL Plus but in the application using devart provider, it is returning just one value which is field value starting with A.
Please let me know you have any solution related to it.
Thanks
Shyam
Re: Case Insensitive Queries are not working
Posted: Tue 08 Apr 2014 14:28
by Pinturiccio
We could not reproduce the issue with the latest version of dotConnect for Oracle. Please try setting (NLS_COMP:LINGUISTIC,NLS_SORT:BINARY_CI) before executing your query:
Code: Select all
OracleConnection conn = new OracleConnection("your connection string");
conn.Open();
OracleCommand comm = new OracleCommand("alter session set NLS_COMP=LINGUISTIC", conn);
comm.ExecuteNonQuery();
comm.CommandText = "alter session set NLS_SORT=BINARY_CI";
comm.ExecuteNonQuery();
comm.CommandText = "select * from \"Test\" where \"Field1\" like 'A%'";
OracleDataReader reader = comm.ExecuteReader();
while (reader.Read())
{
for (int i = 0; i < reader.FieldCount; i++)
Console.Write(reader.GetValue(i) + "\t");
Console.WriteLine();
}
Re: Case Insensitive Queries are not working
Posted: Wed 09 Apr 2014 02:35
by shyam.pundkar
We are using 7.6.202.0.
We tried your approach but then we would have to execute these queries every time we make a connection to database as ours is a web application which use Entity Framework.
The solution we got to this is to set below properties
Devart.Data.Oracle.Entity.Configuration.OracleEntityProviderConfig.Instance.QueryOptions.CaseInsensitiveLike = true;
Devart.Data.Oracle.Entity.Configuration.OracleEntityProviderConfig.Instance.QueryOptions.CaseInsensitiveComparison = true;
Which solved the issue.
But can you confirm from your side whether its a right thing to use these properties?
Also let us know if you know a better solution to this problem.
Thanks
Shyam
Re: Case Insensitive Queries are not working
Posted: Thu 10 Apr 2014 09:35
by Shalex
shyam.pundkar wrote:
The solution we got to this is to set below properties
Code: Select all
Devart.Data.Oracle.Entity.Configuration.OracleEntityProviderConfig.Instance.QueryOptions.CaseInsensitiveLike = true;
Devart.Data.Oracle.Entity.Configuration.OracleEntityProviderConfig.Instance.QueryOptions.CaseInsensitiveComparison = true;
Which solved the issue.
But can you confirm from your side whether its a right thing to use these properties?
This is a correct approach for the Entity Framework applications.