Connecting to a database through RMySQL package in R 3.0.2

Question

I'm trying to connect to the ucsc genome server and then trying to select a database hg19 in it.

Method I already know(works perfectly fine):

hg19 <- dbConnect(MySQL(),user="genome",db="hg19",host="genome-mysql.cse.ucsc.edu")

Alternative Method I wish to know:

ucscDb <- dbConnect(MySQL(),user = "genome",host = "genome-mysql.cse.ucsc.edu")

Now, is there any way to use query such as use hg19; to select the database hg19 through the connection handle ucscDb?

The difference b/w the two methods is the use of db argument within the dbConnect function.

In the former, I specify the db in the arguments themselves. In the latter, I just connect to the ucsc server and then try to work on hg19 database through queries may be using dbGetQuery function.

Answer 1

The dbConnect command requires a db parameter. It sets the default schema for the connection However, there is no rule that in your dbSendQuery command you have to utilize the default schema. For example, if you connect with

hg19 <- dbConnect(MySQL(),user="genome",db="hg19",host="genome-mysql.cse.ucsc.edu")

but maybe want to query against a database called "hg18". You could simple run the following:

query = dbSendQuery(hg19, "SELECT * FROM hg18.sometable);
hg18Data = fetch(query, n=-1);