[ensembl-dev] problem connecting to ensembl directly with DBConnector

[Pablo Marin-Garcia]

On Thu, 30 Dec 2010, Andrea Edwards wrote:

> I thought that was the best way to issue mysql queries directly against the 
> database?

Not necesarily. You can access to the $dbc directly from any adaptor [1]

     [1] http://www.ensembl.org/info/docs/Pdoc/ensembl/modules/Bio/EnsEMBL/DBSQL/BaseAdaptor.html#POD9

As you want the variation database you need to use a variation_adaptor:

---<code>---

use Bio::EnsEMBL::Registry;
my $reg = 'Bio::EnsEMBL::Registry';
$reg->load_registry_from_db(-host => 'ensembldb.ensembl.org',-user=>'anonymous') ;

my $va  = $reg->get_adaptor('human','variation', 'Variation')
$sth = $va->prepare("select * from source limit 10")
$sth->execute
print $sth->dump_results
---</code>---

---<result>---
'1', 'dbSNP', '131', 'Variants (including SNPs and i...', 
'http://www.ncbi.nlm.nih.gov/pr...', '0'
'2', 'Affy GeneChip 100K Array', undef, 'Variants from the Affymetrix G...', 
'http://www.affymetrix.com/', '0'
'3', 'Affy GeneChip 500K Array', undef, 'Variants from the Affymetrix G...', 
'http://www.affymetrix.com/', '0'
'4', 'Affy GenomeWideSNP_6.0', undef, 'Variants from the Affymetrix G...', 
'http://www.affymetrix.com/', '0'
'5', 'NHGRI_GWAS_catalog', undef, 'Variants associated with pheno...', 
'http://www.genome.gov/gwastudies/', '0'
'6', 'EGA', undef, 'Variants imported from the Eur...', 
'http://www.ebi.ac.uk/ega/', '0'
'7', 'Illumina_CytoSNP12v1', undef, 'Variants from the Illumina Cyt...', 
'http://www.illumina.com/', '0'
'8', 'Illumina_Human660W-quad', undef, 'Variants from the Illumina Hum...', 
'http://www.illumina.com/', '0'
'9', 'Illumina_Human1M-duoV3', undef, 'Variants from the Illumina Hum...', 
'http://www.illumina.com/', '0'
'10', 'Uniprot', undef, 'Variants with protein annotati...', 
'http://www.uniprot.org/', '0'
10 rows
10

---</result>---


you can see which database you are connected with this code:

print $va->dbc->dbname
> homo_sapiens_variation_60_37e




>
> On 30/12/2010 23:10, Pablo Marin-Garcia wrote:
>> 
>> Hello Andrea,
>> 
>> As Ian has said, now the latest ensembl mysql instances are in the port 
>> 5306. So he has already answered your question.
>> 
>> But I only would like to point that unless there is an inevitable cause for 
>> using a direct and explicit connection with DBConnection, I would suggest 
>> connecting using the Registry:
>> 
>> --- <code>---
>> use Bio::EnsEMBL::Registry;
>> 
>> my $reg = 'Bio::EnsEMBL::Registry';
>> 
>> $reg->load_registry_from_db(-host => 'ensembldb.ensembl.org', 
>> -user=>'anonymous') ;
>> 
>> ---</code>---
>> 
>> This will connect to the database that match your API version. So your 
>> scripts are more flexible and prevent database-API mismatches when you 
>> update ensembl code. In order to use different database versions  you only 
>> need to change your PERL5LIB environmental variable (or 'use lib') pointing 
>> to the ensembl API checkout of the desired version.
>> 
>>
>>    -Pablo
>> 
>> 
>> 
>> On Thu, 30 Dec 2010, Ian Sealy wrote:
>> 
>>> Dear Andrea,
>>> 
>>>> What should the database name be for this code to work on the ensembl 
>>>> human core/variation and cow core/variation databases?
>>> 
>>>> This code works fine when it run it against local installations but says 
>>>> dbname does not exist when i run against ensembl server. I looked up most 
>>>> recent database name from ftp site.
>>>>
>>>>  $dbc = Bio::EnsEMBL::DBSQL::DBConnection->new(    -user   => 
>>>> 'anonymous', -dbname => 'homo_sapiens_variation_60_37e',    -host   => 
>>>> 'ensembldb.ensembl.org',    -driver => 'mysql',  );
>>>>  # SQL statements should be created/executed through this modules
>>>>  # prepare() and do() methods.
>>>> 
>>>> $sql =  "select allele_id from allele limit 100";
>>>> $sth = $dbc->prepare($sql);
>>>> 
>>>> 
>>>> 
>>>> Could not connect to database homo_sapiens_variation_60_37e as user 
>>>> anonymous using 
>>>> [DBI:mysql:database=homo_sapiens_variation_60_37e;host=ensembldb.ensembl.org;port=3306] 
>>>> as a locator:
>>>> Unknown database 'homo_sapiens_variation_60_37e' at 
>>>> C:\Perl\site\lib\ensembl-api\ensembl\modules/Bio/EnsEMBL/DBSQL/DBConnection.pm 
>>>> line 290.
>>>> 
>>>> -------------------- EXCEPTION --------------------
>>>> MSG: Could not connect to database homo_sapiens_variation_60_37e as user 
>>>> anonymous using 
>>>> [DBI:mysql:database=homo_sapiens_variation_60_37e;host=ensembldb.ensembl.org;port=3306] 
>>>> as a locator:
>>>> Unknown database 'homo_sapiens_variation_60_37e'
>>>> STACK Bio::EnsEMBL::DBSQL::DBConnection::connect 
>>>> C:\Perl\site\lib\ensembl-api\ensembl\modules/Bio/EnsEMBL/DBSQL/DBConnection.pm:299
>>>> STACK Bio::EnsEMBL::DBSQL::DBConnection::db_handle 
>>>> C:\Perl\site\lib\ensembl-api\ensembl\modules/Bio/EnsEMBL/DBSQL/DBConnection.pm:618
>>>> STACK Bio::EnsEMBL::DBSQL::DBConnection::prepare 
>>>> C:\Perl\site\lib\ensembl-api\ensembl\modules/Bio/EnsEMBL/DBSQL/DBConnection.pm:647
>>>> STACK toplevel C:/Documents and 
>>>> Settings/Administrator/Desktop/PHD/java/Code/ensembl 
>>>> database/variation.pl:23
>>>> 
>>>> 
>>>> Are there any other ways to issue SQL queries directly?
>>> 
>>> From version 48 onwards, you need to change the port from the default 3306 
>>> to 5306. Like so:
>>> 
>>> mysql -h ensembldb.ensembl.org -u anonymous -e "SHOW DATABASES LIKE 
>>> 'homo_sapiens_variation_60_37e'"
>>> mysql -h ensembldb.ensembl.org -u anonymous -P 5306 -e "SHOW DATABASES 
>>> LIKE 'homo_sapiens_variation_60_37e'"
>>> +------------------------------------------+
>>> | Database (homo_sapiens_variation_60_37e) |
>>> +------------------------------------------+
>>> | homo_sapiens_variation_60_37e            |
>>> +------------------------------------------+
>>> 
>>> For more info, see:
>>> 
>>> http://www.ensembl.org/info/data/mysql.html
>>> 
>>> Cheers,
>>> Ian
>>> 
>>> _______________________________________________
>>> Dev mailing list
>>> Dev at ensembl.org
>>> http://lists.ensembl.org/mailman/listinfo/dev
>>> 
>> 
>> 
>> -----
>>
>>   Pablo Marin-Garcia
>> 
>> 
>> 
>
>


-----

   Pablo Marin-Garcia