Hi Rebecca,
We had the same issue with our (Oracle backed) biomart. We managed to
solve that by editing one of the Biomart libraries...
It's the file 'lib/Biomart/Configuration/DBLocation.pm' you need to
edit.
Replace the Perl subroutine 'openConnection' with the code at the end
of this email.
Let me know how you get on.
Thanks,
Daz
----
The code:
sub openConnection {
my $self = shift;
unless (defined($self->host ) && defined ($self->databaseType)
&& defined($self->database) && defined($self->user)) {
BioMart::Exception::Database->throw("Database Specifications must
contain at least the database type (".$self->databaseType.") host (".
$self->host."), database (".$self->database."), and user (".$self-
>user.")");
}
my $dsn;
if ($self->databaseType eq 'oracle'){
$dsn=sprintf(ORACLEDSNTEMPLATE, $self->host, $self->database,
$self->port);
} elsif ($self->databaseType eq 'mysql') {
$dsn=sprintf(MYSQLDNSTEMPLATE, $self->database, $self->host,
$self->port);
} elsif ($self->databaseType eq 'postgres') {
$dsn=sprintf(POSTGRESTEMPLATE, $self->database, $self->host,
$self->port);
} elsif ($self->databaseType eq 'odbc') {
$dsn=sprintf(ODBCDNSTEMPLATE, $self->database);
} else {
warn("unsupported RDBMS type: \"$self->databaseType\" - please
use the correct name or supported RDBMS ...... skipping connection");
}
$self->dsn($dsn);
my $dbh;
## The code below has been modified by do2 to make Oracle queries
case-insensitive
eval {
if ($self->databaseType eq 'oracle') {
use DBD::Oracle;
$dbh = DBI->connect(
$dsn,
$self->user,
$self->password,
{
InactiveDestroy => 1,
RaiseError => 1,
PrintError => 1,
}
);
my $sth_htgt = $dbh->prepare('alter session set
NLS_SORT=BINARY_CI'); $sth_htgt->execute();
my $sth_htgt2 = $dbh->prepare('alter session set
NLS_COMP=LINGUISTIC'); $sth_htgt2->execute();
} else {
$dbh = DBI->connect(
$dsn,
$self->user,
$self->password,
{InactiveDestroy => 1, RaiseError => 1,
PrintError => 1}
);
}
## End of change
};
if($@ || !$dbh) {
BioMart::Exception::Database->throw("Could not connect to ".
$self->databaseType." database ".$self->database.": ".$@);
}
$self->dbh($dbh);
}
----
End of code....
On 15 May 2009, at 11:52, Richard Holland wrote:
Oracle is case-sensitive by default when using the 'like' and '='
clauses, but MySQL is not by default. BioMart has no settings of its
own
that control this, and so in terms of case-sensitivity it will
behave in
whatever way the database it is using is configured to behave.
You can test your own database's behaviour by opening a database
prompt
(SQLPlus on oracle, or a MySQL command line for MySQL) and doing a
couple of test SELECT queries using the '=' and 'LIKE' operators in
the
where clause and a variety of differently cased values.
cheers,
Richard
On Fri, 2009-05-15 at 11:41 +0100, Rebecca Shepherd wrote:
Hi
We have a gene name search box in our mart and the search is case
sensitive. We would like it to be case insensitive. Ensembl seem to
have
a similar search which is case insensitive. I have had a look at the
settings for Ensembl in MartEditor and they seem to be the same as
ours.
We are using an oracle database and they are using mysql. Could this
make a difference?
Thanks
Rebecca
--
Richard Holland, BSc MBCS
Finance Director, Eagle Genomics Ltd
T: +44 (0)1223 654481 ext 3 | E: [email protected]
http://www.eaglegenomics.com/
--
The Wellcome Trust Sanger Institute is operated by Genome Research
Limited, a charity registered in England with number 1021457 and a
company registered in England with number 2742969, whose registered
office is 215 Euston Road, London, NW1 2BE.
