[Linux+Oracle] Sugar is case sensitive

[groupeHEC]

Hello all,
My version of sugar is case sensitive.
However, my Oracle base is insensitive.
When I do (from linux SQLPLUS):
select * from leads where first_name=' toto'
or
select * from leads where first_name=' TOTO'

I have exactly the same number of results.

On other hand, under sugar, if I seek a lead under toto or TOTO the result is different.

I wish that sugar is insensitive, to login with all syntaxe: Admin, ADMIN, admin....

The problem come from: my php config, my Oracle config or my linux config?

Thanks for your help

ps: I use
Linux Redhat
PHP 5
ORACLE 10g

[groupeHEC]

Hi all,
I really need to have sugar insensitive, Anyone have the same problem?

Thank you for your help

[tvannatta@tracintermodal.com]

I'm running into the same issue. I've looked at the queries that it runs (by setting log level to INFO and examining sugarcrm.log) - there is a query that actually gets the data from the LEADS or ACCOUNTS or whatever table. It will have a critical part that says something like

where ((accounts.name like 'imcs%'))
which needs to change to :
where (upper(accounts.name) like upper('imcs%'))

Then, you need to create a function-based or case-insensitive index on the text fields you are searching for, so the above search would correctly use the index on account name. Or you can live with the full table scans that the queries will produce, depending on how much data you have.

I think it's a matter of updating OracleManager.php in ./include/database. But I don't know exactly what to update. It only needs to uppercase text fields - and uppercase both sides of the query.

In much of SugarCRM, the Oracle support is "not quite there". It works, but...

[tom_v]

Fixes, sort of:
1. Add line to config.php
'oracle_enable_ci' => 'true',


We then got errors (really oracle warnings) which made searches break for non-admin users. Our (ugly) fix was:
2. Edit include/database/OracleManager.php, function checkOCIerror

private function checkOCIerror(
$obj
)
{
$err = oci_error($obj);
if ($err != false){
//////TV TOM_V 10-20-2011 Fix for the bogus error (that is just a warning)
// Added if ($err["code"] != 24347) (and related "}" ) below.
if ($err["code"] != 24347)
{
$result = false;
$GLOBALS['log']->fatal("OCI error:".var_export($err, true));
return true;
}
}
return false;
}

[jmertic]

Fixes, sort of:
1. Add line to config.php
'oracle_enable_ci' => 'true',


We then got errors (really oracle warnings) which made searches break for non-admin users. Our (ugly) fix was:
2. Edit include/database/OracleManager.php, function checkOCIerror

private function checkOCIerror(
$obj
)
{
$err = oci_error($obj);
if ($err != false){
//////TV TOM_V 10-20-2011 Fix for the bogus error (that is just a warning)
// Added if ($err["code"] != 24347) (and related "}" ) below.
if ($err["code"] != 24347)
{
$result = false;
$GLOBALS['log']->fatal("OCI error:".var_export($err, true));
return true;
}
}
return false;
}

Can you detail what that error code is in Oracle, and what the ramifications of ignoring that error are?