Case Insensitive Queries are not working

Case Insensitive Queries are not working

Postby shyam.pundkar » Mon 07 Apr 2014 12:21

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
shyam.pundkar
 
Posts: 3
Joined: Mon 07 Apr 2014 12:09

Re: Case Insensitive Queries are not working

Postby Pinturiccio » Tue 08 Apr 2014 14:28

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();
}
Pinturiccio
Devart Team
 
Posts: 1862
Joined: Wed 02 Nov 2011 09:44

Re: Case Insensitive Queries are not working

Postby shyam.pundkar » Wed 09 Apr 2014 02:35

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
shyam.pundkar
 
Posts: 3
Joined: Mon 07 Apr 2014 12:09

Re: Case Insensitive Queries are not working

Postby Shalex » Thu 10 Apr 2014 09:35

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.
Shalex
Devart Team
 
Posts: 7377
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for Oracle